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.

Quick Tip

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:

  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.
Custom number format dialog
Custom number format dialog

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.

Custom format sections
Custom format sections

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

For example

This example demonstrates each of the four sections and when it is used.

Custom format sections example
Custom format sections example

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

CodeDisplay impactExample contentExample formatExample display
#Show content, displaying only significant digits12
12.04
12.048
#.0#
#.0#
#.0#
12.0
12.04
12.05
0Round to or force a set number of significant digits12
12
12.048
=PI()
0000
#.00
#.00
#.000
0012
12.00
12.05
3.142
,Show thousands separator12
1200
12000
1200000
#,##0
#,##0
#,##0
#,##0
12
1,200
12,000
1,200,000
%Convert to percentage0.2
1
12
0.23858
#%
#%
#%
#0.0%
20%
100%
1200%
23.9%
Number format codes for custom display formats

Custom format codes for dates

CodeDisplay impactExample contentExample 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
Date format codes for custom display formats

Custom format codes for time

CodeDisplay impactExample content *Example formatExample 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 separator1:30:00 amh:mm1:30
AM/PMAM/PM suffix
(omit to designate 24 hour time)
1:30:00 amh:mm AM/PM1:30 am
[]Elapsed time designation1:30:00 am[mm]90
Date format codes for custom display formats

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


Bonus Tip

Please log in to see the bonus tip.

No login?
Get one for free.

Don’t have a login?

Get a login for free on our standard plan, and get a new tip each week.

Get started >>

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

Get quicker >>

View our plans to see what else you are missing, or join for free on our Standard plan.

 

Expert Tip

Please log in to see the expert tip.

Don’t have a login? Get one.

Download files for this tip

Get Quicksheet

Quicksheet

Download example

Example file

No login?
Get one for free.