How to Simplify Excel Conditional Formatting for Task Status
Question details
The user needs to streamline their data presentation by creating formula-based conditional formatting rules that apply to entire ranges for tracking project elements like completed tasks, aging contact dates, and cancelled accounts.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a project tracker or task list where multiple distinct formatting styles are needed based on real-time calculations (like date aging) and specific textual statuses.
- Observed behavior
- The goal is to apply efficient formula rules to target ranges instead of maintaining fragmented individual rules, ensuring completed tasks, aging dates, and cancelled accounts format automatically without manual updating.
Clear any existing conditional formatting rules from your working data range (e.g., rows 2 to 150) to prevent old overlapping rules from conflicting with your new formulas.
Apply Formula-Based Rules to Master Task Status Formatting
Use specific formula-based conditional formatting rules applied to broad column ranges to automatically highlight aging tasks, completed statuses, and cancelled accounts.
By utilizing absolute column references (like $P2) in your formulas, Excel evaluates the condition against a specific cell but applies the resulting format across the entire designated row or range.
Select the cells containing your dates (e.g., P2:P150) with P2 as the active cell. Go to the Home tab, click Conditional Formatting > New Rule > 'Use a formula to determine which cells to format'. Enter =AND($P2<>"",$P2<TODAY()-10). Click Format, go to the Fill tab, select yellow, and click OK.
Keep the same selection (P2:P150). Repeat the process by clicking Conditional Formatting > New Rule. Enter the formula =AND($P2<>"",$P2<TODAY()-20). Click Format, select a red fill, and save the rule.
Select the range you want to highlight for completed tasks, such as O2:P150, ensuring O2 is active. Create a new formula-based rule and enter =$O2="Done". Choose a light green fill in the Format menu and click OK.
Select your entire data range, for example, A2:Z150, with A2 as the active cell. Create a new rule using the formula =$N2="CANCELLED". Click Format, choose a dark gray fill, and under the Font tab, check the 'Strikethrough' option. Click OK.
To ensure older dates take precedence, click Conditional Formatting > Manage Rules. Locate the red (20-day) rule and use the 'Move Up' arrow to place it above the yellow (10-day) rule. Check the 'Stop If True' box next to the red rule to prevent formatting overlap.

Use WPS Spreadsheet to Manage Conditional Formatting Effortlessly
WPS Spreadsheet offers a highly intuitive Conditional Formatting Rules Manager. You can quickly apply, prioritize, and edit complex formula-based formatting to highlight aging dates or track project statuses in a familiar, Excel-compatible workspace.
- 1. Open your data file: Launch WPS Spreadsheet and open your existing project tracker or task list.
- 2. Access Conditional Formatting: Highlight your target range, navigate to the Home tab, and click the Conditional Formatting icon, then select 'New Rule'.
- 3. Apply custom formulas: Choose 'Use a formula to determine which cells to format', input your status-tracking formula, and customize the background colors or font styles.
- 4. Rearrange easily: Click 'Manage Rules' to easily drag or use arrows to organize rule priority, ensuring your statuses display exactly as needed.

Frequently Asked Questions
Why is my entire row not highlighting when the task status changes?
If only a single cell changes color instead of the entire row, you likely missed the absolute column reference. Ensure there is a dollar sign ($) before the column letter in your formula (e.g., =$O2="Done" instead of =O2="Done"). Also, check that the 'Applies to' range in the Rules Manager encompasses all your data columns.
How do I ensure the 20-day red color overwrites the 10-day yellow color?
Open the Conditional Formatting Rules Manager. Since any date older than 20 days is also older than 10 days, both conditions are technically true. Select the 20-day (red) rule and use the 'Move Up' button to place it at the top of the list. Check the 'Stop If True' box so Excel stops evaluating once the red condition is met.
Why does the strikethrough format not apply to my text?
When creating the rule, you must manually enable the strikethrough font effect. After entering your formula, click the 'Format' button, navigate to the 'Font' tab, and check the 'Strikethrough' box located under the Effects section before clicking OK.
Can I use these conditional formatting rules for blank cells?
Yes. If you notice empty date cells turning yellow or red due to how Excel reads blanks as zeros, you can add a condition to ignore blanks. The formula =AND($P2<>"", $P2<TODAY()-10) uses $P2<>"" specifically to tell Excel to only format the cell if it is not blank.




