In Excel, select the cells you want to format, then choose Home > Conditional Formatting and pick a rule, such as Highlight Cells Rules or Data Bars. Conditional formatting changes a cell’s appearance automatically when its value meets a condition, making patterns and exceptions easier to spot without changing the underlying data.
What conditional formatting does
Conditional formatting applies visual formatting—such as a fill color, font color, border, data bar, color scale, or icon—to cells when specified conditions are true. The appearance can update when the cell values change. For example, a sales table can shade values below a target, mark overdue dates, or use bars to compare amounts at a glance.
The formatting is separate from the cell’s value and ordinary number format. A red fill does not alter a number, turn text into a different value, or affect calculations. It is a visual signal layered on top of the worksheet. If a condition stops being true, Excel removes the conditional appearance and displays the cell’s other formatting instead.
Conditional formatting is useful for monitoring lists, budgets, schedules, survey results, and other tables where a reader needs to find particular values quickly. It can also draw attention to duplicates or unusual results. It is not a substitute for checking the data: a highlighted cell tells you that a rule matched, not whether the data or rule is correct.
Where to find the controls
In Excel for Microsoft 365 on Windows, the main controls are on the Home tab in the Styles group: Home > Conditional Formatting. The menu contains built-in rule categories, options for creating rules, and controls for clearing or managing them.
Excel for Microsoft 365 on Mac uses the Home tab and the Conditional Formatting menu as well, though ribbon layout and some dialog details can differ from Windows. In Excel for the web, use Home > Conditional Formatting; the available rule types and rule-management controls may vary from the desktop app. For a complex rule or detailed priority changes, the desktop version generally offers the fuller set of controls.
A rule applies to a range, sometimes called its Applies to range. Selecting the intended cells before creating the rule usually makes this range easier to set correctly. You can later inspect or change it through Home > Conditional Formatting > Manage Rules.
Apply a built-in rule
Built-in rules are the quickest way to start. For instance, to make values below 50 stand out:
- Select the cells containing the values, such as C2:C30. Avoid including a heading unless you want the rule to evaluate it too.
- Choose Home > Conditional Formatting > Highlight Cells Rules > Less Than.
- Enter 50 in the value box, choose a formatting style, and select OK.
Excel checks each selected cell against the condition. A cell containing 42 meets a less-than-50 rule; a cell containing 50 does not. If the value later changes to 55, the conditional formatting disappears because the condition is no longer true.
The built-in dialog may offer preset styles and a custom format option. Choose a readable style that contrasts with the cell contents. If several different rules are needed, create them one at a time and check that each rule applies to the correct range.
Common built-in rule types
The Conditional Formatting menu includes several categories suited to different kinds of comparisons. The rule is evaluated against the selected range, and the format signals which cells meet its criteria.
- Highlight Cells Rules: Find values greater than, less than, between, or equal to a specified value; text containing a phrase; dates in a named time period; or duplicate and unique values.
- Top/Bottom Rules: Highlight items such as the highest or lowest values, a percentage of the range, or values above or below the range average.
- Data Bars: Add bars inside cells to show relative size. They are useful for comparisons, but a bar’s scale and the range being compared matter when interpreting it.
- Color Scales: Shade cells along a two- or three-color gradient according to their values. A scale helps show variation across a range; it does not automatically mean that one color is good or bad.
- Icon Sets: Display symbols such as arrows or traffic-light icons based on thresholds. Check the thresholds and direction so that the icons communicate the intended meaning.
These rules answer different questions. A duplicate rule asks whether a value appears more than once; a color scale shows relative position within a range. A data bar emphasizes magnitude, while a text-containing rule can flag entries such as a status label. Choose the type that matches the decision a reader needs to make.
Use a formula for a custom condition
When the built-in choices do not express the condition you need, create a formula-based rule. A formula can test one cell and apply formatting to another cell or a whole row. The formula must return TRUE or FALSE for the cell Excel is evaluating.
For example, suppose task names are in A2:A20, statuses are in B2:B20, and you want to shade the full task row A2:D20 whenever its status is Late. Select A2:D20, choose Home > Conditional Formatting > New Rule, and choose the option to use a formula to determine which cells to format. With the selected range beginning in row 2, enter =$B2="Late", choose a format, and confirm.
The dollar sign before B keeps the test in column B as Excel evaluates cells across each row. The row number is not fixed, so Excel checks B2 for the first row, B3 for the next, and so on. If the formula were =$B$2="Late", both the column and row would be fixed; the rule would keep checking only B2 for every row. This distinction between relative and absolute references is a common source of unexpected results.
Formula-based rules can also use comparisons and supported worksheet functions, but the formula should be checked against a few sample rows. Make sure its references line up with the top-left cell of the Applies to range. A rule can be logically correct yet appear wrong if it starts on a different row or references the wrong column.
Edit, prioritize, or remove rules
To review rules, select a cell in the affected area and choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, set the display option to show rules for the current selection or the worksheet, then inspect the rule, its format, and its Applies to range. The exact dialog layout can vary slightly between platforms.
A worksheet can have more than one rule affecting a cell. The Rules Manager shows rule priority; when rules conflict, priority and the Stop If True setting can affect which formatting is displayed or whether lower-priority rules are evaluated. If a cell looks different from what you expect, check for overlapping rules rather than assuming the newest rule is the only one in effect. You can move a rule up or down in priority in the desktop Rules Manager.
To remove a rule, use Manage Rules to delete the specific rule, or choose Home > Conditional Formatting > Clear Rules and select whether to clear rules from the selected cells or the entire worksheet. Clearing a rule removes conditional formatting, not the underlying values. If you only want to change the range, edit Applies to rather than rebuilding the rule.
Common problems and how to fix them
- The wrong cells are colored: Check Applies to and confirm that the range includes the intended rows and columns only. A rule copied or extended from another area may cover more cells than expected.
- A formula rule shifts unexpectedly: Review which references need dollar signs. Use a relative row when each row should test its own status, and lock a column when the test should always look in the same column.
- Dates do not match: Confirm that the entries are actual Excel dates, not text that merely looks like a date. Date rules compare stored values, so inconsistent formats or imported text can prevent a match.
- Numbers behave like text: Imported values may be stored as text. Check whether the cells are numeric values and whether the rule’s comparison matches the data type. Changing the display format alone does not always convert text into numbers.
- Nothing appears to happen: Check that at least one cell satisfies the condition and that the selected formatting is visible against the worksheet. Also inspect overlapping rules and the selected range.
For color scales and data bars, remember that Excel evaluates the range specified by the rule. Adding more values or changing the range can alter the visual comparison even when a particular cell’s value stays the same. This is expected: those formats often communicate relative position, not a fixed threshold.
Make conditional formatting easier to read
Use formatting to support a clear interpretation, not to decorate every cell. A restrained color palette helps readers distinguish important exceptions from ordinary data. Include labels or a short note when a color has a business meaning, such as red for overdue, instead of relying on color alone. Color-only signals may be difficult for some readers to distinguish and can be less clear in print or grayscale.
Apply rules to the smallest useful range, use consistent thresholds, and avoid creating multiple rules that communicate the same thing. Test the rule with values that should match and values that should not. If a table grows, consider whether the rule’s range will include newly added rows; an Excel table can help keep related data organized, but check the rule’s Applies to range after structural changes.
Conditional formatting versus manual formatting
Manual formatting stays as you set it until you change it. Conditional formatting is rule-driven: it can update its appearance when values change. That makes it useful for live status tracking, but it also means the visible color may not be obvious from the cell’s ordinary formatting settings. To understand why a cell is colored, inspect the rules rather than applying another manual fill on top.
Conditional formatting is also different from sorting and filtering. It highlights data without rearranging or hiding rows. If you need to isolate all overdue tasks, a filter on the status or date may be more useful; if you need an at-a-glance overview, conditional formatting can make exceptions visible while the full list remains in place. These tools can be used together.
Frequently Asked Questions
Does conditional formatting change the value in a cell?
No. It changes the cell’s appearance when a rule is met; it does not replace the value or change how that value is stored. Formulas that refer to the cell continue to use its underlying value.
Can Excel highlight an entire row based on one cell?
Yes. Select the rows or table range to format, create a formula-based rule, and refer to the status column with a locked column reference and an appropriately relative row. For a range starting at row 2, =$B2="Late" checks column B separately for each row.
Can I highlight duplicate values?
Yes. Select the relevant cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a format and confirm. Check that the selected range is the one in which duplicates should be compared.
Why does a conditional format disappear when I edit a cell?
The edited value may no longer meet the rule, so Excel stops showing its conditional appearance. Review the condition and the cell’s new value. If the value should still match, check its data type, the rule’s range, and any competing rules.
Can I copy conditional formatting to another range?
Yes. You can use Format Painter or copy and paste formats, then inspect Manage Rules to confirm that the new rule applies to the intended cells. Formula references may adjust during copying, so test the copied rule on representative rows.
Can I use more than one conditional formatting rule on a cell?
Yes. Multiple rules can apply to the same cell, but their priority and settings can affect the result when formats conflict. Use Manage Rules to review their order, ranges, and Stop If True settings.
Related
- How to Find and Clear a Circular Reference in Excel
- How to Enter an Absolute Cell Reference in Excel
- How to Use Absolute Cell References in Excel on Dell
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Pull Data Dumps With No Blank Rows in ExcelIf your data dump is in A2:D1000 and column A contains a required record ID, enter =FILTER(A2:D1000,A2:A1000<>""," ")…
- Add an Equation to an Excel Scatter Plot: Steps & TipsTo add an equation to an Excel scatter plot in Microsoft 365 for Windows, select the chart, then…
- Clear Cache and Cookies in Outlook: Step-by-StepTo clear Outlook cookies, remove the site data for Outlook in the browser you use. To refresh cached…
- Add a Drop-Down List in Excel: Setup, Tips, and FixesTo add a drop-down list in Excel for Microsoft 365 on Windows, select the cells, choose Data >…
- Google Sheets Duplicate Drawing: How to Copy a DrawingTo duplicate a drawing in Google Sheets, click the drawing once to select it, then press Ctrl+C and…
- Outlook Webmail Signature: How to Create OneTo create a signature in Outlook webmail, open Settings, find the signature editor, write and format your signature,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.