When working with large Excel spreadsheets, comparing data from two columns can be time consuming. Instead of analyzing the columns and writing down the “match” or “mismatch” in a separate column, you can use Excel’s functions to streamline the process.
We’ll take a look at how to use various Excel functions to compare two columns and identify matching or mismatching data.
1. How to highlight duplicate data
If you want to compare two columns in Excel but don’t want to add a third column to show whether data exists in both columns, you can use the Conditional Formatting feature.
Excel will now highlight the names that are present in both the columns.
2. How to Highlight Unique Data
If you want to identify data that is not part of both the columns, you can use the same function.
While these methods are fairly easy to use, they may not be efficient for large spreadsheets, so you will need to think of an efficient way to organize your spreadsheet. So we’ll take a look at more complex solutions that show you which rows contain the same data or use an additional column to indicate whether the data matches.
3. Highlight rows with similar data
If you need a better visual representation of similar data, you can have Excel find matching values in two columns and highlight the rows with matching data. We’ll use the Conditional Formatting feature as we did on the previous method, but add a few extra steps.
This way, you’ll have a visual indicator that will help you identify matching data faster than reading through a separate column.
To customize how Excel will highlight the rows, click Format, and in the Format Cells window, select the Fill tab. You can choose background color, pattern style and pattern color. You’ll get a sample so you can preview the design. Click OK after completing the customization process.
When comparing two columns in Excel using this method, you can also highlight rows with different data. Go through the above steps, and at step 5, enter the formula =$A2<>$B2 where this formula is the correct field.
4. Identify matching with true or false
You can add a new column when comparing two Excel columns. Using this method, you will add a third column that will display TRUE if the data matches and FALSE if the data does not.
For the third column, use the formula =A2=B2 to compare the first two columns. If you think your spreadsheet will look too full of TRUE and FALSE rows, you can set up filters in Excel so it will only show TRUE values.
5. Compare Two Columns with the IF Function
A different method for analyzing Excel data from two columns is to use the IF function. This is similar to the above method, but comes with the advantage that you can customize the displayed value.
Instead of having TRUE or FALSE values, you can set values for matching or different data. For this example, we will use data matches and data does not match the values.
6. Compare Two Columns with the VLOOKUP Function and Find Matching Data
Another way to have Excel find duplicates in two columns is to use the VLOOKUP function. Excel will compare each cell in the second column against the cell in the first column.