When entering numbers into a spreadsheet, one often needs to ensure that the number format is consistent. For example, if the numbers represent prices, you may want to display the appropriate currency symbol or you may simply want to ensure that the number of decimals displayed is always the same.
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.
When setting the number format, the best idea is usually to highlight the whole column. To do this, click on the letter or letters at the top of the column. (Any text contained in the highlighted column will not be affected by the number format you specify.)
Number formats are displayed in the "Numbers" section of the Home Tab of the Ribbon. There are three main formats related to numbers: the first is called "Number", the second "Currency" and the third "Accounting". To gain access to the full range of number formats, choose "More Number Formats" from the drop-down menu. Another way of accessing the same dialog box is to click on the launch button in the "Numbers" section of the Home Tab.
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 final option in the "Number" category controls the display of negative numbers. The default is to display a minus sign in front of the number and leave the colour of the number unchanged. However, you can also dispense with the minus sign and change the colour of negative numbers to red; or you can both change the colour of negative numbers to red and display the minus sign.
When you click on the "Currency" category, you have pretty much the same choices with the addition of a currency symbol. One can specify which currency symbol is used or one 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.
In addition to using the number dialog box, you'll notice that there are a series of handy buttons which can apply each of the appropriate formats with one click. There are also two buttons for increasing and decreasing the number of decimals displayed in the selected numbers.
Finally, there may be times where you enter a number into a cell but do not want Excel to treat it as a number. For example, if you have a column of data representing an ID, although the ID may be numeric, you may not want Excel to regard it as a number or to modify it in any way. You will probably want it to simply stay exactly as it was entered. Whenever that's the case, it's best to format the number as "Text". The simplest way of doing this is to highlight the appropriate column and in the number dialog box choose "Text" as the category. - 16738
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.
When setting the number format, the best idea is usually to highlight the whole column. To do this, click on the letter or letters at the top of the column. (Any text contained in the highlighted column will not be affected by the number format you specify.)
Number formats are displayed in the "Numbers" section of the Home Tab of the Ribbon. There are three main formats related to numbers: the first is called "Number", the second "Currency" and the third "Accounting". To gain access to the full range of number formats, choose "More Number Formats" from the drop-down menu. Another way of accessing the same dialog box is to click on the launch button in the "Numbers" section of the Home Tab.
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 final option in the "Number" category controls the display of negative numbers. The default is to display a minus sign in front of the number and leave the colour of the number unchanged. However, you can also dispense with the minus sign and change the colour of negative numbers to red; or you can both change the colour of negative numbers to red and display the minus sign.
When you click on the "Currency" category, you have pretty much the same choices with the addition of a currency symbol. One can specify which currency symbol is used or one 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.
In addition to using the number dialog box, you'll notice that there are a series of handy buttons which can apply each of the appropriate formats with one click. There are also two buttons for increasing and decreasing the number of decimals displayed in the selected numbers.
Finally, there may be times where you enter a number into a cell but do not want Excel to treat it as a number. For example, if you have a column of data representing an ID, although the ID may be numeric, you may not want Excel to regard it as a number or to modify it in any way. You will probably want it to simply stay exactly as it was entered. Whenever that's the case, it's best to format the number as "Text". The simplest way of doing this is to highlight the appropriate column and in the number dialog box choose "Text" as the category. - 16738
About the Author:
Author is a developer and trainer with TrainingCompany.Com, a UK IT training company offering Microsoft Excel training courses in London and throughout the UK.
