To make Excel display numbers with exactly two decimal places, set a cell or range’s VBA NumberFormat property to "0.00". For example, Worksheets("Sheet1").Range("B2:B20").NumberFormat = "0.00" changes how those cells appear without changing their stored values.
The simplest VBA macro
Use this macro when the amounts to format are in cells B2 through B20 on a worksheet named Sheet1. It applies the format to the entire range in one operation:
Sub FormatAmountsToTwoDecimals()
Dim ws As Worksheet
Dim amountRange As Range
' Set the worksheet and the cells that contain numeric amounts.
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set amountRange = ws.Range("B2:B20")
' Display every value with exactly two decimal places.
amountRange.NumberFormat = "0.00"
End Sub
Change the worksheet name and address to fit your workbook. The range can include constants, formulas, or blank cells; the format is assigned to every cell in it. If the sheet name does not exist in the workbook containing the macro, VBA will stop with an error, so check the spelling before running the procedure.
On Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, insert a standard module using Insert > Module, paste the code, and run the procedure from the editor. On Excel for Mac, Option+F11 opens the editor on many keyboards; you may need Fn+Option+F11 depending on your keyboard settings. You can also use the editor’s Run command.
What “two decimal places” means
A number format controls how a value is displayed in a worksheet. The format code 0.00 tells Excel to show at least one digit before the decimal point and exactly two digits after it. Thus 7 displays as 7.00, 7.4 as 7.40, and 7.456 as 7.46. Excel rounds the displayed result to fit the format, but it does not discard the extra precision in the cell’s underlying value.
That distinction matters when the cell is used in calculations. If B2 contains 7.456 and is formatted as 0.00, the screen shows 7.46, but a calculation using B2 can still use 7.456. The formula bar also generally shows the stored value or formula rather than merely the rounded display. Formatting is therefore appropriate when you want consistent presentation, not when you need to permanently round the data.
Number formats can be applied to cells containing formulas as well as typed numbers. If a formula returns 3.5, the format displays 3.50; it does not alter the formula or its result. A format is also separate from column width: if the column is too narrow, Excel may show number signs or another width-related display instead of the expected value. Widen the column if the formatted number does not fit.
Choosing the right number format
The best format depends on whether you need grouping separators, a currency symbol, or optional decimal places. These are common choices for ordinary numeric data:
0.00shows exactly two decimal places, without a thousands separator.#,##0.00adds grouping separators, such as 12,345.60 in a typical English-language display.0.##shows up to two decimal places but does not force trailing zeroes, so 7.5 stays 7.5 and 7 displays as 7.$#,##0.00adds a dollar sign and grouping separators. Use this only when a dollar sign is appropriate for the data.
For example, with the same range set in the macro above, replace "0.00" with "#,##0.00" if larger amounts should be easier to scan. A custom number format can also have separate sections for positive values, negative values, zero, and text, separated by semicolons. That is useful for specialized reports, but the straightforward two-decimal code is easier to maintain when all values should look alike.
Formats can affect how nonnumeric content appears too. Applying 0.00 to date cells can make Excel display their underlying serial numbers rather than dates. Apply the format only to the numeric cells you intend to present as decimal amounts, or set a date format separately for date columns.
Formatting is not the same as rounding
A frequent source of confusion is that seeing two digits does not mean the value has been rounded to two decimal places for later calculations. Formatting is reversible and affects presentation. If an amount of 1.239 is displayed as 1.24, a later sum can still include 1.239. This is often desirable: preserving precision avoids changing source data just to make a report look tidy.
If you genuinely need to replace the value with a rounded result, use Excel’s worksheet ROUND operation and understand that this changes the cell content. With a numeric value in B2 on Sheet1, the following macro rounds that value to two decimal places and writes the result back:
Sub RoundCellValueToTwoDecimals()
Dim ws As Worksheet
Dim targetCell As Range
' This example assumes B2 contains a numeric value.
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set targetCell = ws.Range("B2")
' Replace the stored value with Excel's rounded result.
targetCell.Value = Application.WorksheetFunction.Round(targetCell.Value, 2)
targetCell.NumberFormat = "0.00"
End Sub
Rounding the stored value and formatting it are separate actions in this example. The first changes the value; the second ensures that the result is displayed with two digits after the decimal. VBA’s built-in Round function uses banker’s rounding for exact halfway cases, which can produce a different result from Excel’s worksheet ROUND function. If you want the same rounding rule as the worksheet function, use Application.WorksheetFunction.Round as shown. Confirm the rule required for your work before changing a set of source values.
Applying the format to different ranges
For a single cell, use an address such as Range("B2").NumberFormat = "0.00". For a fixed block, assign the property to the whole range, as in the first macro. This is usually clearer and faster than looping through each cell because Excel can apply one formatting operation to the complete selection.
If only certain cells need the format, a loop can apply it conditionally. The following example assumes the amounts are in B2:B20 and formats numeric cells only:
Sub FormatNumericCellsOnly()
Dim ws As Worksheet
Dim cell As Range
' Check each cell in the specified amount range.
Set ws = ThisWorkbook.Worksheets("Sheet1")
For Each cell In ws.Range("B2:B20")
If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
cell.NumberFormat = "0.00"
End If
Next cell
End Sub
A loop is useful when cells need different treatment, but it is not necessary just to set the same number format across a continuous range. For a variable-sized list, you can determine the final row in VBA and construct a range that ends there. Choose the column carefully: a method that searches for the last used cell in one column will not necessarily find the final row of data if that column contains gaps or stops before other columns.
NumberFormat, NumberFormatLocal, and Excel settings
For reusable VBA, NumberFormat is generally the safer property because its format codes use VBA’s standard, language-independent notation. In that code, a period in 0.00 represents the decimal place even when a computer’s regional settings use a different decimal separator. Excel adapts the display to the user’s settings.
NumberFormatLocal uses the format-code conventions for the current Excel language and regional settings. It can be useful when code needs to work directly with a locally written format string, but a string copied from one locale may not behave as expected on another. Unless you specifically need a locale-dependent format code, use NumberFormat for a macro intended to be shared across computers.
Setting a number format in VBA corresponds to choosing a format through Excel’s cell-formatting controls; it does not change the workbook’s calculation settings. You can also set a format manually from the Home tab’s Number group or open Format Cells with Ctrl+1 on Windows. On Mac, Command+1 opens Format Cells in Excel. The exact visible decimal and separator symbols depend on the workbook and regional settings, while the VBA format code remains a concise way to repeat the same presentation consistently.
Common problems and safe macro use
If the code runs but the displayed result seems wrong, check the cell contents, selected range, and column width before changing the macro. A value entered as text may not behave like a numeric value, and a formula that returns text will not become a number just because a numeric format was applied. Also check for conditional formatting: a conditional rule can change a cell’s appearance and make the result seem inconsistent with the format you assigned.
- Verify that the range points to the intended worksheet and cells.
- Use
"0.00"for two mandatory decimal places; use"0.##"only if trailing zeroes should be omitted. - Check whether the value is numeric rather than text, and whether the cells contain dates.
- Widen a narrow column if the formatted value is not visible.
Before running a macro that changes a workbook, save a copy. VBA macros cannot be undone with Ctrl+Z, including formatting changes, so a backup is the simplest way to restore the original if the code targets the wrong cells. A workbook containing VBA should be saved in a macro-enabled format such as .xlsm; saving it as a standard .xlsx file does not preserve its VBA project. Run macros only when you understand their source and intended changes.
Frequently Asked Questions
How do I show two decimal places in every used cell? Set NumberFormat = "0.00" on the intended used range. Avoid applying it to the entire worksheet unless that is genuinely what you want: it can change how dates, identifiers, and other values appear. A targeted range is usually safer.
Does NumberFormat round the actual number? No. It rounds the displayed value to fit the format but preserves the underlying value. Use a rounding operation such as Excel’s ROUND function only if you intend to change the value used by later calculations.
How do I show a thousands separator as well? Use "#,##0.00" as the NumberFormat string. Excel displays grouping and decimal symbols according to the applicable regional settings.
Why does 5 display as 5.00? The zero placeholders in "0.00" require two digits after the decimal point, including zeroes. Use "0.##" if you want up to two decimal places without forced trailing zeroes.
Can I format a formula result with VBA? Yes. Assign NumberFormat to the cell or range containing the formula. The formula remains intact; only the displayed result changes.
Related
- Excel: Subtract the Same Value From Multiple Cells
- Excel Spreadsheet Keyboard Shortcuts: A Practical Guide
- Excel Data Validation Lists: Create and Manage Dropdowns
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Resize: Change a Range’s Size with VBA CodeIn Excel VBA, use the Resize property to refer to a range with a different number of rows…
- Left in Excel VBA: Syntax, Examples, and Common IssuesIn Excel VBA, Left returns a specified number of characters from the beginning of a string. For example,…
- Excel VBA Enum: How It Works and When to Use ItAn Excel VBA Enum is a named set of numeric constants: it lets code use readable labels such…
- Excel VBA Freeze Panes at a Specific CellYes. Activate the worksheet, select the cell that marks the top-left corner of the scrolling area, then set…
- Cannot Run a Macro in Excel? Fix Common CausesIf you cannot run a macro in Excel, first confirm that the workbook is saved in a macro-capable…
- Excel VBA Delete a Workbook: Safe Methods and CodeYes. Excel VBA can delete a workbook file from disk, but the workbook must be closed first: Workbook.Close…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.