In Excel, How to highlight missing values?

In Excel, How to highlight missing values?
Last Updated on 12/06/2024
Reading time: 2 minutes

How to highlight the missing values with a color in Excel?

  1. Select 2 columns

    Both columns must be selected. To select more than one column, you must used the Ctrl Key

  2. Apply the conditional formatting tool

    Among the different conditional options, one has been specially created to highlight missing values.

Use a formula to highlight the missing values

In this previous article, you have detailed how to find missing values with the VLOOKUP function. But if you don't find yourself confident to use this function, here is another technique to find missing values with the conditional formatting tool.

NA means missing values

Step 1: Select the 2 columns

The technique is not difficult at all, 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 > Conditional formatting
  2. Highlight Cells Rules
  3. Duplicates (yes, it looks strange, but that's the menu you must select)
Conditional formatting Menu Duplicates

Step 3: Select the Unique option

Here is the trick!

  1. In the dialog box, go to the dropdown list
  2. There, you select 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

Of course, you can customize the formatting with the Rule Manager (Home > Conditional Formatting > Manage rules)

Menu Manage Rules
Menu Manage Rules

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 12/06/2024

How to highlight the missing values with a color in Excel?

  1. Select 2 columns

    Both columns must be selected. To select more than one column, you must used the Ctrl Key

  2. Apply the conditional formatting tool

    Among the different conditional options, one has been specially created to highlight missing values.

Use a formula to highlight the missing values

In this previous article, you have detailed how to find missing values with the VLOOKUP function. But if you don't find yourself confident to use this function, here is another technique to find missing values with the conditional formatting tool.

NA means missing values

Step 1: Select the 2 columns

The technique is not difficult at all, 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 > Conditional formatting
  2. Highlight Cells Rules
  3. Duplicates (yes, it looks strange, but that's the menu you must select)
Conditional formatting Menu Duplicates

Step 3: Select the Unique option

Here is the trick!

  1. In the dialog box, go to the dropdown list
  2. There, you select 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

Of course, you can customize the formatting with the Rule Manager (Home > Conditional Formatting > Manage rules)

Menu Manage Rules
Menu Manage Rules

Leave a Reply

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