Use an Excel IF Formula Based on Conditional Formatting Colors
Question details
Return a red, amber, or green result based on the conditions that determine a cell's conditional formatting color.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants a formula to report the status represented by a conditionally formatted source cell.
- Observed behavior
- The desired result is a text label that matches the displayed conditional formatting color.
Select a source cell and open Home > Conditional Formatting > Manage Rules to check the conditions and their order. Identify the thresholds, cell references, and any rule priority or Stop If True settings that affect the displayed color.
Match an Excel IF formula to the conditional formatting rules
Use this approach when the cell colors are determined by score thresholds.
A standard Excel formula cannot reliably read a cell's displayed conditional formatting color. Instead, reproduce the conditions that generate the color.
The example assumes scores of 80 or higher are green, scores from 50 to 79 are amber, and lower scores are red. Replace these thresholds and labels with those in your rules.
Select the source cell, then open Home > Conditional Formatting > Manage Rules and note the conditions and their priority.
Select a result cell such as B1 and enter =IF(A1>=80,"Green",IF(A1>=50,"Amber","Red")).
Change A1 and the thresholds to match your source cells and rules, then drag the fill handle down to apply the formula to other rows.
Compare the labels in column B with the displayed colors in column A. If rules overlap, adjust the formula's condition order to match the effective rule priority.

Return conditional formatting labels in WPS Spreadsheet
Use a nested IF formula in WPS Spreadsheet to return status labels that match the conditions behind your conditional formatting.
- 1. Open your workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Check the formatting rules: Select a formatted cell and open Home > Conditional Formatting > Manage Rules to review its conditions.
- 3. Enter the status formula: Select a result cell and enter =IF(A1>=80,"Green",IF(A1>=50,"Amber","Red")), adjusting the reference and thresholds to match your rules.
- 4. Apply and save: Drag the result cell's fill handle down to cover the remaining rows, check the labels against the colors, and save the workbook.

Frequently Asked Questions
Can an Excel IF formula read a conditional formatting color directly?
A standard IF formula cannot reliably read the displayed conditional formatting color. Use the underlying rule conditions in the formula instead.
Where can I find the thresholds used by conditional formatting?
Select a formatted cell and open Home > Conditional Formatting > Manage Rules. Review each rule's condition and priority.
Can I use IFS instead of nested IF statements?
Yes, if your Excel version supports IFS. For the example thresholds, enter =IFS(A1>=80,"Green",A1>=50,"Amber",TRUE,"Red") in a result cell.
Why does my formula's label differ from the displayed color?
Check Home > Conditional Formatting > Manage Rules for overlapping conditions, rule order, and Stop If True settings. Make sure the formula uses the same references and effective priority.




