A Comprehensive Google Sheets Custom Number Format Guide

Google Sheets custom number format allows you to completely control the way information is formatted in your spreadsheet. It does not change the underlying data, only how it is visualized.

In this article, you will learn how to maximize your usage of this feature

How to Access Custom Number Format in Google Sheets

Through the Google Sheets interface:

1. Click the "123" icon in the menu bar

2. At the bottom, click "Custom number format"

Access Custom Number Format by 1) Clicking the "123" icon in the menu bar. 2) Clicking "Custom number format"

After you click "Custom number format", the following screen will pop up where you can input the format you want to use

You can also access custom number formats through the custom number format cycling feature in the SheetWhiz Chrome extension. See the section titled "Quickly Cycle Through Custom Number Formats" to learn how you can leverage this feature to more quickly apply custom formats to your data

Custom Number Format Structure

Custom number format structure is comprised of four separate formatting sections for different number and data types. Specifically the four sections in order are for:

1. Positive numbers

2. Negative numbers

3. Zero

4. Text

Each section is separated by a semi-colon.

So in the following format structure

#,##0" "  ;  (#,##0)  ;  "- "  ;  @

Positive number formatting: #,##0" "

Negative number formatting: (#,##0)

Zero formatting: "- "

Text formatting: @

The below picture illustrates how this would look through the Sheets interface:

Note: You do not need to specify a format for each section. However, if you do not specify, the following rules apply:

• If you only have positive number formatting specified, then this formatting is applied to negative and zero values. Text will display as-is

• If you specify only positive and negative number formatting, any zero value takes on the positive value format. Text will display as-is

• If you specify positive, negative, and zero number formatting, all number values will display as specified. Text will display as-is

Top 5 Custom Formatting Language Symbols to Know

Symbol	Description	Example Format	Before → After Formatting
0 (Zero Digit)	Always shows a number. If the number does not have a digit in the indicated position, it will return 0	0.0 00.00	1 → 1.0 1 → 01.00
# (Pound Sign)	Placeholder for a digit, but if there is no digit, no digit will be shown Conversely, if your number has more digits than placeholders, you will only see the number of digits specified by the number of #s	#.# ##.##	1.123 → 1.1 1.123 → 1.12
, (Comma)	Adds a comma in the number wherever indicated You can chop off numbers by putting multiple commas in a row. Combine this with a "K" or "M" and you can turn 1000 to "1K" or 1000000 to "1M". This is great for data summarization	#,### 0,"K" 0,,"M"	1000 → 1,000 1000 → 1K 1000000 → 1M
. (Period)	A period is used to add a decimal point, no matter if you have a decimal or not	#.#	1 → 1.
% (Percentage)	Percentage formats numbers as a percentage	#,###%	100 → 10,000%
() (Parentheses)	You can add parentheses around numbers to change the format from say -1 to (1). This is a common formatting style in finance and accounting	#,###; (#,###)	-1000 → (1,000)

Additional Custom Formatting Language Symbols

/ (Forward Slash)	Forward slash formats your number like a fraction. You use it largely with quotation marks as seen in the example to the right	#?/?	2.5 → 5/2
E (Letter E)	E formats numbers as an exponent. Used in conjunction with the plus sign (+) or the negative sign (-). E- only shows a sign for negative exponents. Lowercase e also works	0.0E+0 0.0E-0 0.0E+0 0.0E-0	1000000 → 1.0E+6 1000000 → 1.0E6 0.000001 → 1.0E-6 0.000001 → 1.0E-6
* (Asterisk)	An asterisk is used to repeat the symbol immediately following the asterisk. Good if you want to fill the rest of the cell with a specific symbol such as "-" or left justify a currency symbol like "$"	-#,### "$"*#,##0	100 → ----100 100 → $ 100
? (Question Mark)	A question mark represents a digit but will return as a space. This is helpful in aligning decimals if your numbers have varying decimal lengths. Now the tenths, hundredths and thousandths places can be aligned without needing to add extra 0's at the end	0.0??	Numbers in a set:   111.1 → 111.1  111.11 → 111.11 111.111 → 111.111 In the Before formatting, the tenths place does not align perfectly. Instead, the numbers are right justified After formatting, all the decimal places are directly over each other and you can more easily compare tenths, hundreds, and thousandths places
_ (Underscore)	Underscore adds space equivalent to the symbol directly following the underscore to the format (not all characters are the same width). This is great for aligning negative numbers with parentheses such as (1) with positive numbers, as shown to the right	#,##_); (#,##)	Numbers in a set: ()123.23 → (123.23 (123.23) → (123.23) (2)123.3 → (2123.3 In the initial formatting, the tenths and hundredths place for two respective positive number aligns with the parentheses of the negative number. In most analyses, this is confusing and you would prefer the last digit of every number to align. With the new formatting, an invisible space equivalent to the size of a parenthesis is added to the end of the positive numbers, allowing positive and negative numbers to be aligned
\ (Back Slash)	Back slash allows you to show any of the special characters in this table as the character versus the function they activate. You can also use quotation marks to achieve the same result	###\#	100 → 100#
@ ("At")	"At" is a text placeholder. It means "don't change the text that is entered." You can also achieve the same result by not including a text format which will default it to the default format which usually displays text as-is. If it isn't, use the @ symbol to specify this	0;(0);0;@	Hello → Hello
"" (Quotation Marks)	Quotation marks allow you to add text in your format	#.0" Days"	1 → 1.0 Days
[] (Brackets)	Brackets allow you to color and apply conditional formatting to your formatsNote: Adding conditions inside of brackets replaces the default positive, negative and zero rules with conditional expressionsThese concepts are explored in the sections below	Coloring: [Red]#,##0_);(#,##0) Conditional Formatting #1 [=1]0" day"; 0" days"	100 → 100 1  → 1 day 2 → 2 days

Advanced Custom Formatting Usage - Using Brackets for Conditional Formatting

You can add conditional formatting into your custom formatting through the use of brackets ([]). When you do this, you replace the default positive, negative, zero, and text structure with the conditional structure

Conditional Formatting Structure:

1. Condition 1

2. Condition 2

3. Catch-all formatting

4. Text

For example, [<10]”Low”;[>99]”High”;00 returns the word “Low” for values below 10, “High” for values above 99, and a two-digit number for anything in-between

Format	Before Formatting	After Formatting
[<10]"Low";[>99]"High";00	9	Low
[<10]"Low";[>99]"High";00	50	50
[<10]"Low";[>99]"High";00	100	High

Some Additional Rules:

• Conditions can only be applied to the first two "rules" (i.e., Condition 1 and Condition 2)

• If a number matches more than one rule, it reflects the first format it matches

• If a number doesn't match either format (i.e., Condition 1 and Condition 2), it will return all "#"s, filling up the cell width

• The fourth format is always used for text

Advanced Custom Formatting Usage - Specifying Colors

You can also specify font color in your custom format through the use of brackets ([]). The colors that can be specified by name are:

Name	Hex Code	Example Formatting	Before Formatting	After Formatting
Black	000000	[Black]#,###	100	100
White	FFFFFF	[White]#,###	100	100
Red	FF0000	[Red]#,###	100	100
Blue	0000FF	[Blue]#,###	100	100
Green	00FF00	[Green]#,###	100	100
Magenta	FF00FF	[Magenta]#,###	100	100
Yellow	FFFF00	[Yellow]#,###	100	100
Cyan	00FFFF	[Cyan]#,###	100	100

You can access more colors by using "Color #" where # is replaced by a number between 1-56 with each number referring to another color. Below are some examples , but you can find a full list of these colors here

Name	Hex Code	Example Formatting	Before Formatting	After Formatting
Color 10	008000	[Color 10]#,###	100	100
Color 33	00CCFF	[Color 25]#,###	100	100

Quickly Cycle Through Custom Number Formats

Now if you use custom number formatting a lot, it can get tedious to always have to open the menu and set the formats to use, especially if you're using the same custom formats over and over again. You'll find yourself using Paste Formatting A LOT to avoid having to do this, but this isn't an ideal approach because you always have to find something to paste from!

Luckily, at SheetWhiz, we offer Custom Number Cycling, which allows you to set custom number formats, save them, and then cycle through the formats by triggering a shortcut

Here's a short video of the tool in action:

And here's a short demo of how to set up Number Cycle:

To use General Number Cycle, first install the SheetWhiz Chrome Extension.

Second, reload a Sheet — the SheetWhiz logo should appear in the upper righthand corner of your screen:

Next, click the logo to view your shortcuts. You’ll see “General Number Cycle” as one of the first shortcuts. You can now click into the text box and edit your formats appropriately.

You can trigger the tool by holding down “Ctrl + Shift + 1” on a PC or “Control + Shift + 1” if on a Mac. Once you trigger the shortcut the first time, you only need to press and release the last shortcut key (in this case 1) to cycle through the four formats you've set

We know many people like to cycle through custom formats depending on the data type they're formatting, so we've added additional cycles called Percent Cycle, Multiples Cycle, and Local Currency Cycle, which you can use in a similar way to General Number Cycle

Conclusion

Custom number formatting is a tricky tool to understand, but it can dramatically improve the consistency and readability of your model. Used in tandem with SheetWhiz's format cycling capabilities, you'll not only have the best looking model, but finish it in record time! Good luck!