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 otherwise specified, all numbers in Excel are rendered in the "General" format. This means that numbers are formatted exactly as you enter them: if you enter two decimals, two decimals are displayed; if you went to a single decimal, a single decimal is displayed; and so on.
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 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 one of the number formats on the left of the "Numbers" dialogue box, you are given a series of choices which enable you to refine the way that the format will work. Thus, if your numbers refer to an hourly rate, you would click the "Number" category in the left column and then specify two decimal places. The option to "Use Thousands Separator" will add the appropriate separator to demarcate thousands. The separator used will depend on your locality: for example, in the UK or USA, a comma will be used; in European countries, 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.
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.
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, 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. - 16928
Unless otherwise specified, all numbers in Excel are rendered in the "General" format. This means that numbers are formatted exactly as you enter them: if you enter two decimals, two decimals are displayed; if you went to a single decimal, a single decimal is displayed; and so on.
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 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 one of the number formats on the left of the "Numbers" dialogue box, you are given a series of choices which enable you to refine the way that the format will work. Thus, if your numbers refer to an hourly rate, you would click the "Number" category in the left column and then specify two decimal places. The option to "Use Thousands Separator" will add the appropriate separator to demarcate thousands. The separator used will depend on your locality: for example, in the UK or USA, a comma will be used; in European countries, 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.
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.
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, 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. - 16928
About the Author:
The author is a training consultant with TrainingCompany.Com, a UK IT training company offering Microsoft Excel training courses at their central London training centre.
0 comments:
Post a Comment