Excel Custom Number Formatting in 2026: Display Data Exactly the Way You Want
One of the most underappreciated skills in Excel is custom number formatting — the ability to control exactly how numbers, dates, currencies, and text appear in your spreadsheet, without changing the underlying data. A sales figure can appear as $1.2M. A date can look like Monday, 21 June 2026. A negative number can display in red with parentheses. All of this is possible through Excel's custom number format system. In 2026, Excel's Copilot assistant can even help you write custom format codes — but understanding the underlying syntax gives you far more control.
How Excel Number Formatting Works
When you type a number into a cell and apply a format, Excel stores the actual numeric value but displays it according to the format rules you define. This means your formulas always calculate with the true values, while the display is purely cosmetic. Custom number formats use a code language with up to four sections separated by semicolons, covering: positive numbers; negative numbers; zeros; and text.
Accessing the Custom Number Format Dialog
Select the cell or range you want to format.
Press Ctrl+1 to open the Format Cells dialog box (or right-click > Format Cells).
Go to the Number tab.
Scroll to the bottom of the Category list and click Custom.
In the Type box, type or paste your format code.
Watch the Preview to see how your number will look.
Click OK to apply.
The Building Blocks of Format Codes
Before diving into examples, here are the core symbols used in format codes:
0 — displays a digit, shows 0 if no digit present (e.g., 0.00 shows 5 as 5.00)
# — displays a digit, shows nothing if no digit present (e.g., #.## shows 5 as 5)
, — thousands separator (1234 becomes 1,234)
. — decimal separator
% — multiplies by 100 and shows percent sign
@ — text placeholder (used in the fourth section for text values)
[Red] / [Blue] / [Green] — applies colour to that section
"text" — inserts literal text in the format
Essential Custom Formats to Know
Thousands and Millions Abbreviation
The code #,##0,"K" displays 45000 as 45K. The trailing comma tells Excel to divide by 1,000. Adding two commas (#,##0,,"M") divides by 1,000,000, so 4500000 displays as 4.5M with the code #,##0.0,,"M". This is widely used in financial dashboards and executive reports.
Currency with Colour Coding
To show positive values in black and negative values in red with parentheses, use: $#,##0.00;[Red]($#,##0.00). This is the standard accounting display used in financial reports and matches the conventions of most professional spreadsheets.
Show Zeros as Dashes
Many financial models replace zero values with a dash for readability. Use: #,##0.00;(#,##0.00);-. The third section (after the second semicolon) controls how zeros are displayed.
Percentage with Controlled Decimals
Use 0.0% for one decimal place, 0.00% for two, or simply 0% for whole numbers. Custom percentage formats give you precise control over the display without needing to recalculate the underlying value.
Phone Numbers
For US-style phone numbers stored as plain numbers, the format 000-000-0000 displays 2025551234 as 202-555-1234. The leading zeros in the format code ensure all digits are shown even when the number starts with zero.
Advanced Date Formats
Excel stores dates as serial numbers and displays them based on the format code. Key date symbols include:
d / dd — day number without/with leading zero
ddd / dddd — abbreviated/full day name (Mon / Monday)
m / mm — month number without/with leading zero
mmm / mmmm — abbreviated/full month name (Jan / January)
yy / yyyy — two/four-digit year (26 / 2026)
Combining these, the code dddd, d mmmm yyyy displays a date as Monday, 21 June 2026. The format d-mmm displays it as 21-Jun. You can include literal text by wrapping it in quotes within the code.
Conditional Formatting via Number Format
Custom number formats can include up to two conditions using square brackets. The format [>=1000000]#,##0.0,,"M";[>=1000]#,##0.0,"K";#,##0 displays large numbers in millions, smaller ones in thousands, and the rest as-is — all from a single format code. Condition operators are >, <, >=, <=, =, <>. The condition must be in the first or second section of the four-section format code.
Using Copilot to Write Format Codes in 2026
In Excel 2026 with Copilot enabled, you can ask Copilot to generate a custom number format for you. Open the Copilot pane and type a request such as: Create a custom number format that shows positive numbers in green with a dollar sign, negatives in red with parentheses, and zeros as a dash. Copilot responds with the format code and an explanation. Copy it and paste it into the Custom format box in the Format Cells dialog.
Sharing Custom Formats Across the Workbook
Custom formats you create in one cell are available throughout the entire workbook from the Custom category in Format Cells. However, they do not automatically transfer to new workbooks. To reuse your custom formats across files, save the workbook as an Excel template (.xltx), which will carry the formats over to any new file created from that template.
Common Mistakes
Using number formatting to actually change the value — formatting only affects display. To round a value, use the ROUND function.
Mixing up the semicolon sections — the order is always: positive; negative; zero; text.
Forgetting that m after h is treated as minutes, not months — Excel reads m as minutes when it follows hours (h) in a time format code.
Conclusion
Custom number formatting is one of the most powerful yet least-taught skills in Excel. Once you understand the format code syntax, you can make your spreadsheets look exactly the way you want without touching the underlying data — and without relying on calculated helper columns to reformat values. In 2026, with Copilot available to help generate codes on demand, there has never been a better time to master this skill. Start with the examples in this guide, experiment in a test workbook, and you will quickly find yourself formatting data in ways that impress your colleagues and make your reports far more readable.
