Custom number formats and how to use them

Quick Tip

About custom number formats

This tip is about how to create and edit your own display formats, without impacting the contents of the cell.

Although display formats are called “number formats”, they can apply to cells containing numbers, text or both.

First, let’s clarify the impact of number formats (including custom formats).

Number formats affect cell display but not contents

A cell’s content and how that content is displayed are two different things.

A cell’s content can be either a value or a formula (or, the cell may be empty).

You can think of a cell’s content as being what is “stored” in the cell.

How that cell’s content is displayed determines how it is shown on the screen (or in prints or exports), but does not impact the content itself.

You can think of a cell’s display as how its content looks when viewed by a user.

Impact of custom formats on cell calculations

Changing or customizing how a cell’s contents are displayed does not impact how formulas using that cell are calculated.

In some advanced use cases, you might want a calculation to be influenced by how its inputs are displayed. This can be done deliberately, but in all other cases the above statement holds true.

Choosing how cell contents are displayed without impacting the actual contents of the cell can be a huge advantage in many different cases, such as:

• If you want cells to display a combination of number and text, but calculate using only the number (e.g. “6 KG” multiplied by “5 bags” = “30 KG”)
• If you want cell values to show rounded figures (such as in thousands or millions), but keep all calculations based on the original underlying numbers (e.g. in financial results summaries)

Show the custom number format type editor

To create or edit custom number formats, you first need to show the custom number format editor. This is called the “Type” box.

How to access the custom number format type editor:

1. Open the “Format Cells” dialog
   (You can do this quickly by pressing Ctrl + 1).
2. Select the “Number” tab
   This tab is usually selected by default.
3. Select the “Custom” category
   This is the last item in the category list on the left.
4. Click in the “Type” box
   Here you can create or edit a custom format.

Creating a new custom number format type

To create a new custom format from scratch, you can go directly to the custom format type editor.

Create a custom format based on a built-in format

To use an existing built-in format as a template for a new custom number format:

1. Select the built-in format
2. Go to the custom format editor.
3. The code for the existing format will be displayed, ready to edit.

When you edit a built-in format, your edited version becomes a new custom format; the original built-in format is not modified.

How are custom number formats structured?

Custom number formats are structured in up to four sections, with a semicolon (;) between each section.

The four sections are:

1. Format for positive numbers
2. Format for negative numbers
3. Format for zeroes
4. Format for text

The “text” format here actually means any “non-number” content. Often this is text, but it can also include any input that is not recognized as a number (such as input that contains both numerals and letters).

How custom number format sections work

You do not have to use every section.

If a custom format has only 1 section:

• it is applied to numeric values (positive, negative or zero); and
• text is ignored.

If a custom format has 2 sections:

• the 1st section is applied to numeric values that are positive or zero;
• the 2nd section is applied to numeric values that are negative; and
• text is ignored.

If a custom format has 3 sections:

• the 1st section is applied to numeric values that are positive;
• the 2nd section is applied to numeric values that are negative;
• the 3rd section is applied to numeric values that are zero; and
• text is ignored.

If a custom format has 4 sections:

• the 1st section is applied to numeric values that are positive;
• the 2nd section is applied to numeric values that are negative;
• the 3rd section is applied to numeric values that are zero; and
• the 4th section is applied to text.

Number format codes

Use these common number format codes to build or edit your custom format:

Custom format codes for numbers

Code	Display impact	Example content	Example format	Example display
#	Show content, displaying only significant digits	12 12.04 12.048	#.0# #.0# #.0#	12.0 12.04 12.05
0	Round to or force a set number of significant digits	12 12 12.048 =PI()	0000 #.00 #.00 #.000	0012 12.00 12.05 3.142
,	Show thousands separator	12 1200 12000 1200000	#,##0 #,##0 #,##0 #,##0	12 1,200 12,000 1,200,000
%	Convert to percentage	0.2 1 12 0.23858	#% #% #% #0.0%	20% 100% 1200% 23.9%

Custom format codes for dates

Code	Display impact	Example content	Example display
d dd ddd dddd	Day of month Day of month (2 digits) Day name (abbr.) Day name (full)	1 1 1 1	1 01 Jan January
m mm mmm mmmm	Month number Month number (2 digits) Month name (abbr.) Month name (full)	8 8 8 8	8 08 Aug August
yy yyyy	Year (2 digits) Year (4 digits)	15 15	15 2015

Custom format codes for time

Code	Display impact	Example content *	Example format	Example display
h hh	Time (hour) Time (hour – 2 digits)	1:30:00 am 1:30:00 am	h:m hh:mm	1:30 01:30
m mm	Time (minute) Time (minute – 2 digits)	1:08:00 am 1:08:00 am 1:08:00 am	h:m h:mm hh:mm	1:8 1:08 01:08
ss ss.0 ss.00	Time (second) Time (secs/10ths) Time (secs/100ths)	1:20:05 am 1:20:05.862 am 1:20:05.862 am	h:mmss h:mmss.0 h:mmss.00	1:20:05 1:20:05.9 1:20:05.86
:	Time separator	1:30:00 am	h:mm	1:30
AM/PM	AM/PM suffix (omit to designate 24 hour time)	1:30:00 am	h:mm AM/PM	1:30 am
[]	Elapsed time designation	1:30:00 am	[mm]	90

* Example content shown in this column is as it would appear in the formula bar in these examples. This is different to how date and time values are actually stored in cells, which is in the form of a decimal number; for more details see the tip “Insert current date or time”.

For quick access to common number formats using keyboard shortcuts, see the tip “Quick access to cell formatting”.

Check out the Bonus tip below for how to control alignment of numbers and decimal places in custom formats, and how to use symbols and text as part of custom number formats.