Function Problems

Use an Excel IF Formula Based on Conditional Formatting Colors

Natalie TaylorNatalie Taylor Nov 18, 2026 868 views

Question details

Return a red, amber, or green result based on the conditions that determine a cell's conditional formatting color.

Use an Excel IF Formula Based on Conditional Formatting Colors
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.
Before you start

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.

Solution 1Recommended

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.

1
Inspect the rules

Select the source cell, then open Home > Conditional Formatting > Manage Rules and note the conditions and their priority.

2
Enter the formula

Select a result cell such as B1 and enter =IF(A1>=80,"Green",IF(A1>=50,"Amber","Red")).

3
Adjust and apply

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.

4
Check the results

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.

Match an Excel IF formula to the conditional formatting rules
Other rule types: If the formatting depends on another cell or a formula rather than the source value alone, reproduce those references and conditions in the result formula.
Work with spreadsheet formulas

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. 1. Open your workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Check the formatting rules: Select a formatted cell and open Home > Conditional Formatting > Manage Rules to review its conditions.
  3. 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. 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.
Match red, amber, and green labels to your formatting thresholdsOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and fill handlesWork with spreadsheets in a free Office suite
microsoft office alternative - wps office

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.