Yes—Microsoft Excel includes conditional formatting. It changes how cells look when they meet rules you set, making values, dates, text, and trends easier to spot without changing the underlying data.
What Conditional Formatting Means in Excel
Conditional formatting is a feature that applies visual formatting automatically when a condition is true. For instance, you can make overdue dates red, highlight sales above a target, or shade the largest values in a range. If the data changes and a rule’s condition is no longer met, Excel updates the formatting accordingly.
The formatting is visual, not a change to the cell’s contents. A cell containing 125 remains 125 even if a rule displays it with a red fill and bold text. The rule does not replace a formula, add a label, or alter the value used in calculations. This distinction matters when you sort, filter, print, or share a workbook: the data remains available in its original form, while the formatting helps people interpret it.
So if you mean, “Does Excel have conditional formatting?” the answer is yes. If you mean, “Can Excel highlight cells containing Yes or No?” that is also yes: text-based rules can format cells containing either word.
How Conditional Formatting Works
A rule connects a condition to a format. Excel checks the cells covered by the rule, evaluates the condition, and displays the chosen formatting for cells that qualify. Conditions can be based on a fixed value, a comparison, text, dates, duplicates, or a formula. Some rules, such as color scales, use the values in the range to show relative differences rather than simply marking cells that pass a yes-or-no test.
Conditional formatting is usually dynamic. If a value changes from 80 to 120 and the rule highlights values greater than 100, the cell’s appearance changes when Excel recalculates. A formula-based rule can also refer to other cells, so a cell’s format can depend on a status, date, or target elsewhere in the worksheet.
Rules apply to a specified range, often called the “Applies to” range in the rule manager. One rule may cover a single column, a table, or an entire block of rows. Choosing the correct range is important: a sound condition applied to the wrong cells can produce confusing results.
How to Add a Basic Rule
In Excel for Microsoft 365 on Windows, select the cells you want to format, then use Home > Conditional Formatting. Choose a rule category, set its condition, select a format, and confirm. For example, to highlight values above 100, select the number range and choose Highlight Cells Rules > Greater Than. Enter 100, choose a style, and select OK.
For a Yes/No column, select the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter Yes, choose a format, and confirm. Add a separate rule for No if you want it to have a different appearance. These text rules are useful for quick visual distinctions, but they do not change the text or force a cell to contain only those choices.
A practical setup usually follows these steps:
- Select the exact cells that should respond to the rule.
- Choose a built-in rule or create a new rule under Home > Conditional Formatting > New Rule.
- Set the condition and choose a readable font, fill, border, data bar, or color scale.
- Check the result against several cells, including values just above and below the threshold.
- Revisit the rule manager if the formatting appears in unexpected places.
Useful Rule Types and Examples
Excel offers several built-in approaches, and the best choice depends on what the reader needs to notice. Highlight Cells Rules handle comparisons and text; Top/Bottom Rules identify rankings; Data Bars show approximate magnitude inside cells; Color Scales show relative values with a gradient; and Icon Sets use symbols to categorize values. The exact available options can vary slightly with the Excel version and the selected rule.
- Flag exceptions: Highlight inventory quantities below a reorder point or quality scores below a minimum.
- Show progress: Use data bars for task completion percentages, while keeping the percentage values visible.
- Compare results: Use a color scale for a range of monthly figures when relative highs and lows matter more than a single cutoff.
- Spot repeated entries: Use a duplicate-values rule to draw attention to repeated invoice numbers or names that may need review.
- Track dates: Use date rules to identify dates in a selected period, or use a formula when “overdue” depends on another status field.
Consider the purpose before choosing a visual. A color scale is useful for scanning a continuous range, but it may exaggerate small differences if the values are tightly grouped. A simple highlight is often clearer when a specific threshold determines action.
Formula-Based Conditional Formatting
Formula rules let you express conditions that the built-in menus may not cover. In Excel for Microsoft 365 on Windows, select the target range, choose Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format. Enter a formula that returns TRUE for cells to format, choose the desired style, and confirm.
For a row-highlighting example, assume the worksheet has dates or names in columns A and C, sales figures in column B, and records in rows 2 through 20. Select A2:D20 as the range to format. The formula below checks the sales figure in column B for each row:
=$B2>100
The dollar sign fixes the column reference at B while the row number remains relative. As Excel evaluates each row in the selected range, it checks that row’s value in column B. The rule therefore highlights the whole row when its sales figure is greater than 100. The value 100 itself does not qualify because the comparison uses >, not >=.
For an exact Yes/No example, assume the cells A2:A50 contain status text and you want to format the cells whose value is Yes. Select A2:A50 and use this formula as the rule:
=A2="Yes"
The relative reference starts with the first cell in the selected range, so Excel evaluates each cell in turn. If the formula seems to format the wrong rows, check both the first-cell reference and the dollar signs. Relative and absolute references determine whether the rule moves across columns, down rows, or stays fixed on a particular cell.
Rule Order and Managing Existing Rules
When several conditional formatting rules apply to the same cells, their order can affect the result. Open Home > Conditional Formatting > Manage Rules to inspect rules, adjust their priority, edit conditions, or change the ranges they affect. In the manager, check whether the display is showing rules for the current selection or the whole worksheet; otherwise, a rule you expect to see may be outside the current view.
Higher-priority rules are evaluated before lower-priority rules. When rules set conflicting formats, priority determines which formatting takes precedence. The Stop If True option can prevent lower-priority rules from being evaluated for cells where a higher-priority rule is true. Use it deliberately: it can simplify layered rules, but it can also suppress formatting you expected to see.
Rules can accumulate when cells are copied, pasted, or formatted repeatedly. If a worksheet suddenly contains many similar rules, review the manager and remove unnecessary ones rather than adding more exceptions on top. You can clear formatting rules for selected cells or a worksheet from Home > Conditional Formatting > Clear Rules; take care to choose the intended scope.
Common Problems and How to Fix Them
Conditional formatting that looks wrong is often caused by the range, formula references, or rule order—not by a problem with the cell values. Check the following before rebuilding a rule:
- The wrong cells are highlighted: Inspect the rule’s “Applies to” range and confirm that it begins and ends where intended.
- A formula shifts unexpectedly: Check whether the row or column reference needs a dollar sign. For row-wide formatting, the checked column is often fixed while the row changes.
- A text condition misses some entries: Look for extra spaces or inconsistent wording. A cell that looks like Yes may contain a trailing space or different text.
- One rule seems to override another: Review priority, overlapping ranges, and the Stop If True setting.
- Colors are hard to interpret: Use stronger contrast and avoid relying on color alone to communicate an important status. Keep the underlying value or a text label visible.
Also check whether the cells contain numbers stored as text. A comparison rule may not behave as expected if entries that look numeric are actually text values. Confirm the data type and, where appropriate, convert the entries to numbers before relying on numeric conditions.
When to Use It—and When Not To
Conditional formatting is a good fit when people need to scan a changing worksheet for exceptions, thresholds, trends, or categories. It is especially useful in trackers and reports that are updated regularly, because a rule can respond to new values without requiring someone to recolor cells manually each time.
It is less suitable when the appearance itself must be permanent, when a color is being used as the only record of a decision, or when a complicated set of rules makes the workbook hard to maintain. Formatting is not a substitute for a clear data model: keep meaningful statuses in cells, use formulas for calculations, and use conditional formatting to draw attention to those results. If a printed report must communicate clearly in grayscale, test it in print preview and include labels or symbols where color alone would be ambiguous.
Excel Versions and Availability
Conditional formatting is a standard Excel feature; it is not a separate paid add-on. The instructions above use Excel for Microsoft 365 on Windows. Excel for Mac also provides conditional formatting from Home > Conditional Formatting, though dialog layouts and some controls can differ. Excel for the web includes conditional formatting under the Home tab, but the desktop app provides fuller options for creating and managing complex rules. If a workbook contains advanced rules, open it in desktop Excel when you need precise control over them.
Frequently Asked Questions
Does Excel have conditional formatting?
Yes. Excel can apply formatting automatically when cells meet rules you define, including comparisons, text matches, dates, duplicates, formula tests, and visual scales.
Can conditional formatting highlight Yes or No?
Yes. Select the relevant cells and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Create separate rules if Yes and No should look different.
Does conditional formatting change the cell value?
No. It changes the cell’s appearance, not its contents. A number, date, or status remains the same value for formulas and other workbook operations.
Can conditional formatting color an entire row?
Yes. Select the rows or table range, create a formula-based rule that checks the relevant column, and use relative and absolute references carefully so the test follows each row.
Why is conditional formatting not working?
Check the rule’s applied range, its priority, the formula’s cell references, and the underlying data type. Extra spaces in text or numbers stored as text can also prevent a condition from matching as expected.
Related
- Excel Flash Fill: Automatically Fill Repeating Patterns
- Automatically Merge Duplicate Cells in Excel? What Works
- Automatically Remove Duplicates in Excel: Methods and Risks
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 See Named Ranges in Excel and Check Their CellsTo see a named range in Excel, open Formulas > Name Manager to view its name and the…
- How to Make Columns in Word After a Title or HeadingTo put text in columns below a full-width title in Microsoft Word, select the body text and choose…
- How to Lock Selected Cells in Google Sheets EasilyTo lock selected cells in Google Sheets, select the cells, open Data > Protect sheets and ranges, add…
- Excel Table Color Not Changing? Common Causes and FixesIf an Excel table’s color is not changing, select a cell inside the table, open Table Design, and…
- How to Remove Filters and Sorting in Excel QuicklyTo remove filter arrows in Excel, select a cell in the filtered range and choose Data > Sort…
- How to Insert a Row in Excel Between Existing DataTo insert a row between existing data in Excel, select the row heading immediately below where you want…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.