A number format is the display layer of a workbook. It sets what a cell shows on screen, on a printout and in a PDF export. It has no effect on the value the cell holds. A cell displaying 13m from a value of 12,500,000 still stores 12500000, and that stored number is what every SUM, chart, lookup, pivot and export uses.

Most finance users learn custom formats by copying one from a colleague. The approach holds until a report needs a nil shown as a dash, negatives in brackets and a variance column a reader can scan. At that point the structure behind the codes earns its place, because it turns a copied string into something you can build.

This guide covers the four sections of a custom format, the scaling codes for thousands and millions, the accounting conventions for negatives and zeroes, and the cases where a helper column does the job better than a format.

Formats Change the Display, Never the Value

Every other rule here follows from one distinction. A number format is a property of a cell, sitting alongside its font and its fill. It describes how Excel should draw the value. The value itself sits underneath, untouched, at full precision.

Click a cell showing 12.5m and the formula bar shows the complete number. Copy the column as values and the full numbers arrive. Sort the column and Excel sorts on the stored values. Chart it and the chart plots the stored values. Export to CSV and the stored values leave the workbook.

That behaviour is what makes the display layer safe in a financial report. A reader who doubts a figure clicks it and sees the value, a reviewer who copies the schedule gets the unrounded numbers, and the format stays a presentation choice.

The Four Sections of a Custom Format

A custom format string can carry up to four sections, separated by semicolons. The sections are read in a fixed order, and each one controls a different kind of cell content.

The text section applies to a value that is text, so a cell holding the word Forecast follows the fourth section while a cell holding a number follows the first three.

Build these on the Custom page of the Format Cells dialog, reached with Ctrl+1 on Windows or Command+1 on a Mac. The Sample box previews the result as you type. Two habits save time: type the code in stages and watch the sample box, and keep the codes in a template workbook so every schedule shares the same conventions.

A few rules govern what belongs inside a section. Digits use 0, # and ?. A zero shows a digit and pads with a zero when there is none. A hash shows a digit and shows nothing when there is none. A comma inside the digit sequence groups thousands, and most letters need quotation marks, because unquoted h, m, s, d and y carry date and time meanings.

Scaling to Thousands and Millions

A comma placed after the last placeholder divides the displayed value by a thousand, and each additional comma divides again. Two commas therefore scale a figure to millions, and the code scales only the display.

The comma still groups digits when it sits between placeholders. A format of #,##0,, shows a value in millions with the readable digits grouped, which matters when the same code lands on numbers that never reach seven figures.

A suffix is added in double quotes. That is how a k, an m or a currency word attaches to a scaled figure.

The last row is the trap. A small figure pushed to millions displays as 0.0m and tells the reader nothing, so check the smallest figure a column will hold before choosing the scale.

Rounding deserves its own sentence. A value of 12,500,000 under #,##0,,"m" prints 13m because the zero placeholder rounds to the nearest whole number, and the value underneath is unchanged. Where a reader might add the figures in their head, use one decimal place and print 12.5m.

Brackets, Dashes and Red for Negatives

Accounting convention shows a negative figure in brackets. Excel's built-in Accounting format does this, and a custom format reproduces it in a code you control.

Wrapping the second section in brackets gives the convention. A loss of 4,500 prints as (4,500), and the stored value stays negative, so every formula reading that cell still receives a negative number.

A third section handles zero. A quoted dash shows a nil as a dash, which keeps a column of real figures legible, and an empty third section hides zeroes for schedules where a nil line is noise.

Colour is added with a code at the start of a section, and Excel accepts eight named colours there plus a numbered code for the rest of the palette.

Hiding content uses the same structure. An empty zero section hides zeroes and nothing else, an empty text section hides text entries, and a format of three semicolons hides everything in the cell while the value stays in place and every calculation keeps using it. That last code conceals a figure only from a reader who has not clicked the cell.

Text, Percentages, Currency and Dates

Text in a cell is controlled by the section that follows the third semicolon, and the @ symbol carries the entry through the format. A prefix written into the code, such as the word Actual, works from the same section, and an @ on its own displays text exactly as typed.

Percentages use the % code, which multiplies the displayed value by a hundred and adds the sign. One decimal place comes from 0.0%, two from 0.00%. The stored value stays a fraction, so 0.075 displays as 7.5% and reads as 0.075 in the formula bar.

Currency is mostly a literal character. The dollar sign works without quotation marks, so $#,##0 and "$"#,##0 both print a dollar sign beside a grouped number. Excel also accepts a locale-tagged currency in square brackets, which pins a symbol and a regional convention to the code regardless of the machine the workbook is opened on.

