Excel number formats FAQ

The previous FAQ instalment considered font formats including the use of Excel Styles and Themes. This time we will consider issues around the formatting of numbers, explains Simon Hurst.

How do I display negative numbers in brackets?

There are two main ways to switch between using a minus sign and a set of brackets to indicate a negative number. The first involves a system setting outside of Excel. Windows Control Panel includes a set of Regional and Language options. The exact sequence varies according to the version of Windows, but there is a ‘Customize’ screen that includes tabs for Numbers, Currency, Time and Date. It seems that it is the Currency tab that controls the options available within Excel. There is a ‘Negative Currency Format’ dropdown in the Currency tab. If you choose the first of the bracketed options, this should ensure that Excel includes options for bracketed negatives in the Format, Cells, Number tab: Number and Currency categories:

It is also possible to create a format for bracketed negatives within Excel. Regardless of the Regional settings described above, you can create a 'Custom' number format that controls how positive and negative values are displayed:

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

This Custom number format uses two of the available four sections of a custom number format. Section 1 defines how a positive number is displayed and section 2 controls the negative number display. The two sections are separated by a semi-colon. Let’s look at the positive section first:

#,##0_)

The #s are used as placeholders so that we can get up to 4 characters to show we want to use a comma as a thousands separator. The 0 is used so that values less than 1 will be displayed as 0.x rather than just .x. The last part of the positive format is _). The underscore tells Excel to leave a space, equal in width to the following character. This leaves a space at the end of each positive number to ‘balance’ the closing bracket at the end of any negative figure. The format of the negative value is comparatively straightforward, with the only added element being the use of brackets and the colour ‘Red’ in square brackets at the beginning of the format.

What else can I do with a custom format?

So far, we have looked at two of the four sections of the custom number format. The third section defines how a zero value is displayed:

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

Here we have added a third section which is simply a dash and a question mark. The dash will be displayed in place of a zero and the ? adds a standard space so that the dash isn’t right at the right-hand edge of the cell.

The fourth section defines how text entered in the cell should be displayed. If there is no fourth section a text entry would be displayed as entered. An empty fourth section suppresses the display of the text. If text is entered in the fourth section it will replace the actual text in the cell. To add text to the actual text entered, you can combine a text entry in section 4 with the use of the @ code which will cause text o be displayed as entered:

#,##0_);[Red](#,##0);-?;"Text entry: "@

If the word ‘Sales’ is entered into a cell with the above format for the fourth section, the cell will display:

Text entry: Sales

Why does my custom format display a 0 even when I’ve entered a dash for section 3?

If your custom format is set to display to round pounds and the value in your cell is not zero but is greater than -0.5 and less than +0.5, the negative or positive section will be used rather than the third, zero, section. So, the display will show the value as 0 not -. You can avoid this by using the ability of a custom number format to incorporate conditions:

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

Here we have entered conditions for the positive and negative formats. The conditions are entered in sets of square brackets and evaluated from left to right. So now, any values greater than 0.49 will be formatted using the first custom format section and any numbers less than -0.49 will use the second format. Values outside both conditions will be formatted using the third section.

How can I format numbers to display round thousands or millions?

Display to thousands:

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

Or maybe:

0,k_);[Red](0,k);-?

Display to millions

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

Can I display numbers as fractions?

If you enter a number as a fraction, the fraction format will be applied automatically. For example 1 7/8 (the space between the number and the fraction is obviously vital otherwise you’ll end up with 17/8. You also need to be careful with fractions that could be confused with dates. 7/8 will become the 7th August. You can use a leading zero and space to ensure the entry is treated as a fraction, not as a date:

0 7/8

If you have entered a number normally, you can convert the format to a fraction by using the Format Cells dialog, Fraction category:

How do I make it easier to apply my custom number formats?

The previous instalment in this FAQ series looked at the use of Excel styles. The number format options are part of each style. In fact, a set of three styles deals specifically with the following number types:

• Comma
• Currency
• Percent

These built-in styles are linked to three toolbar or ribbon options:

If you change the number format of one or more of these styles to match the custom format that you want to use, then the existing buttons will apply the style and thereby your custom format. As covered in the previous FAQ, the Merge Styles option will allow you to copy styles from another open workbook. Alternatively, you could add your custom formats, or modify the number format styles, in Excel’s default workbook template: Book.xltx (2003: Book.xlt). This template needs to be located in Excel’s XLSTART folder and it will then be used as the default new workbook.

Are there any useful keyboards shortcuts for working with number formats?

Control+Shift+4: Currency format with 2 decimal places

Control+Shift+1: Number format with 2 decimal places

Control+Shift+5: Apply percent style

How do I find out more about number formats?

For further information

• ExcelZone Compendium archive – numbers and dates
• ExcelZone Compendium: Formatting tips (Excel 2007 update)