Calculation Issues

How to Set Persistent Conditional Formatting Colors in Excel

Phi Hung VoPhi Hung Vo Oct 16, 2026 871 views

Question details

The user wants to set up conditional formatting where a cell turns green under specific conditions but turns red after a due date, and permanently stays red even if the source data is later modified or removed.

How to Apply Persistent Conditional Formatting with Multiple Criteria in Excel
Product
Excel
Device & OS
not provided
Scenario
Tracking shipping or delivery statuses where an overdue status (red color) must permanently lock into place once triggered, regardless of subsequent data edits.
Observed behavior
Standard conditional formatting dynamically recalculates whenever the source cells change, meaning the cell loses its red color if the underlying data that triggered the rule is cleared or updated.
Before you start

Before proceeding, ensure you understand that this workaround requires enabling iterative calculation, which allows circular references in your workbook and may impact overall formula calculation performance.

Solution 1Recommended

Use a Helper Column with Iterative Calculation

Since standard conditional formatting recalculates constantly, use a self-referencing formula in a helper column to lock the TRUE/FALSE state permanently once triggered.

By default, conditional formatting is dynamic. To force a cell to remember a previous state (such as turning red and staying red), you must introduce a circular reference.

By enabling iterative calculation, you can write a formula that checks its own cell value. Once the condition evaluates to TRUE, it remains TRUE even if the original data changes.

1
Enable Iterative Calculation

Go to File > Options > Formulas. Under the Calculation options section, check the box for 'Enable iterative calculation' and click OK.

2
Create a Self-Referencing Helper Column

In an empty column (e.g., Column E), enter a formula that checks the criteria and references itself. For example: =OR(AND(A2=0,B2=0,C2<D2),E2=TRUE). This means if the primary criteria are met OR the cell E2 is already TRUE, the result will be TRUE.

3
Apply Formula to the Column

Click the bottom-right corner of the cell containing your new formula (E2) and drag it down to apply it to all relevant rows in the dataset.

4
Set Up Conditional Formatting

Select the cells you want to change color. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

5
Link to the Helper Column

Enter the formula =E2=TRUE in the rule box, click Format to choose a red fill color, and click OK. The target cell will now stay red permanently once E2 becomes TRUE.

Use a Helper Column with Iterative Calculation
Resetting the Color: If you need to reset the persistent red color back to green (for example, if data was deleted by mistake), you must manually delete the formula in that specific helper cell and recreate it.
Advanced Spreadsheets Solution

Manage Complex Conditional Formatting with WPS Spreadsheets

WPS Spreadsheets provides comprehensive support for iterative calculations, circular references, and advanced conditional formatting rules, empowering you to build robust tracking systems with permanent states.

  1. 1. Enable Iterative Calculation: Open your spreadsheet in WPS Office, go to Menu > Options > Calculation, and check 'Iterative calculation'.
  2. 2. Configure the Helper Column: Set up your self-referencing helper column by typing your specific criteria alongside the self-referencing check, such as =OR([Your Conditions], E2=TRUE).
  3. 3. Apply Conditional Formatting: Navigate to Home > Conditional Formatting > New Rule, select 'Use a formula to determine which cells to format', and link it directly to your helper column to secure persistent colors.
Fully compatible with Microsoft Excel conditional formatting and complex formulas.Supports iterative calculations to easily handle self-referencing formulas and persistent cell states.Lightweight and fast calculation engine, even when managing large datasets with complex tracking rules.Free to use with a highly intuitive, multi-tab interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting change when I update the source data?

Standard conditional formatting is designed to be fully dynamic. Whenever a referenced cell changes, Excel instantly recalculates the condition and updates the applied format accordingly, discarding previous states.

How do I enable iterative calculation in Excel?

Click on File > Options > Formulas. In the Excel Options dialog, look under the Calculation options section, check the box for 'Enable iterative calculation', and then click OK to save your changes.

How can I clear the persistent red color if the data was entered by mistake?

Because the helper cell securely locks into a TRUE state via a circular reference, you must manually delete the formula in that specific helper cell. You can then drag the formula down again from an adjacent cell to reset the condition.

Does enabling iterative calculation slow down my workbook?

It can in some cases. Iterative calculation allows circular references to loop repeatedly up to a maximum number of iterations. If your workbook contains a large volume of complex formulas, this setting might noticeably decrease calculation performance.