Number Formatting in Excel: Currency, %, and More

What You'll Be Able to Do, and Why Number Formatting in Excel Changes Everything

Display format is not data, and confusing the two is the single most dangerous mistake you can make in Excel. After a decade building financial reports as a staff accountant, I've watched smart people wreck otherwise-correct spreadsheets because a cell looked like it held 5% but actually held 0.05. Or the reverse. This guide walks you through applying currency, percentage, accounting, and custom number formats in Excel, and more importantly, explains what's actually happening underneath so you don't get burned. The one thing you need before starting: a spreadsheet with raw numbers already in it. Formatting empty cells causes its own problems, which we'll get to.

Everything here applies to Excel for beginners through seasoned users, and works across Microsoft 365, Excel for Mac, and most versions back to Excel 2016. Some custom format codes behave slightly differently in older versions, and I'll flag those as they come up.

Formatting changes how a number looks. It does not change the number itself. Every formula in your workbook reads the underlying value, not what you see in the cell.

---

Step 1: Open the Format Cells Dialog and Apply a Built-In Number Format

Select the cells you want to format, then press Ctrl+1 (or Cmd+1 on Mac). That opens the Format Cells dialog, the real control panel. The ribbon buttons are useful shortcuts, but they hide half the options. Once you're in, click the Number tab if it isn't already active. You'll see a list of categories on the left. This is where most number formatting in Excel actually happens.

Currency Format vs. Accounting Format: Pick the Right One

Both Currency and Accounting format display a dollar sign and two decimal places by default, so people treat them as interchangeable. They're not. The Currency format places the dollar sign immediately next to the number: $1,234.56. The Accounting format pushes the dollar sign to the far left edge of the cell, leaving a gap, so the number and symbol don't sit together. That gap is intentional. It makes long columns of financial figures easier to scan.

In ten years of building financial dashboards, I used Accounting format for any formal report: income statements, budget summaries, anything going to senior management. Currency format is fine for invoices or quick-reference tables where alignment matters less. The other difference worth knowing: Currency format lets you display negative numbers in red or with parentheses. Accounting format always uses parentheses, with no option to change it.

Applying Percentage Format Without Losing Your Actual Value

This is the one that trips up almost everyone. There are two correct ways to enter a percentage in Excel, and they require completely different input.

1. Type 0.05 into a cell, then apply Percentage format. Excel displays 5%. The underlying value stays 0.05, which is what your formulas will use.
2. Format the cell as Percentage first, then type 5. Excel displays 5% and stores 0.05.

Mix those up (type 5 into an already-formatted cell, then reformat it, or type 5 into an unformatted cell and apply Percentage) and you get 500%. I've debugged this exact issue with real users more times than I want to admit. The fix is always the same: check what's actually in the cell, not what it's displaying.

---

Step 2: Build a Custom Number Format in Excel for Thousands, Millions, or Any Pattern

Once you've got the built-in formats down, the Format Cells dialog has one more category worth learning: Custom. This is where number formatting in Excel gets genuinely useful for anyone working with large figures or specialized reports.

Reading Number Format Codes So You Can Write Your Own

In the Custom category, you type a format code directly into the Type field. The two characters you'll use constantly are 0 and #. Zero forces a digit to display even if it's empty, so 0.00 always shows two decimal places, even for whole numbers. The pound sign only shows a digit if one exists, so #.## on the number 5 just shows 5.

To display large numbers in thousands, use a comma after your format code: #,##0,"K" turns 1,500,000 into 1,500K. Two commas, #,##0,,"M", gets you millions. The comma after the last zero tells Excel to divide by 1,000 per comma, but the number in the cell stays untouched. That's the whole point: the format changes the display, never the value.

A format like #,##0.00 "units" will display a number followed by the word "units", and the cell still behaves like a number in formulas, unlike text conversion. That distinction matters if you're summing the column.

For a deeper look at custom number format codes in Excel, including how to handle negative values and color coding inside a format string, that's worth reading separately. It's a bigger topic than one section can cover.

---

Step 3: Use the TEXT Function to Format Numbers Inside a Formula

Cell formatting only affects how a number looks in its cell. The moment you pull that number into a text string, say, a label for a dashboard header, the formatting disappears. That's where the TEXT function comes in.

The syntax is =TEXT(value, format_code). The format_code uses the same codes from the Custom category. So =TEXT(A2,"$#,##0") on a cell containing 47500 returns the text string $47,500. You can combine it with other text like this:

="Revenue: "&TEXT(A2,"$#,##0")

That produces Revenue: $47,500, useful for dynamic report titles or dashboard callouts where you want a formatted number inside a sentence.

The tradeoff: TEXT converts the number to a text string, so you can't sum or calculate with the result. Use it for display and labels only. For everything else, stick with cell formatting.

This fits naturally into the kind of data entry and formatting workflows where presentation and calculation live in different cells.

---

Common Number Formatting Mistakes in Excel, Including the Date Trap Most Guides Skip

Three gotchas come up constantly, and I've seen all of them cause real problems in submitted reports.

The fraction-as-date problem. Type 1/2 into a cell and Excel reads it as January 2nd, not one-half. To enter an actual fraction, either preformat the cell as Fraction in the Format Cells dialog before typing, or type 0 1/2 (zero, space, then the fraction). This one surprises people in 2026 as much as it did ten years ago.

Formatting a column before entering data. Applying Percentage format to an empty column, then typing your values, is the fastest way to end up with 500% instead of 5%. Format after entry, or type decimals from the start.

Currency vs. Accounting misalignment in reports. Mixed use of these two formats in the same table looks wrong immediately. The dollar signs jump around. Pick one and apply it consistently. For anything going to a finance audience, Accounting format is the safer default. Consistency here isn't about aesthetics; a report with misaligned symbols looks untrustworthy even if every number is correct.

If your numbers are showing #####, that's not a formatting error. The column is just too narrow to display the value. Widen it. And if a format you've applied isn't behaving as expected, check what the cell actually contains using the formula bar. Nine times out of ten, the cell holds something different from what you think.

For related issues around how Excel interprets what you type, the guide on entering text, numbers, and dates in Excel covers the underlying data type behavior that makes number formatting work the way it does.

If you take one thing from this article: formatting changes what a number looks like, not what it is, and every formula in your workbook sees the real value, not the formatted one.

---