Excel VBA Format: Format Cells, Dates, and Numbers

In Excel VBA, “format” can mean either converting a value to display text with the Format function or changing how a worksheet cell displays its value with the NumberFormat property. Use Format when you need a text string; use Range.NumberFormat when you want the cell to keep its underlying number or date while displaying it differently.

What “format” means in Excel VBA

VBA and Excel have related but distinct formatting tools. The VBA Format function takes a value and a format pattern, then returns a string. For example, it can turn a numeric amount into text that looks like currency. By contrast, a cell’s number format is a display rule stored with the cell: the cell still contains its numeric value, so Excel can continue to calculate with it.

This difference matters in everyday macros. If a cell contains the number 1250 and its number format is set to currency, Excel can still add it to other amounts. If a macro converts 1250 to the text string “$1,250.00” and writes that string to a cell, calculations, sorting, and numeric comparisons may behave differently because the result is text.

Choose Format(value, pattern) to create a formatted string for a message, label, filename, or report heading.
Choose Range.NumberFormat to control how a cell or range appears without changing its numeric value.
Use other cell properties, such as Font.Bold or Interior.Color, for appearance that is not a number, date, or time format.

Using the VBA Format function

The basic syntax is Format(Expression, [Format]). The expression is the value to format; the optional pattern describes the appearance of the returned string. The function does not apply formatting to a worksheet cell by itself. You must assign its result somewhere if you want to use it.

For example, this procedure creates a currency-looking string. The result uses the operating system’s regional conventions for separators and currency display, so the punctuation or symbol may differ between computers.

Sub MakeAmountLabel()
    Dim amount As Currency
    Dim labelText As String

    ' Format a number as text for a label.
    amount = 1234.5
    labelText = Format(amount, "$#,##0.00")
    MsgBox labelText
End Sub

With common English-language regional settings, the message is $1,234.50. The result is a string, not a Currency value. Keep the original numeric variable for calculations and use the formatted string only where text is wanted.

VBA supports named formats as well as custom patterns. Common named formats include General Number, Currency, Fixed, Standard, Percent, Scientific, Short Date, and Long Date. For consistent output in a report, a custom pattern is often clearer than relying on a named format whose appearance may vary with regional settings.

Formatting worksheet cells with NumberFormat

To format cells, set the NumberFormat property on a Range. The pattern is an Excel custom number format, and the cell’s stored value remains numeric. For example, the following macro formats three columns on a worksheet named Report. It assumes amounts are in column B, dates in C, and percentages in D.

Before running any macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.

Sub FormatReport()
    Dim ws As Worksheet

    ' Apply display formats without changing cell values.
    Set ws = ThisWorkbook.Worksheets("Report")
    ws.Range("B2:B100").NumberFormat = "$#,##0.00;[Red]-$#,##0.00"
    ws.Range("C2:C100").NumberFormat = "yyyy-mm-dd"
    ws.Range("D2:D100").NumberFormat = "0.0%"
End Sub

The first format section displays positive amounts with two decimal places; the second displays negative amounts in red with a minus sign. The date column uses a year-month-day display, and the percentage column shows one decimal place. A value of 0.125 in column D appears as 12.5%, not 0.1%; Excel stores percentages as numbers such as 0.125 and the percent format displays them multiplied by 100.

For a single cell, the same property can be set on a direct reference such as Worksheets("Report").Range("B2").NumberFormat. To apply a font or fill change, use a different property; number formats do not make text bold or change a cell’s background color.

Number, date, and time format patterns

Excel number formats can control decimal places, digit grouping, currency symbols, percent signs, and the display of zero or negative values. A semicolon separates format sections. A pattern can specify up to four sections, in order: positive numbers, negative numbers, zero values, and text. For example, a pattern that displays negatives in red or shows zero as a dash changes only the display, not the stored data.

0 forces a digit to appear, while # shows a digit only when needed. Thus 00000 can display 42 as 00042, useful for a fixed-width identifier when the value is numeric.
0.00 displays two decimal places, rounding the displayed result as needed. It does not round the underlying value.
0% displays a number as a percentage with no decimal places; 0.0% shows one decimal place.
Date patterns such as dd-mmm-yyyy display a date as day, abbreviated month, and year. The cell must contain a valid Excel date serial for this to show as a date.

Time patterns deserve special care because the format tokens differ between Excel cell formats and VBA’s Format function. In Excel cell number formats, mm commonly represents minutes when used in a time context, while m or mm can represent a month in a date context. In VBA’s Format function, use nn for minutes; mm means month. For example, a VBA pattern such as hh:nn:ss represents hours, minutes, and seconds. Copying a pattern between VBA and Excel without checking this distinction can produce a plausible but incorrect result.

