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

Data Analysis

  • Example of COUNTIFS with variable table column in Excel
  • Conditional Formatting Data bars Examples in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • Create Scatter Chart in Excel
  • How to Create Gantt Chart in Excel

References

  • Approximate match with multiple criteria in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • How to get address of named range in Excel
  • How to use Excel INDIRECT Function
  • How to get last row in text data in Excel

Data Validations

  • Excel Data validation with conditional list
  • Excel Data validation specific characters only
  • Excel Data validation number multiple 100
  • Excel Data validation don’t exceed total
  • Excel Data validation exists in list

How To Compare Two Lists in Excel

by

These example describes how to compare two lists using conditional formatting.

Example 1: Compare Lists of Customers for May 2010 and April 2010.

  1. Select cells in both lists (select first list, then hold CTRL key and then select the second)
  2. Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values
  3. Press ok.

See other examples

Example 2: You may have two lists of NFL teams.

To highlight the teams in the first list that are not in the second list, execute the following steps.

1. First, select the range A1:A18 and name it firstList, select the range B1:B20 and name it secondList.

2. Next, select the range A1:A18.

3. On the Home tab, in the Styles group, click Conditional Formatting.

4. Click New Rule.

5. Select ‘Use a formula to determine which cells to format’.

6. Enter the formula =COUNTIF(secondList,A1)=0

7. Select a formatting style and click OK.

Result. Miami Dolphins and Tennessee Titans are not in the second list.

Explanation: =COUNTIF(secondList,A1) counts the number of teams in secondList that are equal to the team in cell A1. If COUNTIF(secondList,A1) = 0, the team in cell A1 is not in the second list. As a result, Excel fills the cell with a blue background color. Because we selected the range A1:A18 before we clicked on Conditional Formatting, Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =COUNTIF(secondList,A2)=0, cell A3 =COUNTIF(secondList,A3)=0, etc.

8. To highlight the teams in the second list that are not in the first list, select the range B1:B20, create a new rule using the formula =COUNTIF(firstList,B1)=0, and set the format to orange fill.

Result. Denver Broncos, Arizona Cardinals, Minnesota Vikings and Pittsburgh Steelers are not in the first list.

Post navigation

Previous Post:

TRUE function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Pie Chart

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

  • Return blank if in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Invoice status with nested if in Excel
  • OR function Examples in Excel
  • How to use Excel OR Function

Date Time

  • Add days exclude certain days of week in Excel
  • Get project end date in Excel
  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • YEAR function: Description, Usage, Syntax, Examples and Explanation

Grouping

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

General

  • Share Excel data with Word documents
  • How to calculate percentage discount in Excel
  • How to generate random number between two numbers in Excel
  • Basic text sort formula in Excel
  • How to Delete Cells, Row and Rows in Excel
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning