Google Sheets – Custom Number Formatting Made Easy

You can have complete control over the look and feel of the numbers in your spreadsheet. You can follow along with this example by starting with this template.

This article will walk you through the process of customizing the appearance of the numbers in your spreadsheet. It will teach you how to have control over the visual presentation of your numbers, with currency signs, arrows, and more.  This is called Custom Number Formatting. To have a comprehensive understanding of Custom Number Formatting, see this video from the Prolific Oaktree Youtube Channel.

Besides the default look which presents your data in the black font color, you can infuse some level of creativity in your presentation by assigning different font colors to enhance the message of your presentation. For instance, you may want to present debits in red and credits in green.

Take a look at the image below…

red and green custom numbers
Red and Green custom numbers

The data in red have a minus sign (-) before each, which indicates negative, hence, the use of red. On the other hand, the data in green is positive.

Changing the number formatting allows you to change the look, but the values of each cell will not change. It only gives it a different appearance through the color assigned to individual rows of data.

How To Format Your Number in Google Sheets

The procedure is simple; locate your menu bar at the top of your spreadsheet and select Format>Number. 

Google Sheet- How to format number
How to Format Numbers

You will notice that there is a preset format for the display of your data. If that is what you want, you do not need to change anything. Just click on it and you have your option activated.

However, if what you want is to give the data in each of the cells on your spreadsheet a custom look, you need to dig deeper. We will walk through how to get that done. 

Read here for Conditional Formatting an Entire Row.

Customize the Look of Your Data in Google Sheets

The way your spreadsheet looks is up to you. If you are not satisfied with the general format, this is how you can change it.

  • Highlight the columns with the numbers to be changed.
  • Click on Format on the menu bar.
  • Click on Number from the pop-down menu.
  • Check if there are pre-defined number format for you to use (if not..).
  • Click on More Formats.
  • Click on More Number Formats.

A dialogue box will pop up for you to choose the syntax:

 

Google Sheet- Custom Number formats
Custom Number format

The syntaxes are below:

Input
0; -0; “-“; “not a number”

Output
Positive number is formatted with 0

Negative number is formatted with -0

Zero is shown as a dash (“-“).

A non-number will be shown as “not a number”. Anything in quotes in programming as a syntax remains as it is. The quotes signify that it is a string variable. The semicolon is to separate the columns.

If you intend to format a long number such as “$8,000,000,”, you would use the “$#,##0.00” syntax. In case you don’t intend to include the decimal points, simply enter “$#,##” and click the “Apply” option. Voila!

How To Insert Currency Before a Number in Google Sheets

To insert a particular currency before your number The sign should precede the numbers in the syntax dialogue box. Such as we have below: 

$* 0.00 – positive

$* – 0.00 – negative

The asterisk (*) is to give space between the number and the currency sign and the two zeroes after the decimal is forcing the display of tenths and hundredths.

 

How to Add Colour to Number in Google Sheets

As earlier stated, you can give colors to your numbers for easy understanding.

To achieve customized color for your data, type the following syntax:

0[Green]; -0[Red]; ‘-‘[Black] 

NOTE: The name of the color to set for each number format will come after each of the numbers in parentheses. Make sure you enclose the color for each cell in square brackets (Check the image below).

For the image below, the name for positive numbers is GREEN, negative numbers is assigned RED, while zero is set to appear in BLACK.

Google Sheet- Custom Colour format
Custom Colour format

How to Insert Special Character in Your Data in Google Sheets

Adding special characters to your presentation can make your Google Sheet easier to understand.

To achieve this

  • Go to the “search box” if you use Windows and type “character map”.
  • Click on the result highlighted in the image below
Character Map
  • A box of special character will pop up on your screen. Scroll through to select any character of your choice.
  •  Copy the selected characters and paste them on your Google sheet
  • Select the cells you want to customize
  •  Select Format>Number>More Format>Custom Number Format
  • Add your character to the custom syntax you already have in the pop-up
  • Make sure your special character is placed after each parenthesis as we have below…

             #,##0[Green]▲; ‘-#,##[Red]▼’; ‘-‘[Black];”Not a number”

 

The result is what we have below…

Hopefully, the techniques shown in the article will help you to tell the story of your data in the most compelling way possible.

 Live examples in Sheets

Go to this spreadsheet for examples combining data.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>