Custom Number Formats: What the Cell Shows, What the Cell Holds, and the Total That Says 3:00

Ten service jobs, one week. The Time column has a thirty-hour install in it, two twelve-hour days and a scatter of shorter visits, and the total at the bottom says 3:00.

Three hours. For a week that ran to seventy-five.

Nothing about the data is wrong. Every cell holds the right value, the SUM underneath is a perfectly ordinary SUM, and if you click the total and look at the formula bar, the number sitting there is correct to the last decimal. What is wrong is a format — four characters in a box in a dialog nobody opens — and the fix is to add two more.

This is what number formatting is: a mask laid over a value. Get it right and a column of raw decimals turns into money, hours, percentages and dates that a person can read at a glance. Get it wrong and the sheet lies to you fluently, in your own house style, with the correct value still sitting underneath where nobody looks.

What you need. Custom number formats are in every version of Excel — Windows, Mac, web — and in Google Sheets, with a handful of differences noted as they come up. Everything here happens in Ctrl + 1 ▸ Number ▸ Custom. There is no function to learn and nothing to install.

1) The Cell Holds a Number. The Format Decides What You See.

🎯 Scenario: The week's jobs as they arrive from the field-service system — no currency, no percent signs, no times, every column showing exactly what the cells hold.

Click E2. The formula bar reads 0.3125. The cell, once it is formatted, reads 7:30.

Both are true. 0.3125 is the value — the thing stored in the file, the thing SUM adds, the thing a lookup compares against. 7:30 is the display, produced by running that value through a format string. Change the format and the display changes. The value does not move.

That distinction is the whole subject, so it is worth being blunt about the two directions it cuts:

• A format cannot break your arithmetic. Round a column to whole numbers on screen and the totals still add the decimals. Nothing is lost.
• A format cannot fix your arithmetic either. A column that displays whole numbers and totals to something ending in .47 is not a bug, and no amount of formatting will make those two agree.

If you actually want the value changed, that is ROUND, and it belongs in a formula, not in a dialog. The rounding article covers where the pennies go when you do.

2) Where They Live: Ctrl + 1, Custom, and the Type Box

Home ▸ Number gives you the shortlist: Currency, Percentage, Comma, and the two buttons that add and remove decimal places. That covers most days.

Everything else is behind Ctrl + 1 (Format Cells) ▸ Number ▸ Custom, where there is a list of format strings and a Type box you can type into. Three things about that box:

• Start from something. Pick the built-in format closest to what you want first — Currency, or Date — then switch to Custom, and the Type box is pre-filled with its format string, ready to edit. Writing one from a blank box is a beginner's tax nobody needs to pay.
• The formats belong to the workbook. A custom format you invent lives in the file you invented it in. It arrives in another workbook only when a cell carrying it is copied there — and it does arrive that way, which is why old workbooks accumulate dozens of nearly identical formats in that list.
• The format travels with the cell. Copy a cell and you copy its format. Paste Special ▸ Formats (or the Format Painter) moves the format alone, which is how you apply a column's worth of decisions without touching the data.

The word General is also a format — the default one, the one that shows numbers roughly as typed — and it is a keyword you can use inside a custom format string when you want one section to fall back to it.

3) The Placeholders: 0, # and ?

A format string is a pattern of placeholders. There are three, and the difference between them is what happens when a digit is missing.

On the same value, 1750:

#,##0.00 is the workhorse and worth reading once, slowly: the #,## says "put a separator every three digits if there are digits to separate", the 0 says "always show at least one digit before the point, even for a value of zero", and the .00 says "always show exactly two after it".

The ? placeholder is the one people never meet. In a proportional font, 0.5 and 0.75 in the same column do not line up. Format them 0.?? and the missing digit becomes a space, so the decimal points stack. It is also how fraction formats work: # ?/? displays 0.75 as 3/4, and # ??/?? gives the halves and quarters room to align.

One rule underneath all of them: the display rounds, it never truncates. 0.1375 under 0.0% shows 13.8%, not 13.7%. Excel rounds halves away from zero on screen exactly as ROUND does in a formula.

4) The Comma Does Two Completely Different Jobs

A comma between digit placeholders is a thousands separator. A comma after the last digit placeholder divides the display by a thousand. Each extra trailing comma divides by a thousand again.

🎯 Scenario: A board pack where every figure is in thousands and the column is too narrow for 12,750,000.

