๐Ÿš€The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more โ†’
Back to Blog

VBA NumberFormat in Excel โ€” Change How a Number Displays Without Changing Its Value

|

VBA NumberFormat in Excel โ€” Change How a Number Displays Without Changing Its Value

TL;DR โ€” Range.NumberFormat is the display layer: it changes how a stored number reads and never touches the number itself. Range("A1").Value = 5.4999 with Range("A1").NumberFormat = "0" shows 5 on screen โ€” but A1.Value is still 5.4999, and A1 + A1 is 10.9998. NumberFormat is not rounding. This is the layer where "appearance versus value" actually costs you money, because a date is just a serial number in a costume, and NumberFormat is the costume โ€” not the number.

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Report")
ws.Range("A1").Value = 5.4999
ws.Range("A1").NumberFormat = "0"           ' shows 5...
Debug.Print ws.Range("A1").Value            ' ...but the value is still 5.4999

Of the three formatting layers โ€” Font for the text, Interior for the fill, and NumberFormat for the display โ€” this is the one that fools people, because the disguise is convincing. A red font never makes anyone think the value changed. A cell that shows 5 absolutely does. This guide is built around that single fact: NumberFormat changes what you see, never what is stored, and once you hold that, dates, currency, percentages and the classic "my totals don't add up" bug all make sense.

What you'll learn

  • The mental model โ€” NumberFormat is the display layer, and display is not value
  • The rule that matters most โ€” it is not rounding, and the stored number is unchanged
  • Why a date is a serial number wearing a costume
  • NumberFormat (the cell property) versus the Format function (a string)
  • The format code language โ€” #, 0, the four sections, currency and color
  • The locale trap โ€” NumberFormat versus NumberFormatLocal

The mental model: NumberFormat is the display layer

Every cell stores a value and, separately, a rule for how to draw that value. NumberFormat is that rule. Setting it to "0.00", "$#,##0", "yyyy-mm-dd" or "0%" changes the presentation โ€” the glyphs Excel paints โ€” while the underlying number sits untouched in Range.Value. Think of the cell as holding a number, and NumberFormat as a lens you put in front of it. Change the lens and the view changes; the number behind it does not move.

That separation is the whole topic. It is why you can show the same 0.5 as 50%, 0.50, or 1/2 without ever changing the value that formulas use โ€” and why hiding decimals for display does not make them go away.

The rule that matters most: it is not rounding

Say it once and never forget it: NumberFormat does not round the stored value. It rounds the picture.

ws.Range("A1").Value = 5.4999
ws.Range("A2").Value = 5.4999
ws.Range("A1:A2").NumberFormat = "0"        ' both display as 5
ws.Range("A3").Formula = "=A1+A2"           ' displays 11? No - it shows 10.9998 rounded to display...
' The true sum is 10.9998. Formatting hid the decimals; it did not remove them.

This is the single most common NumberFormat bug: format a column to "0" to "clean it up," then watch a total come out one off because every cell still carries decimals the eye can't see. If you need the value itself to actually be 5, you must change the value, not the format:

ws.Range("A1").Value = WorksheetFunction.Round(ws.Range("A1").Value, 0)   ' now it really is 5

Rule of thumb: format when you only need it to look right; Round when the number must be right. Reports that must foot to the penny round the values; dashboards that just need to read cleanly format the display. Confusing the two is how spreadsheets end up "off by a rounding error."

A date is a serial number wearing a costume

Dates make the appearance-versus-value split vivid. Excel stores a date as a serial number โ€” days since 1899-12-30 โ€” and a date "is" a date only because of its NumberFormat:

ws.Range("B1").Value = 45900                 ' just a number
ws.Range("B1").NumberFormat = "yyyy-mm-dd"   ' now it READS as a date - the number is still 45900

Two consequences fall straight out of this. First, to get a real, math-able date you must put a real date value in the cell (ws.Range("B1").Value = DateSerial(2025, 9, 15)) and then format it โ€” formatting alone on the wrong value shows a wrong date. Second, applying "yyyy-mm-dd" to a text string like "2025-09-15" does nothing useful: text has no serial number behind it, so there is no number to dress up. If dates arrive as text, convert them (CDate, or see VBA CStr for the reverse problem) before formatting. The costume only works over a real number.

NumberFormat versus the Format function

There are two things in VBA with "format" in the name, and mixing them up is a real source of bugs. They are opposites in an important way:

  • Range.NumberFormat is a property of the cell. It sets a persistent display rule and leaves the underlying number fully intact and editable. This is what you want on a worksheet.
  • Format(value, "pattern") is a function that returns a String. It is covered in VBA Format, and it is for building text โ€” a message box, a file name, a concatenated label โ€” not for formatting a cell.

The trap is using Format to put a "formatted number" into a cell:

ws.Range("C1").Value = Format(1234.5, "$#,##0.00")   ' writes the STRING "$1,234.50"
' C1 is now text. You cannot SUM it, sort it numerically, or chart it.

