Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Subtotal function in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Create Scatter Chart in Excel
  • Remove Duplicates Example in Excel

References

  • How to use Excel LOOKUP Function
  • How to retrieve first match between two ranges in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Lookup entire row in Excel
  • How to get address of last cell in range in Excel

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation number multiple 100
  • Excel Data validation date in specific year
  • Excel Data validation whole percentage only
  • Excel Data validation specific characters only

Highlight missing values in Excel

by

This tutorial shows how to Highlight missing values in Excel using the example below;

Formula

=COUNTIF(list,A1)=0

Explanation

To compare lists and highlight values that exist in one but not the other,  you can apply conditional formatting with a formula based on the COUNTIF function. For example, to highlight values A1:A10 that don’t exist C1:C10, select A1:A10 and create a conditional formatting rule based on this formula:

=COUNTIF($C$1:$C$10,A1)=0

Note: with conditional formatting, it’s important to enter the formula relative to the “active cell” in the selection, which is assumed to be A1 in this case.

How this formula works

This formula is evaluated for each of the 10 cells in A1:D10. A1 will change to the address of the cell being evaluated, while C1:C10 is entered as an absolute address, so it won’t change at all.

The key to this formula is the =0 at the end, which “flips” the logic of the formula. For each value in A1:A10,  COUNTIF returns the number of times the value appears in C1:C10. As long as the value appears at least once in C1:C10, COUNTIF will return a non-zero number and the formula will return FALSE.

But when a value is not found in C1:C10, the COUNTIF returns zero and, since 0 = 0, the formula will return TRUE and the conditional formatting will be applied.

Named ranges for simple syntax

If you name the list you are searching (C1:C10 in this case) with a named range, the formula is simpler to read and understand:

=COUNTIF(list,A1)=0

This works because named ranges are automatically absolute.

Case-sensitive version

If you need a case sensitive count, you can use a formula like this:

=SUMPRODUCT((--EXACT(A1,list)))=0

The EXACT function performs a case-sensitive evaluation and SUMPRODUCT tallies the result. As with the COUNTIF, this formula will return when the result is zero. Because the test is case-sensitive, “apple” will show as missing even if “Apple” or “APPLE” appears in the second list. See this page for a more detailed explanation.

Post navigation

Previous Post:

VLOOKUP with two client rates in Excel

Next Post:

How to Create Gantt Chart in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • IF, AND, OR and NOT Functions Examples in Excel
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • How to use Excel OR Function

Date Time

  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Add days to date in Excel
  • Get week number from date in Excel
  • Convert decimal seconds to Excel time
  • Calculate years between dates in Excel

Grouping

  • Map inputs to arbitrary values in Excel
  • Group numbers with VLOOKUP in Excel
  • Group times into 3 hour buckets in Excel
  • Map text to numbers in Excel
  • Group numbers at uneven intervals in Excel

General

  • List sheet names with formula in Excel
  • How to calculate project complete percentage in Excel
  • How to create dynamic named range with INDEX in Excel
  • Creating and Opening an existing file in Excel
  • How to generate random number between two numbers in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning