To format cells as numbers with Excel VBA, assign a number-format code to the range’s NumberFormat property. For example, Range("B2:B100").NumberFormat = "#,##0.00" displays values with a thousands separator and two decimal places; it changes their appearance, not the values stored in those cells.
What NumberFormat Does
Excel stores a cell’s value separately from its number format. The value might be 1234.5, while the format determines whether Excel displays it as 1,234.50, $1,234.50, 123450%, or a date. VBA’s NumberFormat property applies that display instruction to one cell or a range of cells.
This distinction matters when a worksheet looks correct but calculations behave unexpectedly. Applying a number format will not turn text such as “1234.5” into a numeric value, and it will not change 0.125 into 12.5. It only tells Excel how to show the existing value. If you need to convert data, do that separately.
Number formats can be applied to individual cells, multiple ranges, entire columns, or a range held in a VBA variable. The same property is available whether you run a short macro from the Visual Basic Editor or include the statement in a larger procedure that imports, cleans, or reports data.
Apply a Number Format with a Macro
The essential syntax is someRange.NumberFormat = "format code". In the example below, the macro formats cells B2 through B100 on the active worksheet. Change the range to match your data. Before running any macro, save a copy of the workbook: macro changes cannot be reversed with Ctrl+Z.
Sub FormatAsNumber()
Dim target As Range
' Set the cells to format on the active worksheet.
Set target = ActiveSheet.Range("B2:B100")
target.NumberFormat = "#,##0.00"
End Sub
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the procedure into the module, and run it from the editor or the Macro dialog. Save a workbook that contains VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the code. Excel for Mac also supports VBA and the NumberFormat property, although the keyboard shortcut for opening the Visual Basic Editor differs by keyboard and Excel version. Excel for the web cannot run VBA macros; use the desktop app.
Choose a Format Code
A format code is a small pattern made from placeholders and symbols. The most useful starting points are:
0displays a whole number and shows a zero when the value rounds to zero.0.00displays two decimal places, adding trailing zeros where needed.#,##0displays whole numbers with a thousands separator where appropriate.#,##0.00combines a thousands separator with two decimal places.0.0%displays a value as a percentage with one decimal place. A stored value of 0.125 appears as 12.5%."$"#,##0.00displays a dollar sign and two decimal places. Choose a suitable currency symbol and format for the workbook’s intended locale.yyyy-mm-dddisplays a date in year-month-day order when the cell contains a valid Excel date value.
In a custom format, 0 is a required digit placeholder: it displays a zero if there is no digit in that position. The # placeholder displays a digit when one is present but does not add insignificant zeros. For example, with the value 7, 0.00 displays 7.00, while 0.## displays 7. These patterns affect display precision, not the stored number’s precision.
Excel also supports format sections for positive values, negative values, zero, and text, separated by semicolons. A custom format can therefore show negative values differently or suppress the display of zeros. Such patterns are useful in reports, but keep them understandable: hiding a zero or changing a negative number’s color can make data less obvious to someone reviewing the sheet.
Common Number-Formatting Examples
Use a format that reflects what the values mean, rather than applying the same pattern to every numeric-looking column. For example, counts usually need no decimal places, while measured quantities or prices may need decimals. The following procedure applies different formats to three columns on the active worksheet:
Sub FormatReportColumns()
Dim ws As Worksheet
' Use the active worksheet's report columns.
Set ws = ActiveSheet
ws.Range("B2:B100").NumberFormat = "#,##0"
ws.Range("C2:C100").NumberFormat = "0.00"
ws.Range("D2:D100").NumberFormat = "0.0%"
End Sub
Here, column B is suitable for counts, column C for values that need two decimal places, and column D for proportions stored as decimals. If D2 contains 0.25, the displayed result is 25.0%. If the source instead stores 25 to mean 25%, applying a percentage format would display 2500.0%; understand the stored scale before formatting.
For a whole column, a statement such as ws.Columns("B").NumberFormat = "0" applies the format to every cell in column B, including cells added later. That can be convenient for a fixed-purpose field, but it may affect headers, notes, or other content in that column. Restricting the range to the data rows is often safer.
Formatting Is Not the Same as Converting Text
A common misconception is that changing NumberFormat fixes numbers stored as text. It does not. If a cell contains the text string “00123”, applying 0 will not reliably turn that text into a number; the cell remains text, and arithmetic or sorting may not behave as expected. A green error indicator or left-aligned values can be clues, but neither is conclusive because alignment can be changed manually.
Before converting, inspect a few representative cells and consider whether leading zeros are meaningful. An account code such as 00123 is often an identifier, not a quantity, so converting it to the number 123 would lose information. For genuine numeric data, choose a conversion method that suits the source and regional settings. VBA’s CDbl, for example, interprets decimal and thousands separators according to the current locale; text imported with a different separator convention may need cleaning first.
Also take care not to overwrite formulas when converting values. A macro that assigns a converted result back to every cell in a range can replace formulas with fixed values. If formulas must remain, target only the imported text cells or work on a copy. Once values are numeric, set NumberFormat separately to control how they appear.
Format a Selected Range or a Changing Range
For a one-time action, you can format a selected range by referring to Selection, provided the selection is a range on the intended worksheet. For reusable procedures, an explicit worksheet reference is usually clearer: it avoids formatting the wrong sheet if the user has switched tabs. Qualifying a range with its worksheet also makes the macro easier to maintain.
When the number of data rows changes, determine the range from the worksheet instead of hard-coding its last row. For example, if column B has a header in B1 and a continuous list beneath it, the last used row can be found with Cells(Rows.Count, "B").End(xlUp).Row. The method depends on the layout: blank cells inside a list, totals below the data, or unrelated content in the same column can change what counts as the last row. Test the boundary logic on a copy before using it on important workbooks.
Another option is to format an Excel table column. Table ranges expand when new rows are added, and table formatting can carry into new rows. A macro can refer to a table’s data body range when it exists, but an empty table may not have a data body range. Consider that case if the macro runs before any records have been added.
NumberFormat and NumberFormatLocal
For most macros, NumberFormat is the appropriate property. It uses Excel’s format-code conventions, such as 0.00 or yyyy-mm-dd, and is generally preferable when code may run under different regional settings. Excel may display the resulting separators according to the user’s regional configuration.
NumberFormatLocal reads or sets a format using the language and regional conventions of the current Excel installation. This can be useful when a macro must match a format string produced for a particular local interface, but the same literal code may not be portable between locales. If a workbook will be shared internationally, test the macro in the target environments and avoid assuming that the characters users see in a cell are identical to the format-code characters.
Neither property changes the underlying value. If the goal is only to show a date or percentage differently, apply a suitable format. If the goal is to change a text value into a number, date serial, or other data type, use a conversion step and verify the result independently.
Common Problems and How to Fix Them
- The display does not change: Confirm the macro ran, the range points to the intended sheet, and the format string is valid. A cell may also be displaying text rather than a numeric value.
- Numbers appear as dates: A date is stored as a number in Excel. If a cell has a date format, the same underlying serial value is shown as a calendar date. Apply the intended numeric format to display the serial as a number.
- Decimals seem to disappear: The format may be set to
0or#,##0. The stored decimals may still be present; show them with a format such as0.00to check. - Values display as hashes: Excel may be unable to show a formatted date or number in the current column width. Widen the column or use a shorter display format; hashes do not by themselves mean the value is damaged.
- A percentage is 100 times too large: Percentage formats multiply the displayed value by 100. Check whether the stored value is 0.25 or 25 before using a percentage format.
- The code fails on a sheet reference: Check the worksheet name and whether the code is using the intended workbook.
ActiveSheetdepends on which sheet is active, whileThisWorkbookrefers to the workbook containing the VBA code.
Formatting may also be affected by conditional formatting, which can change how cells appear when rules are met. If a cell looks different from neighboring cells despite having the expected number format, inspect its conditional formatting rules as well as its regular cell format.
Frequently Asked Questions
How do I format a cell as a number with no decimals in VBA?
Set the cell or range’s NumberFormat property to "0", for example Range("A1").NumberFormat = "0". This controls display; it does not remove decimal places from the stored value.
How do I show two decimal places in Excel VBA?
Use the format code 0.00, such as Range("B2:B20").NumberFormat = "0.00". Values display with two decimal places, including trailing zeros, while their underlying values remain unchanged.
How do I add commas to a number using VBA?
Use a code such as #,##0 for whole numbers or #,##0.00 for two decimal places. Excel uses the appropriate regional display separators when showing the formatted value.
Does NumberFormat convert a number stored as text?
No. NumberFormat changes only the display format. Convert text to a numeric value separately, taking care with regional separators, leading zeros, and formulas that must be preserved.
Can Excel for the web run a VBA number-format macro?
No. Excel for the web can display workbook formatting but cannot run VBA macros. Open the workbook in the Excel desktop app for Windows or Mac to run VBA.
Related
- Excel Shortcut to Insert a Row: Windows and Mac
- Excel Shortcut to Move Between Sheets in a Workbook
- Excel Shortcut to Insert a Table Row on Windows and Mac
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Reference: Where to Find It and How to Use ItAn Excel VBA reference can mean either Microsoft’s documentation for Excel’s VBA object model or a library that…
- Excel VBA Find LookIn: What It Means and How to Use ItIn Excel VBA, LookIn is an argument of the Range.Find method that tells Excel what kind of content…
- Google Sheets Macros: How to Create, Run, and Edit ThemYes. You can create macros in Google Sheets using its built-in recorder: in a desktop web browser, choose…
- VBA Print Variable in a MsgBox: Syntax and ExamplesTo display a VBA variable in a message box, use MsgBox variableName, or combine a label and the…
- Access VBA: How to Delete Table Rows Safely and CorrectlyIn Access VBA, a “row” is usually a record in a table. To delete matching records, run a…
- Macro Recorder for Xbox: What Works and What Doesn’tIf you mean a device or feature that records a sequence of Xbox controller inputs and plays it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.