Often, when you enter numbers into a spreadsheet, you need to ensure that the number format is consistent. For example, if these numbers are prices, you may want to display the currency symbol or you may simply want to specify that the number of decimals displayed is always two.
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 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 final option in the "Number" category lets you control the display of negative numbers. The default is to preced the number with a minus sign and to leave the colour of the text unchanged. However, you can also suppress the minus sign and change the colour of negative numbers to red; or, if you prefer, you can change the colour of negative numbers to red as well as displaying 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" 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 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.
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 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 final option in the "Number" category lets you control the display of negative numbers. The default is to preced the number with a minus sign and to leave the colour of the text unchanged. However, you can also suppress the minus sign and change the colour of negative numbers to red; or, if you prefer, you can change the colour of negative numbers to red as well as displaying 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" 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 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.
About the Author:
Author is a developer and trainer with TrainingCompany.Com, an independent computer training company offering Microsoft Excel Classes at their central London training centre.
0 commentaires:
Enregistrer un commentaire