That cell looks formatted, but it now holds text, and its number is gone. The correct version keeps the number and sets the display rule:

ws.Range("C1").Value = 1234.5
ws.Range("C1").NumberFormat = "$#,##0.00"            ' displays $1,234.50, still a real number

The rule: NumberFormat for cells, Format for strings. If the result needs to stay a number, never route it through Format.

The format code language

Format codes are a small language, and four rules read almost all of them:

  • 0 is a forced digit, # is an optional one. "0.00" always shows two decimals (5 becomes 5.00); "#.##" shows up to two and drops trailing zeros (5 stays 5).
  • , groups thousands. "#,##0" turns 1234567 into 1,234,567.
  • Literal symbols pass through. $, %, spaces and text in quotes appear as written โ€” "$#,##0.00", "0%" (which also multiplies by 100 for display), "0.0 kg".
  • Semicolons split it into sections: positive;negative;zero;text. This is the powerful one:
' Positive black, negatives red in parentheses, zero as a dash, text in quotes shown literally.
ws.Range("D2:D100").NumberFormat = "#,##0.00;[Red](#,##0.00);\-;@"

[Red] (and other bracketed color names) sets the display color per section, and @ is the text placeholder. You rarely need all four sections, but knowing they exist lets you read any format string you find. For dates the tokens are d/m/y and h/m/s โ€” "yyyy-mm-dd hh:mm" โ€” with the quirk that m means month after y or d, and minute after h.

The locale trap: NumberFormat versus NumberFormatLocal

The last trap bites on shared workbooks. NumberFormat always uses US-English format codes โ€” , for thousands, . for the decimal, m/d/y for dates โ€” no matter what regional settings the user has. NumberFormatLocal uses the user's local symbols (in German, . groups thousands and , is the decimal).

ws.Range("E1").NumberFormat = "#,##0.00"        ' portable: same result on every machine
ws.Range("E2").NumberFormatLocal = "#.##0,00"   ' German-style codes - only right on a German locale

Write your macros against NumberFormat with US codes and they behave identically everywhere. Reach for NumberFormatLocal only when you are deliberately reading or matching what a user typed in their own locale. Feeding local-style codes into NumberFormat (or vice versa) is a subtle bug that works on your machine and breaks on a colleague's.

How ExcelMaster helps

NumberFormat hides more decisions than any other formatting layer: whether you need to round the value or just the display, whether a date cell holds a real serial or a text string, whether a "formatted" cell is secretly text from Format, and which of NumberFormat or NumberFormatLocal keeps the code portable. Each wrong turn returns a plausible-looking cell with a broken number behind it.

ExcelMaster lets you say what the numbers should look like. Ask it to "show column D as currency with red negatives" or "make the dates read as 2025-09-15," and it sets NumberFormat on the cell โ€” keeping the value a real, math-able number โ€” rounds with Round when the total actually has to change, and uses portable US format codes. You keep the workbook and the code; you skip the report that footed wrong because the decimals were only hidden, not gone.

Frequently asked questions

Does NumberFormat change the actual value of a cell in VBA?

No. Range.NumberFormat changes only how the stored number is displayed. A cell holding 5.4999 formatted as "0" shows 5 but still contains 5.4999, and formulas use the full value โ€” so sums can look "off by one." To change the value itself, use WorksheetFunction.Round(value, digits) and assign it back; formatting alone never rounds the stored number.

How do I format a cell as currency or a date in VBA?

Set the value, then the format. For currency, Range("A1").Value = 1234.5 then Range("A1").NumberFormat = "$#,##0.00". For a date, Range("B1").Value = DateSerial(2025, 9, 15) then Range("B1").NumberFormat = "yyyy-mm-dd". Setting the format alone on a text string does nothing, because there is no underlying number to display.

What is the difference between NumberFormat and the Format function?

Range.NumberFormat is a cell property that sets a display rule and keeps the value a real number. Format(value, "pattern") is a function that returns a String. Use NumberFormat on worksheet cells; use Format to build text for messages, file names or labels. Writing Format(...) into a cell turns it into text you can no longer sum or chart.

Why do my totals come out wrong after I format the numbers?

Because NumberFormat hides decimals without removing them. Formatting a column to "0" shows whole numbers, but each cell still stores its decimals, so the true total includes them and can differ from the sum of the displayed figures. If the total must match what is shown, round the values with WorksheetFunction.Round rather than only formatting them.

What is the difference between NumberFormat and NumberFormatLocal?

NumberFormat uses US-English format codes (comma for thousands, period for the decimal) on every machine, so it is portable. NumberFormatLocal uses the user's regional symbols, which differ by locale. Write macros against NumberFormat for consistent behaviour everywhere, and use NumberFormatLocal only when deliberately matching what a user sees in their own settings.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 โ€” last verified 2026-08-11.

Related guides: VBA Font ยท VBA Cell Color ยท VBA Format ยท VBA CStr ยท VBA Range