Google Sheets offers custom number formatting rules that allow you to specify special formatting rules for numbers. These custom rules control how numbers are displayed in your spreadsheet without altering the underlying values. It’s a powerful technique because you can combine visual effects without changing your data.
Using Custom Number Formatting in Google Sheets
You can access custom number formats through the menu: Format > Number > Custom number format. The custom number format editor looks like this:
You enter your rule in the top box and click “Apply” to apply it to the selected cell or range. Below the input box, you’ll see a preview of how the rule will affect your numbers. This gives you a helpful and fairly accurate indication of how your numbers will appear with the applied rule.
Previous rules are displayed under the preview pane. You can click to restore and reuse any of them.
Structure of Custom Number Format in Google Sheets
You have four “rules” to manipulate, which are entered in the following order and separated by semicolons:
- Format for positive numbers
- Format for negative numbers
- Format for zeros
- Format for text
You have four “rules” to manipulate, which are entered in the following order and separated by semicolons:
1. Format for positive numbers
#,##0.00 ; [red](#,##0.00) ; 0.00 ; "some text "@The first rule, before the first semicolon (;), tells Google Sheets how to display positive numbers.
2. Format for negative numbers
#,##0.00 ; [red](#,##0.00) ; 0.00 ; "some text "@The second rule, between the first and second semicolons, tells Google Sheets how to display negative numbers.
3. Format for zeros
#,##0.00 ; [red](#,##0.00) ; 0.00 ; "some text "@The third rule, between the second and third semicolons, tells Google Sheets how to display zero values.
4. Format for text
#,##0.00 ; [red](#,##0.00) ; 0.00 ; "some text "@The fourth rule, after the third semicolon, tells Google Sheets how to display text values.
Is it necessary to use all four rules?
No, you’re not required to specify all four rules every time. If you only specify one rule, it will apply to all values. If you only specify rules for positive and negative numbers, all zero values will take on the format of positive numbers.
Here are some examples of formats with one or more rules:
| Rule | Positive | Negative | Zero | Text |
|---|---|---|---|---|
0 |
1 | -1 | 0 | text |
0;(0) |
1 | -1 | 0 | text |
0;[red]0 |
1 | -1 | 0 | text |
0;[red]-0;[blue]0;[green]@ |
1 | -1 | 0 | text |
Custom Number Format Rules in Google Sheets
Zero Number Rule (0)
The zero (0) is used to display a number or zero when the number has fewer digits than indicated in the format rule. Use the zero number rule (0) to force numbers to have a specific length and display leading zeros.
For example:
| Rule | Before | After |
|---|---|---|
0.00 |
1.5 | 1.50 |
00000 |
721 | 00721 |
Pound Sign Rule (#)
The pound sign (#) is a placeholder for optional digits. If your value has fewer digits than the # symbols in the format rule, the extra #s will display nothing.
| Rule | Before | After |
|---|---|---|
#### |
15 | 15 |
#### |
1589 | 1589 |
#.## |
1.5 | 1.5 |
Thousands Separator (,)
The comma (,) is used to add thousands separators to your format rule. The #,##0 rule will work for numbers in thousands and millions.
| Rule | Before | After |
|---|---|---|
#,##0 |
1495 | 1,495 |
#,##0.00 |
1234567.89 | 1,234,567.89 |
Decimal Point (.)
The period (.) is used to display a decimal point. When you include the period in your format rule, the decimal point will always be displayed, whether or not there are values after the comma.
| Rule | Before | After |
|---|---|---|
0. |
10 | 10. |
0. |
10.1 | 10. |
0.00 |
10 | 10.00 |
Thousands (k or K) or Millions (m or M)
If you add thousands separators but do not specify a format after the comma (e.g., 0,), the hundreds will be dropped from the number. Combined with a “k” or “K” to indicate thousands, this allows for abbreviated numbers. For millions, you need to specify two commas.
| Rule | Before | After |
|---|---|---|
0.0, |
2500 | 2.5 |
0,"k" |
2500 | 3k |
0.0,"k" |
2500 | 2.5k |
0.0,,"M" |
1234567 | 1.2M |
Negative Number in Parentheses ()
Parentheses can be added to the negative number rule to change the format from -100 to (100), which is often used in accounting and finance.
| Rule | Before | After |
|---|---|---|
0;(0) |
-100 | (100) |
Asterisk (*)
The asterisk (*) is used to repeat characters in your format rule. The character following the asterisk is repeated to fill the width of the cell.
In the following example, the dash is repeated to fill the width of the cell in Google Sheets:
| Rule | Before | After |
|---|---|---|
*-0 |
100 | —————-100 |
Question Mark (?)
The question mark (?) is used to properly align values by adding necessary spaces, even when the number of digits doesn’t match.
See this example:
Underscore (_)
The underscore (_) also adds spacing to your number formats. The character following the underscore determines the size of the space to be added (although it is not displayed). This rule allows you to add precise amounts of space.
For example, #,##0.00_);(#,##0.00) adds a space after the positive sign that matches the width of a parenthesis, so the decimal point aligns with negative numbers in parentheses.
You can see it clearly in the following image, where the first line has no spacing, but the second line does. The red highlight has been added to show the result of the spacing:
Escape Character ()
Suppose you actually want to display a pound sign in your format. If you simply place it in your format rule, Sheets will interpret it as a placeholder for optional digits (as mentioned above).
To display the pound sign, precede it with a backslash () to ensure it appears.
This applies to all other special characters as well.
| Rule | Before | After |
|---|---|---|
#0 |
10 | 10 |
#0 |
10 | #10 |
At Symbol (@)
The at symbol (@) is used as a placeholder for text, meaning not to modify the entered text.
| Rule | Before | After |
|---|---|---|
0;0;0;"Special text value!" |
Some text | Special text value! |
0;0;0;@ |
Some text | Some text |
Fraction (/)
The forward slash (/) is used to indicate fractions.
For example, the rule # ?/? will display numbers as fractions:
| Rule | Before | After |
|---|---|---|
# ?/? |
2.3333333333 | 2 1/3 |
Percentage (%)
The percent sign (%) is used to format values as percentages. Like other rules, you first specify the digits and then use the percent sign to convert it to a percentage, such as 0.00%.
| Rule | Before | After |
|---|---|---|
0.00% |
0.2829 | 28.29% |
Exponent (E)
For very large (or very small) numbers, use the exponential format to display them in a more compact form. The rule is number * E+n, where E (the exponent) multiplies the preceding number by 10 to the power of n.
Here’s an example:
| Rule | Before | After |
|---|---|---|
0.00E+00 |
23976986 | 2.40E+07 |
Conditional Custom Number Format Rules in Google Sheets
Adding conditions inside brackets replaces the default rules for positive, negative, and zero numbers with conditional expressions.
For example:
| Rule | Before | After |
|---|---|---|
[<100]"Small";[>500]"Large";"Medium" |
50 | Small |
[<100]"Small";[>500]"Large";"Medium" |
300 | Medium |
[<100]"Small";[>500]"Large";"Medium" |
800 | Large |
Conditional Rules
- Conditions can only be specified in the first two rules.
- The third rule is used as the format for anything that doesn’t satisfy the first two conditions.
- The fourth rule is always used for text and cannot be used for conditional formatting.
Colors in Custom Number Formats in Google Sheets
Add colors to your rules with brackets [].
There are 8 named colors you can use: [black], [white], [red], [green], [blue], [yellow], [magenta], [cyan].
For more colors, use two-digit color codes: [Color1], [Color2], [Color3], …, [Color56].
For a complete list of the color palette with these 56 colors, click here.
Examples of Colors
| Rule | Before | After |
|---|---|---|
0;[red] |
-100 | (100) |
Here’s another example of using custom number format rules in Google Sheets: How to Create a Table in Google Sheets and Make It Appealing.
Where the rule is:
Examples of Custom Number Formats in Google Sheets
Phone Number
Transform any 11-digit number into a formatted phone number with the zero number rule and dashes:
| Rule | Before | After |
|---|---|---|
0 |
000-000-0000 | 18004567891 |
Plural
Use conditional rules to make words plural. Remember, these are still underlying numbers, so you can still perform calculations with them. The formatting part (“day” or “days”) is simply added as an extra layer.
| Rule | Before | After |
|---|---|---|
[=1]0" day"; 0" days" |
1 | 1 day |
[=1]0" day"; 0" days" |
2 | 2 days |
[=1]0" day"; 0" days" |
100 | 100 days |
Conditional
Use conditions to directly categorize numbers:
| Rule | Before | After |
|---|---|---|
[<250]"Small"<em> 0 ; [>750]"Large"</em> 0 ; "Medium"* 0 |
70 | Small 70 |
[<250]"Small"<em> 0 ; [>750]"Large"</em> 0 ; "Medium"* 0 |
656 | Medium 656 |
[<250]"Small"<em> 0 ; [>750]"Large"</em> 0 ; "Medium"* 0 |
923 | Large 923 |
Note: These are still underlying numbers, so you can perform calculations with them. Additionally, the words “Small,” “Medium,” and “Large” exist only in the formatting layer and cannot be used in formulas. For example, you cannot use a COUNTIF to count all values with “Large.” For that, you would need to actually change the value to have the word “Large” in the cell or add a helper column.
The * part of the rule adds a space between the word and the number to fill the width of the cell.
Conditional + Color
Add color scales to the conditional example:
| Rule | Before | After |
|---|---|---|
[color44][<250]"Small"<em> 0;[color46][>750]"Large"</em> 0;[color45]"Medium"* 0 |
70 | Small 70 |
[color44][<250]"Small"<em> 0;[color46][>750]"Large"</em> 0;[color45]"Medium"* 0 |
656 | Medium 656 |
[color44][<250]"Small"<em> 0;[color46][>750]"Large"</em> 0;[color45]"Medium"* 0 |
923 | Large 923 |
Temperature Example
Combine conditions with emojis to turn numbers into an emoji scale, like this temperature example:
| Rule | Before | After |
|---|---|---|
[>90]🔥🔥🔥;[>50]🔥;❄️;"No Data" |
37 | ❄️ |
[>90]🔥🔥🔥;[>50]🔥;❄️;"No Data" |
75 | 🔥 |
[>90]🔥🔥🔥;[>50]🔥;❄️;"No Data" |
110 | 🔥🔥🔥 |
[>90]🔥🔥🔥;[>50]🔥;❄️;"No Data" |
N/a | “No Data” |
Other Resources
- How to Add Superscripts and Subscripts in Google Sheets
- Google’s Documentation on Number Formatting in Sheets
- Custom Number Format Builder for Google Sheets and Excel
Have questions? Comments? Have you used custom number formats? Have you seen any interesting examples? Leave a comment below.
