To highlight cells or entire rows in Excel based on a condition, select the range, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that returns TRUE for the cells you want highlighted, choose a format, and confirm. This uses conditional formatting, so the highlight updates when the worksheet data changes.
What You’ll Need
- Excel for Microsoft 365 on Windows. The steps are similar in current Excel for Mac; some dialog labels may differ slightly. Excel for the web also supports many formula-based conditional formatting rules, though its controls are arranged differently.
- A worksheet with the data you want to check and a clear idea of the condition to highlight, such as a status, a repeated value, or a number above a limit.
- The range you want formatted. Decide whether the rule should color only one column or each cell across a matching row.
Step-by-Step
- Arrange the data and decide what the highlight means. For example, imagine a task list with task names in column A, owners in B, due dates in C, and statuses in D. You might want to shade every task row marked Overdue. Keep a header row above the data, and identify the first data row; in this example, it is row 2. The row number in your formula must correspond to that first row.
- Select the cells that should receive the formatting. To shade full task rows, select A2:F100, assuming the table extends through column F and row 100. To shade only the status cells, select D2:D100 instead. The selection is important: Excel applies the rule to the selected range, while the formula determines which cells in that range meet the condition. Do not include the header unless you also want the header evaluated by the rule.
- Open the formula-based rule dialog. On the Windows ribbon, choose Home > Conditional Formatting > New Rule. In the New Formatting Rule window, select Use a formula to determine which cells to format. This option lets you write a condition that refers to worksheet values rather than choosing a fixed comparison from a preset rule.
- Enter a formula for the row-status example. With task statuses in D2:D100 and the selected range A2:F100, enter
=$D2="Overdue". The dollar sign before D locks the check to the status column, but the row number remains relative so each row checks its own status. When D2 contains Overdue, the formula returns TRUE for the first row and Excel formats A2:F2. When Excel evaluates the next row, it checks D3, then D4, and so on. If a status is not Overdue, the formula returns FALSE and that row is not formatted. - Choose how the matching cells should look. Click Format in the rule window, then use the tabs in the Format Cells dialog to choose a fill color, font color, bold text, or a border. For a row highlight, a light fill usually keeps the text readable. Click OK to close Format Cells, then OK again to create the rule. The exact shade is a design choice; the formula controls which cells qualify, not the color.
- Check the rule and the Applies to range. Choose Home > Conditional Formatting > Manage Rules. In the rule manager, confirm that the formula is correct and that Applies to covers the intended cells, such as =$A$2:$F$100. If the range is wrong, select the rule and edit the Applies to box. In the Show formatting rules for menu, choose the current selection or worksheet as needed to find the rule. Confirm your changes when finished.
- Try a rule that highlights duplicate entries. With product names in A2:A100 and that same range selected, create another formula rule using
=COUNTIF($A$2:$A$100,A2)>1. COUNTIF counts how many times the value in the current row appears in the full list. If A2:A5 contains North, South, North, and West, the count for North is 2, so both North cells meet the condition and are highlighted; South and West each occur once and are not. The dollar signs keep the list being searched fixed, while A2 shifts to the cell being checked. - Use a numeric condition when the rule is about a threshold. With amounts in column C, records in rows 2 through 50, and A2:F50 selected, use
=$C2>1000to shade every row where the amount is greater than 1,000. A row with 1,250 returns TRUE and is highlighted; a row with 900 returns FALSE and is not. If you want to color only the amounts, select C2:C50 instead. To include exactly 1,000, change the comparison to greater than or equal to in the formula.
In these examples, the formula is entered without typing a leading apostrophe. Excel evaluates the rule for each cell in the Applies to range. The first cell in the selected range is the reference point for relative references: when the selection starts in row 2, use row 2 in the formula. The dollar signs make references absolute in one or both directions; use them selectively so the rule can move across rows or columns as intended.
Common Mistakes
- Using the wrong starting row. If the selected range starts at row 2 but the formula checks row 1, each formatted row may be based on the header or the row above it. Make the row number in the formula match the top row of the selected data.
- Locking the row when it should change. In the status example,
=$D$2="Overdue"checks D2 for every row, so all selected rows follow the same status. Use a locked column and a changing row when checking a field in each row. - Leaving out needed dollar signs. If you want a status in column D to control a whole row, a reference that shifts from D to E as Excel formats cells across the row can test the wrong column. Lock the column with a dollar sign.
- Selecting only part of the intended area. A correct formula cannot format cells that are outside the Applies to range. Select the full table width if the whole row should change, or edit the rule range in Manage Rules.
- Expecting a formula rule to change cell values. Conditional formatting changes appearance, not the underlying text or number. It does not replace a value, sort records, or permanently color cells independent of the rule.
Tips
- Test the condition in a spare cell before creating the rule if you are unsure what it returns. For example, with the status in D2, enter
=D2="Overdue"in an unused cell. If D2 contains Overdue, the test should display TRUE. Remove the test formula afterward if it is not needed. - Use an easy-to-read fill color and check the result against several records, including one that should match and one that should not. A subtle fill can distinguish flagged rows without making the sheet hard to scan.
- Use Manage Rules to review overlapping conditional formatting. If two rules apply to the same cells, both may affect the result. The rule manager lets you inspect their order and, where relevant, use Stop If True to prevent lower rules from being applied after a matching rule.
- Keep the rule’s Applies to range aligned with your data. If you add new rows later, extend the range or apply the rule to an Excel table column so the formatting can cover added records. Check the result after expanding the data.
- For dates, compare actual Excel date values rather than text that only looks like a date. A cell formatted to display a date may still contain text, which can make a comparison behave differently than expected.
Troubleshooting
Nothing is highlighted. Check that the formula returns TRUE for a row you expect to match. Confirm that the text is spelled the same way as the rule’s condition and that the cell is not blank or storing a different value. Then open Home > Conditional Formatting > Manage Rules and verify that the rule applies to the selected cells. If you changed the data range, update Applies to.
The wrong rows are highlighted. Check the formula’s row number against the first row of the Applies to range. Also inspect references: a locked row can make all records check the same cell, while an unlocked column can make a whole-row rule look in different columns. For a row-based test, lock the column being checked and allow the row number to adjust.
Excel rejects the formula. Make sure it begins with an equals sign, that any text value is enclosed in straight double quotation marks, and that parentheses are balanced. Some regional settings use a different list separator in formulas; if Excel flags a formula with multiple arguments, check the list separator expected by your installation. Also make sure you chose the formula rule type rather than a preset rule that expects a value or range.
The rule worked, but the color is not visible. Reopen the rule’s Format settings and check that a fill or font color was actually selected. Another conditional formatting rule or a manually applied format may also affect what you see. Review rules that apply to the same range in Manage Rules, and temporarily remove or adjust conflicting rules to identify the cause.
New rows are not highlighted. The rule may stop at its original last row. Expand its Applies to range in Manage Rules, or use an Excel table for the data and check whether the rule covers the added rows. If you copy a row that already has conditional formatting, inspect the resulting range to ensure you have not created overlapping or duplicate rules.
Frequently Asked Questions
Can I use a formula to highlight an entire row?
Yes. Select the full row-width range you want formatted, such as A2:F100, and use a formula that checks the appropriate column while allowing the row number to change. Choose a fill in Format. The full row is highlighted when the condition is TRUE.
Can I highlight a cell based on another cell?
Yes. Select the cells that should receive the color, create a formula rule, and refer to the cell that controls the condition. For example, the status-column rule checks column D while applying formatting across columns A through F. Use dollar signs to keep a reference fixed where needed.
Does the highlight update when I change the data?
Yes. Conditional formatting recalculates as values change, so a row can gain or lose its highlight when its status or amount changes. If it does not update, check that calculation is enabled and verify that the conditional formatting rule still covers the edited cell.
Can I highlight duplicates with a formula?
Yes. A COUNTIF-based rule can count each value within a fixed list and highlight values that occur more than once. Select the list itself if only duplicate cells should be colored. Check whether you want every occurrence highlighted or only later repeats, because those are different conditions.
Can I highlight blanks or cells containing a word?
Yes. Formula rules can test for blank cells, text, dates, or other conditions. Choose a condition that returns TRUE for the intended cells, and make sure its references match the top-left cell and range selected. For text checks, account for how your data is spelled and whether extra spaces may be present.
Will conditional formatting change the cell’s value?
No. It changes how a cell appears while the condition is true; the stored value remains unchanged. To change values, use a worksheet formula, a data-cleaning step, or another appropriate Excel feature instead of conditional formatting.
Related
- How to Find Duplicate Entries in Excel
- Excel Date Difference: Calculate Days, Months, or Years
- Find Common Names in Two Excel Sheets: A Quick Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- AVERAGEIF Excel Range: Syntax, Examples, and Common FixesIn Excel, the range in AVERAGEIF is the group of cells Excel checks against your condition; the optional…
- Excel Sheet Formula List: Essential Functions and ExamplesAn Excel sheet formula list is a reference to formulas and functions you can use to calculate, summarize,…
- Excel Sheet Filter Shortcut: Turn Filters On and OffIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn sheet filters on or off. With the…
- Excel Sheet Number Sequence: How to Create OneTo create a number sequence in an Excel worksheet, put =SEQUENCE(10) in a blank cell with room below…
- Excel Sheet Comparison Formula: Find DifferencesTo compare the same cell on two Excel sheets, enter =IF(Sheet1!A1=Sheet2!A1,"Match","Different") on a third sheet, then fill the…
- Excel Shared Workbook: Filter Just for Yourself SafelyYes. In Excel for Microsoft 365, use a Sheet View before filtering: open the View tab, choose Sheet…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.