Custom Number Formats menu in Google Sheets on desktop
Khamosh Pathak

By default, Google Sheets doesn’t format numbers. If you’re using the web app for accounting or budgeting purposes, this makes numbers quite hard to read. Thankfully, you can change the number format and create your own custom formats in Google Sheets easily.

How to Change Number Format in Google Sheets

We’ll walk you through this guide using an example. Let’s say you’re creating an inventory sheet for a particular sale. You might have the item list, quantity, rate, and the total cost in their own columns.

Here’s the table that we’ll be working with.

Inventory table without formatting

As you can see, the numbers aren’t easily legible at a glance. There are no commas or decimal points, making it hard to read larger numbers (a million figure would be harder still).

You can change the number formatting using the Format menu. First, select the range of cells that you want to format.

Select cells to format

Next, go to Format > Number. Here, you’ll see different formats like Number, Percentage, Scientific, Accounting, Financial, Currency, and Currency Rounded.

Select number format

The Number format is the most simple option. You can also choose the Financial or the Currency options if you want to add a currency sign to the numbers.

If you want to remove all formatting, you can choose the “Plain Text” option.

Once you’ve selected a new number format, the selected cells in the sheet will be updated instantly.

Inventory table after formatting

How to Create Custom Number Format in Google Sheets

While it’s great that Google Sheets provides a couple of number formatting options, they’re quite limited. For example, what if you want to display a number with commas and a dollar sign, but not include the decimal point?

You can create your custom number format quite easily (it also works for date and time formats).

Start by selecting the cells that you want to format.

A long number without formatting

Then, from the taskbar at the top of the display, go to Format > Number > More Formats > Custom Number Format.

Select custom number formats

Here, scroll down to see all available number formats that are already created for you. You’ll notice that the list is divided into two columns. The right column shows what the number format looks like and the left column shows the syntax for displaying the format.

It may look overwhelming, but as we’re simply changing the number format, you won’t need to deal with most of the syntax options.

Custom Numbers windowCustom Numbers window

The only syntax you need to know is the Hash (#) symbol. It takes the place of a digit. Everything else (like decimal points, dollar signs, and zeros) stays the same.

So, for instance, if you want to format long numbers as “$1,00,000,” you would enter the “$#,##0.00” syntax. If you don’t want to include the decimal points, simply enter “$#,##” and click the “Apply” button.

Format and then click on Apply

The number will be updated instantly.

Long number after formatting and dollar sign

You’ll find the recently created custom number formats at the bottom of the Format > Number submenu, making repeated use easier.

Custom formats added to format menu


New to Google Sheets? Learn all things sheets in our beginner’s guide to Google Sheets.

RELATED: The Beginner's Guide to Google Sheets

Khamosh Pathak Khamosh Pathak
Khamosh Pathak is a freelance technology writer who specializes in tutorials. His work has also been published on iPhoneHacks, Zapier's blog, MakeUseOf, and Guiding Tech. Khamosh has seven years of experience writing how-tos, features and technology guides on the internet.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.