In Google Sheets, there is a simple way to format numbers as “Currency” without having to manually type the dollar sign when entering numbers in the spreadsheet. Before knowing this easy method, many people leave their money numbers in plain numeric format or manually input the dollar sign every time a number is entered.
But you can format cells so that when new numbers are entered into the cell, they automatically register as currency/money, and you can also instantly convert existing numbers into currency format. In this tutorial, I will show you how to format cells as currency, and I will also show you how to use a formula to format values as currency.
For example, if you want the number 1.25 to be displayed as $1.25, simply change the format of the cell that contains the number to “Currency”.
Formatting Currency Values in Google Sheets
To format currency values in Google Sheets, follow these steps:
- Select the cells you want to convert to currency format.
- In the top toolbar, click on the “More formats” menu (The button has the numbers “123”).
- Click on “Currency”.
After following these steps, the values in the selected cells will be displayed in currency format with a dollar sign before the value, and any numbers you enter in these cells in the future will also be displayed in currency format.
Now, let’s go through examples of formatting currency values using two different methods.
Using the “More formats” menu to format currency values
In this example, we will use the menu option that allows you to change the format of cells so that the values in the cells appear in currency format, with a dollar sign displayed before the numbers.
When using the currency format, the dollar sign is simply displayed in the cell and is not part of the actual value/string that is entered into the cell. In other words, the dollar sign is there just to show, it does not interfere with calculations like other non-numeric characters would.
In this example, we have numbers that are entered in columns A and B. The same numbers are listed in each column so that you can see the difference before and after converting to currency format. We will change the cells in column B to “Currency” format.
To do this, first select column B.
Then click on the button in the top toolbar that says “123” (When you hover over this button, the text “More formats” will appear).
Choose “Currency”.
As you can see in the image below, after following the steps to convert the cells to currency format, column B is formatted as currency and displays a dollar sign before each of the values. These dollar signs were not manually entered, they appeared automatically when the cells were converted to currency format.
Cell A2 contains the number “11.34”, and cell B2 displays “$11.34”.
Using the TO_DOLLARS menu option to format currency values
If you prefer, you can use a formula to display your values in currency format in a Google Sheets spreadsheet. The formula we used for this is the TO_DOLLARS function.
To format currency values using a formula, follow these steps:
- Type
=TO_DOLLARS(to start the formula. - Type the number, or the address of the cell that contains the number you want to convert to currency.
- Press “Enter” on the keyboard. The formula will look like this:
=TO_DOLLARS(1.25)or like this when using cell references=TO_DOLLARS(A2).
After following these steps, the formula will display the specified value as currency, with the dollar sign displayed before the value.
In this example, simple numbers are entered in column A, and we will convert the numbers to currency format using a formula. To do this, we simply refer to the cells that contained the numbers, using the TO_DOLLARS function.
=TO_DOLLARS(A2)
As you can see in the image above, cell A2 displays the number “11.34”, and the formula in cell B2 displays the value “$11.34”.
Increasing and decreasing decimals from currency format
By default, when you convert to currency format, Google Sheets automatically displays two decimal places. You can increase or decrease the number of decimals in your currency values by clicking on “Increase decimals” or “Decrease decimals”.
When it comes to currency values, I often decrease the number of decimals to show only whole dollar amounts, as it gives a cleaner look in the spreadsheet and cleaner look when the data is displayed on charts.
Below, you can see what the decimal buttons look like in the top toolbar.
In the image below, you can see three different columns that contain the same numbers (in currency format), where column A has two decimal places, column B has one decimal place, and column C has 0 decimals.
Now you know the two ways to format values in your Google Sheets spreadsheet as currency!
For more tips and tricks on Google Sheets and other related topics, visit Crawlan.com.