Look at the third column. A real job worth 1,750 prints as 2 in a thousands-scaled column, and as 0 in a millions-scaled one. The value is untouched, the total is right, and the row reads as noise. That is the entire risk of scaling: it is a decision about the biggest numbers on the sheet being applied to the smallest ones.

The fix is not to stop scaling — a column of twelve-million-somethings is unreadable — but to say so in the header, and to check what the scale does to your smallest row before you ship it.

5) The Four Sections: positive ; negative ; zero ; text

A format string can have up to four parts, separated by semicolons, applied in this fixed order:

<positive> ; <negative> ; <zero> ; <text>

What happens when you write fewer is the part that catches people:

Two things follow from that table, and both of them have shipped in real reports.

The negative section supplies its own minus sign. Excel does not add one for you. Write #,##0;#,##0 and every negative in the column displays as though it were positive. On the Variance column, that turns -96,200 into 96,200 on screen while the total underneath still says 857,730, because the total is adding the values, not the display. A column where every row looks like a gain and the total agrees with none of them is very hard to spot and very easy to write.

An empty section shows nothing. #,##0;-#,##0; — note the trailing semicolon with nothing after it — is a legitimate format that hides zeros entirely, and #,##0;; hides both negatives and zeros. Useful on purpose. Alarming by accident.

6) Colour and Condition in Square Brackets

Colours. Put a colour name in square brackets at the start of a section: [Black], [Blue], [Cyan], [Green], [Magenta], [Red], [White], [Yellow], or [Color1] through [Color56] for the rest of the legacy palette.

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

Conditions. A section can be prefixed with a comparison in square brackets, and then it applies only to values that satisfy it:

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

On the Amount column that gives 12.8M, 2.5M, 1.3M, 458.8k, 96.2k, 1.8k and 0 — one column, three scales, every row readable at a glance.

Two limits to know before you build on it:

• Two conditions, maximum, plus one final catch-all section. There is no fourth branch.
• Conditions replace the positive/negative/zero structure. Once you write a condition, the sections mean what your conditions say, not what section 5 describes. Negatives fall into whichever branch matches — and in the format above, every negative lands in the catch-all, which is why it shows a sign of its own accord and a red variant would have to be written into that last section.

And the boundary: a custom format can only test the cell's own value. A rule that depends on another cell — highlight the row if the variance beats the quote — is conditional formatting, which is a different tool with its own article.

7) The Variance Column, Written Out

🎯 Scenario: A finance reviewer who wants overruns in parentheses, in red, and wants the two jobs that came in exactly on quote to read as a dash rather than a zero.

Build it a section at a time:

#,##0
#,##0;[Red](#,##0)
#,##0;[Red](#,##0);"—"
#,##0;[Red](#,##0);"—";@

The finished format displays the ten variances like this:

Note what the negative section does not contain: a minus sign. The parentheses are doing that job, and adding both would give you (-18,450). Note also the @ on the end — the text section, which passes any text in the column straight through. Without it, a cell someone typed n/a into would display as n/a anyway (text ignores a three-section format), but with a four-section format present the @ is what keeps that true.

8) Literal Text, Spaces That Line Up, and the Asterisk

Anything in double quotes is printed literally: #,##0" h" shows 75 h, and 0.0" kg" shows 13.8 kg. The value stays a number, so it still sums.

A handful of characters can be typed without quotes — + - / ( ) : space and a few others — and the rest need either quotes or a backslash in front of them: 0\% shows a literal percent sign without multiplying by 100, which is the one case where the backslash earns its keep.

Two symbols do layout rather than text:

• Underscore reserves a space the width of the character after it. _) at the end of the positive section reserves exactly the width of the closing parenthesis the negative section uses, so positives and negatives line up down the column even though only one of them has brackets. That is the whole secret of the built-in Accounting format, which is a long, ugly, entirely mechanical string of _-* #,##0.00_- sections.
• Asterisk repeats the next character to fill the cell width. 0*. gives you dot leaders after the number; _-* #,##0 pushes the number to the right edge and pads the gap with spaces, which is why Accounting has the currency symbol hard against the left border and the digits hard against the right.

And @ is the text placeholder: "Job "@ displays whatever text is in the cell with Job in front of it.

9) Percent, and the 18.4 That Became 1840%

The % sign in a format multiplies the display by 100 and appends the sign. The stored value never changes.

