To display a number as a percentage using a text function in Excel, use the TEXT function with a format code like "0.0%" or "0%"—for example, =TEXT(A1,"0.0%") converts the decimal 0.125 into the text string “12.5%”. This is different from simply applying the Percentage number format, which changes how the cell displays the value without altering the underlying data.
Why Use TEXT Instead of the Percentage Format?
Excel’s built-in Percentage format (Home > Number > Percent Style) is the quickest way to show a value as a percentage, but it only changes the display. The cell still contains the original decimal number, so calculations remain unaffected. The TEXT function, by contrast, converts the number into a text string that is no longer numeric. This is useful when you need to embed a percentage inside a sentence, a label, or a concatenated string, or when you want to control the exact number of decimal places shown regardless of the workbook’s default settings.
For example, if cell A1 contains 0.25, applying the Percent Style shows “25%” in the cell, but the cell still holds 0.25. If you use =TEXT(A1,"0%"), the result is the text “25%”—you can no longer use that cell in arithmetic, but you can combine it with other text using the ampersand (&) operator.
How the TEXT Function Works
The TEXT function takes two arguments: the value to format and the format code. The format code is a text string that uses the same formatting symbols as custom number formats. For percentages, the key symbol is the percent sign (%). When you include % in the format code, Excel multiplies the number by 100 and adds the percent symbol. For example:
=TEXT(0.125,"0%")returns “13%” (rounds to whole percent).=TEXT(0.125,"0.0%")returns “12.5%” (one decimal place).=TEXT(0.125,"0.00%")returns “12.50%” (two decimal places).
If the value is already a whole number that you want to treat as a percentage (e.g., 12.5 meaning 12.5%), you need to divide by 100 first or use a format code that includes the percent sign but also divides. Actually, the simplest approach is to divide by 100 in the function: =TEXT(A1/100,"0.0%"). Alternatively, you can use a custom format like 0.0"%" which just appends a percent symbol without multiplying by 100, but that produces text that is not a true percentage.
Format Codes for Percentages
You can customize the appearance of the percentage text using different format codes:
"0%"– rounds to the nearest whole percent."0.0%"– one decimal place."0.00%"– two decimal places."#,##0.0%"– adds thousands separators if the percentage is large (e.g., 1,234.5%)."0.0E+0%"– scientific notation for very large or small percentages (rarely used).
You can also include text in the format code, such as "0.0% increase", but that is usually better done with concatenation.
Practical Examples
Here are a few real-world scenarios where you might use the TEXT function with percentage format:
- Creating a dynamic label: If cell B2 contains the decimal 0.08 (8% tax rate), you can write
="Sales tax: "&TEXT(B2,"0.0%")to get “Sales tax: 8.0%”. - Building a report header: With a completion rate in C5, use
="Completion rate: "&TEXT(C5,"0%")to produce “Completion rate: 92%”. - Combining with other functions: You can nest TEXT inside other functions, e.g.,
=IF(A1>0.5,"Above target: "&TEXT(A1,"0%"),"Below target").
Remember that the result of TEXT is always text, so you cannot use it in further numeric calculations without converting it back (e.g., using VALUE).
Common Problems and Misconceptions
One frequent issue is that users expect the TEXT function to change the cell’s number format, but it doesn’t—it changes the value itself into text. Another misconception is that the percent sign in the format code is just decorative; actually, it multiplies the number by 100. For example, =TEXT(1,"0%") returns “100%”, not “1%”. If you want to display 1% from the number 1, you need to divide by 100: =TEXT(1/100,"0%").
Another common error is using a semicolon instead of a comma in the function arguments. In some regional settings, Excel expects a semicolon as the list separator, but in the default US English version, it’s a comma. Always use the separator that your Excel version uses; the formula will show a #NAME? error if you use the wrong one.
Also, be aware that the TEXT function can be locale-dependent. If you use a format code like “0.0%”, the decimal point might be displayed as a comma in some European locales. To force a specific decimal symbol, you can use the format code with a period, but Excel may override it based on system settings. For a truly locale-independent result, consider using the FIXED function or a custom VBA function.
Alternatives to TEXT for Percentage Display
If you don’t need the result to be text, the simplest alternative is to apply the Percentage number format directly to the cell (Home > Number > Percent Style or press Ctrl+Shift+% on Windows, Cmd+Shift+% on Mac). This keeps the numeric value intact, allowing you to use it in formulas. You can also use the NUMBERVALUE function to convert a text percentage back to a number, or use the VALUE function in older versions.
Another alternative is to use the CONCATENATE function or the & operator with a manually typed percent sign, but that requires you to multiply by 100 yourself. For example, =A1*100&"%" would give “12.5%” if A1 is 0.125, but this is less flexible because you lose control over decimal places without extra rounding functions.
Frequently Asked Questions
How do I format a percentage with two decimal places using TEXT?
Use the format code "0.00%". For example, =TEXT(0.1234,"0.00%") returns “12.34%”. The number of zeros after the decimal point determines the number of decimal places.
Why does my TEXT formula return #VALUE!?
This error usually occurs if the first argument is not a number or if the format code is not recognized. Make sure the cell contains a numeric value (not text) and that the format code is enclosed in double quotes. Also check that you are using the correct list separator (comma vs. semicolon).
Can I use TEXT to show a percentage without multiplying by 100?
Yes, if you want to display the number as-is with a percent sign, you can use a custom format like "0""%""" (which just appends a percent sign), but that is not a true percentage representation. The standard percent format always multiplies by 100. If you want to show 0.125 as “0.125%”, you would need to use =TEXT(A1,"0""%""") but that is unconventional and can confuse readers.
Does TEXT work in Excel for the web or Mac?
Yes, the TEXT function is available in Excel for Microsoft 365, Excel 2021, Excel 2019, and earlier versions for both Windows and Mac. The syntax is identical. On Mac, the keyboard shortcut for Percent Style is Cmd+Shift+%, but the TEXT function itself works the same.
How do I convert a text percentage back to a number?
Use the VALUE function or multiply by 1. For example, if cell A1 contains the text “12.5%”, you can use =VALUE(A1) to get 0.125. Alternatively, =A1*1 also converts text to a number if the text is a valid number. Note that VALUE will interpret the percent sign correctly.
Can I use TEXT inside a chart label or axis?
No, chart labels and axes use the cell’s number format, not the TEXT function. To change how a percentage appears in a chart, apply the Percentage number format to the source cells or use a custom number format in the axis options.
Related
- Excel Profit and Loss Template: Free Options
- Excel Power Query Wrap Text: How to Do It
- Excel Power Query XML: How to Import & Transform
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF With Dates: Criteria, Ranges, and ExamplesTo count dates in Excel, use COUNTIF with a date value for an exact match, or join a…
- SUBTOTAL Formula in Excel: How to Apply It Step by StepTo apply a subtotal formula in Excel, choose what you want to calculate and enter =SUBTOTAL(9,B2:B7) in the…
- Find Duplicates in Excel with COUNTIFTo find repeated values in Excel, enter a COUNTIF formula beside your list and fill it down: with…
- Excel Weekdays Between Dates: Formulas, Holidays, and TipsTo count weekdays between two dates in Excel, use =NETWORKDAYS(A2,B2), with the start date in A2 and the…
- Excel Formula for Date Range: Count Dates and MatchFor dates in A2:A100 and a start date in D2 and end date in E2, count dates in…
- Excel Named Ranges in Formulas: How to Use ThemYes. In Excel for Microsoft 365, type a defined name into a formula anywhere you would otherwise use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.