In Excel, How to highlight missing values?

In Excel, How to highlight missing values?
Last Updated on 28/09/2023
Reading time: 2 minutes

How to highlight the missing values with a color

  1. Select 2 columns

    Both columns must be selected

  2. Apply the conditional formatting tool

    Select the Unique option

Step 1: Select the 2 columns

This step is not difficult but there is a trick for selecting the 2 columns.

  1. Select the range of cells in the first column
  2. Press the Ctrl key
  3. Select the range of cells in the second column
Select 2 columns with the Ctrl key

Step 2: Open the conditional formatting tool

  1. Go to the menu Home > Conditionnal formatting
  2. Highlight Cells Rules
  3. Duplicates (yes it looks crazy but it's the menu to select)
Conditional formatting Menu Duplicates

Step 3: Select the Unique option

Here is the trick! In the dialog box, you select in the dropdown list, the option Unique

Select the option Unique in the dialog box

Immediately, the missing values between the 2 columns are in red

Missing values colored in red in the 2 columns

Use a formula to highlight the missing values

As you can see, with this technique you necessarily colored the missing values in the 2 columns. But if you want to find the missing values for only one column, you must use a formula.

NA means missing values

Leave a Reply

Your email address will not be published. Required fields are marked *

In Excel, How to highlight missing values?

Reading time: 2 minutes
Last Updated on 28/09/2023

How to highlight the missing values with a color

  1. Select 2 columns

    Both columns must be selected

  2. Apply the conditional formatting tool

    Select the Unique option

Step 1: Select the 2 columns

This step is not difficult but there is a trick for selecting the 2 columns.

  1. Select the range of cells in the first column
  2. Press the Ctrl key
  3. Select the range of cells in the second column
Select 2 columns with the Ctrl key

Step 2: Open the conditional formatting tool

  1. Go to the menu Home > Conditionnal formatting
  2. Highlight Cells Rules
  3. Duplicates (yes it looks crazy but it's the menu to select)
Conditional formatting Menu Duplicates

Step 3: Select the Unique option

Here is the trick! In the dialog box, you select in the dropdown list, the option Unique

Select the option Unique in the dialog box

Immediately, the missing values between the 2 columns are in red

Missing values colored in red in the 2 columns

Use a formula to highlight the missing values

As you can see, with this technique you necessarily colored the missing values in the 2 columns. But if you want to find the missing values for only one column, you must use a formula.

NA means missing values

Leave a Reply

Your email address will not be published. Required fields are marked *