When entering numbers into a spreadsheet, you sometimes need to ensure that the number format is consistent. For example, if the numbers in question represent prices, you may want to display a currency symbol or you may simply want to make sure that the number of decimals displayed is consistent..

Unless you specify otherwise, all numbers in Excel are rendered in the “General” format. This means that numbers are displayed exactly as you enter them: if you enter two decimals, two decimals are displayed; if you went to one decimal, one decimal is displayed; and so on.

When specifying the number format, the best idea is usually to select the whole column. To do this, click on the letter or letters representing the column. (Any text contained in the selection will not be affected by the number format you specify.)

Number formats are specified in the “Numbers” group of the Home Tab of Excel’s Ribbon. There are three important formats which apply to numbers: the first is simply called “Number”, the second “Currency” and the third “Accounting”. To gain access to the complete range of number formats, click on “More Number Formats” in the “Numbers” drop-down menu. Another way of opening the “Numbers” dialog box is to click on the launch button in the “Numbers” group of the Home Tab of the Excel Ribbon.

When you click on each of the number formats, you are presented with a series of choices which enable you to refine the way that the format will work. For example, if our numbers refer to an hourly rate, we would probably click the “Number” category in the left column and then specify two decimal places. The option labelled “Use Thousands Separator” will insert the appropriate separator to demarcate thousands. The separator which Excel uses will depend on your locality: for example, if you are in the UK or USA, a comma will be used; if you are in a European country, a dot will be used.

The last option in the “Number” category controls the display of negative numbers. The default setting is to display a minus sign in front of the number and leave the colour of the text unchanged. However, you can also choose not to display the minus sign and change the colour of negative numbers to red. Alternatively, you can change the colour of negative numbers to red and also display the minus sign.

Clicking the “Currency” category, reveals pretty much the same choices as “Number” with the addition of a currency symbol. You can specify which currency symbol is used or you can dispense with the currency symbol altogether.

The “Accounting” number format is almost identical to “Currency”. Here again, we can choose our currency symbol. However, there are no choices relating to negative numbers. This is because the convention in accountancy is to put negative numbers in brackets.

As an alternative to using the number dialog box, you can also click on one of the series of handy buttons which are used to apply each of the number formats with single click. There are also two buttons for decreasing and increasing the number of decimals displayed in the highlighted cells.

Finally, there may be times where, although you enter a number into a cell, you do not want Excel to treat it as a number. For example, if you have a column of data containing a client ID, although the ID may be numeric, you may not want Excel to treat it like a number or to modify it in any way. You will probably want it to stay exactly the way it was entered. Whenever this is the case, it is best to format the number as “Text”. The simplest way of doing this is to select the appropriate column and in the number dialog box choose “Text” as the format.

Share This