Custom number formats in Excel

Contents

Zeros in front
Decimals
Add text
Large numbers
Character repetition
Colours
Date and Time

Excel has many built-in number formats that you can use: Monetary, Fiscal, Date, Time, Percentage, etc. If you can’t find a suitable number format, create your own.

Note from the translator: This article is aimed primarily at the English-speaking user, although it is a translation. This must be taken into account when compiling digital codes for custom number formats. For example, in the English version of Excel, a dot is used to separate the integer and fractional parts, and in the version, a comma; a comma is used to separate groups of digits in the English version, and a space in the Russified version, etc. Don’t forget about it!

Zeros in front

For example, you have codes consisting of 5 digits. Instead of typing 00041, just enter 41, and let Excel fill in the missing zeros.

Enter value 41 into a cell A1.
Highlight a cell A1, right click on it and press Format Cells (cell format).
Выберите Custom (All formats).
Enter the following numeric format code: 00000.
Press OK.

Note: In the sample (Sample) Excel shows how a number will be formatted.

Result:

Note: Cell A1 still contains a number 41. We only changed the appearance of this value, not the number itself.

Decimals

You can control the number of decimal places. Enter 0 to display the nearest integer value. Enter 0.0to display one decimal place. For two decimal places use 0.00 etc.

Enter value 839.1274 into a cell A1.
Use the following numeric code for the format: 0.00.

Add text

In addition, right within the format, you can add text to the number. For example, “ft«.

Enter value 839.1274 into a cell A1.
Use the following numeric code for the number format: 0.0″ ft».

Note: We just changed the appearance of this number, not the value itself. You, as before, can use this number in your calculations.

Large numbers

You can manage large numbers. Write one comma for thousands and two commas for millions.

Enter values in cells A1, B1, C1 и D1: 1000000, 2500000, 81000000 and 700000.
Use the following numeric code for the format:
0.0,,» M» – for English localization;
0,0 » M» – for localization.

Note: We have entered 0.0 to display one decimal place and » M»to add the letter M (millions) with a space after the value itself.

Character repetition

Write an asterisk (*) followed by a character to fill the cell with that character.

Enter “Hi» to cell A1.
Use the following numeric code for the format: @ *-

Note: Symbol @ used to enter text.

Colours

You can manage positive numbers, negative numbers, zero values and text at the same time. In the number format code, each part is separated by a semicolon (;).

Enter the following values in the cells A1, B1, C1 и A2: 5000000, 0, Hi and -5.89.
Use the following numeric code for the format:
[Green]$#,##0_);[Red]$(#,##0);»zero»;[Blue]»Text: «@
[Green]$# ##0_);[Red]$(# ##0);”zero”;[Blue]”Text: “@

Note: Code #,## used to add a comma in large numbers. To add a space, type an underscore “_” followed by a character. The length of the space will be equal to the length of this character. In the example above, we added the parenthesis “)“. As a result, the rightmost zero in the cell A1 located exactly above the negative number in parentheses.

Use two parts separated by a semicolon to manage only positive and negative numbers. Three parts, separated by a semicolon, to control positive and negative numbers, as well as values equal to zero.

Date and Time

You can manage dates and times. Use one of the existing date or time formats as a starting point.

Enter value 41674 into a cell A1.
Highlight a cell A1, right click on it and press Format Cells (cell format).
Select format Data (Date) and in the list Type (Type) – long date format.

Note: In line sample (Sample) Excel will show you how the number will be formatted.

Click on Custom (All formats).
Slightly change the numeric format code. For example: mm/dd/yyyy, dddd (DD/MM/YYYY, DDDD).
Press OK.