How To Compare Lists in Microsoft Excel

Microsoft Excel is a powerful tool for managing and comparing lists. If you need to compare two lists in Excel, it is fairly simple to do so using some basic functions and tools. Comparing lists allows you to find differences between items, identify items that are missing from one list, and more.

It's useful in both professional projects and personal spreadsheets, so let's take a look at how to compare lists in Excel!

Why Compare Lists in Excel?

There are many reasons why you might want to compare two lists in Excel. For example, if you are working on a project and need to keep track of different items and their status, you may want to create two lists and compare them regularly to see which items have been completed or updated.

You can also use these comparing techniques to make sure customer address data is precise by verifying against internal and external records. There may be two separate columns if you are preparing a spreadsheet for customer addresses. Compare these columns to cross-reference the company-maintained list with user-supplied information to spot any discrepancies or typos before they become an issue.

There are many different ways you can compare lists in Excel:

Use the equal sign operator
Use row difference
Match row difference using the IF function
Highlight matching data with conditional formatting
Use VLOOKUP

Let us dive in a little bit deeper and take a look at how each of these methods works.

Method 1. Highlight Matching Data With Conditional Formatting

The first method for comparing lists in Excel is to use conditional formatting. This is an easy and intuitive way to quickly identify matching data by highlighting important rows or columns in your spreadsheet.

Select your data.
Using the ribbon interface, click on the Conditional Formatting button and choose Highlight Cell Rules > Duplicate Values from the context menu.
Make sure that you select Duplicate from the first drop-down menu. You can also pick a color for your cells using the second drop-down menu. When you’re done, click OK.
Alternatively, you can select Unique from the first drop-down menu to only highlight cells with unique data that are not repeated anywhere else in your list.

Using this technique, you can easily highlight matching cells and find differences between lists in any version of Excel.

Method 2. Match Data With Row Difference

The second method for comparing lists in Excel is to use the row difference function. This allows you to identify all rows that are different within two specified lists, making it easy to spot any discrepancies or mistakes.

Select your data.
Press the F5 key on your keyboard. This Excel shortcut will speed up your process by opening the Go To dialog box for you.
Click on the Special... button.
Select Row differences from the available options, and then click the OK button.
Excel will automatically select all the cells that have a row difference. For easy reference, you can highlight these cells with color.

This technique is especially useful if you’re comparing lists in a database. However, we have some more ways you can easily find differences between lists in Excel. Continue reading to find out more!

Method 3. Match Row Difference Using the IF Function

The IF function is another useful tool for comparing lists in Excel. This allows you to create a formula that will return values of TRUE if the two lists match and FALSE if there are any differences between them.

Start an IF condition cell somewhere in your document. In this example, we'll be using cell C2. Type in the following formula: =IF(A2=B2,"Matching","Not Matching")
Apply the formula by pressing the Enter key on your keyboard.
Hover over the bottom-right corner of the cell, and click on the small green square. Drag your mouse cursor down to apply the formula to all cells in your sheet.

Using the IF formula is one of the fastest ways to find differences between lists in Microsoft Excel. However, there are a couple more ways you can try if you need a different approach.

Method 4. Compare Lists Using the Equal Sign Operator

You can also compare lists in Excel by using the equal sign operator. This allows you to create a formula that will return TRUE if two lists are identical and FALSE if they contain any differences.

Create a Status column next to your data, and then type in the following formula: =A2=B2. Please note that depending on your spreadsheet, you might need to adjust the cell letters and numbers.
This formula will identify if the A2 value is equal to the B2 value. If the values match, it will result in "TRUE". If the values are different, the result will be "FALSE".
Drag the formula down to apply it to the rest of your data.

Method 5. Use the VLOOKUP Function

You can use the VLOOKUP function in Excel to quickly compare two lists of data by searching one list based on the value of another. To do this, follow these steps:

Select an unoccupied cell to display the results of the VLOOKUP function. Click on Conditional Formatting > New Rule under the Home tab.
Enter "=VLOOKUP(" into the cell where you want your result to appear, followed by the letter-number combination denoting the empty cell. Then, add the cells for both target columns and include the number of items in each.
To search through only one other column for comparison, add the value "1". If you want an exact match, provide the number "0". Finally, don't forget to add parenthesis to complete it.
The final formula should read something like =VLOOKUP(B1,A1:A10,1,0), which you can input by pressing Enter on your keyboard.
To search for all the values in your column B list, copy this formula to all the cells below it.

Conclusion

Whether you need to compare customer data, project tasks, or other types of information, there are many different tools and techniques available for comparing lists. We hope that this guide has helped you learn how to compare lists in Microsoft Excel and start using this powerful tool for your own projects and work!

One More Thing

Looking for more tips? Check out our other guides in the SoftwareKeep Blog and our Help Center! You’ll find a wealth of information on how to troubleshoot various issues and find solutions to your tech problems.

Sign up for our newsletter and get early access to our blog posts, promotions, and discount codes. Plus, you'll be the first to know about our latest guides, deals, and other exciting updates!

Find anything you need

Compare Lists in Excel: How To Find Differences Between Lists in Excel

Why Compare Lists in Excel?