How to highlight the missing values with a color in Excel?
- Select 2 columns
Both columns must be selected. To select more than one column, you must used the Ctrl Key
- 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.
Step 1: Select the 2 columns
The technique is not difficult at all, but there is a trick for selecting the 2 columns.
- Select the range of cells in the first column.
- Press the Ctrl key
- Select the range of cells in the second column.
Step 2: Open the conditional formatting tool
- Go to the menu Home > Conditional formatting
- Highlight Cells Rules
- Duplicates (yes, it looks strange, but that’s the menu you must select)
Step 3: Select the Unique option
Here is the trick!
- In the dialog box, go to the dropdown list
- There, you select the option Unique.
Immediately, the missing values between the 2 columns are in red
Of course, you can customize the formatting with the Rule Manager (Home > Conditional Formatting > Manage rules)
