Format Numbers as Currency Using Formulas in Google Sheets

When working with financial data in Google Sheets, it’s important to present numbers in a readable currency format. While you can use the menu option Format > Number > Currency, there’s a more dynamic and formula-driven way to format numbers as currency.

In this guide, you’ll learn how to:

  • Use the TO_DOLLARS and DOLLAR functions in Google Sheets
  • Format plain numbers as currency using formulas
  • Understand the difference between the two functions
  • Choose the right function based on your specific needs

Use Case: Calculating Total Cost in Currency Format

Assume you’re tracking purchase quantities of an item in column B, and the unit rate is $5.

Basic formula to calculate total cost:

=SUM(B2:B10) * 5

This returns a plain number, not a currency-formatted one.

To format this result as currency using a formula, wrap it with either TO_DOLLARS or DOLLAR:

=TO_DOLLARS(SUM(B2:B10) * 5)

How to Use TO_DOLLARS in Google Sheets

The TO_DOLLARS function converts a numeric value into your sheet’s default currency format (based on your locale).

Syntax

TO_DOLLARS(value)

Examples

=TO_DOLLARS(A1)
=ArrayFormula(TO_DOLLARS(A1:A10))
Formula examples using the TO_DOLLARS function in Google Sheets to convert numbers to currency format

Behavior

  • Accepts only numeric input.
  • Returns output in currency format, but still a number (not text).
  • Returns text inputs as-is (no conversion applied).
  • Interprets dates as numbers—not recommended for date fields.

Pro Tip: Use TO_DOLLARS when you want the formatted result to still behave like a number (for calculations, charting, etc.).

How to Use DOLLAR in Google Sheets

The DOLLAR function is more flexible with input types and allows for rounding.

Syntax

DOLLAR(number, [number_of_places])

Examples

=DOLLAR(1234.567)        → "$1,234.57"
=DOLLAR(1234.567, 0)     → "$1,235"
=ArrayFormula(DOLLAR(A1:A10))

Behavior

  • Converts numbers and numeric text to currency format.
  • Returns text (not a number).
  • Accepts Boolean values (TRUE/FALSE) and formats them.
  • Throws #VALUE! error if input is pure text.

Note: Use DOLLAR when you need to display currency in text format or apply rounding.

TO_DOLLARS vs DOLLAR: Quick Comparison

FeatureTO_DOLLARSDOLLAR
Output formatNumberText
Accepts text input❌ Returns as-is✅ Converts to currency format
Works with Boolean (TRUE/FALSE)❌ Returns as-is✅ Formats as currency
Interprets date values✅ Converts to currency (⚠️)✅ Converts to currency (⚠️)
Rounding option❌ Not available✅ Yes, via second argument
Use in charts/calculations✅ Recommended❌ Not ideal (text output)

In the screenshot below, you can see how the TO_DOLLARS function (left column) and the DOLLAR function (right column) format the same input data differently. This highlights the key differences in output type, handling of text values, and formatting behavior.

Side-by-side comparison of TO_DOLLARS and DOLLAR functions in Google Sheets showing differences in currency formatting

Example: Format Total Purchase Cost as Currency

Let’s apply what we’ve learned.

Plain formula:

=SUM(B2:B10) * 5

With TO_DOLLARS:

=TO_DOLLARS(SUM(B2:B10) * 5)
Real-life example showing how to format numbers as currency using formulas in Google Sheets

This version ensures the output remains a number in currency format, perfect for additional calculations or chart visualization.

If you need to display the value as text or round to specific decimals, use:

=DOLLAR(SUM(B2:B10) * 5, 0)

When to Use Each Function

  • ✅ Use TO_DOLLARS when:
    • You want a number output.
    • You need to retain numeric behavior for formulas or charts.
    • Input values are strictly numbers.
  • ✅ Use DOLLAR when:
    • You want rounded currency values.
    • You’re converting numeric text to currency format.
    • You’re displaying currency as plain text.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

More like this

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.