Excel Number Formatting: Currency, Percent, Dates, and Custom Codes

Number formatting is the highest-leverage formatting in a spreadsheet, and it's the part people skip because the Format Cells dialog looks like a settings screen from 1997. Once you understand the code syntax underneath it, you stop clicking through preset lists and start writing exactly what you want in about four seconds.

The single most important thing to know first: a number format changes how a value is displayed, never the value itself. A cell showing 1,235 may hold 1234.5678. Formulas use the stored value. Rounding for display and rounding for math are different operations, and confusing them is the source of a lot of "my totals are off by a penny" reports.

Where the codes live

Select cells, press Ctrl+1 to open Format Cells, choose Custom, and type into the Type box. Google Sheets has the same thing under Format → Number → Custom number format, and it accepts most of the same syntax, though some of the padding characters behave a little differently.

There are also the built-in shortcuts, which are worth muscle memory:

Shortcut	Applies
`Ctrl+Shift+~`	General (strips formatting)
`Ctrl+Shift+!`	Number with thousands separator, 2 decimals
`Ctrl+Shift+$`	Currency, 2 decimals
`Ctrl+Shift+%`	Percent, 0 decimals
`Ctrl+Shift+#`	Date (`dd-mmm-yy`)
`Ctrl+Shift+@`	Time (`h:mm AM/PM`)

The building blocks: 0, #, and ?

Three digit placeholders, and the difference between them is the whole game.

0 — shows the digit, and shows a zero if there isn't one. 0.00 turns 5.1 into 5.10.
# — shows the digit, shows nothing if there isn't one. #.## turns 5.1 into 5.1.
? — shows the digit, shows a space if there isn't one. Used to line up decimal points in a proportional font.

So #,##0.00 reads as: show thousands separators, always show at least one digit before the decimal, always show exactly two after. That's the workhorse code for almost any money or measurement column.

The rest of the syntax is small:

, between digit placeholders is a thousands separator; a , at the end divides the displayed value by 1,000 each time it appears
% multiplies the display by 100 and appends a percent sign
"text" inserts literal text
@ is the placeholder for text values
_x leaves a blank the width of character x
*x repeats character x to fill the column width
[Red], [Blue] and six other named colors set the font color
[>100] and similar set a condition

The four sections

A format code can have up to four parts, separated by semicolons:

positive ; negative ; zero ; text

Give it one section and it applies to everything. Two sections, and the second handles negatives. Three, and the third handles zero. Four, and the last handles text entries.

This is how you get accountant behavior — negatives in red parentheses instead of a minus sign that's easy to miss at a glance:

#,##0.00;[Red](#,##0.00)

And this is how you hide zeros without deleting them, which declutters a sparse table enormously:

#,##0.00;-#,##0.00;""

Reference table

Code	Value	Displays
`#,##0`	1234.56	`1,235`
`#,##0.00`	1234.5	`1,234.50`
`$#,##0.00`	1234.5	`$1,234.50`
`#,##0.00;[Red](#,##0.00)`	-1234.5	`(1,234.50)` in red
`#,##0,"K"`	1234567	`1,235K`
`#,##0.0,,"M"`	1234567	`1.2M`
`0%`	0.125	`13%`
`0.0%`	0.125	`12.5%`
`0.00E+00`	1234.5	`1.23E+03`
`# ?/?`	1.25	`1 1/4`
`"SKU-"0000`	42	`SKU-0042`
`yyyy-mm-dd`	2026-08-09	`2026-08-09`
`d mmm yyyy`	2026-08-09	`9 Aug 2026`
`ddd, mmm d`	2026-08-09	`Sun, Aug 9`
`h:mm AM/PM`	13:45	`1:45 PM`
`[h]:mm`	1.5 days	`36:00`

Note the percent rows: the stored value has to be 0.125 for the display to read 12.5%. If your column holds 12.5 and you apply a percent format, you'll get 1250%. That's the most common percent bug there is, and it usually means the source data was already converted.

Accounting alignment, in full

