In Excel for Microsoft 365 on Windows, find Conditional Formatting on the Home tab: select the cells, then choose Home > Conditional Formatting and select a rule. Conditional formatting changes how cells look when their values meet conditions; it does not change the values themselves.
What Conditional Formatting Does
Conditional formatting applies visual cues—such as a fill color, font color, icon, or data bar—to cells based on their contents or on a rule you define. Because Excel updates the formatting when worksheet values change, it can make important patterns easier to spot without requiring you to recolor cells manually each time.
For example, a sales report could highlight values below a target, a project tracker could flag overdue work, and a grade sheet could use color scales to show relative results. The underlying numbers remain available for calculations, sorting, and filtering. Conditional formatting is therefore a presentation aid, not a substitute for correcting or validating the data.
Formatting is evaluated against the selected cells and the rule’s settings. A rule may compare a value with a threshold, find duplicate entries, or use a formula to test a more specific condition. A cell can match more than one rule, so the order and interaction of rules can affect what you see.
Where to Find the Option
In Excel for Microsoft 365 on Windows, select a cell or range, open the Home tab, and choose Conditional Formatting in the Styles group. The menu offers common rule categories, color scales, data bars, icon sets, and commands for creating or managing rules.
In Excel for Mac, Conditional Formatting is also on the Home tab, although ribbon layout and dialog details can vary by release. Excel for the web includes conditional formatting controls as well; available editing and rule-management options can differ from the desktop app. If a workbook contains a complex rule and the web interface does not expose the setting you need, open it in the desktop version of Excel.
To inspect existing rules in Windows, choose Home > Conditional Formatting > Manage Rules. The Rules Manager lets you review rule order and the ranges to which rules apply. Its display can be scoped to the current selection or to the worksheet, so check the “Show formatting rules for” setting if a rule seems to be missing.
Common Rule Types and When to Use Them
The built-in options cover many routine comparisons. Select the relevant data first, then choose a rule that reflects the question you want the worksheet to answer:
- Highlight Cells Rules: Mark values greater than, less than, equal to, or between specified values. Text containing, dates occurring, and duplicate or unique values are also available options.
- Top/Bottom Rules: Identify the highest or lowest values, percentages, or above- and below-average entries. These are useful for ranking a set of numbers, but they do not explain why a result is high or low.
- Data Bars: Place a bar inside each cell to show the relative size of its value. They work well in a numeric list when readers need a quick visual comparison alongside the numbers.
- Color Scales: Use a gradient to show how values fall between two or three points. A scale can reveal a range of high and low results, but the colors are relative to the selected data and do not automatically imply a meaningful pass/fail threshold.
- Icon Sets: Add symbols such as arrows or indicators based on value ranges. Review the thresholds carefully; the displayed icon depends on the rule’s cutoffs, not on an independent judgment about the data.
For a simple example, select a column of invoice amounts, then choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than and enter a threshold. Excel applies the chosen appearance to matching cells. If you need to highlight the entire invoice row rather than just the amount, a formula-based rule is usually a better fit.
How to Create a Formula-Based Rule
Formula rules let you test a condition that is not covered by a single built-in comparison. They are especially useful when a result in one column should determine the formatting of a different column or an entire row.
- Select the cells you want Excel to format. For a table of tasks, that might be the full range A2:D100.
- Choose Home > Conditional Formatting > New Rule.
- Select the option to use a formula to determine which cells to format. In the New Formatting Rule dialog, enter a formula that evaluates to TRUE or FALSE.
- Choose Format, select the fill, font, or border you want, and confirm the dialog boxes.
- Use Manage Rules to verify the rule’s “Applies to” range and adjust it if necessary.
Suppose task names are in A2:A100, due dates are in C2:C100, and status is in D2:D100. To highlight each task row when its due date has passed and its status is not Complete, select A2:D100 and create a formula rule using =AND($C2<TODAY(),$D2<>"Complete"). The dollar signs lock the tested columns C and D while allowing the row number to change as Excel evaluates each row. The formula starts with row 2 because row 2 is the first row in the selected range.
This rule highlights a row only when both tests are true: the date in column C is earlier than today, and the status in column D is not the text Complete. A blank status also meets the second test, so if blanks should not be highlighted, add a condition that checks the status is not blank. Always test a new rule on a small sample so you can confirm that its logic matches the intended cases.
Choosing a Clear and Useful Format
Choose formatting that makes the result easier to interpret without overwhelming the worksheet. A subtle fill is often enough for exceptions, while data bars or a color scale can suit a numeric comparison. Use icons only when their meanings are obvious to the audience, and avoid relying on color alone when the distinction is important to readers who may not perceive those colors easily.
Set thresholds to reflect the real meaning of the data. A red fill for every value below the average may imply that those values are failures even when the average is not a target. Likewise, a three-color scale will always distribute colors across its range, even if every value is acceptable. For operational decisions, explicit limits—such as a stated minimum or due date—are often more informative than a relative ranking.
Keep formatting consistent across comparable columns and worksheets. Too many colors, icons, and overlapping signals can make patterns harder to read. It can also help to include a short legend or clear column labels so readers understand what a color or icon represents.
Rule Order, Ranges, and Conflicts
Conditional formatting can involve multiple rules for the same cell. In the Rules Manager, Excel displays rules in an order that can matter when their formatting overlaps. Use the move-up and move-down controls to prioritize a rule when appropriate. The “Stop If True” option, available for applicable rule types in desktop Excel, can prevent lower-priority rules from being evaluated for a cell once a higher rule is true.
The “Applies to” range is just as important as the condition. A rule might be correct but appear not to work because it covers the wrong cells. When a formula rule is applied to a range, Excel interprets relative references from the top-left cell of that range. A formula written for a selection beginning at row 2 may give unexpected results if the range later starts at row 1 without adjusting the formula.
Copying and pasting cells can also carry conditional formatting, creating additional rules or extending an existing range. If a worksheet accumulates many overlapping rules, use Manage Rules to inspect them and remove obsolete entries. Before deleting rules, confirm which cells they affect; clearing a rule removes its visual behavior, not the cell’s value.
Common Problems and How to Fix Them
- The wrong cells are highlighted: Check the Applies to range and, for a formula rule, confirm that the formula is written relative to the range’s first cell.
- A rule seems to do nothing: Check the comparison value, data type, and rule range. A date stored as text may not behave like an actual Excel date, and a number stored as text may not compare as expected.
- A color scale looks misleading: Remember that scale colors depend on the values and settings in the selected range. Use fixed thresholds or a different rule if readers need to distinguish a specific target from acceptable values.
- Formatting changes after adding data: Review the rule’s Applies to range. If new rows fall outside it, extend the range or use an Excel table so the formatting can extend as the table grows.
- Unexpected formatting appears: Open Manage Rules and look for duplicate or overlapping rules, then review their order and settings.
Also consider whether a filter is hiding the rows you expect to see. Conditional formatting does not make a hidden row visible. If values appear to be treated inconsistently, inspect a few representative cells directly and compare their contents, formulas, and number formats rather than relying only on their appearance.
Conditional Formatting Compared with Manual Formatting
Manual formatting is appropriate when a cell should always have a particular appearance, such as a worksheet title or a fixed section label. Conditional formatting is better when appearance should respond to changing values. If a score changes from below a threshold to above it, a conditional rule can update the highlight automatically; a manually applied fill will remain until someone changes it.
Neither method changes the underlying value, and neither should be used as the only record of an important status. For example, if a task is overdue, keep a due date or status value that can be searched and analyzed. A colored cell alone may be overlooked, may print differently, or may be hard to interpret outside the workbook.
Frequently Asked Questions
Where is Conditional Formatting in Excel?
On Windows, select the cells and choose Home > Conditional Formatting. Excel for Mac also places the feature on the Home tab; exact ribbon details can vary by version.
Does conditional formatting change the cell’s value?
No. It changes the cell’s appearance when a rule is met. The contents remain the same, so formulas and other calculations continue to use the underlying value.
Can I highlight an entire row based on one cell?
Yes. Select the row range, create a formula-based rule, and use a formula that tests the relevant column while keeping that column fixed with a dollar sign. Make sure the formula’s row reference matches the first row of the selected range.
Why does my conditional formatting apply to only part of a range?
Check the rule’s Applies to range in Home > Conditional Formatting > Manage Rules. The rule may not include newly added cells, or its formula references may be offset from the range’s first cell.
Can I remove conditional formatting without deleting data?
Yes. Select the affected cells and use Home > Conditional Formatting > Clear Rules, choosing whether to clear rules from the selection or the entire sheet. This removes the conditional formatting rules, not the cell contents.
Is conditional formatting available in Excel for the web?
Yes, Excel for the web supports conditional formatting, though some rule creation and management details may differ from the Windows desktop app. Use desktop Excel if you need a setting that is not exposed in the web interface.
Related
- Can You Compare Two Excel Sheets? Methods That Work
- Can You Add Search to an Excel Slicer? How It Works
- Can You Split One Cell in Excel? What Actually Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Column Height: What It Means and How to Set ItExcel columns do not have individual heights: each column runs down the worksheet, while height is set for…
- Insert a Checkbox in Word: Shortcut and OptionsIn Microsoft Word for Windows, type 2610 and press Alt+X to insert an empty checkbox (☐); use 2611…
- Create Rules in Outlook for iOS: What Works and HowIn Outlook for iOS, you can make a simple rule for a sender by opening one of their…
- How to Double Space an Entire Document in Microsoft WordTo double-space an entire document in Microsoft Word, press Ctrl+A on Windows (Command+A on Mac), then choose Home…
- Excel Box and Whisker Plot Examples With Setup TipsExcel box-and-whisker plot examples include comparing test scores across classes, delivery times across routes, or monthly measurements across…
- Outlook Block Sender: How to Block Unwanted EmailsTo block a sender in Outlook, select a message from that person, open the message’s options, and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.