The Margin column holds decimals. 0.184 under 0.0% shows 18.4%. Under 0% it shows 18%, and the 0.6 of a point that disappeared is still in every calculation that touches the cell.

The trap arrives when somebody hands you numbers that are already scaled. A column pasted from a report as 18.4, 22.1, 9.6 looks like percentages. Apply a percent format and it displays 1840.0%, 2210.0%, 960.0% — because to Excel the value 18.4 means eighteen point four of one, which is 1840%. The format is doing exactly what it says. The data was never in the units you assumed.

There is one more wrinkle, and it is the reason two people can disagree about what Excel does here:

• Typing 18.4 into an empty, unformatted cell and then applying percent gives you 1840.0%. The value was 18.4 before the format arrived.
• Typing 18.4 into a cell that is already percent-formatted gives you 18.40%, because File ▸ Options ▸ Advanced has Enable automatic percent entry switched on by default, and it converts as you type.

Same keystrokes, different result, depending on the order. When a percentage column looks a hundred times wrong, the question to ask is not what the format says but what the values are: click a cell and read the formula bar.

10) Dates and Times Are Numbers Too

A date in Excel is a whole number counting days from the start of 1900, and a time is the fraction of a day after it. 46252 is 18 August 2026. 0.3125 is 07:30. 46252.3125 is both.

Which means a date format is just another mask over a number:

🎯 Scenario: A completion-date column that has to read Tue 18 Aug in the report and 18/08/2026 07:30 in the export.

ddd d mmm
dd/mm/yyyy hh:mm

The famous ambiguity is m. It means month, except when it follows an h or precedes an s, when it means minutes. mm/dd is a date; hh:mm is a time; mm:ss is minutes and seconds. Excel decides from context, and it decides correctly nearly always — the exception being a format that starts with minutes and has no hours in front of it, where you have to write [mm] or put an h in to make yourself understood.

The last thing a date format cannot do: turn text into a date. If an import left 18/08/2026 in the cell as text — left-aligned, with a little green triangle — no format will convert it. That is a data problem, and it is Power Query's or DATEVALUE's to solve.

11) [h]:mm — The Brackets That Stop the Clock Wrapping

Here is the 3:00 from the top of this article.

The Time column holds 0.3125, 0.0625, 0.5, 0, 0.25, 0.0625, 1.25, 0.125, 0.375 and 0.1875 — fractions of a day. Formatted h:mm they read as ordinary times, and one of them does not:

h is the hours within a day. A value of 1.25 days is one whole day plus six hours, and h:mm reports the six. The overnight install that ran thirty hours displays as a six-hour job, and the week's total — three days and three hours — displays as three hours flat.

Square brackets around the unit turn off the wrapping. [h]:mm shows total elapsed hours, however many days that is. There is a [m] for total minutes (4500 for the week) and an [s] for total seconds, and the brackets are only ever allowed on the first unit in the format.

Two habits worth adopting on any sheet that adds up time:

• Any total of times gets [h]:mm. Always. The individual rows can be h:mm if they are all under a day; the total cannot be trusted to be.
• If the hours have to be multiplied by anything, get them out of time. =SUM(E2:E11)*24 returns 75 as a plain number, ready for a rate. A cell displaying 75:00 is still holding 3.125, and multiplying that by an hourly rate produces a number nobody can explain.

12) Leading Zeros, Part Codes, and the Lookup That Fails Because of Them

The jobs on this sheet are J-2041 to J-2050, which are text and behave themselves. The trouble starts when the source system exports the same jobs as the numbers 2041 to 2050 and the contract calls them 002041.

A custom format fixes the display in one step: 000000 pads any number out to six digits, so 2041 shows as 002041 while the value stays 2041. It sorts numerically, it sums, it is a number in every way that matters.

Except one. A lookup matches values, not displays. =XLOOKUP("002041", A2:A11, B2:B11) searches for the six-character text 002041 and finds nothing, because the cells contain the number 2041. The screen agrees with your search term and the formula returns #N/A, which is about the most confusing failure Excel offers.

So decide which one it is:

• A quantity you might do arithmetic on — keep it a number, use 000000 to display it, and search for it as a number.
• An identifier — postcodes, part codes, phone numbers, anything with a leading zero that must survive being copied out of the workbook — make it text before it is typed: format the cells as Text first, or type a leading apostrophe ('002041). The apostrophe is not stored in the value; it is Excel's marker for "treat this as typed".

There is a hard limit underneath this. Excel stores 15 significant digits. A 16-digit account number typed as a number silently becomes a different account number ending in zero, and no format brings the digit back. Long identifiers are text, always, no exceptions.

13) ;;; and the Other Ways to Show Nothing

