sodcalculator

10 Tech How-To

Currency Formatting in Excel: A How-To Guide

Guide 10-55997 · Updated · Step by step

This guide walks you through applying currency formatting in Microsoft Excel, covering the built-in formats, customizing symbols and decimals, and handling common issues. You’ll learn how to make financial data clear, consistent, and professional, whether you’re working with a simple budget or a complex financial report.

What You’ll Need

  • Microsoft Excel (2016, 2019, 2021, or Microsoft 365) installed on your computer.
  • A spreadsheet containing the numeric values you want to format.

Step-by-Step

  1. Select the cells containing the numbers you want to format. You can click and drag to select a range, or click a column/row header to select an entire column or row. To select non-adjacent cells, hold Ctrl (Windows) or Command (Mac) while clicking.
  2. Go to the Home tab on the Excel ribbon. Look for the “Number” group, which typically contains a dropdown box showing “General” (the default format).
  3. Click the dropdown arrow next to the number format box. A list of common formats will appear, including “Currency” and “Accounting”.
  4. Choose “Currency” from the list. This applies the default currency format: your system’s local currency symbol (e.g., $, €, £), two decimal places, and thousand separators (commas). Negative numbers will be shown with a minus sign by default.
  5. Adjust decimal places if needed. In the “Number” group, click the “Increase Decimal” (arrow pointing left with a decimal) or “Decrease Decimal” button to change the number of digits after the decimal point.
  6. Customize the currency symbol (optional). If you need a different symbol (e.g., € instead of $), click the small arrow in the bottom-right corner of the “Number” group to open the “Format Cells” dialog box. Go to the “Number” tab, select “Currency”, then choose the desired symbol from the “Symbol” dropdown. You can also set the number of decimal places and how negative numbers are displayed (e.g., in red, with parentheses, or with a minus sign). Click “OK” to apply.
  7. Use the Accounting format for column alignment (optional). The Accounting format (also in the dropdown) aligns currency symbols and decimal points in a column, which is ideal for financial statements. To apply it, select the cells and choose “Accounting” from the dropdown. You can customize its symbol and decimals via the same “Format Cells” dialog.
  8. Apply formatting to empty cells for future entries. If you format a range of empty cells, any numbers you type later will automatically use that format.

Common Mistakes

  • Using the wrong format: Confusing “Currency” with “Accounting”. Currency places the symbol immediately next to the number, while Accounting aligns symbols at the left edge of the cell. Choose based on your report style.
  • Formatting text as numbers: If you type a number with a currency symbol manually (e.g., “$100”), Excel may treat it as text, preventing calculations. Always enter numbers as plain digits and apply formatting.
  • Forgetting to update decimal places: After applying Currency, the default is two decimals. If you need whole numbers (e.g., for large totals), remember to set decimals to 0.
  • Applying formatting to the wrong range: Double-check that you’ve selected all the cells you intend to format, including any that may be hidden or in merged cells.

Tips

  • Use keyboard shortcuts: Press Ctrl+Shift+$ (Windows) or Command+Shift+$ (Mac) to quickly apply the default Currency format with two decimals.
  • Create a custom format for special cases: In the “Format Cells” dialog, choose “Custom” and enter a format code like "$"#,##0.00 to force a specific symbol and format. This is useful when the standard options don’t fit.
  • Use the Format Painter: If you’ve formatted one cell perfectly, double-click the Format Painter (in the Clipboard group) to copy the format to multiple cells or ranges.
  • Keep negative numbers readable: Choose a display style that suits your audience. For financial reports, parentheses (e.g., (100)) are common; for general use, a minus sign is fine.
  • Combine with conditional formatting: Highlight negative values in red automatically by using conditional formatting rules, making trends easier to spot.

Troubleshooting

  • The currency symbol doesn’t appear: Ensure the cell is not formatted as “Text”. Select the cells, go to the Home tab, and change the number format from “Text” to “Currency”. If the cell contains a formula, make sure it returns a number, not text.
  • Numbers show as ######: This means the column is too narrow to display the formatted number. Double-click the right edge of the column header to auto-fit, or drag to widen it.
  • The wrong currency symbol shows: Excel uses your system’s regional settings by default. To override, open “Format Cells” and select the desired symbol from the dropdown. If the symbol isn’t listed, you may need to change your Windows regional settings or use a custom format.
  • Formatting doesn’t apply to new rows: If you add rows below a formatted table, the new cells may not inherit the format. Use Excel Tables (Ctrl+T) to automatically extend formatting to new rows, or copy the format manually.
  • Values are rounded unexpectedly: Check the decimal places setting. If you set 0 decimals, Excel will round the displayed value, but the underlying value remains unchanged. To avoid confusion, increase decimals or use the ROUND function in formulas if needed.

Frequently Asked Questions

How do I change the currency symbol for a whole column?

Select the entire column (click the column header), then apply the Currency format and choose the desired symbol in the “Format Cells” dialog. This will apply to all cells in that column, including future entries.

What’s the difference between Currency and Accounting formats?

Currency places the symbol right next to the number and uses a minus sign for negatives. Accounting aligns the symbol at the left edge of the cell, aligns decimal points, and shows negatives in parentheses by default. Accounting is preferred for financial statements.

Can I format only part of a number as currency?

No, formatting applies to the entire cell. If you need to show a currency symbol only for certain digits, consider using text or a custom format, but that may break calculations.

Why does Excel show a green triangle on some formatted cells?

This is an error indicator, often meaning the number is stored as text. Select the cells, click the warning icon, and choose “Convert to Number” to fix it.

How do I remove currency formatting?

Select the cells, go to the Home tab, and choose “General” from the number format dropdown. This removes all number formatting and shows the raw values.

Can I format a cell to show currency without decimals?

Yes, apply the Currency format, then click the “Decrease Decimal” button until it shows 0 decimals. The symbol and thousand separators will remain.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →