Custom Number Formats

Excel Guides / Shortcuts & Formatting

Shortcuts & Formatting

Show thousands as k, zeros as dashes and negatives in red without changing a single value.

Updated 12 Oct 2026 Ā· 6 min read Ā· By Sky Dashboards

A number format changes how a value looks, never what it is. The value 1284000 can show as 1,284,000, as 1,284k or as 1.3m, while every formula still sees the full number. Custom formats are how a report gets tidy figures without helper columns or rounding formulas, and once you know a handful of codes they take seconds to write.

Where custom formats live

  1. Select the cells and press Ctrl + 1 (Cmd + 1 on a Mac) to open Format Cells.
  2. On the Number tab, choose Custom at the bottom of the Category list.
  3. Type your code in the Type box. The Sample line above it previews the active cell.
  4. Click OK.

A quick way in: pick the closest built-in format first (Number, Currency or Date), then click Custom. The Type box starts with that format's code, so you only need to edit it.

Note

Custom formats belong to the workbook. A code you write in one file appears in that file's Custom list only. To use it elsewhere, copy a formatted cell across or use Format Painter.

The four sections

A format code can have up to four parts separated by semicolons. Each part handles a different kind of value:

positive;negative;zero;text

Leave a section empty, for example #,##0;;, and that kind of value is hidden.

  • One section (#,##0) applies to every number.
  • Two sections (#,##0;(#,##0)) use the first for positives and zeros and the second for negatives.
  • Three sections add a separate format for zero.
  • Four sections add a format for text typed into the cell.

Once you write a negative section, Excel drops the minus sign and leaves the styling to you. That is why #,##0;(#,##0) shows -2,150 as (2,150). If you want the minus back, type it: #,##0;-#,##0.

The codes that build a format

Code What it does Example Result
0 Digit that always shows, padding with zeros 0000 on 812 0812
# Digit that only shows when needed #,##0 on 48250 48,250
, Thousands separator, or divide by 1,000 when it ends the number #,##0, on 48250 48
. Decimal point 0.00 on 4.5 4.50
% Multiply by 100 and add a percent sign 0.0% on 0.0842 8.4%
"text" Show the text exactly as typed 0" pcs" on 48 48 pcs
@ Stands for the text in the cell "SKU-"@ on AB12 SKU-AB12
\ Show the next character as it is 0\% on 12 12%
[Red] Colour for that section 0;[Red]-0 on -12 -12 in red
[>=1000] Condition for that section [>=1000]0.0,"k";0 on 2400 2.4k
_) Leave a space as wide as the next character #,##0_);(#,##0) on 500 500, lined up with bracketed negatives
* Repeat the next character to fill the cell $* #,##0 on 1250 $ on the left edge, 1,250 on the right

Tip

A few characters show without quotes: $ - + / ( ) : and the space. Put letters and anything else in double quotes, or use a backslash for a single character.

Show thousands and millions

A comma at the very end of the number part divides the display by 1,000. Two commas divide by a million. The stored value does not change, so totals and charts still use the full figure.

A B C
1 Stored value Format code Displays
2 1284000 #,##0 1,284,000
3 1284000 #,##0,"k" 1,284k
4 1284000 #,##0.0,,"m" 1.3m
5 18640 #,##0.0,"k" 18.6k
6 920 #,##0.0,"k" 0.9k

The same stored values with different scales. The format rounds the display only.

When sizes vary a lot, such as follower counts, add conditions so each number picks its own scale:

[>=1000000]0.0,,"m";[>=1000]0.0,"k";0

1,284,000 shows as 1.3m, 18,640 as 18.6k and 920 as 920. A format can hold two conditions at most; the third section catches everything else.

Watch out

Scaling only changes what you see. If a cell shows 1.3m and someone copies 1.3 into a calculation by hand, the answer is out by a factor of a million. Label scaled columns clearly, for example Revenue ($k).

The comma here is your thousands separator. If your Windows region uses a full stop or a space for thousands, use that character instead.

Dashes for zeros, colours and conditions

Show zeros as dashes

Financial reports often swap zeros for a dash so the real numbers stand out. Put the dash in the third section:

#,##0;(#,##0);"-"

Positives with commas, negatives in brackets, zeros as a dash. Text typed into the cell still shows as normal.

Colours

Put a colour name in square brackets at the start of a section. Excel accepts [Black], [Blue], [Cyan], [Green], [Magenta], [Red], [White] and [Yellow], or [Color1] to [Color56] for the workbook palette.

#,##0;[Red]-#,##0

Negatives in red, with the minus sign kept.

Conditions

A comparison in square brackets, such as [>=1000] or [<500], makes a section apply only to matching values. It suits one simple rule, such as showing anything under a target in red: [Red][<500]#,##0;#,##0.

Tip

For more than two rules, or for fills, bars and icons, use Conditional Formatting instead. A number format can only change the font colour.

Date and time codes

Dates are numbers too, so the same Type box handles them. These results are for Monday 5 January 2026 at 2:30 PM.

Code Meaning Shows
d Day, no leading zero 5
dd Day, two digits 05
ddd Short weekday Mon
dddd Full weekday Monday
m Month number 1
mm Month, two digits 01
mmm Short month name Jan
mmmm Full month name January
yy Two-digit year 26
yyyy Four-digit year 2026
h:mm AM/PM 12-hour time 2:30 PM
hh:mm 24-hour time 14:30

Combine them freely: d-mmm-yy gives 5-Jan-26, dd/mm/yyyy gives 05/01/2026 and mmm yyyy gives Jan 2026, which suits month headers.

Note

m means month, except straight after h or hh or straight before ss, where it means minutes. For durations over 24 hours, put the hours in square brackets: [h]:mm shows a 35 hour 20 minute week as 35:20, where h:mm would show 11:20.

Habits for clean reports

  • Format for looks, round for maths. A format hides decimals but keeps them, so 1.4 + 1.4 displays as 1 + 1 = 3. If the figures on screen must add up, wrap the values in ROUND.
  • One format per measure. On a Sky Dashboards template every revenue tile uses the same $k format and every rate the same decimals. Mixed scales on one page make readers stop and check.
  • Formatted text in a label. A format only applies inside its own cell. To build a title such as Sales: $1.3m, use the TEXT function with the same code.
  • Hide without deleting. ;;; makes a cell look empty while formulas can still read it. The formula bar still shows the value, so treat it as tidiness, not security.
  • Remove a custom format by selecting it in the Custom list and clicking Delete. Cells that used it go back to General.

Frequently asked questions

Does a custom number format change the value in the cell?

No. It only changes the display. Formulas, charts and PivotTables use the full stored value, which you can always see in the formula bar.

How do I show numbers in millions in Excel?

End the format with two commas, for example #,##0.0,,"m". The value 1,284,000 then shows as 1.3m while the cell still holds the full number.

Why does my custom format show ####?

The formatted result is wider than the column. Widen the column or AutoFit it. A negative date or time also shows #### whatever the width.

How do I keep leading zeros on product codes?

Use a format with one zero per digit, such as 00000, which shows 812 as 00812. If the code is an identifier you will never add up, format the column as Text before typing instead.

Want the finished dashboard instead?

My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.

Browse templates ā†’

Related guides

All 32 Excel guides