Function Problems

How to Highlight Values Missing from Another Column in Excel

Muhammad Talha Oct 1, 2026 869 views

Question details

Highlight values in one column that do not appear in another reference column.

How to Highlight Values Missing from Another Column in Excel

Product: Excel
Device & OS: not provided
Scenario: Comparing two lists of data within a spreadsheet to identify and highlight missing records.
Observed behavior: The user needs to visually isolate unique values in the target column that are absent from the reference column, ensuring that blank cells are not incorrectly highlighted.

Before you start

Ensure that both columns of data you want to compare are in the same worksheet, and take note of their exact cell ranges (for example, target range B2:B100 and reference range G2:G25).

Solution 1Recommended

Use COUNTIF and AND Functions in Conditional Formatting

Apply a custom formula rule using the COUNTIF function to identify missing values, combined with the AND function to exclude empty cells from being highlighted.

This method involves creating a new conditional formatting rule based on a logical formula. The formula checks each cell in your target column against the reference column. If the count is zero (meaning it is missing) and the cell is not blank, the formatting is applied.

Select the target range

Click and drag to select the cells in the column you want to format, such as B2:B100. Make sure B2 is the active cell in your selection.

Open the Conditional Formatting menu

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

Enter the custom formula

Choose the option 'Use a formula to determine which cells to format'. In the formula box, enter: =AND(COUNTIF($G$2:$G$25,B2)=0,B2<>"") replacing $G$2:$G$25 with your actual reference column range.

Apply a fill color

Click the Format button, go to the Fill tab, and choose a distinct color like red to highlight the missing values. Click OK twice to apply the rule to your data.

Use COUNTIF and AND Functions in Conditional Formatting

Understanding the Formula: The COUNTIF($G$2:$G$25,B2)=0 part checks if the value in B2 does not exist in column G. The B2<>"" part ensures that blank cells in column B are ignored and not highlighted in red.

Analyze Data Faster

Highlight Missing Values Easily in WPS Spreadsheet

WPS Spreadsheet fully supports custom conditional formatting formulas like COUNTIF and AND. You can effortlessly compare lists, highlight missing data, and analyze your spreadsheets with a familiar and highly compatible interface.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing the two columns you wish to compare.
2. Select your data range: Highlight the target column data (e.g., Column B) that needs to be checked for missing values.
3. Create a new formatting rule: Go to the Home tab, click Conditional Formatting, select New Rule, and choose 'Use a formula to determine which cells to format'.
4. Input the formula and format: Enter the formula =AND(COUNTIF($G$2:$G$25,B2)=0,B2<>""), click Format to select a highlight color, and click OK.

Fully compatible with Microsoft Excel (.xlsx) formulas and conditional formatting rules.Free, lightweight, and fast office suite for Windows, Mac, and Linux users.Familiar user interface ensures a seamless transition with zero learning curve.Built-in advanced data comparison tools to boost your daily productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Can I highlight values missing from a column on a different sheet?

Yes, you can reference a different sheet in your COUNTIF formula by including the sheet name. For example: =AND(COUNTIF(Sheet2!$G$2:$G$25,B2)=0,B2<>"").

Why are my blank cells getting highlighted by the conditional formatting rule?

If you only use the COUNTIF function, Excel may treat blank cells as zeroes and highlight them. To prevent this, always pair COUNTIF with the AND function and add the condition B2<>"" to explicitly ignore empty cells.

How do I highlight matching values instead of missing ones?

To highlight values that exist in both columns, modify the formula to look for a count greater than zero. Use this formula instead: =COUNTIF($G$2:$G$25,B2)>0.

Does this conditional formatting formula work for text values as well as numbers?

Yes, the COUNTIF formula evaluates both text strings and numerical values identically, making it perfect for comparing names, product codes, or inventory numbers.

Excel & Data Problems