Choosing between Format and NumberFormat

Decide whether you need text or a formatted cell. If a macro builds a sentence such as “Report created on November 2, 2026,” use the VBA function to create the date text. If it writes a date into a worksheet column that should remain sortable and usable in date calculations, write the date value and set the cell’s number format instead.

For example, a report heading can use Format(Date, "mmmm d, yyyy") to create a string for a title. A date stored in a worksheet cell should generally remain a date value, with Range.NumberFormat controlling whether it appears as 2026-11-02 or Nov 2, 2026. Those displays can represent the same underlying date.

There is no single best pattern for every workbook. Consider who will read the workbook, whether the displayed values need to be consistent across regions, and whether users will filter, sort, or calculate with the data. A format that looks attractive but turns values into text can undermine later analysis.

Common formatting problems and how to avoid them

A frequent source of confusion is assuming that formatting changes the value. Setting a cell to show two decimal places does not necessarily round the stored number. If a calculation uses the full stored value, it may use more precision than the cell visibly shows. If the calculation itself must round, use an appropriate rounding operation in the calculation rather than relying on the display format.

Another common issue is applying a date format to text that merely looks like a date. Number formatting cannot reliably convert arbitrary text into a true date value. Check the cell’s underlying value and convert the input to a valid date before applying a display pattern. Similarly, a value stored as text will not become a numeric amount just because the cell is assigned a currency format.

Format seems ignored: Check whether the cells contain text, whether a conditional format overrides the appearance, and whether the macro targets the intended worksheet and range.
Dates show as numbers: Excel dates are stored as serial numbers. Apply a date number format to valid date values, and verify that the cells are not text.
Unexpected commas or decimal marks: Regional settings affect separators and currency conventions. Test the result on the computers that will use the workbook.
Leading zeros disappear: Numeric formats can display leading zeros, but IDs that are fundamentally text may be better stored as text so their identity is preserved.
The macro changes the wrong cells: Qualify ranges with a worksheet variable instead of relying on whichever sheet happens to be active.

Running VBA and saving the right file

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On Mac, Option+F11 is the common shortcut; some keyboards also require Fn. You can also use the Developer tab’s Visual Basic command. If the Developer tab is hidden on Windows, enable it through File > Options > Customize Ribbon and select Developer. On Mac, use Excel > Settings > Ribbon & Toolbar and enable Developer.

Paste a procedure into a standard module in the Visual Basic Editor, then run it while the intended workbook is open. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros; use the desktop Excel application for VBA.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) or another format that supports macros. Saving as the regular .xlsx format removes VBA code. An Excel Binary Workbook (.xlsb) can also store macros, while an Excel add-in (.xlam) is used to distribute reusable code. File format and macro security are separate concerns: only enable macros in files from sources you trust, and keep a backup before running code that edits a workbook.

Alternatives to VBA formatting

If the task is a one-time visual change, the Excel ribbon may be simpler than a macro. Select the cells and use Home > Number Format to choose a built-in format, or use Home > Number Format > More Number Formats to create a custom number format. For appearance that depends on cell values, use Home > Conditional Formatting rather than writing a macro that repeatedly changes colors.

For recurring, rule-based formatting across many sheets or files, VBA can save time and make the process repeatable. However, a macro is not always the best tool for a shared workbook: users may have macros disabled, Excel for the web does not run VBA, and a macro can modify many cells quickly. Document what a macro changes and use a copy for initial runs.

Frequently Asked Questions

What is the Excel VBA format function?
The VBA Format function converts an expression into a formatted string, using a pattern such as #,##0.00 or a date pattern. It does not directly format a worksheet cell.

How do I format a cell using VBA?
Set the range’s NumberFormat property, for example by assigning "0.00%" to a percentage range. This changes how valid numeric values display while retaining their underlying values.

What is the difference between NumberFormat and Format?
NumberFormat is a property of an Excel range and controls cell display. Format is a VBA function that returns text. Use the former for worksheet values you still need to calculate with.

Why does VBA Format show the wrong month or minutes?
VBA and Excel cell formats do not use identical tokens. In VBA’s Format pattern, nn represents minutes and mm represents months. Excel cell formats use their own date and time context rules.

Can Excel for the web run VBA macros?
No. Excel for the web can display and edit workbook content, but VBA macros must be created, edited, and run in desktop Excel. Use a macro-enabled file format such as .xlsm to retain the code.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.