How to Format Excel Cells Red or Green Based on Percentage Values
Question details
The user needs to set up conditional formatting to automatically fill percentage cells with red or green colors based on whether they fall above or below a specific benchmark percentage.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing a list of percentage values against a single target benchmark (such as a golf handicap, sales goal, or performance metric) to visually differentiate passing and failing scores.
- Observed behavior
- Requires creating a conditional formatting rule using a custom formula that correctly applies color fills by comparing two percentage values.
Ensure your target data cells are formatted as Percentages, and that you have a specific cell containing your benchmark percentage to reference in your rule.
Apply Conditional Formatting with a Custom Formula
Create two formula-based conditional formatting rules to evaluate your data range against the reference cell, assigning green for meeting the target and red for falling short.
Using a formula in conditional formatting allows you to dynamically compare a range of cells against a single benchmark cell. By mixing relative references for your data and absolute references for the benchmark, Excel will correctly evaluate each individual cell.
Highlight all the cells containing the percentage values you want to format (for example, A2:A20).
Go to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'. Choose 'Use a formula to determine which cells to format'.
In the formula box, type `=A2>=$B$2` (assuming A2 is the first cell in your selected range and B2 is your absolute benchmark cell). Click 'Format', choose a green Fill color, and click 'OK'.
Keep the same range selected, click 'Conditional Formatting', and select 'New Rule' again. Choose the formula option once more.
Type `=A2<$B$2` in the formula box. Click 'Format', choose a red Fill color, and click 'OK' to apply.
Apply Conditional Formatting with WPS Spreadsheet
WPS Spreadsheet features an intuitive and robust conditional formatting tool that perfectly supports formula-based rules. It makes comparing percentages and visualizing data effortless.
- 1. Open your file: Launch WPS Spreadsheet and select the range of percentage cells you wish to format.
- 2. Access Conditional Formatting: Navigate to the 'Home' tab and click on the 'Conditional Formatting' button.
- 3. Set a new rule: Click 'New Rule' from the drop-down menu and choose 'Use a formula to determine which cells to format'.
- 4. Input the formula and format: Enter your comparison formula (e.g., `=A2>=$B$2`), set the desired background color, and click 'OK'.
Frequently Asked Questions
Why is my conditional formatting applying the wrong color to the entire column?
This usually happens if you used an absolute reference (like $A$2) for your starting data cell instead of a relative reference (like A2). Check your rule and remove the dollar signs from the data cell reference so it can evaluate row by row.
Can I use three colors instead of two for my percentage comparison?
Yes. You can create a third rule with another formula for a specific range, or you can use the built-in 'Color Scales' feature under Conditional Formatting to apply a dynamic three-color gradient based on the percentage values.
How do I clear conditional formatting rules if I make a mistake?
Select the cells with the incorrect formatting, go to the 'Home' tab, click 'Conditional Formatting', choose 'Clear Rules', and then select 'Clear Rules from Selected Cells'.
Does conditional formatting work if the benchmark cell is on another worksheet?
Yes, you can reference a benchmark cell located on another worksheet. Your formula would simply include the sheet name, looking something like `=A2>=Sheet2!$B$2`.
