Home ALL NEWS & INFO DIGITAL SKILLS How to Compare Two Lists in Microsoft Excel

How to Compare Two Lists in Microsoft Excel

42
0
How to Compare Two Lists in Microsoft Excel How to Add Numbers in Microsoft Excel If you need to get the sum of two or more numbers in your spreadsheets, Microsoft Excel has multiple options for addition. We’ll show you the available ways to add in Excel, including doing it without a formula.

How to Compare Two Lists in Microsoft Excel

Microsoft Excel offers two different methods to help you compare two lists and highlight the missing items in each one.

You can choose to highlight the missing items in both lists or in a single one. We’ll show you how.

The Quick Way: Highlight Unique Cells to Compare Lists

A quick way to compare two lists in your spreadsheet is to use Excel’s unique highlight feature. This feature highlights the items in a list that are not found in the other list. This way you know exactly what items are missing from your lists.

To use the method, first, select the lists you want to compare in your spreadsheet.

Select Both Lists.

While your lists are highlighted, in Excel’s ribbon at the top, click the “Home” tab.

Select "home" At The Top.

On the “Home” tab, in the “Styles” section, click Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Choose Conditional Formatting > Highlight Cells Rules > Duplicate Values.

In the “Duplicate Values” box, click “Duplicate” and choose “Unique.” Click the second drop-down menu and choose how you would like to highlight the missing items. To specify your own formatting, choose “Custom Format” from the menu.

Then apply your changes to your lists by clicking “OK.”

Highlight Unique Cells In Excel.

Excel will highlight the missing items in your lists. For example, in your first list, you will have those items highlighted that are missing from the second list, and so on.

Two Lists Compared In Excel.

And that’s it.

The Formula Way: Use Conditional Formatting to Compare Lists

The above method highlights items in both your lists. If you’d only like to highlight missing items in a specific list, then use a formula with conditional formatting as explained below.

First, in your spreadsheet, select all rows of your first list. Then, in the top-left corner, click the text box, type FirstList, and press Enter. This assigns a unique name to your range of cells, which lets you refer to all these cells using a single name.

Assign "firstlist" To The First List.

Assign a unique name to your second list by first selecting all rows of your list. Then, in the top-left corner, click the text box, type Second List, and press Enter.

Assign "secondlist" To The Second List.

Select all rows of your first list by clicking the text box in the top-left corner and choosing “First List.”

Choose "firstlist."

RELATED ARTICLES

Basic Excel Functions Everybody Should Know
Essential Microsoft Excel Functions for Budgeting
Ways to Concatenate in Microsoft Excel
How to List and Sort Unique Values and Text in Microsoft Excel
How to Make One Page Landscape in Microsoft Word
Ways How to Rearrange Pages in Word
How to Make a Table of Contents in Word
How to Add Numbers in Microsoft Excel
How to Use Conditional Formatting to Find Duplicate Data in Excel
How to Find the Percentage of Difference Between Values in Excel
How to Compare Two Lists in Microsoft Excel

In Excel’s ribbon at the top, click the “Home” tab and choose Conditional Formatting > New Rule.

Select Conditional Formatting > New Rule.

On the “New Formatting Rule” window, you’ll specify how your missing items will be highlighted. On this window, from the “Select a Rule Type” section, choose “Use a Formula to Determine Which Cells to Format.”

In the “Format Values Where This Formula is True” box, type the following:

=COUNTIF(SecondList,A1)=0

Select the “Format” button and specify how you’d like to format the missing items in your list. Then, save your changes by clicking “OK.”

Format Cells To Compare Lists In Excel.

Back on the spreadsheet, Excel has highlighted the items in your first list that are missing from the second list. Your job is done.

Two Lists Compared In Excel.

And that’s how you quickly perform a comparison of two different lists in your Excel spreadsheets. Very useful!

RELATED ARTICLES

Basic Excel Functions Everybody Should Know
Essential Microsoft Excel Functions for Budgeting
Ways to Concatenate in Microsoft Excel
How to List and Sort Unique Values and Text in Microsoft Excel
How to Make One Page Landscape in Microsoft Word
Ways How to Rearrange Pages in Word
How to Make a Table of Contents in Word
How to Add Numbers in Microsoft Excel
How to Use Conditional Formatting to Find Duplicate Data in Excel
How to Find the Percentage of Difference Between Values in Excel
How to Compare Two Lists in Microsoft Excel

LEAVE A REPLY

Please enter your comment!
Please enter your name here