Conditional formatting in Excel helps you visually highlight data patterns, outliers, and trends. You can apply rules to cells based on their values, text content, dates, or formulas. Manually building these rules requires navigating multiple menus and writing formulas correctly. Copilot in Excel lets you create conditional formatting rules using natural language prompts. This article explains how to use Copilot to generate and apply conditional formatting rules in Excel.
Key Takeaways: Creating Conditional Formatting Rules with Copilot
- Copilot pane > Prompt box: Type natural language instructions to create rules like highlight top 10 values or color cells red if overdue.
- Copilot > Review rule before applying: Copilot shows a preview of the rule logic before it is added to your sheet.
- Copilot > Modify existing rules: Ask Copilot to change a rule’s color, threshold, or range without rewriting the rule manually.
What Copilot Can Do with Conditional Formatting
Copilot in Excel uses natural language processing to interpret your request and generate the appropriate conditional formatting rule. You do not need to know the exact menu path or formula syntax. Copilot supports common conditional formatting types including color scales, data bars, icon sets, highlight cell rules, and formula-based rules.
Prerequisites
You need an active Microsoft 365 subscription that includes Copilot for Microsoft 365. The Excel desktop app or Excel for the web must be signed in with your work or school account. Your data must be formatted as a table or a named range. Copilot works best when your data has clear headers in the first row.
Steps to Create a Conditional Formatting Rule with Copilot
Follow these steps to create a conditional formatting rule using Copilot in Excel.
- Open the Copilot pane
In Excel, go to the Home tab and select Copilot in the Ribbon. The Copilot pane opens on the right side of the window. - Select the target data range
Click and drag to select the cells you want to format. Include the header row if your data uses headers. Copilot uses the selected range to understand which columns to apply the rule to. - Type your formatting request
In the Copilot prompt box at the bottom of the pane, type a natural language instruction. For example: Highlight cells in column C where the value is greater than 100 in green. Press Enter to submit your prompt. - Review the proposed rule
Copilot displays a preview of the rule logic and a sample of the formatting. Read the description to confirm the rule applies to the correct range and condition. - Apply the rule
Select the Apply button in the Copilot pane. Excel adds the conditional formatting rule to your selected range. The cells update immediately.
Advanced Conditional Formatting Prompts
You can use more specific prompts to create complex rules. Copilot understands conditions involving multiple columns, formulas, and date logic.
Highlight Top or Bottom Values
- Prompt for top values
Type: Highlight the top 10 values in column D with a green fill. Copilot creates a Top N rule. - Prompt for bottom values
Type: Highlight the bottom 5 values in column D with a red fill. Copilot creates a Bottom N rule.
Color Scales and Data Bars
- Apply a color scale
Type: Apply a green-yellow-red color scale to column E. Copilot adds a 3-color scale. - Add data bars
Type: Add blue data bars to column F. Copilot inserts gradient data bars.
Formula-Based Rules
- Rule based on another column
Type: Highlight row if column A contains the word Overdue. Copilot creates a formula that evaluates the entire row. - Date comparison
Type: Highlight cells in column G if the date is older than 30 days from today. Copilot uses the TODAY function in the rule.
Things to Avoid When Using Copilot for Conditional Formatting
Copilot Does Not Apply Rules to Non-Table Ranges
If your data is not formatted as a table, Copilot may refuse to create a rule or apply it to the wrong range. Convert your data to a table by selecting the range and pressing Ctrl+T. Name the table in the Table Design tab.
Copilot Ignores Merged Cells
Conditional formatting rules created by Copilot do not work reliably on merged cells. Unmerge cells before using Copilot. Select the merged area, go to Home > Merge & Center, and select Unmerge Cells.
Copilot Overwrites Existing Rules
When you apply a new rule through Copilot, it adds the rule to the existing list. If you ask Copilot to change a rule, it may create a duplicate instead of modifying the original. To avoid duplicates, clear existing rules first. Go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
Copilot Does Not Support All Rule Types
Copilot cannot create icon sets with custom icons or rules that reference external workbooks. For these advanced scenarios, use the standard Conditional Formatting menu manually.
Copilot vs Manual Conditional Formatting: Key Differences
| Item | Copilot | Manual |
|---|---|---|
| Rule creation method | Natural language prompt | Menu navigation and formula entry |
| Formula knowledge required | None | Basic to advanced formula writing |
| Rule preview | Automatic preview before applying | Must use Preview button in dialog |
| Supported rule types | Common types only | All types including custom formulas |
| Duplicate rule handling | May create duplicates | Full control over rule order and duplicates |
Copilot speeds up the creation of common conditional formatting rules. Manual creation gives you full control over every aspect of the rule, including formula complexity and rule priority. Use Copilot for quick formatting of standard conditions. Use manual creation for rules that require custom formulas or multiple conditions on the same range.
You can now use Copilot to create conditional formatting rules in Excel by typing natural language prompts. Start by selecting your data range and opening the Copilot pane. Try prompts for highlight cell rules, color scales, or data bars to see how Copilot interprets your instructions. For more precise control, combine Copilot with manual adjustments using the Conditional Formatting Rules Manager.