قالب وردپرس درنا توس
Home / Tips and Tricks / How to change and create custom number formats in Google Sheets

How to change and create custom number formats in Google Sheets



  Google Sheets desktop custom number format menu
Khamosh Pathak

By default, Google Sheets does not format numbers. Using the web app for accounting or budgeting purposes makes the numbers quite difficult to read. Thankfully, you can easily change the number format and create your own custom formats in Google Sheets.

How to change the number format in Google Sheets

We will go through this guide using an example. Let's say you create a warehouse for a particular sale. You may have the item list, quantity, interest rate and the total cost in their own columns.

Here is the table that we will work with.

 Inventory table without formatting

As you can see, the figures are not easily readable at a glance. There are no commas or decimal places, which makes it difficult to read larger numbers (a million figure would still be more difficult).

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

 Select cells to format

Then go to Format> Number. Here you will see different formats such as Number, Percentage, Scientific, Accounting, Financial, Currency and Currency Rounded.

 Select number format

The number format is the simplest option. You can also select the Financial or Currency options to add a currency sign to the numbers.

To remove all formatting, select the "Plain Text" option.

After removing a selected number format, the selected cells in the sheet will be updated immediately.

 Inventory table after formatting

How to create custom number format in Google Sheets

While it is great that Google Sheets provide a few number format options, they are quite limited. For example, if you want to show a comma number 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). [19659006] Start by selecting the cells you want to format.

 A long number without formatting

Then go to the taskbar at the top of the screen to Format> Number> More Format> Custom Number Format.

 Select custom number formats

Scroll down to see all available number formats that have already been created for you. You will notice that the list is divided into two columns. The right column shows what the speech format looks like and the left column shows the syntax for displaying the format.

It may look overwhelming, but since we are simply changing the number format, you do not have to deal with most of the syntax options.

 The Custom NumbersCustom Numbers window

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

So, for example, if you want to format long numbers like "$ 1,000,000", you would enter "$ #, ## 0.00" Syntax. If you don't want to include decimal points, just enter "$ #, ## "and click on the" Apply "button.

 Format and then click Apply

updates instantly.

 Long number after formatting and dollar sign

You find the newly created custom number formats at the bottom of the Format> Numbers submenu, which makes repeated use easier. [19659006]   Custom styles added in the format menu


New to Google Sheets? Learn all the things in our Google Sheets beginner's guide.

RELATED: Google Sheets Beginner's Guide


Source link