Excel VBA Format Cell as Percentage: Full Guide

VBA & macros · Updated November 22, 2026

To format a cell as a percentage in Excel VBA, set the cell’s NumberFormat property to "0.00%" (or "0%" for no decimals). For example, Range("A1").NumberFormat = "0.00%" displays the value 0.1234 as 12.34%. This simple line changes only the display format; the underlying value remains a decimal fraction, which is exactly how Excel stores percentages.

Why Percentages Are Stored as Decimals

Excel treats a percentage as a fraction of 1. So 25% is stored as 0.25, and 100% is stored as 1. When you apply a percentage format, Excel multiplies the underlying value by 100 and appends a percent sign for display. This is crucial for VBA: if you write a value like 25 into a cell and then format it as a percentage, it will display as 2500%. To display 25%, you must write 0.25 or use the Value property with a division by 100.

Basic VBA Code to Format a Cell

The most direct way is to assign a number format string to the NumberFormat property. Here are the common formats:

  • "0%" – whole number percentage (e.g., 0.25 → 25%)
  • "0.0%" – one decimal place (e.g., 0.253 → 25.3%)
  • "0.00%" – two decimal places (e.g., 0.2534 → 25.34%)
  • "0.00E+00%" – scientific notation with percent

To apply to a single cell:

Range("A1").NumberFormat = "0.00%"

To apply to a range:

Range("B2:B10").NumberFormat = "0.0%"

To apply to an entire column:

Columns("C").NumberFormat = "0%"

Remember to save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z.

Formatting a Cell That Contains a Formula

If a cell contains a formula that returns a decimal (e.g., =A1/B1), you can format that cell the same way. The format applies to the result, not the formula. For example, if A1=50 and B1=200, the formula returns 0.25, and with "0%" format it displays 25%.

Using the NumberFormatLocal Property

In some locales, the percent sign might be placed differently or a different decimal separator is used. NumberFormatLocal uses the user’s language settings. For instance, in German Excel, "0,00%" might be needed. However, NumberFormat is language-independent and is the safer choice for most macros. If you need to match the user’s regional settings, use NumberFormatLocal.

Common Mistakes and How to Avoid Them

One frequent error is writing a value like 25 and then formatting as percentage, expecting 25%. Instead, you get 2500% because 25 is 2500% of 1. Always divide by 100 or write the decimal. Another mistake is using Format() function to convert a value to a string with a percent sign – that changes the cell to text, breaking calculations. Use NumberFormat instead.

Also, if you apply a percentage format to a cell that already contains text, nothing changes visually. The format only affects numeric values.

Formatting a Cell Based on a Condition

You might want to format a cell as percentage only if it meets a condition. For example, if the value is between 0 and 1, apply percent format; otherwise, leave as general. Use an If statement:

If Range("A1").Value >= 0 And Range("A1").Value <= 1 Then
Range("A1").NumberFormat = "0%"
Else
Range("A1").NumberFormat = "General"
End If

This is useful when importing data that might contain both fractions and whole numbers.

Formatting a Cell as Percentage Using the User Interface vs. VBA

In the Excel interface, you click the Percent Style button in the Home tab or press Ctrl+Shift+% (Windows) or Cmd+Shift+% (Mac). That applies the default percent format (usually two decimals). VBA gives you more control over the number of decimals. The VBA equivalent of the Percent Style button is:

Range("A1").NumberFormat = "0.00%"

If you want to mimic the exact default, use "0.00%".

Frequently Asked Questions

How do I format a cell as percentage with no decimals?

Use NumberFormat = "0%". For example, 0.25 displays as 25%, and 0.253 displays as 25% (rounded).

How do I convert a number to a percentage in VBA without changing the cell format?

Multiply by 100 and concatenate a percent sign, but that creates text. Better to keep the numeric value and apply the format. If you need a string for display in a message box, use Format(0.25, "0%") which returns “25%”.

Why does my percentage show as 2500%?

Because the cell value is 25, not 0.25. Divide by 100 or enter the decimal. For example, if you have a number in A1 that you want to display as a percentage, use Range("A1").Value = Range("A1").Value / 100 before formatting.

Can I format a cell as percentage using VBA without changing the value?

Yes, the NumberFormat property only changes the display, not the underlying value. The value remains a decimal, so calculations still work correctly.

How do I format a cell as percentage in Excel for the web or Mac?

VBA is not supported in Excel for the web; you would need to use Office Scripts or manually format. On Mac, VBA works the same, but the keyboard shortcut for percent style is Cmd+Shift+%.

Alternatives to VBA for Formatting Percentages

If you don’t want to use VBA, you can use conditional formatting to automatically apply percentage format based on rules, or use the Format Cells dialog (Ctrl+1) to set the format manually. For dynamic formatting, you can also use the TEXT function in a formula, but that returns text, not a number. For most cases, the built-in formatting is sufficient.

Related

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.