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

Data Analysis

  • What-If Analysis: Scenarios and Goal Seek in Excel
  • How to Use Solver Tool in Excel
  • How To Compare Two Lists in Excel
  • How To Load Analysis ToolPak in Excel
  • Chart Axes in Excel

References

  • Basic INDEX MATCH approximate in Excel
  • How to get first column number in range in Excel
  • Merge tables with VLOOKUP in Excel
  • How to get last column number in range in Excel
  • How to use Excel LOOKUP Function

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation must begin with
  • Excel Data validation date in next 30 days
  • Excel Data validation date in specific year
  • Excel Data validation allow weekday only

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

  • OR function Examples in Excel
  • Extract multiple matches into separate rows in Excel
  • SWITCH function example in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get first day of previous month in Excel
  • Get work hours between dates custom schedule in Excel
  • Get days between dates ignoring years in Excel
  • Convert Excel time to decimal minutes
  • Count times in a specific range in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Group numbers with VLOOKUP in Excel
  • How to randomly assign people to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • Map text to numbers in Excel

General

  • List sheet names with formula in Excel
  • How to Insert Cells, Row and Rows in Excel
  • Subtotal invoices by age in Excel
  • How to Create Calendar in Excel
  • How to generate random times at specific intervals in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning