The author is a training consultant with Macresource Computer Solutions, an independent computer training company offering Microsoft Excel 2007 traini

by Lynne Kramer



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 the user specifies otherwise, all numbers in Excel are rendered in the "General" format. What this means is that numbers are displayed exactly as one enters them: if you enter two decimals, two decimals are displayed; if you went to one decimal, one decimal is displayed; and so forth.

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

Number formats are found in the "Numbers" section of the Home Tab of the Excel Ribbon. There are three principal formats related to numbers: the first is "Number", the second "Currency" and the third "Accounting". To access the full range of number formats, choose "More Number Formats" from the "Numbers" drop-down menu. Another method of accessing the same dialog box is to click on the launch button in the "Numbers" group of the Home Tab.

Clicking on any of the number formats on the left, displays a series of choices which enable you to refine the way that the format will work. For example, if you have numbers referring to an hourly rate, you might click the "Number" category in the left column and then specify two decimal places. The option which reads "Use Thousands Separator" is used to insert the appropriate separator to demarcate thousands. The separator used will depend on your locality: for example, in most European countries a dot will be used; in the UK or USA, a comma 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.

When we click the "Currency" category, we have pretty much the same choices with the addition of the currency symbol. We can specify which currency symbol is used or we can dispense with the symbol altogether.

The "Accounting" category is pretty much the same as "Currency". Once again, you can choose a particular currency symbol. However, you will notice that you do not have any choices relating to negative numbers. The convention in accountancy circles is to always place negative numbers in brackets.

As well as using the number dialog box, you’ll notice a series of handy buttons which can apply each of the number formats with one click. There are also a couple of buttons for increasing and decreasing the number of decimals displayed in the selected cells.

Finally, there will be times where you enter a number into a cell but do not want Excel to regard it as a number. For example, if you have a column of data with an ID of some sort, although the ID may be numeric, you may not want Excel to see it as a number or to change it in any way. You will probably want the ID to simply stay exactly as it was entered. In this scenario, it’s best to format the number as "Text". The easiest way of doing this is to highlight the appropriate column and in the number dialog box select the "Text" category.



About the Author:
The writer of this article is a developer and trainer with TrainingCompany.Com, an independent computer training company offering Microsoft Excel Classes at their central London training centre.