Presenting Numbers

Presenting Numbers

Numbers are the most important part of most spreadsheets and it is vital to get the formatting of numbers right.

When presenting a set of numbers the priority is making sure that they are clear. To do this there are a number of formats you can use to ensure that data is clear, consistent and that the user knows what figures are representing. Any ambiguity has the potential to mislead users and result in significant errors.

Compare the three columns of this PivotTable:

Where numbers are at a high level (e.g. thousands), including separators and not showing lower denominations makes it much easier to compare the numbers in the column. If you do need to include lower denominations, it’s important to use a fixed number of decimal places so each ‘unit’ of value is shown in the same place throughout the column.

Number formats

There are two different sets of number formats in Excel. There is a list of built-in number formats that includes formats for financial values, dates, fractions and scientific notation for example, and there is a Custom Formats option that allows you to specify the format you want to use in exact detail by using special number format ‘codes’.

Built in formats

To apply a number format, you need to first select the cell or cells to which to apply the format. In the case of a PivotTable, right-clicking on any of the numbers in the values area displays a Number Format option in the menu. This will format all the numbers for that PivotTable field. If you want to format a cell or selection of cells then go to the Home ribbon tab, Number group. This group includes tools to Increase and Decrease Decimal places directly as well as three buttons to apply Accounting Number Format, or Percentage Style or Comma Style to numbers.

The Number group of the Home ribbon tab

The Number Format dropdown lists the built-in number format options and includes a More Number Formats option that opens the Format Cells dialog box open to the Number tab:

The Format Cells dialog, Number tab

Here we have chosen the built-in Currency option and chosen to show 2 Decimal places, the pounds currency Symbol and Negative numbers in brackets and red. Be careful in choosing only a colour to differentiate positive and negative numbers in case the eventual output format (black and white laser printer for example) doesn’t make the different colours clear.

The Currency and Accounting formats (unlike Number) can include currency symbols as part of the format. This can be useful, but if you are working with a whole column of figures all of the same currency, then including a currency symbol in front of each individual number is usually less clear than omitting the currency symbol from the number format and, instead, including it in the column heading as in our PivotTable example.

Custom formats

For maximum clarity, or because you need your numbers formatted in a particular way, you might want to exercise more control over the number format. The Custom option allows you to do this.

From the Format Cells dialog box, Number tab, clicking on Custom will display a list of custom format codes that you can use as the basis for creating your own code:

To explain the custom format codes, we will look at the codes for a typical format for showing financial figures: round pounds with thousands separators, negatives in red and with brackets and zeros as dashes.

This is the format we will use and we will go on to explain it in detail:

#,##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 if the number is less than 1.00. Finally, the comma makes Excel use the comma as the thousands separator.

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

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.

Alan Gurney is AAT Comment’s Excel tips writer.