Partner The Knowledge Base
Share this content

EZ guide to number formatting

9th Aug 2016
Partner The Knowledge Base
Share this content
analysing graphs
istock_Ridofranz

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  

Objective

Improve the clarity of Excel reports by creating appropriate number formats and applying them with a single mouse click.

Overview

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.

Steps shortcut

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:

Number formatting

Number formatting

2. Find the Comma Style option in the Number Format section, right-click on it and choose Modify:

Number cruncher

3. Click the Format button and choose Custom from the Number tab, Category list. Delete any existing content in the Type: text box:

number formatting

4. Type, or Copy and Paste, the following format code:

#,##0_);[Red](#,##0);-?

5. You can now apply your number format by clicking the Comma Style button in the Number group of the Home Ribbon tab:

number formatting

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:

Number formatting

4. Choose Trusted Locations. Find and select the User StartUp folder. Click the Modify button and copy the location path:

Number formatting

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:

Number formatting

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:

Number formatting

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

#,##0_);[Red](#,##0);-?

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:

Format Value Result
#,##0.00 .98 0.98
#,###.00 .98 .98

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.

 

Replies (13)

Please login or register to join the discussion.

avatar
By chatman
14th Aug 2016 10:21

Hi Simon. Thanks for the article. Can you help me with this bit?

"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."

What is the difference between not using the section and leaving it blank?

Thanks (0)
Replying to chatman:
Simon Hurst
By Simon Hurst
14th Aug 2016 13:46

If the section is not used at all (i.e. no third semi-colon) text will be displayed as it has been entered. If the section is used by adding the third semi-colon but with nothing following, text entered will not be displayed.

Thanks (1)
Replying to shurst:
avatar
By chatman
14th Aug 2016 14:47

Thanks Simon.

Thanks (0)
avatar
By sacox1960
14th Aug 2016 11:04

What do you do if there is a file called Book .xltx when you check (point 5). I seemed to overwrite it but the old default formatting still occurs when I open a new workshhet.

Thanks (0)
Replying to sacox1960:
Simon Hurst
By Simon Hurst
14th Aug 2016 13:50

Saving a new Book.xltx over an existing one should replace it - you could check the date to make sure this has happened. If it has been overwritted but your changes are not present in a new default document (using Control+n or the New buttton, not File, New, Blank workbook) it would be worth seeing if there is a different book.xltx in any of the other Trusted Locations startup folders.

Thanks (0)
Replying to shurst:
Simon Hurst
By Simon Hurst
14th Aug 2016 13:52

or overwritten even, just slipped into Bluebottle speak for a moment there...

Thanks (0)
avatar
By The Accountant
14th Aug 2016 11:29

I tried using that formula, but it rounded 0.98 up to 1 unless it was a negative figure. Is there something I am missing. I need it to display to 2 decimal places.

Thanks (0)
Scalloway Castle
By scalloway
14th Aug 2016 12:46

Your format needs to be set up like this to format both positive and negative numbers.

#,##0.00;-#,##0.00

Thanks (1)
Replying to scalloway:
avatar
By The Accountant
14th Aug 2016 13:31

Thank you! Tried that mod and it works fine.

Thanks (0)
avatar
By AndrewV12
19th Nov 2016 15:20

A good article, most of us only use only 40% of Excel's programmes.

Thanks (0)
avatar
By Dinger
05th Jan 2017 10:06

Hi Simon
liked the article and will use eventually, but, I have a pivot table set up to have 0 to format as - but a calculation brings out a sum of 5.36843418860808E-14. To get rid of the 0 I enter the minus of the above, but then get a minus sign before the number above and the pivot table still shows the 0.
The rest of the pivot table seems to be ok. I have checked the data source and none of the calculations round to this. How can this be sorted?

Thanks (0)
JP
By 12jpotts
27th Jan 2017 13:20

Very useful, great time saver thanks Simon

Thanks (0)
avatar
By Akrigg
06th Sep 2017 12:30

This is great - thanks

Thanks (0)