Regarding data analysis between similar lists or different ones, Microsoft Excel and Comparing Two Lists in Excel are one of most popular software programs. It comes with many features for users. So we can create and compare data. One of the most useful features is the ability to compare two lists in Excel.
Table of Contents
In case you are trying to find duplicates or differences, you can use functions. So they can help you in this task. Here in this guide, we will cover everything about comparing two lists in Excel and formulas you need to apply.
Understanding the Basics of Lists: how do i compare two lists in excel
Before we look at different methods for comparing two lists, it’s essential to understand what a list is. In Excel, a list is simply a range of cells that contains related data. Lists can be used for various purposes. Such as keeping track of inventory, customer information or employee salaries.
When comparing two lists in Excel, it is important to ensure structure correctly. Ideally, both lists should have the same number of rows and columns. And the data should be arranged in a similar format.
Comparing Two Lists in Excel Method 1: Using Conditional Formatting
Conditional formatting is great tool for highlighting differences between two lists. It can format cells based on specific criteria. Such as whether the cell contains a certain value or text.
If you wanna use conditional formatting to compare two lists, you will follow these steps.
- As a starter, you select the first list. And you can click on the Conditional Formatting button in the Home.
- Then you can select Highlight Cells Rules and then choose Duplicate Values.
- In the Duplicate Values box, you will select Duplicate or Unique depending on what you wish to highlight.
- Now you click OK and Excel will highlight the cells meeting criteria.
- Here you can repeat the process for the second list and then compare the highlighted cells for differences.
excel comparing lists 2: Using VLOOKUP, vlookup to compare two lists
VLOOKUP is quite handy and must know function in Excel. Because with that, you can search for a specific value in a range of cells. Then the formula returns a corresponding value from another column.
If you wanna use VLOOKUP to compare two lists, you will do it like below.
- First, you can create a new column next to the first list. And you can enter the VLOOKUP formula in first cell.
- In the VLOOKUP formula, you will specify the value you want to search for. Then, the range of cells to search in and the column number with needed value.
- Then you will copy the formula down the column to apply it.
- Now you can repeat the process for the second list and identify any differences.
Method 3: Using EXACT Function: how to compare two lists in excel and highlight duplicates
The EXACT function in Excel can compare two cells and determine whether they contain the same text. This function is particularly useful when comparing two lists that contain text data.
You can use the EXACT function to compare two lists with below steps:
- First of all, you will create a new column next to the first list. And now, you enter the EXACT function in the first cell.
- In the EXACT function, you will specify the cell to compare and the cell with necessary value in the second list.
- Now you can have this formula down the column for copying.
- Again, you will do this process for the second list and then compare then compare.
Comparing Two Lists in Excel Method 4: Using the CONCATENATE Function
Excel CONCATENATE function is useful for combining two or more cells into one cell. This function is particularly good when comparing two lists with multiple data columns.
excel compare two lists for duplicates
In case you wanna compare two lists using the CONCATENATE function in Excel, you should learn and apply below steps.
- Now firstly, you should create a new column next to the first list. And then you will enter the CONCATENATE function in the first cell. For example, if you wanna combine the first name and last name columns in the first list, you can use the formula =CONCATENATE(A2, ” “, B2). Here we are assuming the first name is in column A. While the last name is in column B. This will create a new column containing the individuals’ full names tough.
- Second steps is copying the formula in the first cell. And then, you will paste it in the remaining cells in the new column.
- You can have same steps for the second list and creating a new column. With the full names of the individuals in that list.
- Now, you will use the VLOOKUP function to compare the two lists. In a new column, you should enter the formula =VLOOKUP(C2, D:E, 2, FALSE). This formula will return the value in the second column of the second list that matches the full name in column C of the first list.
- All left is now Copying the formula in the first cell. And then, you will paste it in the remaining cells in the new column.
If you are using the CONCATENATE function and the VLOOKUP function in Excel, you can quickly compare two lists containing multiple data columns. This formula can save you time and effort when working with large datasets.
A dedicated Career Coach, Agile Trainer and certified Senior Portfolio and Project Management Professional and writer holding a bachelor’s degree in Structural Engineering and over 20 years of professional experience in Professional Development / Career Coaching, Portfolio/Program/Project Management, Construction Management, and Business Development. She is the Content Manager of ProjectCubicle.