Three semicolons with nothing between them — ;;; — is a complete, valid format with four empty sections. It displays nothing at all: positives, negatives, zeros and text alike. The cell looks empty; the formula bar shows the contents; the value still calculates; printing shows a blank.

It is genuinely useful for staging cells, working columns and the helper rows a dashboard needs but should not show. It is also, and this needs saying plainly, not security. Anyone who clicks the cell sees the value in the formula bar. Hiding data from people who should not have it is sheet protection with hidden formulas, and there is an article about that too.

Alongside it, the smaller ways of showing nothing:

• #,##0;-#,##0; — hides zeros only, leaving the rest normal.
• #,##0;; — hides negatives and zeros. Worth recognising in an inherited workbook, worth avoiding in a new one.
• File ▸ Options ▸ Advanced ▸ Show a zero in cells that have zero value does the same thing for a whole sheet, in one switch, without touching any format.
• A dash instead of a zero — the ;"—" section from section 7 — says "we looked, and it was zero", which is a different statement from an empty cell that might mean nobody filled it in.

14) What a Format Never Changes

Everything in this list reads the value, not the display:

• SUM, AVERAGE and every other function. The Amount column totals 17,495,900 whether it is displayed as 17,495,900, 17,496 or 17.5.
• Sorting and filtering. They order by value. The filter dropdown lists the formatted text, which is why a scaled column shows a list of duplicates — three different jobs all listed as 0.1M — but the sort itself is exact.
• Comparisons and lookups. =A2=1750 is TRUE; =A2="1,750" is FALSE. The comma exists only on screen.
• Charts. A series plots values. The axis has its own number format, set separately, which is why a chart can disagree with the table beside it.
• Power Query and pivot tables. Both read the underlying values. A pivot's own number formatting is set on the field, not inherited from the source cells.

And the one exception, which matters more than the rest of the list put together:

Save As CSV writes what the cell displays. A column formatted to no decimals exports rounded. A scaled column exports the scaled number. This is the single place where a formatting decision leaks out of the workbook and becomes data in a file somebody else will load — and it is why an export destined for another system should be built from raw, unformatted cells, or from TEXT you wrote deliberately.

Related, and permanent: File ▸ Options ▸ Advanced ▸ Set precision as displayed rewrites every stored value in the workbook to match its display, once, irreversibly. It is the only checkbox in Excel that deletes data as a feature. Leave it alone.

15) When the Format Is Not Enough: TEXT

A number format applies to a cell. The moment a number is joined into a sentence, the format is gone:

="Total: " & SUM(C2:C11)

That returns Total: 17495900 — the raw value, unformatted, because concatenation reads values.

TEXT applies a format string inside a formula and returns text:

="Total: " & TEXT(SUM(C2:C11), "#,##0.0,,""M""")   →  Total: 17.5M
="Week to " & TEXT(46252, "ddd d mmm")            →  Week to Tue 18 Aug
=TEXT(SUM(E2:E11), "[h]:mm") & " billable"        →  75:00 billable

The format strings are identical to the ones in the dialog; the quotes inside them have to be doubled because they are already inside a formula's quotes.

The rule of thumb: TEXT for labels, titles and messages; cell formatting for columns of numbers. A TEXT result is text — it will not sum, it sorts alphabetically, and a column of it is a column you cannot calculate with. Every so often that is precisely what you want, and the rest of the time it is a bug that takes an afternoon to find.

One caution for workbooks that cross borders: format codes inside TEXT are localised. In a Spanish Excel the year is aaaa, not yyyy, and =TEXT(A2,"yyyy") returns #¡VALOR! there. Cell formats survive the trip because they are stored in a neutral form; TEXT arguments are strings you typed, and they do not.

16) Seven Things That Bite