The built-in Accounting format is one of the uglier codes you'll meet:

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Decoded: _( reserves a space the width of an open parenthesis so positive and negative rows line up. $* prints the currency symbol and then pads with spaces to push the number to the right edge of the cell — which is why accounting-formatted currency symbols hug the left border while the digits hug the right. "-"?? displays zero as a dash with two character-widths of padding so it aligns with the decimals above it.

You rarely need to type this. But knowing what it does tells you when to use it: accounting alignment is for columns you'll read down, comparing magnitudes. Plain currency format is fine for a single figure in a summary block.

Date codes

Dates in Excel are serial numbers with a format on top, which is why a date column can suddenly display as 45879 — the format got cleared, not the data.

d / dd — day, without or with a leading zero
ddd / dddd — Mon / Monday
m / mm — month number
mmm / mmmm — Aug / August
yy / yyyy — two- or four-digit year

The one trap: m means minutes, not months, when it follows h or precedes s. So h:mm is hours and minutes, while mm:ss is minutes and seconds. Excel reads position, not intent.

For anything that will be sorted, filtered, or shared across countries, use yyyy-mm-dd. It sorts correctly as text, it's unambiguous between US and European readers, and it never turns into a different date on someone else's regional settings.

A worked example: one column, five codes

Take a single stored value, 1234.5678, sitting in a cell. Nothing about the cell changes below — only the code in the Type box does.

Code you type	What appears	Why you'd pick it
`General`	`1234.5678`	Never, in a finished sheet
`#,##0`	`1,235`	Unit counts, headcount, anything where decimals are noise
`#,##0.00`	`1,234.57`	The default for money and measurements
`$#,##0.00;[Red]($#,##0.00)`	`$1,234.57`	A currency column you'll read down for negatives
`#,##0.0,"K"`	`1.2K`	A summary tile where the exact figure doesn't matter

Five displays, one value. Every formula referencing that cell still sees 1234.5678 in all five cases, which is the whole point and also the thing that catches people out.

The reverse case is worth walking too. A column arrives holding 0.0725, 0.15, and 0.0999. Apply 0% and you get 7%, 15%, 10% — three rows that all round to something misleadingly tidy, and 0.0999 and 0.15 now look closer together than they are. Apply 0.00% instead and you get 7.25%, 15.00%, 9.99%. Decimal count isn't a cosmetic choice; it decides which differences a reader can see.

When number formatting goes wrong

Four failure modes, in roughly the order I hit them:

The format does nothing at all. You apply #,##0.00, press OK, and the cell looks exactly the same. That cell holds text, not a number, and number formats only touch numeric values. The tell is alignment: text values hug the left edge while numbers hug the right, so one left-leaning value in an otherwise right-aligned column is your suspect. Fix the type first, then apply the format — the messy spreadsheet checklist covers the conversion methods.

The total is off by a cent. Two rows display 10.00 and 5.00, the total displays 15.01, and someone asks you to explain it. The stored values are something like 10.004 and 5.003; the format rounded each row for display but the SUM used the full values. If the printed total has to equal the printed parts, round the data with ROUND(x, 2) rather than reaching for a format code. Formatting cannot fix an arithmetic requirement.

The percentage is off by 100×. The stored value has to be the decimal fraction. Applying 0.0% to 12.5 gives 1250%. The frustrating variant is a column that already displays 12.5% but stores text — the format then does nothing at all, sending you back to failure mode one.

The scaling commas are hard to count. #,##0, and #,##0,, differ by one character and by a factor of a thousand. If a summary figure suddenly reads 0, count the trailing commas before you go looking at the data.

Excel and Google Sheets, side by side

Most codes port between the two without edits. Three places I check rather than assume:

The shortcut keys are the same digits. On a US layout, Excel's Ctrl+Shift+$, %, and # are physically Ctrl+Shift+4, 5, and 3. Google Sheets uses those same digit combinations for currency, percent, and date. Worth knowing that Excel's shortcuts are defined by the symbol, so on a non-US keyboard layout they move to wherever that symbol lives.
Custom codes live in different menus. Excel: Ctrl+1 → Custom → type into the Type box. Sheets: Format → Number → Custom number format.
Padding and alignment characters are the shakiest part to move. The _ and * characters that make accounting alignment work are the ones I test with a real value rather than trusting the paste, because that's where the two implementations diverge most.

Dates behave consistently enough across both for practical work: a date serial pasted from one into the other lands on the same day. It's the display code that gets lost in transit, not the underlying number.

What I'd actually apply

A quick default set for a typical business table:

Money: #,##0.00;[Red](#,##0.00)
Large money in a summary: $#,##0.0,,"M"
Counts: #,##0
Rates: 0.0%
Dates: yyyy-mm-dd
IDs that must keep leading zeros: 0000 (or however many digits), or store them as text

Applying a consistent set per column type is mechanical enough that it's the first thing SuperSheet's spreadsheet formatter proposes after it reads your headers — it infers the column types, suggests a format code for each, and you can override any of them before it writes the file.

Once the formats are right, the visual pass gets much easier. That's covered in how to make an Excel spreadsheet look professional.