FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
March 31, 2016  
Greetings from The Excel Addict
Hi fellow Excel Addict,

Francis Hayes (The Excel Addict) www.TheExcelAddict.comSpring is trying really hard to come to eastern Newfoundland but when that north wind starts blowing, it feels more like winter. Our forecast calls for warmer temps over the next few days but, as I am writing this and looking through my window, I see snow falling. Arrgh!!

In case you missed Tuesday's 'Excel in Seconds' newsletter, I showed you how to 'Take a Shortcut Out of the Default Templates Folder'. You can read all about it here.


Today's Tip
Most of us who have been using Excel for some time now have created our own custom number formats. And we probably have also discovered that custom number formats get saved only in the workbooks in which they are created. That's a frustration I often hear from my readers. So, today I'm going to show you how to make your custom number formats available for all of your new workbooks. Not only that, I will also show you a little trick to make your favourite formats more easily accessible.

I want to thank everyone who shares my tips. I really do appreciate you sharing your knowledge of Excel as you learn more. So if you know anyone else that my newsletter could help, please feel free to share.


Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)



If you missed my last newsletter, you can click here to view it online.


 
TheExcelAddict.com Quote of the Day

"Spring is nature's way of saying, 'Let's party!'"
-- Robin Williams --

 
If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Make Your Custom Number Formats Available to All Workbooks

If you frequently use custom number formats, you have probably been frustrated that each time you need to use them in another workbook, you have to recreate them from scratch.

Here's a way to make your custom number formats available in all of your new workbooks.

As an example, I frequently use the CTRL+3 keyboard shortcut to quickly format dates to a format (e.g. 5-Dec-15) that makes it obvious which number is the month and which is the day. However when using dates from 2012 and prior (e.g. 12-Dec-11), it may not always be obvious which number is the year. So I prefer to use a dd-mmm-yyyy format (e.g. 12-Dec-2011).

To format cells with a custom number format that you previously created, typically you need to go to the Custom category on the Number tab in the Format Cells dialog. That requires some jumping around with your mouse.

Apply Custom Number Format Dialog in Microsoft Excel 2007 2010 2013 2016 365
To make your some of your number formats easier to access, I recommend creating them as Cell Styles. Then you'll have easier access to them right in the Cell Styles gallery.

Apply Custom Number Format Cell Style in Microsoft Excel 2007 2010 2013 2016 365
Custom number formats are available only in the workbooks they are created in, but you'll want to have your them available for all new workbooks.

Here's how to do that...

Step 1: Create a Custom Number Format Cell Style**

1) Open a blank workbook. Be careful not to make any other changes to this blank workbook that you wouldn't want showing up in all of your new workbooks. Make sure cell A1 is selected;

2) On the Home tab, click the Cell Styles dropdown arrow;

Click Cell Styles Menu in Microsoft Excel 2007 2010 2013 2016 365

3) Click New Cell Style...;
Add New Cell Style in Microsoft Excel 2007 2010 2013 2016 365
4) In the Style dialog, remove the checkmarks from all except Number, then click the Format... button;

5) In the Format Cells dialog, select Custom from the Categories list and type your desired number format in the Type field. You may find it helpful to select an existing format that is similar to what you want and then modify it in the Type field;

Define Cell Style Number Format in Microsoft Excel 2007 2010 2013 2016 365
6) Click OK to close the Format Cells dialog and OK to close the Style dialog. Your custom number format is now available in the Cell Styles gallery.

Apply Custom Number Format Cell Style in Microsoft Excel 2007 2010 2013 2016 365

Step 2: Save the New Cell Style to Your Default Workbook Template

Again, be careful not to make any other changes to this workbook.


1)
From the File tab, click Save As;

2) In the 'Save as Type' dropdown at the bottom of the dialog box, select Excel Template (*.xltx). You will notice that the active folder changes to the Templates folder. However, we want to save this default workbook template to our XLSTART folder;

3) Next, type or 'copy and paste' this path %APPDATA%\Microsoft\Excel\XLSTART into the File Name field and press Enter. This takes you to the XLSTART folder;

4) Change the suggested file name to Book.xltx and click OK;

5) Again, click File, Save As (you should still be in the XLSTART folder);

6) Change the name to Sheet.xltx and click OK.

Save To Xlstart Folder in Microsoft Excel 2007 2010 2013 2016 365
BOOK.XLTX is the template that will open each time you create a new workbook. SHEET.XLTX is the template that will be used when you insert a new worksheet into a workbook. Both of these templates now include your custom number format as a Cell Style and will be available to all new worksheets and workbooks.

Now just select the cells where you want to apply the format and click the desired format in the Cell Styles gallery.

Apply Custom Cell Style in Microsoft Excel 2007 2010 2013 2016 365

** Please understand that I'm not saying that you should create ALL of your custom number formats as Cell Styles. I recommend doing this for some formats that you use frequently because it makes them a little easier to access from the Cell Styles gallery.

Create Custom Number Format (not Cell Style)
To use the usual process to create custom number formats that you can use in all new workbooks, substitute the Step 1: Create a Custom Number Format Cell Style section (above) with the following steps.

Create Custom Number Format Dialog in Microsoft Excel 2007 2010 2013 2016 3651) Open a blank workbook. Be careful not to make any other changes to this blank workbook that you wouldn't want showing up in all of your new workbooks. Make sure cell A1 is selected;

2) Click the Number dropdown on the Home tab;

3) Click More Number Formats...,

4) Select Custom in the Categories list;

5) Create your format in the Type field;

6) Click OK.

Copying Custom Number Formats to Existing Workbooks
As I said previously, custom number formats are available only in workbooks which they are created.
However, to make a custom format or custom Cell Style available in an existing workbook you can simply copy a cell containing the format to the existing workbook.

1) Select a cell containg the custom number format or Cell Style and Copy it;

2) Go to a workbook that dosn't have that format or Cell Style, right click any cell and click Paste (or Paste, Paste Special..., Formats). The number format or Cell Style will then be available in that workbook. When you copy a Custom Cell Style it will be available in the Cell Styles Gallery
;

3) You will need to Save changes for the format to stay with that workbook.




Thanks for supporting this newsletter and website

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630