When working with large Excel spreadsheets, comparing the data from two columns can be time consuming. Instead of analyzing the columns and writing “Match” or “Mismatch” in a separate column, you can use Excel functions to streamline the process.
We’ll look at how to use various Excel functions to compare two columns and identify matching or mismatching data.
1. How to Mark Duplicate Data
If you want to compare two columns in Excel, but don’t want to add a third column that indicates whether the data is in both columns, you can use the Conditional Formatting function.
- Select the data cells you want to compare.
- Go to the House tab.
- From the Styles group, open the Conditional Formatting menu.
- Click Highlight Cell Lines > Duplicate Values.
- In the Duplicate Values window, make sure the Format cells that . contain option is set to Duplicate and choose the format option next to values with.
- Click Okay.
Excel now highlights the names that appear in both columns.
2. How to Highlight Unique Data
You can use the same function if you want to identify data that is not part of both columns.
- Select the data set.
- Go again to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values.
- In front of Format cells that . containTo elect Unique.
- Select how the data mismatch should be marked and click Okay.
Excel now highlights the names that can only be found in one of the two columns.
While these methods are fairly simple to use, they may not be efficient for larger spreadsheets, so you need to think about an efficient way to organize your spreadsheet. So we’re looking at more complex solutions that show you which rows have the same data or use an additional column to display values that indicate whether the data matches or not.
3. Highlight rows with identical data
If you need a better visual representation of identical data, you can have Excel find matching values in two columns and highlight the rows with matching data. As with the previous method, we’ll use the conditional formatting feature, but add a few extra steps.
This way you have a visual indicator that helps you identify matching data faster than reading a single column. Follow these steps to use Excel’s conditional formatting to compare two sets of data:
- Select the data you want to compare (don’t include the headlines) and open the House tab.
- Click Conditional Formatting and select New rule.
- From Select a rule typeClick Use a formula to determine which cells to format.
- Enter =$A2=$B2 in the field below, Format values where this formula is true. Here, A and B correspond to the two columns we are comparing.
- To customize how Excel highlights the rows, click Format and in the Format cells window, select the To fill tab. You can choose the background color, pattern style and pattern color. You will be provided with a preview so that you can preview the design. Click Okay once you have completed the customization process.
- Click Okay in the New Formatting Rule window so that Excel immediately highlights the rows with matching data.
When you compare two columns in Excel with this method, you can also highlight the rows with different data. Follow the steps above, and at step 5enter the =$A2<>$B2 formula within the Format values where this formula is true field.
4. Identify TRUE or FALSE matches
You can add a new column when you compare two Excel columns. This method adds a third column that displays TRUE if the data matches and FALSE if the data does not match.
For the third column, use the =A2=B2 formula to compare the first two columns. If you think your spreadsheet is looking too cluttered with TRUE and FALSE rows, you can set a filter in Excel to show only the TRUE values.
5. Compare two columns with an IF function
Another method of analyzing Excel data from two columns is to use an IF function. This is similar to the method above, but has the advantage that you can adjust the displayed value.
Instead of having the values TRUE or FALSE, you can set the value for matching or different data. For this example we will use the Data match and Data does not match values.
The formula we will use for the results column is =IF(A2=B2,”Data matches”, “Data does not match”).
6. Compare two columns with a VLOOKUP function and find matching data
Another way to make Excel find duplicates in two columns is to use a VLOOKUP function. Excel compares each cell in the second column with the cells in the first column.
Use the =VLOOKUP(B2,$A$2:$A$14,1,0) for the column where the results are displayed. Make sure to adjust the data range.
When using this formula, Excel displays the matching data or uses an #N/A value. However, the #N/A value can be confusing, especially if you’re sending the spreadsheet to someone else. If they don’t have experience with Excel, they might think a mistake has been made.
So to avoid confusion, upgrade the VLOOKUP function to an IFERROR function. If you need to find data that is in column B and also in column A, use the =IFERROR(VLOOKUP(B2,$A$2:$A$14,1,0),”Data mismatch”) formula.
7. How to compare two columns and extract data?
In addition to comparing two Excel columns for matches, you can also use the VLOOKUP function to extract matching data. This saves you time because you don’t have to manually go through the first column and search for relevant data.
If data in the second column is missing from the first column, Excel displays an #N/A value. Use the =VLOOKUP(D2,$A$2:$B$14,2,0) formula.
Remark: If you want to protect your results from spelling mistakes, use the =VLOOKUP(“*”&D2&”*”,$A$2:$B$14,2,0) formula. Here, the star
has the role of a wildcard and replaces any number of characters.
Compare Excel columns with ease
As we discussed, there are plenty of ways and tricks you can use to compare two columns in an Excel spreadsheet and get the best results. You can even add a third column to display the results.
If you’re new to Excel, you should know that you can use it for more than just work assignments. There are plenty of formulas to help you solve real-world problems.