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
- Excel Template for Weekly Attendance: How to Build One
- Excel Template for Time Sheet: How to Make One
- Excel Template for Tracking Monthly Expenses
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Recorder: How to Record a MacroIn Excel for Microsoft 365 on Windows, go to Developer > Code > Record Macro, give the macro…
- MS Access VBA Enum: Syntax, Examples, and Best PracticesAn Enum in Microsoft Access VBA is a named set of numeric constants. Declare it at the top…
- Excel VBA VB Colors: Constants, RGB, and Cell FormattingIn Excel VBA, vbRed, vbBlue, and the other vb color constants are built-in names for eight basic colors.…
- VBA ArrayList: How to Create and Use It in Excel VBAIn VBA, an ArrayList is a resizable, zero-based list that you can create on Windows with CreateObject("System.Collections.ArrayList"). It…
- VBA Code for UserForm Data Entry: Build a Reliable FormTo enter records through an Excel VBA UserForm, create text boxes and a combo box on the form,…
- VBA AutoFilter for Tanaka: Filter Excel Rows by NameTo show only rows whose name is Tanaka, apply Excel’s AutoFilter to a range that includes its header…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.