Custom Number Format in Google Sheets

What Is Custom Number Format in Google Sheets?

Custom number format in Google Sheets allows you to control the way numbers or any information are formatted. Here, the data does not change; only the way it is visualized is changed. This technique is very useful as it allows you to add visual effects to your data in a way that suits the users. There are many existing built-in formats in Google Sheets, but we can create a unique format according to requirements.

In the example below, we have formatted our numbers. Have a look at how they are displayed before and after formatting.

How to Custom Format Numbers in Google Sheets?

We can custom format numbers as shown below.

Access custom number format through the Google tab.

Let us look at how to format a set of numbers through the menu bar.

Step 1: Go to the following:

Format -> Number-> Custom number format

Step 2: Choose any of the desired formats. Click Apply. The box highlighted below shows a sample of how your numbers will be displayed. This is the input box that gives a preview of what the rule does. You get an accurate indication of how the numbers will look when applied.

The format is applied to your numbers.

Another way is through the toolbar in Google Sheets.

Google Sheets Custom Number Format Structure

Now, let us see what these custom number formats represent. The custom number format is made up of four separate formatting sections. These are for different number types and for text. The four sections are:

1. Positive numbers
2. Negative numbers
3. Zero
4. Text

Let us look at them one by one. A semi-colon separates each section.

#,##0.00 ; [red](#,##0.00) ; 0.00 ; “some text “@

#1 – Format for positive numbers

Let us now look at the format for positive numbers. The first rule, which is before the first semicolon, shows how the positive numbers will be displayed.

#2 – Format for negative numbers

The second rule is between the first and second semi-colons. Here, we get to see how the negative numbers will be displayed.  tells Google Sheets how to display negative numbers.

#3 – Format for zeros

The third rule is how to format zeros. It is between the second and third semi-colons and clearly shows how zeros will be displayed.

#4 – Format for text

The fourth rule, which comes last, tells Google Sheets how to display all the text values.

Top 5 Symbols/Rules for Custom Number Format in Google Sheets

Given below are the top 5 symbols.

0 (Zero Digit)

It is used to show a number. If there is no number in that position, it will return a zero.

For example, 0.0 00.00

Before format	After format
2	2.0
2	02.00

, (Comma)

It is used to add a comma in a specified position. You can combine Google Sheets number format comma with either K or M, which turns 2000 into 2K and 10000000 into 10M.

For example, with 1000

Before format	After format
2000	2,000
2000	2K

# (Pound Sign)

This sign acts as a placeholder for a digit. If no digit is present, nothing will be shown. If your number is bigger than the number of #s, you can only view the number of digits specified by #.

For example, with #.## or ##.##,

Before format	After format
2.34 #.#	2.3
42.123 ##.##	42.12

. (Period)

It adds a decimal point to your number.

For example, #,#

Before format	After format
23 ##.##	23.00

% (Percentage)

This is used to format a number as a percentage.
For example, ##,# %

Before format	After format
24	24.0%

Additional Symbols/Rules for Custom Number Format in Google Sheets

/ (Forward Slash)

It is used to format a number like a fraction.

For example, #?/? means 10.5 -> 21/2

* (Asterisk)

An asterisk repeats whichever symbol immediately follows it to fill the width of the cell.

For example, *-0 means 100 is represented as ——————100

E (The Letter E)

It is used to format a number as an exponent. They are used in conjunction with the plus or minus sign. You can use both upper- and lower-case.

For example,

0.0E+0	10000000	1.0E+7
0.0E-0	0.0000001	1.0E-7

? (Question Mark)

The question mark aligns values by adding necessary space, even when the number of digits doesn’t match. It helps align decimals if your numbers have different decimal lengths.

() Parentheses

You can add parentheses around numbers to change the format.

For example,

#,##; (###)         -100 → (100)

_ (Underscore)

It adds space to your number formats. Here, the character following the underscore determines the size of space to be added. For example,

#,##0.00_);(#,##0.00) 1234.56    1,234.56

#,##0.00_);(#,##0.00) -1234.56  (1,234.56)

Conditional Rules for Custom Number Format in Google Sheets

When you add conditions inside the square brackets, the specified conditional expressions replace the default positive, negative, and zero rules.

For example:

Rule	Before	After
[<10]”Small” ; [>20]”Large” ; “Medium”	8	Small
	12	Medium
	25	Large

• Please note that conditions can only be specified in the first two rules.
• The third rule is used when the first two conditions are not satisfied.
• The fourth rule is for text and can’t be used for conditional formatting.
• If a number matches more than one rule, it shows the first format that matches.

Color Rules for Custom Number Format in Google Sheets

You can add color to the rules with square brackets [ ].

The colors which can be used are:

[black], [white], [red], [blue], [green], [yellow], [cyan], [magenta]

To get more colors, use “Color #” where a number between 1-56 replaces #.

[Color1], [Color2], [Color3], …, [Color56]

For example, for using Red, [Red]#,###, we get 1,000

For blue, [Blue]#,###, we get 2,000

Examples

Example #1 – Custom Format Phone Numbers

Custom formatting phone numbers in Google Sheets helps you present the data consistently. Let’s look at an example of how to do it.

Let us try to display some numbers in the US Phone Number Format

Step 1: Given below are some phone numbers we have entered.

Step 2: Select the cells with the phone numbers. Go to Format – Number – Custom number format.

Step 3: Enter the following format:

(###) ###-####

This is the American number format. Click Apply.

You can observe how the display of the numbers is changed.

Always ensure that the phone numbers are stored as plain text or numbers.

Example #2 – Conditional + Color Custom Format

We have a set of temperatures of different cities. Let us apply conditional formatting for certain temperatures along with Color Custom format.

Step 1: Let’s try applying conditional formatting to temperatures below 25. To do this, go to Format – Conditional formatting.

Step 2: In the Conditional format rules pane which opens on the right, choose the Format Rules as Format Cells If “Less Than.”

Step 3: Enter 25, choose a color of your choice, and click Done.

You can observe the change in the colors according to the formatting condition we have set.

Now, to apply Custom Number Format, let us apply all negative numbers in white with brackets.

Step 4: Go to Format – Number – Custom number format. Here, enters the following format in the Input box.

###0_);[White](###0)

Step 5: Click Apply to see the changes.

Important Things to Note

1. There is no particular limit on the number of custom formats you can add, but remember, too many will lead to confusion.
2. You can apply conditional formatting and custom number formats to display values based on different conditions in the style you require.
3. You can create custom formats for dates and times as well.
4. Adding Custom Number Format in Google Sheets can significantly improve the consistency and readability of your model.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand Custom Number Format in Google Sheets with its features and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Custom Number Format in Google Sheets. We learn syntax & how to use Custom Number Format in Google Sheets, examples, working template. You can learn more from the following articles.-

GOOGLEFINANCE in Google Sheets

Formula Parse Error in Google Sheets

PI in Google Sheets