In Excel for Microsoft 365 on Windows, conditional formatting rules automatically change a cell’s appearance when its value meets a condition. Select the cells, then choose Home > Conditional Formatting and pick a rule type or select New Rule to define your own.
What conditional formatting rules do
A conditional formatting rule tells Excel which cells to evaluate, what condition to check, and what format to apply when that condition is true. The format can change a cell’s fill color, text color, font style, border, or number format. Depending on the rule type, Excel can also display data bars, color scales, or icon sets.
The important distinction is that conditional formatting changes how a value looks, not the value itself. A cell that appears red because it fails a target does not become a different number, and its underlying data remains available to formulas, charts, and sorting. This makes the feature useful for drawing attention to exceptions without changing the worksheet’s contents.
For example, a sales list could highlight amounts below a target, a project tracker could flag overdue dates, and an inventory sheet could mark quantities that need replenishing. The formatting updates when the cell values change or when Excel recalculates a rule that depends on a formula.
Create a basic conditional formatting rule
For a built-in rule, select the cells you want to evaluate and open Home > Conditional Formatting. Choose a category such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, or Icon Sets. Select a condition, enter any requested value, choose a format, and confirm it.
For example, to highlight sales below 500 in a range, select the sales cells and choose Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter 500, select a formatting option, and click OK. Excel applies the rule to the selected cells; a value of 420 is highlighted, while a value of 620 is not.
For a rule that needs a custom condition, select the target range and choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose the rule type, set the condition or formula, select Format to define the appearance, and confirm. The available choices vary by rule type, but the logic is the same: specify what Excel should test and what it should show when the test succeeds.
Choose the right rule type
Built-in rules are convenient when a condition is simple and applies directly to a cell’s value. Formula-based rules are more flexible: they can compare values in different columns, check multiple conditions, or format an entire row based on one field. Choose the simplest rule that expresses the requirement clearly.
- Highlight Cells Rules: Mark values greater than, less than, equal to, or between specified values; identify text that contains a phrase; or flag dates in a chosen period.
- Top/Bottom Rules: Emphasize the highest or lowest values, percentages, or above- and below-average values in a selection.
- Data Bars: Show a bar inside each cell to give a quick visual comparison of magnitudes while keeping the number visible.
- Color Scales: Shade cells along a gradient so readers can see relative high and low values across a range.
- Icon Sets: Add indicators such as arrows or traffic-light symbols according to thresholds.
- Formula rules: Apply a format when a formula returns TRUE, including when the condition depends on another cell.
Visual choices should suit the kind of comparison. Data bars can help compare quantities, while a color scale can reveal a broad pattern across a dense table. Neither is a substitute for explaining what a threshold means. If readers need to know whether a value passed a target, a clear label or a simple highlight may communicate that more reliably than a decorative icon.
Use formulas for more control
A formula-based rule is useful when the value to test is not in the cell being formatted. In the example below, the selected range is D2:D100, and those cells contain due dates. The formula checks that a date is present and earlier than today. As of November 2, 2026, a date of October 31, 2026, meets the condition and is formatted; a future date or an empty cell is not.
With due dates in D2:D100 and the conditional-formatting rule applied to that same range, use =AND($D2<>"",$D2<TODAY()).
In a formula rule, Excel evaluates the formula relative to the top-left cell of the “Applies to” range. Here, the row reference is relative, so the rule checks D2 for the first row, D3 for the next, and so on. The dollar sign before D keeps the column fixed if the rule is later applied across multiple columns. If the formula is written for the wrong starting cell, Excel can check the wrong row and produce confusing results.
Another common use is flagging repeated names while ignoring blank cells. With names in A2:A6, and the rule applied to A2:A6, enter the following formula. If A2 and A4 both contain North, both cells meet the condition and are highlighted; a blank cell is not treated as a duplicate.
With names in A2:A6 and the rule applied to A2:A6, use =AND(A2<>"",COUNTIF($A$2:$A$6,A2)>1).
When a formula rule appears to be offset by one row, check both its first cell reference and the range shown under Applies to. Also check whether each reference should move as the rule is evaluated. Relative references move; references with dollar signs in front of the column or row stay fixed in that direction.
Manage rule priority and overlapping formats
A worksheet can have several rules that apply to the same cell. To inspect or change them, choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, select the relevant worksheet or current selection in the “Show formatting rules for” list. Review each rule’s condition and its Applies to range, then use the available controls to change its order, edit it, or remove it.
When multiple rules match a cell, priority can affect the result, particularly if the rules set the same formatting property, such as fill color. In the Rules Manager, rules higher in the list have higher priority. “Stop If True,” when available and selected for a rule, prevents lower-priority rules from being evaluated for cells where that rule is true. It can help make exceptions explicit, but it should not be used without checking how the other rules interact.
For example, a worksheet might have one rule that highlights overdue tasks red and another that colors completed tasks green. If a completed task also has an old due date, both rules may match. Decide which state should take precedence, then arrange the rules accordingly and test a row that meets both conditions. The Rules Manager is also where to look when a format seems to come from a rule other than the one you expected.
Common problems and how to fix them
Conditional formatting can look incorrect even when Excel is applying the stated rule. Often, the issue is a mismatch between the selected range, the formula’s references, or the data’s actual type.
- The rule affects the wrong rows: Confirm the top-left cell used in the formula corresponds to the first cell in Applies to. Correct relative and absolute references as needed.
- Numbers or dates do not match: Check whether the cells contain real numeric values or dates rather than text that only looks like a number or date. Text may not compare as expected.
- Some cells have unexpected colors: Open Manage Rules and check for other rules covering the same cells, their order, and whether Stop If True is enabled.
- The rule misses new rows: Check whether the new cells are included in Applies to. Extending a range or using an Excel table can help keep the data and formatting organized as rows are added.
- A formula rule does not trigger: Verify that the formula returns TRUE for a test cell, that it starts with an equals sign, and that the formula uses the correct references for the selected range.
If the worksheet has accumulated rules over time, use Manage Rules to remove obsolete entries and narrow rules to the ranges where they are needed. To remove formatting rules, select the relevant cells and choose Home > Conditional Formatting > Clear Rules. The menu offers options to clear rules from selected cells or from the entire worksheet, so check the selection before choosing.
Keep formatting useful and readable
Conditional formatting is most helpful when it makes a meaningful exception or pattern easier to spot. Too many competing colors and icons can make a sheet harder to read, especially when several rules overlap. Prefer a small, consistent set of visual signals, and use labels or a legend when the meaning of a color or symbol might not be obvious.
Consider accessibility as well as appearance. A color-only distinction may be difficult for some readers to interpret, and colors can look different when printed or viewed on another screen. Pair color with clear text, a symbol, or a separate status field when the distinction is important. For color scales, think about whether the gradient helps readers understand the values or merely adds visual noise.
For large worksheets, avoid applying complex rules to entire columns when only a smaller data range is needed. Keeping “Applies to” focused makes rules easier to review and can reduce unnecessary work as the workbook changes. It also makes it clearer which cells the rule is intended to govern.
Excel on Mac and Excel for the web
In Excel for Microsoft 365 on Windows, the main ribbon path is Home > Conditional Formatting. Excel for Mac also provides conditional formatting from the Home tab, although dialogs and some controls can be arranged differently. The core idea of selecting cells, creating a rule, and managing its range is the same; if a specific control is not immediately visible, inspect the Conditional Formatting menu and its rule-management options.
Excel for the web supports common conditional formatting tasks, but the available controls can differ from the desktop app, especially for more complex rule editing. If a workbook contains a rule that is difficult to modify in the browser, open it in the desktop version of Excel and use Manage Rules. Before changing a shared workbook, consider whether other users depend on its existing rules and whether their Excel version supports the same options.
Frequently Asked Questions
Do conditional formatting rules change the cell value?
No. A rule changes the cell’s appearance when its condition is met; it does not replace or edit the underlying value. The displayed format can change while formulas still use the original value.
Can I format an entire row based on one cell?
Yes. Select the rows or range to format, create a formula-based rule, and write the formula relative to the first row in the selected range. Use a dollar sign before the condition column if it should stay fixed as the rule applies across the row.
Why does my conditional formatting formula use dollar signs?
Dollar signs lock a reference’s column, row, or both when Excel evaluates the rule across a range. For example, a reference such as $D2 keeps column D fixed while allowing the row number to change.
How do I remove conditional formatting?
Select the cells and choose Home > Conditional Formatting > Clear Rules, then choose the option for the selected cells. To remove rules across the worksheet, choose the worksheet option instead; check which cells are selected before clearing.
Can two conditional formatting rules apply to the same cell?
Yes. Multiple rules can cover the same cell and may both be true. Use Home > Conditional Formatting > Manage Rules to review their priority, formatting, Applies to ranges, and Stop If True settings.
Related
- Can You Merge Cells in an Excel Table? What to Do Instead
- Can You Password Protect a Sheet in Excel? Here’s How
- Excel Row Groups: Grouping, Labels, and Naming Limits
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Make a Monthly Budget Plan in Google SheetsTo make a monthly budget plan in Google Sheets, create columns for spending categories, planned amounts, and actual…
- Outlook Closing Signature: How to Create OneTo create a closing signature in Outlook, open the signature settings for your version, write your sign-off and…
- How to Set Out of Office in Outlook on the WebTo set an out-of-office reply in Outlook on the web, open Settings, find Automatic replies, turn them on,…
- Google Sheets Calendar: Create and Customize a Month ViewCreate a calendar view in Google Sheets by entering the first day of a month in cell B1,…
- How to Change View Options in Outlook for a Better InboxTo change how your inbox looks in Outlook for Microsoft 365 on Windows, open the folder you want…
- Create Rules in Outlook on the Web: A Step-by-Step GuideTo create a rule in Outlook on the web, open Settings, go to Mail > Rules, choose Add…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.