Custom number formats and how to use them
Format cells to display custom number formats, and learn how to use number format codes to make your own custom formats quickly.
How to make custom number formats
Displaying your data in custom formats is quick and easy when you know how!
Once you learn how to use custom formats, you will use them all the time to display numbers or text in cells exactly how you want. In this tip, we show you how to make the most of custom formats.
Here’s how custom number formats work, and how to write them quickly:
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.
For example
If a cell’s content is “12”, here are just a few of the many ways that this can be displayed.
Built-in number format examples:
- 12
- 12.0
- $12.00
- 1200%
- 1.2E+01
- 00012
Custom number format examples:
- +12
- 12 eggs
- INV-10012
- Increase of 12
- 12.00 DR
- Item 12.
Each of these ways of displaying “12” is a number format.
Custom number formats are highly flexible, and have an almost limitless number of use cases.
No matter how many decimal places you want to display, or what 12 represents, it is stored in 2 parts:
- The contents of the cell, “12”; and
- The display format for that cell.
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:
- Open the “Format Cells” dialog
(You can do this quickly by pressing Ctrl + 1). - Select the “Number” tab
This tab is usually selected by default. - Select the “Custom” category
This is the last item in the category list on the left. - 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:
- Select the built-in format
- Go to the custom format editor.
- 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:
- Format for positive numbers
- Format for negative numbers
- Format for zeroes
- 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.
Don’t have a login?
Get a login for free on our standard plan, and get a new tip each week.
All logins also get access to the bonus tip each week.
Get even more
Get even more with one of our reasonably priced paid plans, with access to features like:
- Quicksheet PDF summaries
- Example files
- VBA syntax
- Additional expert tips each week
- Advance publication
View our plans to see what else you are missing, or join for free on our Standard plan.