You can format the data in your cells a few different ways for currency, dates, percentage, text, and numbers. If you’re working with formulas where you want to format those results, you can use a handful of Google Sheets functions.
These functions work with other functions and formulas, but they also work on their own. So whichever way you decide to use them, we’ll show you the syntax and arguments you need.
Format as Dollars: TO_DOLLARS
When you want to convert data to dollars, you can use the TO_DOLLARS function. The syntax is TO_DOLLARS(value) with just that one required argument. You can use a cell reference or inserted value.
The function is equivalent to using Format > Number > Dollars in the menu.
Note: This function does not convert other currencies to U.S. dollars. Instead, you can use the GOOGLEFINANCE function for current exchange rates.
To convert a value in a cell to dollars, insert the cell reference into the formula:
=TO_DOLLARS(A1)
To convert an amount, simply insert the number into the formula:
=TO_DOLLARS(57.29)
To add numbers in a cell range and convert the result to dollars, use this formula:
=TO_DOLLARS(SUM(A1:A5))
Format as Text: TO_TEXT
Maybe you have a number that you want to convert to text. Here, you can use the TO_TEXT function. The syntax is TO_TEXT(value) where you can use a cell reference or number for the argument.
The function is equivalent to placing an apostrophe in front of the number.
To convert a number in a cell, insert the cell reference into the formula:
=TO_TEXT(A1)
To convert a specific number, insert it into the formula instead:
=TO_TEXT(5.99)
Format as Percentage: TO_PERCENT
When you calculate a percentage in Google Sheets, you may want to format it as such. This is when TO_PERCENT comes in handy. The syntax is TO_PERCENT(value) where you can use a cell reference or number for the argument.
The function is equivalent to Format > Number > Percent in the menu.
To convert a value in a cell, insert the cell reference into the formula:
=TO_PERCENT(A1)
To convert a certain value, include it in the formula instead:
=TO_PERCENT(0.123)
To calculate the percentage and format it as such, you can use the following formula. This calculates the percentage for part in cell A1 of the total in cell B1 and formats the result.
=TO_PERCENT(A1/B1)
Format as a Date: TO_DATE
Have you ever tried to enter a date into a cell and ended up with a number instead? For example, you may type in 9/22/2022 and end up with 44826.
The TO_DATE function converts that number to the date you need. The syntax is TO_DATE(value) where you can use a cell reference or number for the argument.
The function is equivalent to Format > Number > Date in the menu and formats the result as MM/DD/YYYY.
To convert a value in a cell, insert the cell reference into the formula:
=TO_DATE(A1)
To convert a specific value, simply insert it into the formula instead:
=TO_DATE(44826)
Format as a Number: TO_PURE_NUMBER
On the opposite side of formatting, you may want to take your dollar, text, percentage, or date and turn it into a pure number. For this, use the TO_PURE_NUMBER function. The syntax is TO_PURE_NUMBER(value) where you can use a cell reference or formatted value for the argument.
The function is similar to Format > Number > Normal only without any formatting such as a comma for thousands.
To convert the date in cell A1 to a pure number, you would use this formula:
=TO_PURE_NUMBER(A1)
To convert a percentage to a number, insert it into the formula:
=TO_PURE_NUMBER(25%)
To add dollar amounts in a cell range and format the result as a pure number, you would use this formula:
=TO_PURE_NUMBER(SUM(A1:A5))
The next time you need to convert or format a specific number or text or the results of a formula, keep these Google Sheets functions in mind.