Number format

<< 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 group_arrow 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.

number_group_detailed

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 number_percent_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 number_currency_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 number_dec_left_icon and number_dec_right_icon 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 arrowgroup_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.

number_group_dialog

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.