Dates are where formats and regional settings collide. A custom format of dd/mm/yyyy fixes the display to the Australian convention of day, month and year, which matters when a workbook is opened on a machine whose default short date is mm/dd/yyyy. The underlying date is a serial number, so the format decides what appears and the sort order stays chronological either way.

Two date notes are worth keeping. A date that arrives from a system export as text will not respond to any date format, because the cell holds a string rather than a date serial number. And the letter m means months in most positions and minutes when it follows an h code, so h:mm prints hours and minutes while mmm-yy prints a month and a two-digit year.

A Variance Column That Reads at a Glance

A variance column has one job beyond being accurate. The reader needs to know the direction of the number before reading the amount.

The format for that job carries a plus sign in the positive section, brackets in the negative section and a dash for nil. A variance of 8,200 over budget prints as +8,200, a shortfall of 3,100 prints as (3,100), and a line with no variance shows a dash that stops drawing the eye.

Red suits the negative section where the reader scans for exceptions. A column where every second figure is red trains the reader to ignore the colour, which costs more than the colour buys.

Percentages take the same structure. A favourable movement of 4.2% prints with a plus sign, an adverse movement prints as (4.2%), and both are driven by the stored decimal in the cell.

The sign convention is a decision the format cannot make. A positive cost variance is usually bad news and a positive revenue variance is usually good news, so the schedule and the commentary need a shared rule, written into the column heading.

When a Helper Column Beats a Format

A custom format is the right tool for display. It becomes the wrong tool the moment the value has to behave like data.

The clearest case is a sort. Formatting a column to show whole thousands leaves the stored values unchanged, so the sort orders full precision numbers and the list appears to be in the wrong order to the reader.

The second case is text. A format is not a value, so it cannot be joined into a sentence, a chart title or a Word table. Where a formatted figure has to appear inside narrative text, the TEXT function builds the string from the value and the format code.

The third case is a total that has to foot on its own printed page. Where a schedule must add up in the displayed units, the underlying figures need rounding with ROUND in a helper column, and the total needs to be the sum of those rounded figures. Label the column as rounded, because the model total and the rounded total differ on purpose.

The fourth case is a rule that goes beyond sign and magnitude. A custom format handles four cases, which are positive, negative, zero and text. Anything richer, such as a scale bar, an icon or a word such as ahead or behind, needs conditional formatting, a lookup table or a helper column that returns a label.

The fifth case is the data that leaves Excel. Formats travel with a workbook and disappear in a CSV export or a database load, so wherever a figure has to arrive intact somewhere else, keep the rounded number in a helper column and send that.

Formats and the Numbers in a Board Pack

A pack reporting in millions will show figures that appear not to add up, and the cause is display rounding.

Three divisions each reporting 1,250,000 with a format of #,##0.0,,"m" print as 1.3m, 1.3m and 1.3m. The group total of 3,750,000 prints as 3.8m. The displayed parts sum to 3.9m and the displayed total says 3.8m, so a director with a calculator will find the difference.

Three responses work. Leave the rounding as a display choice, note in the pack that figures are rounded, and let the model carry the accuracy. Round the figures in a helper column so the parts add to the total on the page, and disclose the treatment. Or report with an extra decimal place so the difference falls below the level anyone checks.

The response that fails is using a format to imply precision the workbook does not hold. A figure shown to the dollar in one column and in millions in the next raises a question about every other number in the pack.

Two limits are worth stating plainly. A format cannot change a stored value, and it cannot stop a reader from clicking a cell and seeing what is underneath.

The codes below cover most of what a financial schedule needs, and they are worth assembling once in a template workbook.

' Money, scaled to the audience
#,##0                 12,500,000
#,##0,"k"             12,500k
#,##0,,"m"            13m
#,##0.0,,"m"          12.5m
' Accounting negatives
#,##0;(#,##0)         4,500 shows as (4,500)
#,##0;[Red](#,##0)    negatives in red brackets
#,##0;(#,##0);"-"     10,000, (4,200), -
' Variance, signed, with a dash for nil
+#,##0;(#,##0);"-"
+0.0%;(0.0%);"-"
' Percentages and currency
0.0%
"$"#,##0;("$"#,##0)
' Dates, Australian convention
dd/mm/yyyy
d mmm yyyy
mmm-yy
' Text control and hidden content
@
0;-0;;@
;;;

Two habits keep the display layer safe in a report. Leave the value layer untouched, so a format is the only thing applied to a figure, and round in a helper column whenever the rounding has to be real. Then design the display layer once in a template, so money, percentages, dates and variances look the same in every schedule.

Related reading

• Financial Modelling Best Practices: Build Robust Models
• 10 Time-Saving Excel Formulas for Managers
• Sales Variance Analysis and Forecasting for E-Commerce