Formatting Issues

How to Apply Conditional Formatting for Values in an Excel List

Amos Gikunda Oct 22, 2026 868 views

Question details

The user needs to format cells in a column if their values match any item present in a predefined list, aiming to do this with a single rule instead of creating dozens of separate rules.

How to Apply Conditional Formatting for Values Found in an Excel List

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing data sets and visually highlighting matches using a reference list.
Observed behavior: A dynamic, formula-based conditional formatting rule is required to check each cell against an entire column of reference values.

Before you start

Ensure both your target column (where formatting will be applied) and your reference list (the values to match against) do not contain extra leading or trailing spaces, as these can prevent exact text matches.

Solution 1Recommended

Use the COUNTIF Formula for Conditional Formatting

Create a single formula-based rule using the COUNTIF function to dynamically check and highlight matching values across a designated list.

Instead of setting up multiple rules for each specific value, you can use the COUNTIF function. This function counts how many times a value appears in a range. By checking if the count is greater than zero, the conditional formatting rule automatically knows if the value exists in your reference list.

Select the Target Range

Highlight the cells you want to format. For example, select all the data in Column A.

Open Conditional Formatting

Navigate to the 'Home' tab on the ribbon, click 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Choose the Formula Option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the COUNTIF Formula

Input the formula: =COUNTIF($D:$D, A1)>0 (assuming your reference list is in Column D and your highlighted target range starts at cell A1). Adjust the column letters and starting cell as needed.

Apply Formatting

Click the 'Format' button, select your desired fill color or font style for the highlighted cells, and click 'OK' twice to apply the rule.

Use the COUNTIF Formula for Conditional Formatting

Absolute vs Relative References: Using the dollar sign ($) for the reference list column (e.g., $D:$D) creates an absolute reference, ensuring the lookup range stays fixed as Excel evaluates each relative cell (e.g., A1, A2, A3) in your target column.

WPS Spreadsheet Solution

Highlight List Matches Easily in WPS Spreadsheet

WPS Spreadsheet offers robust conditional formatting tools that are perfectly compatible with Excel formulas, allowing you to highlight duplicates or match lists effortlessly.

1. Open your document: Launch WPS Spreadsheet and open the .xlsx file containing your data sets.
2. Select the Data: Highlight the target data range you wish to apply the conditional formatting to.
3. Create a New Rule: Navigate to Home > Conditional Formatting > New Rule on the top toolbar.
4. Input the Formula: Select the formula option, input your COUNTIF formula, customize your highlight color, and click OK.

Free, lightweight, and fast spreadsheet application100% compatible with Microsoft Excel formulas and .xlsx formatsIntuitive conditional formatting interface for data visualizationCross-platform support for Windows, Mac, Linux, and mobile

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting rule highlighting the wrong cells?

This usually happens if the starting cell in your formula (e.g., A1) does not match the very first active cell of your highlighted selection. Ensure the relative reference exactly points to the top-left cell of your selected target range before applying the rule.

Can I use a list located on a different worksheet for conditional formatting?

Yes. You can reference another sheet by including the sheet name in the formula. For example, use =COUNTIF(Sheet2!$A:$A, A1)>0 to check values against Column A on a worksheet named 'Sheet2'.

How do I highlight cells that do NOT match the reference list?

You can change the mathematical condition in your COUNTIF formula to equal zero. Use the formula =COUNTIF($D:$D, A1)=0 to highlight values in your target range that are missing from the reference list.

Document & File Issues