1. Two sections instead of three. #,##0;#,##0 strips the minus sign off every negative. On this sheet that is 131,670 of overruns displayed as gains, in a column that still totals 857,730 correctly.
2. The scaled column's smallest row. #,##0, shows the 1,750 job as 2. Check what your scale does to your smallest value before shipping, not after somebody asks why a customer shows zero revenue.
3. m after h is minutes. hh:mm is a time. mm:ss is minutes and seconds. A format of mm alone, with no hours near it, is a month — and will cheerfully display 08 for every time on the sheet.
4. h:mm on a total. Any sum of times over 24 hours wraps. [h]:mm is the fix and there is no downside to using it everywhere totals appear.
5. Percent applied after the fact. Formatting an existing 18.4 as a percentage gives 1840%. The values were never fractions; the format is not the thing to change.
6. Leading zeros that are only on screen. 000000 is a display. Lookups, COUNTIF criteria and comparisons all use the value, so "002041" finds nothing.
7. The formats pile up. Every format you invent is stored in the workbook, and pasting from other files brings theirs along. In a workbook that has been round the office a few times you will eventually meet "Too many different cell formats" — Excel's cap on distinct cell-format combinations — at which point the fix is a clear-formats pass over the junk, not a bigger format string.

17) What the Formats Are Worth on This Sheet

Four numbers, all from the same ten rows.

75:00 against 3:00. Two square brackets. Without them the week's billable time — seventy-five hours, most of a full-time week and a half — appears on the report as three hours. Nobody queries a total that looks like a plausible number.

12,750,000 against 12.8M against 12,750. One value, three columns of a board pack, all correct, none of them equal to each other. Which one is on screen is a formatting decision; which one lands in the CSV somebody imports next week is the same formatting decision, which is a much bigger thing than it looks.

1,750 against 2. The smallest job on the sheet, scaled into near-invisibility by a format chosen for the biggest one.

131,670 against 857,730. The overruns and the net. A two-section format that drops the minus sign leaves every one of those five negative rows reading as a gain, while the total quietly stays right — a sheet that is correct in aggregate and wrong on every line that matters.

None of this is difficult, and none of it is even work. It is a dialog, a box, and a few characters typed once into the column that everybody reads and nobody checks.

18) Mini Exercises

Use the ten jobs above.

1. The mask. Format E2 as h:mm, then click it and write down what the formula bar says. Explain in one sentence why those two things are both correct.
2. Scale it. Apply #,##0, to the Amount column and list every row whose display is now misleading. Then write a format that scales the millions and leaves the small jobs readable.
3. Four sections. Write the format that shows variances as 132,000, overruns as red (18,450), and exact-quote jobs as —. Then delete the third section and say what changes.
4. The missing sign. Apply #,##0;#,##0 to the Variance column. Give the total, give the sum of the rows as displayed, and explain the gap.
5. The total that wraps. Put =SUM(E2:E11) under the Time column and format it h:mm. Then [h]:mm. Then =SUM(E2:E11)*24 as a plain number. Say which of the three you would put next to an hourly rate.
6. Percent, both ways. In one empty cell type 0.184 and format it 0.0%. In another, type 18.4 and format it 0.0%. Explain the difference without using the word "format".
7. The lookup that fails. Format a cell holding 2041 as 000000, then write =XLOOKUP("002041", …) against it. Say what you get and give two different fixes, one for a quantity and one for an identifier.
8. Into a sentence. Write a single formula returning Week to Tue 18 Aug: 75:00 across 10 jobs, 17.5M invoiced, taking every number from the sheet.

Summary

A number format is a mask. The cell holds 0.3125; the screen says 7:30; SUM adds 0.3125 and always will. Nothing you type into the Type box changes a value, which is what makes formatting safe to experiment with and what makes a bad format so hard to catch — the arithmetic underneath stays right while the page in front of you goes wrong.

The mechanics are small. Three placeholders: 0 forces a digit, # drops it, ? pads it with a space. A comma between digits separates thousands and a comma after them divides by a thousand. Four sections in a fixed order — positive, negative, zero, text — and the negative section has to supply its own minus sign, or the column stops having negatives in it. Square brackets carry colours and up to two conditions, which is enough to put k and M scales in one column.

Dates and times are the same idea over numbers that count days, and they hide the one trap this article is named after: h means hours within a day, so a week of work totals to 3:00 until you write [h]:mm and it becomes 75:00.

And then the boundary, which is where the real mistakes live. Formatting is display; lookups, comparisons, sorts and every function read the value underneath. A leading zero you added with 000000 is not in the data. A percentage you displayed is not a fraction unless it already was. And the one moment the display becomes data — Save As CSV — is the moment to check what your columns are actually showing, because whatever it is, that is now the file.