Excel conditional formatting automatically changes a cell’s appearance (fill color, font, borders) based on rules you define, so key data stands out without manual formatting. For example, you can highlight all sales figures above $10,000 in green with one rule. Here’s how to use it, what it can do, and how to avoid common pitfalls.
What Is Conditional Formatting For?
Conditional formatting is a built-in Excel feature that applies formatting only when a condition is true. It turns raw numbers into a visual dashboard: low stock levels turn red, overdue dates get bold, duplicate entries become highlighted. Unlike static formatting, it updates automatically when your data changes, so a cell that was green yesterday can turn red today if the value drops below your threshold.
How to Apply Conditional Formatting (Step by Step)
In Excel for Microsoft 365 on Windows, the basic workflow is:
- Select the range you want to format (for example,
B2:B20). - Go to Home > Conditional Formatting.
- Choose a rule type: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, or Icon Sets.
- Set the condition and formatting, then click OK.
For a custom formula rule, choose New Rule > Use a formula to determine which cells to format. Enter a formula like =B2>100 and pick a fill color. On Mac, the menu path is the same (Home tab), but some dialog buttons appear slightly different. On Excel for the web, conditional formatting is available under Home > Conditional Formatting, but some advanced options like “Manage Rules” are limited.
How to Choose the Right Rule Type
Each rule type suits a different pattern:
- Highlight Cells Rules – for simple comparisons (greater than, less than, equal to, text contains, duplicates).
- Top/Bottom Rules – for the highest or lowest N items or percentages (e.g., top 10% of scores).
- Data Bars – a horizontal bar inside the cell that visually compares values (like a mini bar chart).
- Color Scales – a gradient (e.g., green-yellow-red) that shows relative magnitude across a range.
- Icon Sets – arrows, circles, or flags that categorize values (e.g., red down arrow for below target).
For most business dashboards, a combination of highlight rules and data bars works well. Use color scales when you need to see distribution at a glance, but avoid them if you need precise thresholds.
Using Formulas in Conditional Formatting
Formulas give you unlimited flexibility. The key rule: the formula must be relative to the top-left cell of the selected range. If you select A2:C10 and enter =A2>100, Excel applies it to each cell as if the formula were copied down and across. The row/column references change automatically.
Example: Highlight entire rows where the status in column C is “Overdue”. Select A2:D10, use a formula rule with =$C2="Overdue" (note the absolute column reference $C but relative row 2). This locks the column so each row checks its own C cell.
Another common use: highlight weekends in a date list. Select A2:A100, formula =WEEKDAY(A2,2)>5. The WEEKDAY function returns 6 for Saturday and 7 for Sunday.
Managing and Editing Rules
To see or change existing rules, go to Home > Conditional Formatting > Manage Rules. Here you can:
- Edit a rule’s condition or formatting.
- Change the order of rules (top rules take priority).
- Delete a rule.
- Set the range each rule applies to.
By default, if two rules conflict, the first rule in the list wins. You can also check “Stop If True” to prevent later rules from applying when the current rule matches.
Common Problems and How to Fix Them
“My rule doesn’t work” – Usually a formula reference issue. If you selected B2:B20 but your formula refers to B1, the rule will be off by one row. Always test the formula in a cell first to see if it returns TRUE for the first cell of your range.
“Formatting doesn’t update when I add rows” – Conditional formatting rules apply to a fixed range. If you insert rows within the range, Excel usually extends the range, but if you add rows below, you must update the range in Manage Rules. Better: format entire columns (e.g., B:B) but be careful not to format header cells.
“Performance is slow” – Too many rules or whole-column references can slow Excel. Use the smallest range possible, and avoid volatile functions like NOW() in rules unless necessary.
“I can’t see the formatting after I apply it” – Check if the cell has a fill color that overrides conditional formatting, or if the rule’s format is invisible (e.g., white font on white background). Also ensure the rule is not disabled (in Manage Rules, the checkbox is on).
Conditional Formatting vs. Other Methods
Conditional formatting is not the only way to visualize data. Compare it with:
- Static formatting – You manually color cells. It’s simple but doesn’t update when data changes.
- Charts – Better for trends and relationships, but they don’t highlight individual cells.
- Pivot tables – Summarize data, but they don’t format based on values unless you add conditional formatting to the pivot range.
- Data validation – Prevents invalid input, but doesn’t visually flag existing data.
For quick, rule-based visual cues, conditional formatting is the fastest and most dynamic option.
Cost and Availability
Conditional formatting is a standard feature in all desktop versions of Excel, including Excel 2016, 2019, 2021, and Microsoft 365. It’s also available in Excel for Mac and Excel for the web, though some advanced rule types (like icon sets) may be limited on the web. There is no extra cost; it’s included with your Excel license or Office subscription.
Frequently Asked Questions
Can I use conditional formatting to highlight duplicate values? Yes. Select the range, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, and choose a format. It highlights any value that appears more than once in the selected range.
How do I conditionally format an entire row based on one cell? Use a formula rule. Select the entire data range (e.g., A2:D10), then create a new rule with a formula that references the first row’s key cell with an absolute column reference, like =$C2="Yes". The formatting applies to all cells in that row.
Why is my conditional formatting not working when I copy cells? When you copy and paste cells, the rule may be copied along with the cell, but the range may not adjust as expected. Use Paste Special > Formats to copy only formatting, or use the Format Painter carefully. For dynamic rules, consider using a table (Insert > Table) so rules automatically extend to new rows.
Can I use conditional formatting with dates that are past due? Yes. For example, to highlight dates older than 30 days, use a formula rule like =AND($A2<>"",$A2<TODAY()-30). Note that TODAY() is volatile, so it recalculates when the workbook opens.
How do I remove conditional formatting without deleting my data? Select the range, go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Or go to Manage Rules and delete each rule individually.
Related
- Balance of Trade Data: What It Is and How to Read It
- F.08 Export to Excel: How to Do It
- Extracting Numbers from Text in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Insert an E-Signature in Word: Steps and OptionsTo insert an e-signature in Microsoft Word, add a picture of your handwritten signature with Insert > Pictures,…
- Create Rules in New Outlook 365: Step-by-Step GuideTo create a rule in New Outlook for Microsoft 365, open Settings, go to Mail > Rules, choose…
- Google Sheets Checkbox Color: What You Can ChangeGoogle Sheets does not provide a separate color picker for the checkbox control itself. To make a checked…
- Checklist in Excel: Create, Track, and Manage TasksYes. In Microsoft Excel for Microsoft 365 on Windows, select the cells where you want checkboxes and choose…
- Excel Keyboard Shortcuts: How to Work Without a MouseYes. In Excel for Microsoft 365 on Windows, you can navigate worksheets, select cells, enter and format data,…
- How to Get Rid of the Paste Options Box in Excel 365To stop Excel for Microsoft 365 on Windows from showing the floating Paste Options button after you paste,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.