To apply conditional formatting in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting and pick a rule or choose New Rule to create one. Conditional formatting changes how cells look when their values meet conditions; it does not change the values themselves.
What conditional formatting does
Conditional formatting makes patterns in a worksheet easier to spot. A rule can highlight a late date, mark negative amounts, shade values according to a scale, or add bars that compare numbers. Excel updates the appearance as the underlying values change, so a status color does not need to be reapplied manually every time data is revised.
The formatting is a visual layer, not a replacement for the cell’s contents or number format. A red fill does not mean a cell contains a particular status unless a rule defines that relationship. A cell can also have ordinary formatting—such as a number format or manually applied font style—alongside conditional formatting. When a condition is true, the conditional format can override some visual properties, such as fill or font color.
Apply a built-in rule
Built-in rules are the quickest choice when you want to highlight values by a straightforward comparison. For example, to identify sales below a target, select the sales cells and use the Greater Than or Less Than rule. The dialog lets you enter a comparison value and choose a preset style or a custom format.
- Select the cells to format. Include data cells, but leave out a heading row unless you intend to format it too.
- On the ribbon, choose Home > Conditional Formatting.
- Choose a rule category, such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, or Icon Sets.
- Set the requested value or threshold, then choose a formatting style. For a custom appearance, use the formatting option in the rule dialog.
- Check that the result highlights the intended cells. If it does not, use Home > Conditional Formatting > Manage Rules to inspect the rule and its range.
For example, selecting a column of scores and choosing Highlight Cells Rules > Less Than lets you enter a passing threshold such as 60. Excel highlights each selected value below 60. The rule compares actual cell values, not the text color or fill already present in the cells.
Create a custom rule with a formula
A formula rule is useful when the condition depends on more than a single comparison or when the formatting should respond to another column. Select the cells to format, choose Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, enter a formula, and choose Format to set the appearance. The formula must return TRUE or FALSE for each cell Excel evaluates.
For example, assume due dates are in D2:D100 and you want to highlight overdue dates while leaving blank cells unformatted. Select D2:D100 and create a formula rule with =AND($D2<>"",$D2<TODAY()). The dollar sign fixes the column, while the row number remains relative so the rule checks each row’s date. The empty-cell check matters because a blank can otherwise behave like a low value in comparisons.
To highlight duplicate names, assume names occupy B2:B100. Select B2:B100 and use =COUNTIF($B$2:$B$100,B2)>1. The fixed range remains the same for every comparison, while B2 shifts as Excel evaluates each cell. Every name appearing more than once is highlighted, including all occurrences rather than only later repeats.
To format an entire row based on a status, suppose the data occupies A2:D100 and the status appears in column D. Select A2:D100 and use =$D2="Late". The fixed D column makes every cell in a row check that row’s status. The spelling in the formula must match the cell text; extra spaces or different wording can prevent a match.
Choose the right rule type
Different rule types answer different questions. A basic comparison is clearest when a reader needs to know whether a value is above or below a fixed target. A color scale is better for seeing relative variation across a range, while data bars show approximate magnitude alongside the number. Icon sets can summarize categories, but icons may be less clear if users do not know what each symbol means.
- Highlight Cells Rules: flag values equal to, greater than, less than, or between specified values; they also include text, date, and duplicate-value options.
- Top/Bottom Rules: identify high or low values, percentages, or values above or below an average.
- Data Bars: compare magnitudes visually within cells while retaining the values.
- Color Scales: apply a gradient based on each value’s position between selected low, middle, and high points.
- Icon Sets: assign symbols to value bands using thresholds.
Do not use a color scale to imply an absolute pass/fail threshold unless its settings actually represent that threshold. A gradient usually communicates relative position in the selected range: the same value can receive a different shade if other values are added or removed.
Understand rule ranges and references
Every rule applies to a range. That range may be a single column, a block of cells, or an entire table area. Formula rules are evaluated relative to the upper-left cell of the selected range, which is why the row and column references in a formula matter. A reference such as A2 can shift in both directions; $A2 fixes the column; A$2 fixes the row; and $A$2 fixes both.
When a rule is applied to multiple columns, consider which part should move as Excel evaluates each cell. For a whole-row status rule, lock the status column but leave the row relative. If you lock both the column and row, every cell may test the same status cell instead of the one on its own row. A quick check is to inspect a few cells in the Applies to range and confirm the intended row or column is being tested.
When adding records, verify that the Applies to range includes the new rows. Excel tables often extend formatting as rows are added, but a rule created on a fixed range may not cover data pasted beyond its last row. In Manage Rules, select the relevant worksheet in the rules list and review the Applies to field before expanding or correcting the range.
Manage order, overlaps, and removal
More than one conditional formatting rule can apply to the same cell. Excel evaluates rules in the order shown in Home > Conditional Formatting > Manage Rules. If two rules set the same property, such as fill color, their order can affect which appearance is visible. Move a rule up or down when needed. The Stop If True option can prevent lower-priority rules from being evaluated when a higher rule is true; use it only when that behavior is intended.
The Manage Rules window also lets you edit a rule, change its range, or delete it. To remove formatting without deleting the cell contents, choose Home > Conditional Formatting > Clear Rules, then select whether to clear rules from the selected cells or the entire worksheet. Clearing a rule is different from using the normal Clear command or deleting data, so check the selected option before applying it.
Copying and pasting cells can also affect conditional formatting. A paste may bring rules from the source cells, and repeated pastes can create several overlapping rules. If the worksheet starts behaving inconsistently, inspect Manage Rules for duplicate or unnecessarily fragmented ranges rather than assuming the displayed color is manually applied.
Common problems and practical checks
If nothing changes, first confirm that the rule’s Applies to range contains the cells you selected and that the condition is true for at least one of their values. Check whether numbers are stored as text, whether dates are genuine Excel dates, and whether a formula rule refers to the correct row. For text-based conditions, check for trailing spaces or inconsistent labels. The rule can be correct but still produce no visible result if its chosen format is indistinguishable from the worksheet’s existing appearance.
If the wrong rows are highlighted, review relative and absolute references. A common mistake is writing a formula for the first cell of a selected range but using a row number from somewhere else. Another is fixing a row or column that should change during evaluation. Confirm the selected range’s upper-left cell, then test the formula’s logic against that first cell before checking the rest of the range.
If colors seem unexpected, examine overlapping rules and their order, then check the thresholds for color scales, bars, or icons. Automatic thresholds may be based on the values in the range, so inserting an outlier can change the appearance of many cells without changing their values. To make the visual meaning stable, configure explicit thresholds where the rule type allows it and explain the scale to worksheet users.
Finally, conditional formatting does not validate data. A cell can be highlighted as suspicious, but the user can still enter an invalid value unless a separate control—such as data validation or a formula-based check—prevents or flags it. Treat formatting as a way to draw attention, not as proof that all entries are correct.
Excel for Mac and Excel for the web
In Excel for Mac, select the cells and use the Home tab’s Conditional Formatting controls; the dialog layout and some commands can differ from Windows. Excel for the web also provides conditional formatting from Home > Conditional Formatting, though available editing controls can vary from the desktop app. For a complex formula rule or a rule that is difficult to manage in the browser, open the workbook in the desktop version and inspect the rule there. The underlying idea—select a range, define a condition, and choose a format—is the same.
Frequently Asked Questions
Does conditional formatting change the cell value?
No. It changes the cell’s appearance when a rule is met. The value, formula, and ordinary number format remain separate from the conditional formatting rule.
Can I highlight an entire row based on one cell?
Yes. Select the full data range, create a formula rule, and use a reference that locks the condition’s column but leaves its row relative. For example, a status in column D can control formatting across columns A through D when each row is evaluated independently.
How do I highlight duplicate values?
Select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a format in the dialog. For more control over the range or condition, use a formula rule with COUNTIF.
Why did conditional formatting stop applying to new rows?
The rule may have a fixed Applies to range that ends above the new records. Open Manage Rules and extend the range, or use an Excel table when appropriate so new table rows can inherit table behavior and formatting.
Can I use more than one conditional formatting rule on a cell?
Yes. Multiple rules can apply to the same cell, and their order can affect the result when they set the same visual property. Review and reorder them in Manage Rules, and use Stop If True only when lower rules should be skipped after a match.
Related
- Excel Consolidate: How to Combine Data Across Sheets
- Consolidate in Excel: Example, Steps, and Common Issues
- Consolidate Data in Excel Across Multiple Worksheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- TRIM Excel Not Working? Fix Spaces That TRIM MissesIf TRIM appears not to work in Excel, the text may contain a character that TRIM does not…
- Excel Divide by Zero Error: Causes and Practical FixesTo fix a #DIV/0! error in Excel, check that the formula’s divisor is not zero or blank, then…
- Microsoft Excel VLOOKUP Not Working? Fix Common ErrorsIf Microsoft Excel VLOOKUP is not working, first set its fourth argument to FALSE for an exact match,…
- Excel Consolidate: Fix Overlapping Source ReferencesYes. In Excel for Microsoft 365 on Windows, Data > Consolidate can reject a source reference if it…
- Transpose Not Showing in Excel? Fix the Missing OptionIf Transpose is not showing in Excel, copy the source cells first, then select a single empty destination…
- SUMIFS Between Dates Not Working? Common FixesIf your dates are in A2:A100, amounts are in B2:B100, and the start and end dates are in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.