Excel and Compare Two Columns in Excel is a common function for businesses and individuals to manage data. One of common tasks in Excel is to compare two columns to find differences or similarities between them. Here below, we will cover various methods to compare two columns in Excel. Such as using conditional formatting, VLOOKUP and IF function. These each can help for finding differences.
Table of Contents
Using excel conditional formatting to Compare Two Columns in Excel
Conditional formatting is a useful feature in Excel. Because it highlights cells based on certain conditions. This feature can compare two columns and highlight their differences or similarities.
In case you need to use conditional formatting to compare two columns in Excel, you may follow these steps.
- Firstly, you can select the cells you want to compare. For example, if you want to compare column A and column B, you select both columns.
- You will go to the Home tab and click on Conditional Formatting.
- And you will select Highlight Cells Rules and then Duplicate Values.
- In the Duplicate Values, you should select Unique or Duplicate. This is depending on whether you want to find unique values or duplicates.
- You can now choose the formatting options. Such as font color or fill color and click OK.
Using VLOOKUP to compare columns in excel: vlookup comparing two columns
VLOOKUP in Excel can search for a specific value in a table and return a corresponding value. This function can compare two columns in Excel.
how to use vlookup to compare two columns
- You should create a new column for comparing first.
- In the new column, you will enter the following formula: =VLOOKUP(A1,B:B,1,FALSE)
- A1 is the cell in the first column you want to compare.
- B:B is cells in the second column.
- 1 means you want to return the first column in the range (B:B).
- FALSE means you want an exact match.
- So, you can copy the formula down to the other cells or in a new space.
- Now, you can filter the new column to show only the rows where the values are different.
Using IF Function to Compare Two Columns in Excel: match formula in excel
The IF function in Excel can perform logical tests and return different values. This function can work for comparing two columns in Excel by conditioning on differences.
In case you wanna use IF function to compare two columns in Excel, here are the steps.
- You can now create a new column next to the two columns of comparison.
- In new column, you can use this formula: =IF(A1=B1,”Match”,”No Match”)
- A1 is the cell in the first one want to compare.
- B1 is the cell in the second to compare.
- Now, it is as easy as to copy the formula down to the other cells.
- Again you can filter the new column to show different values.
Step 1: how to find difference between two columns in excel? Open Excel and Load Data
To compare two columns in Excel, you first need to open the program and load the data you want to compare. Also, you can do this by opening a new workbook or an existing one.
Step 2: Selecting the Columns to Compare
Once your data is here, you need to select the columns you want to compare. You will be dragging your mouse to select the entire column. Then, you can hold down the Ctrl key and click on the second column to select.
Step 3: Use Conditional Formatting to Highlight Differences
Conditional formatting feature can highlight the differences between two columns. To do this, select the cells you want to apply conditional formatting and go to Home tab. Now, you can click on Conditional Formatting button and select Highlight Cell Rules > More Rules. In Format Cells box, you should select Custom Format and enter the formula =A1<>B1 . Here we are assuming A1 and B1 are the first cells in the columns you want to compare. Then, you will choose a format to highlight the differences and lastly, you click OK.
Step 4: Use Excel Formulas to Identify Differences
One of popular formulas is the IF function. To use this formula, you will enter the formula “=IF(A1=B1,””,”Different”)” into a cell and drag it down. This formula will compare the values in cells A1 and B1. And it returns the word Different if they are not the same.
Step 5: Use Excel’s Data Comparison Tools
In Excel, you can also use Data Validation feature. Here, you will select the cells you want to compare and go to Data menu. Now, you will click on Data Validation and select Data Validation again. Also, in the Settings tab, you should select List from the “Allow” drop-down menu. And next step is entering the values from the second column you want to compare in Source box. Then, you can click OK to close. Any values in the first column do not match the values in the second column will pop up.
Step 6: Clean Up Your Data
Before you compare two columns in Excel, you should have a clean and consistent data.
Conclusion
Comparing two columns in Excel is necessary especially when dealing with large datasets. However, it can be done with the right tools and techniques.
how to compare two columns in excel
In conclusion, whether you need Comparing two columns in Excel for data analysis or other purposes, several methods can help you.
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.
