Custom number formats change how Excel displays a value without changing the value itself. That means calculations, sorting, and formulas still use the real number underneath.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows or Mac when built-in formats do not show values the way you need, such as 007, 1.25K, 2.5M, red negatives, or accounting-style parentheses.
Apply a built-in format first
Many common needs are already covered.
- Select the cells to format.
- Right-click and choose Format Cells, or press Ctrl+1 on Windows or Cmd+1 on Mac.
- On the Number tab, choose Currency, Accounting, Percentage, Date, Number, or another category.
- Adjust decimal places, symbols, or other options.
- Click OK.
If a built-in option is close enough, use it. If you need a specific display rule, create a custom code.
Build your own custom format code
Custom codes live under Format Cells > Custom.
- Select the cells and open Format Cells.
- Click Custom at the bottom of the Category list.
- Clear the Type box or edit the existing code.
- Enter the format code you need.
- Watch the Sample preview.
- Click OK.
- 0000 displays 7 as 0007.
- [$$]#,##0.00 or $#,##0.00 displays currency with commas and two decimals, depending on your locale.
- 0.0,”K” displays 2500 as 2.5K.
- 0.0,,”M” displays 2500000 as 2.5M.
- #,##0;[Red]-#,##0 shows negative numbers in red.
- #,##0;(#,##0) shows negatives in parentheses.
More control
Use four format sections
A custom number format can contain positive;negative;zero;text sections separated by semicolons. This lets each value type display differently.
Hide values without deleting them
The format ;;; makes a cell display blank while keeping the underlying value available for formulas. Use it carefully so hidden values do not confuse other users.
Formats are saved in the workbook
After you use a custom format once, it appears in that workbook custom format list so you can reuse it without typing it again.
Formats are not data conversion
Leading zeros from a format are visual. If an exported file must contain the zeros as actual text, convert or prepare the values accordingly before exporting.
Sources
- Microsoft Support – Create or delete a custom number format (2025)
- Microsoft Support – Number format codes (2025)
- Microsoft Support – Format numbers as percentages (2024)