قالب وردپرس درنا توس
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



  Custom Number Formatting Menu in Google Sheets on Desktop
Khamosh Pathak

By default, Google Sheets does not format numbers. If you use the web app for accounting or budgeting purposes, figures are therefore quite difficult to read. Fortunately, you can change the number format and easily create your own custom formats in Google Sheets.

Changing the Number Format in Google Sheets

We'll walk you through this guide with an example. Let's say you create an inventory sheet for a particular sale. You may have the item list, quantity, rate, and total cost in their own columns.

Here is the table we are going to work with.

 Inventory list without formatting

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

You can change the format of numbers using the Format menu. First select the range of cells you want to format.

 Select cells to format

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

 Select number format

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

If you want to remove all formatting, you can choose the option "Plain text".

Once you Select a new number format, the selected cells in the sheet are immediately updated.

 Inventory Inventory List

How to Create a Custom Number Format in Google Sheets

While it's great that Google Sheets have a few number formatting options, they are quite limited. For example, what if you want to display a number with commas and a dollar sign, but not use a decimal point?

You can easily create your custom number format (it also works for date and time formats). [19659006] Start by selecting the cells you want to format.

 A long number without formatting

Then from the taskbar at the top of the screen, go to Format> Number> More formats> Custom number format

 Select custom number formats

Scroll down here to see all available number formats already created for you. You will 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 since we're just changing the number format, you don't have to deal with most of the syntax options.

 Custom Numbers Window Custom Numbers Window

The only syntax you need to know is the Hash symbol (#). It replaces a number. Everything else (such as decimals, dollar signs and zeros) remains the same.

So, for example, if you want to format long numbers as "$ 1.00.000", enter "$ #, ## 0.00". "Syntax. If you don't want to include the decimals, just enter "$ #, ##" and click the "Apply" button.

 Format and then click Apply

The song will be updated immediately.

 Long number after formatting and dollar sign

You can find the recently modified custom number formats at the bottom of the Format> Number submenu, which makes repeated use easier. [19659006]   Added custom layouts to layout menu


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

RELATED: The Beginner's Guide to Google Sheets


Source link