Control the Defaults for Excel Number Formats

Have you ever wanted to change the default behavior of the built-in number formats on the Formatting toolbar in Excel? Take the Currency command for example; it is really an Accounting format masquerading as the Currency format.

The Accounting number format aligns the dollar signs to the left edge of the cell, whereas the Currency format places the dollar sign to the left of the number.

The Currency, Percent, and Comma Styles on the Formatting toolbar are tied to built-in Styles in Excel. It's just a matter of modifying the Style to change the format that is applied when you use the associated command.

You can accomplish this task in just a few easy steps. In this example we'll reassign the Currency command to its rightful format.

1. Create a new workbook.
2. On the Format menu select Style.
   
3. In the Style Name combo box select Currency.
   
4. Click the Modify command.
   
5. On the Number tab select Currency, set the decimal places to 2 if necessary, and click OK.
   
6. In the Style dialog box click Add, then Close.
   

Now try it out by applying the Currency format from the Formatting Toolbar to a number.

Keep in mind that the changes you made were only for the current workbook. Excel doesn't have a physical file you can open and modify but you can create a default workbook template named Book.XLT to replace the built-in defaults:

1. Press Ctrl + A to select the entire workbook.
   
2. On the Edit menu select Clear then All to clear all data and formats.
   
3. On the File menu select Save As.
   
4. Change Files of Type at the bottom to Template (*.XLT).
   
5. Name the new template Book.XLT
   
6. Change Save in to reflect your XLStart folder and click Save.
   

Note: Typically the location of the XLStart folder is: ..\Program Files\Microsoft Office\Office.

Or if you prefer, you can specify an Alternate Startup location for Book.XLT by selecting the Tools menu, then Options, and then the General tab.

Note of caution! Your Startup folder for Excel was designed to open all files located in the folder. If you create an Alternate Startup location you may want to create a new folder rather than use a folder that already contains Excel workbooks.