To format percentage values in Excel, select the cells, then choose Home > Conditional Formatting and pick a rule such as Highlight Cells Rules > Greater Than. Enter a percentage threshold, such as 20%, and choose a format. For more control, use a formula rule such as =B2>=20%—assuming the percentages are in B2:B20—and apply it to the whole range.
What percentage conditional formatting does
Conditional formatting changes how cells look when their values meet conditions you set. It does not change the underlying values or calculations. You can use it to draw attention to percentages above a target, flag results below a minimum, or show a range of performance with color scales.
Excel stores a percentage as a number: 20% is stored as 0.2, and 125% as 1.25. A percentage number format displays that stored number multiplied by 100 and followed by a percent sign. This distinction matters when you create rules. If a cell contains the numeric value 20%, compare it with 20% or 0.2—not with the whole number 20. Typing 20 into a cell and then applying percentage formatting displays 2000%, because the stored value is 20.
Conditional formatting evaluates the underlying value, not the way it appears on screen. A cell displaying 35% can therefore be compared with a threshold of 30%, even though the stored values Excel compares are 0.35 and 0.30.
Apply a built-in percentage rule
For a straightforward cutoff, a built-in rule is usually the quickest option in Excel for Microsoft 365 on Windows:
- Select the percentage cells you want to format, such as B2:B20.
- Choose Home > Conditional Formatting > Highlight Cells Rules, then choose Greater Than, Less Than, Between, or another suitable rule.
- Enter the threshold as a percentage, such as 20%, and select a format from the available choices. Choose Custom Format if you want to set a fill, font, or border yourself.
- Select OK to apply the rule.
If B2:B20 contains numeric percentages, a Greater Than rule set to 20% highlights values above that threshold. A value equal to 20% is not greater than 20%; use a formula rule with >= if the threshold itself should also be highlighted. Excel for Mac has conditional formatting on the Home tab as well, though some dialog layouts differ. Excel for the web also supports common conditional formatting rules, but available controls can vary from the desktop app.
Use a formula for exact conditions
A formula rule is useful when the built-in choices do not express the condition you need, or when you want to compare each cell with a separate target. In Windows Excel, select the cells and choose Home > Conditional Formatting > New Rule. Select Use a formula to determine which cells to format, enter the formula, choose Format, and confirm. Check the Applies to range in the rule so it covers the intended cells.
With numeric percentages in B2:B20, use =B2>=20% to highlight values at least 20%. Apply the rule to B2:B20. The reference B2 is relative: Excel adjusts it for each cell in the range, checking B3 against the same threshold in the next row, and so on.
With percentages in B2:B20 and a shared target in C1, use =B2>=$C$1 and apply it to B2:B20. The dollar signs keep the target reference fixed as Excel evaluates each row. If the target is in C2 and changes for each corresponding row, use =B2>=C2 instead, with data in B2:B20 and targets in C2:C20. Here both references should move together from row to row.
Formula rules need a TRUE or FALSE result for each cell. For a row-based rule involving other columns, apply the rule to the rows you want formatted and use a relative reference for the row. For example, if percentages are in B2:B20 and status labels are in A2:A20, =AND(B2>=20%,$A2="Complete") highlights qualifying percentages only when the corresponding status is Complete. Apply it to B2:B20. The column lock on A keeps the status check in column A, while the row adjusts for each record.
Use color scales and data bars
To show how percentages compare across a group, select the range and choose Home > Conditional Formatting > Color Scales. A two-color scale assigns one color to lower values and another to higher values; a three-color scale adds a midpoint. Data Bars, available from the same Conditional Formatting menu, display an in-cell bar whose length reflects the value relative to the other values in the selected range.
Color scales and data bars are often useful for scanning a list, but they answer a different question from a threshold rule. A color scale emphasizes relative position within the selected range; it does not automatically mean that a particular color represents passing a fixed target such as 80%. If the business meaning is pass or fail, use a threshold rule or add a separate rule for the cutoff. A data bar also needs a clear number format: percentage formatting displays the cell value as a percent, while the bar shows its relative magnitude.
In a color-scale rule, Excel can use the range’s lowest and highest values or specified numbers, percentages, or percentiles as scale points. A scale based on the minimum and maximum is recalculated as the data changes. If the same color must always mean the same absolute score across reports, configure fixed scale values rather than relying on each report’s own minimum and maximum.
Distinguish top percentages from percentage values
One common source of confusion is the Top/Bottom Rules menu. Home > Conditional Formatting > Top/Bottom Rules > Top 10% does not mean highlight values greater than 10%. It means highlight the highest portion of the selected cells, with 10% as the default portion. You can edit the rule to use a different proportion. This can help identify the strongest results in a group even when the actual percentages vary from one report to another.
If you instead want to highlight values at or above a fixed percentile cutoff, use a formula. With numeric percentages in B2:B101, apply this rule to B2:B101: =B2>=PERCENTILE.INC($B$2:$B$101,0.8). It highlights cells at or above the 80th percentile of that range. This is a value cutoff calculated from the data, not simply a request to highlight exactly 20% of cells. Ties at the cutoff can mean more cells are highlighted than the proportion suggests.
Check the data before creating rules
A rule can be correct and still appear not to work if the values are stored as text or the selected range is wrong. Before troubleshooting, click a few cells and check that Excel recognizes them as numbers. A value entered as 25% is normally numeric; a value imported with a leading apostrophe or as text may not compare as expected. Converting text percentages to numbers may require cleaning or re-importing the source data, rather than changing the conditional formatting rule.
- Confirm the cells contain numeric values and that percentage formatting is being used for display, not to disguise whole-number values.
- Check the selected range and the rule’s Applies to field. A rule applied to B2:B20 will not format new rows outside that range.
- Check whether blank cells, errors, or unexpected entries are included in the range. They can affect color scales and percentile calculations.
- For a formula rule, make sure its first cell reference matches the top-left cell of the Applies to range. A mismatch can shift the condition to the wrong rows.
For example, if the formula starts with B2 but the rule applies to B3:B20, Excel evaluates the relative reference from a different starting point. Aligning the first reference with the first cell in the range is a useful way to prevent off-by-one errors.
Manage overlapping rules and changing ranges
When a cell meets more than one rule, multiple formats may compete. Choose Home > Conditional Formatting > Manage Rules to review the rules, edit their Applies to ranges, and change their order. The Stop If True option can prevent lower-priority rules from being evaluated when a higher-priority rule is true. This can help when, for example, a red warning should take precedence over a general color scale. Use it carefully: an earlier rule can then prevent later formatting from appearing.
Rules can also be limited to the current selection or displayed for the worksheet, depending on the choice in the Manage Rules dialog. If formatting seems to have disappeared, inspect the full list for duplicate rules or rules that apply only to part of the data. When a dataset grows, extend the Applies to range or use an Excel table so new records are easier to include. Review the rule after adding data, especially if the condition depends on a percentile or a color scale whose results change with the range.
Conditional formatting is visual guidance, not data validation. A red cell does not prevent someone from entering a value that fails a target, and a green cell does not prove that the source data is correct. If entries must meet a requirement, consider using Data Validation as well; use conditional formatting to make results easier to see.
Frequently Asked Questions
How do I highlight percentages above a certain value in Excel?
Select the percentage range and choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than. Enter the threshold, such as 25%, and choose a format. For an inclusive cutoff, create a formula rule using a condition such as =B2>=25%, adjusted to match the first cell in your range.
Why does Excel show 2000% when I enter 20?
Excel stores 20 as the number twenty. Applying percentage formatting displays that number multiplied by 100, so it appears as 2000%. Enter 20% or 0.2 if you mean twenty percent.
Does Top 10% mean values greater than 10%?
No. The Top 10% rule selects the highest portion of the selected cells. To highlight every value above a fixed threshold of 10%, use a Greater Than rule and enter 10%, or write a formula rule with the appropriate comparison.
Can conditional formatting compare percentages in two columns?
Yes. If actual percentages are in B2:B20 and targets are in C2:C20, create a formula rule with =B2>=C2 and apply it to B2:B20. Each actual value is compared with the target on the same row.
Why does a percentage rule not highlight some cells?
Check that the values are numeric rather than text, that the threshold uses the intended percentage, and that the Applies to range includes those cells. For formula rules, verify that the first cell reference matches the top-left cell of the range and that other rules are not overriding the format.
Related
- Can You Undo Remove Duplicates in Excel?
- Can You Use Solver in Excel for the Web? What Works
- Can You Split One Cell in Excel? What Actually Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete Blank Rows in an Excel Column SafelyTo delete entire rows where a chosen Excel column is blank, select the data range, choose Home >…
- Power Query Circular Reference: Causes and FixesA Power Query circular reference happens when a query depends on itself, either directly or through other queries,…
- Small Excel Table: Create, Format, and Use OneTo make a small Excel table in Microsoft 365 for Windows, put your headings and records in adjacent…
- Excel Shortcut to Highlight a Cell Yellow in Microsoft 365In Excel for Microsoft 365 on Windows, select the cell or range and press Alt, H, H to…
- Excel Page Break Dotted Lines: Meaning and Fixes ExplainedExcel page break dotted lines mark where the worksheet is expected to split across printed pages. To hide…
- How to Edit a Mail Merge Document in WordTo edit a mail merge in Word, open the main merge document, change its text or merge fields,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.