The Excel formula to display a number as a percent is =TEXT(A1,"0.0%"), where A1 contains the decimal value (e.g., 0.25 becomes “25.0%”). For a built-in percent format without changing the underlying number, select the cell and press Ctrl+Shift+% (Windows) or Cmd+Shift+% (Mac).
Why Percent Formatting Matters in Excel
Excel stores percentages as decimal fractions: 25% is stored as 0.25, 100% as 1, and 150% as 1.5. When you type 25% into a cell, Excel automatically converts it to 0.25 and applies a percent number format. But when you build formulas that output text—like a label or a concatenated string—you need the TEXT function to convert that decimal into a readable percentage. Without TEXT, =A1&"%" would show 0.25% instead of 25%.
The TEXT Function for Percentages
The TEXT function converts a number to text with a specified format. Its syntax is =TEXT(value, format_text). For percentages, the format codes are:
0%– rounds to whole percent (0.256 → “26%”)0.0%– one decimal place (0.256 → “25.6%”)0.00%– two decimals (0.256 → “25.60%”)#,##0.0%– adds thousands separator for large percents (1234.5% → “1,234.5%”)
Example: With value 0.125 in A1, =TEXT(A1,"0.0%") returns the text string “12.5%”. Note that the result is text, not a number, so it cannot be used in further arithmetic without converting back.
Using TEXT in Concatenated Strings
When building a sentence or label, TEXT is essential. Suppose you have sales growth in B2 (0.15) and you want to display “Growth: 15%”. Use:
=CONCATENATE("Growth: ", TEXT(B2,"0%"))
or the shorter ="Growth: "&TEXT(B2,"0%"). Both produce the same result. For multiple cells, say B2 and C2, use ="Growth: "&TEXT(B2,"0%")&" vs "&TEXT(C2,"0%").
Formatting Percentages Without Changing the Number
If you only need to display a percent, use Excel’s built-in number format instead of TEXT. Select the cells and press Ctrl+Shift+% (Windows) or Cmd+Shift+% (Mac). This applies the default percent format with no decimals (e.g., 0.25 shows as 25%). For more control, go to Home > Number > the small arrow at the bottom-right of the Number group, choose Percentage, and set the decimal places. This keeps the underlying value as a number, so you can still use it in calculations.
Common Problems and Fixes
Three issues appear often:
- Seeing 0% or 100% unexpectedly: If you type
25and format as percent, Excel treats it as 2500% because it assumes the raw number is a decimal. Always enter the decimal (0.25) or type the percent sign directly. - TEXT result not calculating: TEXT returns a text string, so
=TEXT(A1,"0%")*2gives a #VALUE! error. Use the original cell for math. - Negative percentages: TEXT handles negatives automatically (e.g., -0.1 → “-10%”). To show parentheses, use a custom format like
0%;[Red](0%)in the TEXT function:=TEXT(A1,"0%;[Red](0%)").
Alternatives to TEXT for Percentages
If you need a percent in a formula but also want to keep it numeric, consider these:
- Use the number format: For display only, apply the percent format to the cell.
- Multiply by 100 and add &”%”:
=A1*100&"%"gives the same visible result but loses the formatting (e.g., 0.25 → “25%”). This works but doesn’t round to a specific decimal place unless you also use ROUND:=ROUND(A1*100,1)&"%". - Custom number format: Use a format like
0.0"%"in the Format Cells dialog. This displays a percent symbol while keeping the cell numeric, but it’s not a formula—it’s a display setting.
Frequently Asked Questions
How do I convert a decimal to a percent in Excel without the TEXT function?
Multiply by 100 and add the percent sign: =A1*100&"%". This returns a text string. If you need a numeric value, just multiply by 100 and format the cell with the percent style (which will multiply by 100 again, so be careful).
Why does my TEXT formula show 0% for 0.5?
That happens if you use the format code 0% on a number that is already a percentage. For example, if A1 contains 0.5 and you apply =TEXT(A1,"0%"), Excel treats 0.5 as a decimal and multiplies by 100, giving 50%. If you see 0%, check that the cell contains a decimal like 0.005, which equals 0.5%.
Can I use TEXT to show a percentage with a plus sign for positive values?
Yes, use a custom format: =TEXT(A1,"+0%;-0%"). This shows a plus sign for positive numbers and a minus for negatives. For example, 0.15 → “+15%” and -0.05 → “-5%”.
Does the TEXT function work in Google Sheets?
Yes, Google Sheets has a TEXT function with similar syntax, but the format codes differ slightly. For example, Google Sheets uses 0.00% the same way, but some custom codes like [Red] are not supported. Use =TEXT(A1,"0.00%") in both.
How do I show a percentage in a chart label?
In Excel charts, right-click the data labels, choose Format Data Labels, and under Number, select Percentage. This uses the underlying value as a decimal and displays it as a percent without any formula. If you need custom text, you can link labels to cells containing TEXT formulas.
What is the difference between TEXT and the percent number format?
TEXT converts the number to a text string that cannot be used in math. The percent number format keeps the cell numeric and only changes the display. Use TEXT when you need the percent as part of a string; use the number format when you want to keep the value for calculations.
Related
- Excel Spreadsheet Attendance Template: How to Build One
- Excel Spreadsheet Calendar Template: How to Make One
- Excel Split Numbers From Text: 4 Easy Ways
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Running Total: Formulas, Tables, and TroubleshootingTo create a running total in Excel, enter your amounts in B2:B7, type =SUM($B$2:B2) in C2, and fill…
- Excel Time Converter: Formulas, Formats, and TipsTo convert decimal hours in Excel into a time value, divide by 24; to convert an Excel time…
- Excel COUNTIFS Multiple Criteria Range ExplainedThe direct answer: use =COUNTIFS(range1, criteria1, range2, criteria2, ...) where each range must be the same size and…
- Excel Working Hours Formula: Calculate Time WorkedIf A2 contains a shift start time and B2 its end time on the same day, enter =(B2-A2)*24…
- Excel Age from Date of Birth with DATEDIF ExplainedTo calculate age in completed years in Excel, put the date of birth in A2 and enter =DATEDIF(A2,TODAY(),"Y")…
- SPLIT in Snowflake SQL: Functions, Examples, and TipsIn Snowflake SQL, SPLIT(string, delimiter) divides a string wherever a literal delimiter occurs and returns the pieces as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.