best way to compare two excel lists

Best Way to Compare Two Excel Lists

By Sarah Smith
AD

The need to compare two lists in Excel typically arises when you want to analyze consistencies, discrepancies, or overlaps between them. This could be for various reasons, such as data verification, identifying errors, or finding opportunities for consolidation. In this guide, we will explore two of the most efficient and user-friendly methods to compare two Excel lists.

Before diving into the methods, ensure you have both lists in separate columns (A and B) in the same Excel sheet for ease of comparison. Assign distinct headings to your columns to avoid confusion. Now, let's delve into the tried-and-true comparisons methods.

Using Conditional Formatting

Conditional formatting is an inbuilt feature in Excel that uses rules to apply specific formatting to cells based on their values. It's a straightforward method to compare Excel lists.

AD
Excel – Using VLOOKUP to compare two lists
Excel – Using VLOOKUP to compare two lists
How to Compare Two Lists in Excel Fast!
How to Compare Two Lists in Excel Fast!
Compare Two Lists in Excel
Compare Two Lists in Excel

Let's start using conditional formatting to highlight differences and similarities between your lists.

Highlighting Differences

Select both columns and apply conditional formatting, choosing 'Highlight Cells Rules' and 'Equal to'. Enter the value from Column A (B1) and choose the formatting (color, icon, etc.).

Even Faster Way to Compare 2 Lists in Excel [Quick Tip] » Chandoo.org - Learn Excel, Power BI & Charting Online
Even Faster Way to Compare 2 Lists in Excel [Quick Tip] » Chandoo.org - Learn Excel, Power BI & Charting Online
compare lists in excel
compare lists in excel
Compare Excel Lists Tool, Find Duplicates, Missing Items, Changes (Excel, Google Sheets)
Compare Excel Lists Tool, Find Duplicates, Missing Items, Changes (Excel, Google Sheets)

Duplicate this for Column B, entering its value instead. This will highlight any differences in values with two distinct colors.

Highlighting Similarities

To highlight the similarities, repeat the steps, but this time, choose 'Highlight Cells Rules' and 'Does Not Equal' for the first column.Enter the value from the other column in the dialog box.

AD

For the second column, choose 'Does Not Equal' and enter the value from the first column. Now, any shared values will appear in the uncolored cells, while differences are highlighted.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation
3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation
an excel chart showing the number of teams in each team's roster and their positions
an excel chart showing the number of teams in each team's roster and their positions
Excel Training | How to Compare Two Excel Sheets and Find Differences. Highlight differences between 2 sheets with conditional formatting #excelfunctions… | Instagram Excel Formula Comparison, How To Understand Excel Operators, How To Compare Two Excel Sheets, Excel File Comparison Tutorial, Excel Formula For And, Practical Excel Training Guide, Advanced Excel Techniques For Professionals, Excel Sheet Comparison Settings, How To Combine Excel Sheets
Excel Training | How to Compare Two Excel Sheets and Find Differences. Highlight differences between 2 sheets with conditional formatting #excelfunctions… | Instagram Excel Formula Comparison, How To Understand Excel Operators, How To Compare Two Excel Sheets, Excel File Comparison Tutorial, Excel Formula For And, Practical Excel Training Guide, Advanced Excel Techniques For Professionals, Excel Sheet Comparison Settings, How To Combine Excel Sheets
How To Compare Two Lists in Excel With 10 advance Methods
How To Compare Two Lists in Excel With 10 advance Methods
How to Compare Two Excel Sheets (for differences)
How to Compare Two Excel Sheets (for differences)
Find Duplicates in Excel Instantly | Smart Formula Trick You Must Know 💥 | Excel Tips for Beginners
Find Duplicates in Excel Instantly | Smart Formula Trick You Must Know 💥 | Excel Tips for Beginners

Using VLOOKUP and IF Functions

Another powerful way of comparing Excel lists is by using the VLOOKUP and IF functions. VLOOKUP checks for the existence of a value in a table or range, while IF checks for a condition and returns a value based on the condition.

Applying VLOOKUP and IF together allows you to cross-check and compare your lists accurately.

AD

Setting Up the VLOOKUP Function

In Column C, enter the VLOOKUP formula: `=VLOOKUP(A1, B:B,1, FALSE)`. This checks if the value in Column A exists in Column B and returns a value if it does.

Now, drag the formula down to the rest of your data. Any cells that don't return a value indicate differences in data.

Using IF to Highlight Differences

In any cell where you want to highlight differences, enter this formula: `=IF(ISNA(E1),"Difference Detected","")`. This checks if the cell in Column E (where you input your VLOOKUP formulas) is showing an error (meaning there’s no match), it labels the cell “Difference Detected”.

AD

Copy and paste this formula into all cells where you want to highlight differences, and your two lists are now effectively compared, making inconsistencies easy to spot.

With these methods, you have the keys to effectively compare your Excel lists. However, always remember to save your work regularly, as these processes can modify your data. Happy comparing!

AD