<< Click to Display Table of Contents >> Number format |
On the ribbon tab Home | group Number you can change the number format of cells.
The number format determines how numbers are represented in these cells. For example, if you enter 12.3456 in a cell and then apply one of the "Currency" number formats to it, it will be displayed with the chosen currency symbol and rounded to two decimal places (for example, as $12.35).
Note: When you apply a number format that has a fixed number of decimal places, the number will only be displayed as a rounded value, but still keeps all of its decimal places. To actually round numbers, use arithmetic functions like the ROUND function.
To change number formats of cells, you can either simply use directly the buttons of the command group Number or open the dialog box of the command group Number for additional options.
Applying common number formats directly: Via the buttons of the command group "Number"
Common number formats can be used via the buttons of the ribbon tab Home | group Number.
Here, you have the following options:
▪In the upper expandable list you will find different categories of number formats such as Default, Currency, Accounting etc. You can apply these directly with a mouse click. For detailed descriptions of the individual formats, see List of all number formats.
If you would like to change additional settings, select More in this list to open the dialog box with additional options (see below). |
▪With the icon you can display a number directly as a percentage (multiplied by 100). Click on this icon again to return to the default value.
▪With the icon you can quickly convert a number into a currency value, e.g. Dollar. Click again on the icon for the default value.
▪With the two icons and you can easily increase or decrease the number of decimal places (the numbers will only be displayed rounded, but they actually keep all of their decimal places).
Additional options: Via the dialog box of the command group "Number"
For advanced options (e.g. custom number formats) use the dialog box. To do this, click on the group arrow in the bottom right corner of the ribbon tab Home | group Number.
Alternatively: Right click to open the context menu and select the entry Cell.
In the following dialog box, switch to the Number format tab.
The following settings are available in the dialog box:
▪Category
Here you can select the desired type of number format. For more information on the individual formats, see List of all number formats. |
▪Format
Some number formats also have several subtypes to choose from. Select the desired subtype in this list. |
▪Decimal places
Some formats allow you to specify the number of decimal places to be displayed. |
Note: Numbers will only be displayed rounded, but actually are not rounded. However, it still keeps all of its decimal places. |
▪Thousands separator
Some formats allow you to specify if numbers should be displayed with thousands separators (e.g., 5,000,000) or without (e.g., 5000000). |
▪Negative numbers in red
Some formats allow you to specify if negative numbers should be displayed in red color. |
▪Suppress minus sign
Some formats allow you to specify if the minus sign of negative numbers should be suppressed. |
▪Don't show zero
Some formats allow you to specify if the content of cells containing the number zero should be suppressed. |
When you confirm with OK, the numbers in the selected cells will be displayed in the chosen formatting.