How to Use Copilot in Excel to Build Multi-Level Conditional Formatting

You want to apply conditional formatting rules that highlight data based on multiple conditions, such as flagging overdue tasks that are also high priority. Manually building these rules in Excel requires writing complex formulas and managing rule priority, which can be error-prone and time-consuming. Copilot in Excel can generate these multi-level conditional formatting rules for you using natural language prompts. This article explains how to set up Copilot for this task, write effective prompts, and avoid common mistakes when building layered formatting rules.

How Copilot Handles Multi-Level Conditional Formatting

Copilot in Excel can generate conditional formatting rules that use formulas, including rules that evaluate multiple conditions with AND, OR, and nested IF logic. When you describe the formatting goal in natural language, Copilot translates it into the correct Excel formula and applies the rule to the selected range. This feature works for rules that highlight cells, rows, or entire tables based on values in other columns.

Before using Copilot for this task, ensure you have the following prerequisites:

• An active Microsoft 365 subscription with Copilot enabled (Copilot for Microsoft 365 or Copilot Pro).
• Excel version 2402 or later for Windows, or version 16.81 or later for Mac. Copilot is also available in Excel for the web with similar functionality.
• Data organized in a table format with clear column headers. Copilot works best when it can reference column names directly.

Copilot does not create conditional formatting rules automatically. You must open the Copilot pane and type a prompt that describes the condition and the formatting. Copilot then inserts the rule into the Conditional Formatting Rules Manager, where you can adjust the priority and range.

Steps to Create Multi-Level Conditional Formatting with Copilot

Method 1: Single Condition with Copilot

Start with a simple rule to confirm Copilot responds correctly. This method builds confidence before moving to multi-level rules.

1. Open the Copilot pane
   Go to the Home tab and click Copilot in the ribbon. The pane opens on the right side of the Excel window.
2. Select the data range
   Click and drag to highlight the cells or rows you want to format. Include all columns that the condition will reference.
3. Type a clear prompt
   In the Copilot pane, type: “Highlight cells in column A where the value is greater than 100 with a light green fill.” Press Enter.
4. Review and apply the rule
   Copilot displays a preview of the rule. Click Apply to add it to the Conditional Formatting Rules Manager.

Method 2: Two Conditions with AND Logic

Multi-level formatting often requires two conditions to be true simultaneously. The AND operator ensures both conditions must be met.

1. Open Copilot and select the range
   Same as Method 1. Ensure the range includes all columns involved in the conditions.
2. Write a prompt with AND
   Type: “Highlight the entire row if column B says ‘Overdue’ AND column C says ‘High’ with a red fill.” Press Enter.
3. Verify the formula
   Copilot generates a formula like =AND($B1=”Overdue”,$C1=”High”). Check that the cell references use absolute columns and relative rows.
4. Apply the rule
   Click Apply. The rule appears in the Rules Manager. If you need to adjust the range, click the range selector in the Rules Manager and select a new area.

Method 3: Three or More Conditions with Nested Logic

For three or more conditions, use nested AND/OR operators or a combination of both. Copilot can handle up to about five conditions in a single prompt.

1. Open Copilot and select the range
   Select the entire table or the rows you want to format.
2. Write a prompt with multiple conditions
   Type: “Highlight the row if column D is ‘Complete’ AND column E is before 01/01/2024 AND column F is ‘Yes’ with a yellow fill.” Press Enter.
3. Check the rule priority
   After applying, open Conditional Formatting Rules Manager (Home > Conditional Formatting > Manage Rules). Ensure the multi-condition rule is at the top if it should override other rules.
4. Add more rules for additional levels
   Repeat the process with different prompts. For example, create a second rule for rows where D is ‘In Progress’ and E is after today. Copilot adds each rule as a separate entry.

Method 4: Using OR Logic for Alternative Conditions

Use OR when any one of the conditions should trigger the formatting. This is common for flagging multiple statuses with the same color.

1. Open Copilot and select the range
   Same as before.
2. Write a prompt with OR
   Type: “Highlight the cell in column G if it contains ‘Error’ OR ‘Warning’ with an orange fill.” Press Enter.
3. Apply and test
   Click Apply. Manually enter test values in column G to confirm both conditions trigger the formatting.

Common Mistakes and How to Avoid Them

Copilot returns a formula but no formatting appears

This usually happens when the selected range does not match the formula’s references. For example, if you select only column A but the formula references column B, the rule will not apply. To fix this, select the entire row range before prompting Copilot, or adjust the range in the Rules Manager after applying the rule.

Rules conflict and the wrong cells are highlighted

When multiple rules exist, Excel applies the highest-priority rule first. A rule that formats entire rows can override a rule that formats single cells. Open the Conditional Formatting Rules Manager and drag rules into the correct order. Place the most specific condition at the top.

Copilot misunderstands relative date references

Prompts like “before today” or “within the last 7 days” may not translate correctly. Copilot might use a static date instead of a dynamic function like TODAY(). After Copilot inserts the rule, manually edit the formula in the Rules Manager. Replace a hard-coded date with =TODAY() or =EDATE(TODAY(),-1) for relative dates.

Formatting does not update when data changes

This occurs if the rule applies to a fixed range instead of an entire column or table. In the Rules Manager, change the range to include the whole column, such as $A:$Z, or use a dynamic named range. Copilot sometimes applies the rule only to the selected cells at the time of creation.

Item	Single Condition Rule	Multi-Level Rule
Number of conditions	One condition, no logical operators	Two or more conditions with AND/OR/NOT
Copilot prompt example	“Highlight cells where value > 100”	“Highlight row if A is ‘Overdue’ AND B is ‘High'”
Formula structure	=A1>100	=AND($A1=”Overdue”,$B1=”High”)
Rule priority management	Usually not needed	Required to avoid conflicts between overlapping rules
Best use case	Simple data validation or flagging	Complex dashboards, project trackers, risk matrices

You can now use Copilot to create conditional formatting rules that combine multiple conditions with AND, OR, and nested logic. Start with a simple rule to verify Copilot understands your data structure, then layer additional rules for different formatting levels. Always check the rule priority in the Conditional Formatting Rules Manager after Copilot inserts the rules. For advanced scenarios, manually edit the formulas to add dynamic functions like TODAY() or INDIRECT() for greater flexibility.