ThisCalculator

Everyday

Highlight Cells That Equal: Excel Conditional Formatting

Updated · Standard formula, shown below

This guide explains how to highlight cells in Microsoft Excel that equal a specific value, using conditional formatting. You enter the value you want to match, and Excel instantly applies a fill color or font style to every cell that contains exactly that value. The process takes under a minute and works for numbers, text, and dates.

Why Highlight Equal Values?

Highlighting cells that equal a certain value is one of the most common data-cleaning tasks in Excel. It helps you spot duplicates, flag specific statuses like “Paid” or “Overdue,” or isolate a particular product SKU in a long list. Instead of scanning hundreds of rows manually, conditional formatting does the visual work for you, and it updates automatically if you change the data later.

How to Highlight Cells That Equal a Specific Value

Follow these steps for Excel 2013 through Microsoft 365 (the menus are nearly identical in all recent versions):

  1. Select the range of cells you want to check. For example, click and drag from A2 to A100 if your data is in column A.
  2. On the Home tab, click Conditional Formatting in the Styles group.
  3. Choose Highlight Cells Rules > Equal To…
  4. In the dialog box, type the value you want to match. You can type a number, text (no quotes needed), or a date. For example, type Paid or 42.
  5. Select a formatting style from the dropdown (e.g., Light Red Fill with Dark Red Text).
  6. Click OK. Every cell in your selected range that equals that value now gets the formatting.

Using a Cell Reference Instead of a Fixed Value

Often you want to highlight cells that equal a value you might change later. Instead of typing the value directly, you can reference a cell.

In the Equal To dialog box, click the icon at the right of the input box, then click a cell that contains your target value (e.g., $D$1). Now, if you change the value in D1, the highlighting updates automatically. This is especially useful for dashboards or when you want to quickly test different thresholds.

Highlighting Entire Rows When One Cell Equals a Value

The basic rule only highlights the matching cell itself. To highlight the entire row, you need to use a formula-based rule.

  1. Select the whole data range (e.g., A2:F100).
  2. Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  3. Enter a formula that locks the column letter of the cell you want to check. For example, if your status is in column C, use: =$C2="Paid"
  4. Click Format and choose a fill color, then click OK.

The dollar sign before C locks the column, while the row number (2) is relative, so each row is evaluated based on its own status cell.

Common Mistakes and How to Avoid Them

  • Matching partial text: The Equal To rule matches the entire cell content. If you want to highlight cells that contain a substring (e.g., any cell with “apple” in “pineapple”), use Text that Contains instead.
  • Case sensitivity: By default, the rule is not case-sensitive. “PAID” and “paid” are treated the same. If you need case-sensitive matching, you must use a formula with EXACT (e.g., =EXACT(A2,"Paid")).
  • Numbers stored as text: If a cell looks like a number but is actually text (left-aligned by default), the equal rule may not match. Use =VALUE(A2)=42 in a formula rule to force numeric comparison.
  • Applying to the wrong range: Always double-check the “Applies to” field in the Conditional Formatting Rules Manager. If you selected only one cell before creating the rule, it will only apply to that cell.

Alternatives to Conditional Formatting

If you don’t need dynamic highlighting, you can use Find & Select (Ctrl+F) to locate equal values, but that doesn’t leave a permanent visual mark. Another option is to use Excel’s Filter to temporarily show only rows that equal a certain value. For a one-off check, a simple filter might be faster. However, conditional formatting is the only method that keeps the highlight visible while you continue editing other parts of the sheet.

Frequently Asked Questions

How do I highlight cells that equal any of several values?

Use a formula rule with the OR function. For example, to highlight cells equal to “Red”, “Green”, or “Blue”, select your range and create a new rule with =OR(A2="Red",A2="Green",A2="Blue"). Alternatively, you can create three separate Equal To rules, but the formula method keeps everything in one rule.

Why isn’t my equal-to rule working?

Check the cell format. If your data contains leading or trailing spaces, the equal rule will fail because ” Paid” is not equal to “Paid”. Use =TRIM(A2)="Paid" in a formula rule to ignore spaces. Also verify that numbers are truly numeric—use the ISNUMBER function to test.

Can I highlight cells that equal a blank?

Yes. In the Equal To dialog, leave the input box empty and click OK. Excel will highlight truly empty cells. Note that cells with a formula that returns “” are not considered empty; for those, use a formula rule like =A2="".

How do I remove the highlighting later?

Select the range, go to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes all conditional formatting from that range. To remove only one rule, open the Rules Manager, select the rule, and click Delete Rule.

Does highlighting slow down my spreadsheet?

Conditional formatting is recalculated whenever the sheet changes, so very large ranges (100,000+ cells) with many rules can cause lag. To improve performance, limit the applied range to only the cells that actually contain data, and avoid using volatile functions like TODAY() in your rules unless necessary.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.