In this step-by-step Excel Zone guide Simon Hurst explains how you can improve Excel reports through using number formatting.
Time to implement
Up to 10 minutes
Improve the clarity of Excel reports by creating appropriate number formats and applying them with a single mouse click.
Although Excel has many built-in number formats, implementing a perfect number format may require the use of the Excel Custom Number format feature. Once your format has been set up, you can use an Excel style to ensure that it can be applied in all new workbooks using an existing ribbon command. You can also use a simple macro and a custom Quick Access Toolbar command to make the number format available to all existing workbooks.
If you just want to implement the solution as quickly as possible, these are the required steps. A full explanation of the different elements of the custom number format code is included later.
Set up and apply then number format in the current workbook
These steps assume that you aren't already using the existing Comma Style to apply a different number format.
1. From the Home Ribbon tab, Styles group click on the 'More' button at the bottom right corner of the Styles gallery:
2. Find the Comma Style option in the Number Format section, right-click on it and choose Modify:
3. Click the Format button and choose Custom from the Number tab, Category list. Delete any existing content in the Type: text box:
4. Type, or Copy and Paste, the following format code:
5. You can now apply your number format by clicking the Comma Style button in the Number group of the Home Ribbon tab:
Create a default template including the Comma Style custom number format.
These steps assume that you aren't already using a custom Excel default workbook.
1. Create a new blank workbook (keyboard shortcut: Control+n).
2. Follow the above steps to allocate your custom number format to the Comma Style.
3. Go to File, Options, Trust Centre. Click on the Trust Centre Settings button:
4. Choose Trusted Locations. Find and select the User StartUp folder. Click the Modify button and copy the location path:
5. Cancel back out of Excel options. Use the F12 shortcut to open the Save As dialog. Change the Save as type: to Excel Template. Paste your StartUp location into the path text box and press Enter. Check to ensure there is not already a file called Book.xltx in the StartUp folder. Set the File name: to Book and click the Save button:
6. Close your new Workbook. Use Control+n to create a new blank workbook. Enter some figures and test to see if the Home Ribbon tab, Number group, Comma Style button now applies your chosen custom number format.
Create a macro to change the Comma Style to your custom number format in existing workbooks
You could just follow the steps to change the number format of the custom style in any workbooks that pre-date your new default template, but you could record a simple macro to speed the process up.
1. From the Macros button dropdown in the Macros group of the View Ribbon tab choose Record Macro… Enter a Macro name: of CustomCommaStyle (note that no spaces are allowed in a macro name) and choose Personal Macro Workbook from the Store macro in: drop down. Click Ok to record the macro.
2. From the Styles Gallery, More button, right-click on the Comma Style and choose Modify and then click on the Format button (as for the above steps). Delete the existing format code and reapply your custom format from the list. Click on OK, then OK again, then stop recording by choosing the Stop Recording command in the Macros dropdown of the View Ribbon Tab, or by clicking on the square Stop button in the Status bar:
3. You should now have a macro available to all workbooks that will change the Number Format of the Comma Style to your chosen custom format. It should only need to be run once in each workbook and can be run from the View Ribbon tab, Macros dropdown, View Macros command or by adding it as a custom button in the Quick Access Toolbar.
Custom number format code - explanation
The format is in three sections, separated by semi-colons. The sections control positive numbers, negative numbers and zeros respectively. There is a fourth section for text, but this is not generally required unless you need to include specific text for all cells with this format.
Section 1 - positive numbers
#,##0 - the hashes (#) will only display ‘significant’ digits, this means leading zeros will be suppressed. The 0 means display a zero in this position even if the number is less than 1.00. An example should make this clearer:
Finally the comma makes Excel uses the comma as the thousands separator.
Note also the use of the underscore. This causes Excel to leave a space equal to the width of the character that follows the underscore. So in this example we have used _) to include an amount of space at the end of a positive number equal to the width of a closing bracket. This makes sure that negative and positive figures in the same column line up properly.
Section 2 - negative numbers
[Red](#,##0) - In the second section there are a couple of additional elements:
The name of one of the eight main colours in square brackets causes the numbers to be displayed in that colour. In our case negative numbers will be red. It is perfectly possible to apply colours to positive numbers as well, just precede the first section with the colour in square brackets, e.g. [blue].
Brackets are used to ensure that negative figures are shown in brackets.
Section 3 - zero values
-? - We have entered a dash followed by a question mark. The question mark inserts a space to move the dash in one character from the right-hand edge of the cell.
Section 4 - text
Not used - if you do not use this section, text will be displayed as it is entered. If the fourth section is blank, any text entered will not be displayed. The use of the 'at' character (@) will display any text as it is entered. You can also combine the text entered with some fixed text:
#,##0_);(#,##0);;"Sales of "@
will display 'Sales of ' followed by the text entered into the cell, or if a number is entered, the number formatted as shown by the positive and negative sections.