How to Compare Two Columns in Excel? (5 Easy Methods)

MS Excel spreadsheets are useful for data storage, manipulation, and decision-making. Since such spreadsheets are huge and often multiple spreadsheets are linked to one another. In that case, manually comparing columns is a tedious task. It might take hours or days to find the missing data. Knowing certain functions that facilitate tasks and optimise your productivity is necessary. In this blog, we will cover how to compare two columns in Excel.

Depending on your specific goal, you can use various methods to compare columns with rows with the same data in Excel. Here's how you can do it:

Table of Content

• “Equal” Function
• “EXACT” Function
• “IF” Function
• “VLOOKUP”
• Conditional Formatting
• What is the Purpose of Comparing Two Columns In Excel?
• Limitations of Comparing Two Columns in Excel

Compare Two Columns In Excel Using the “Equal” Function

The Equal function compares values row by row to identify exact matches. Here are the steps involved:

Step 1 - Identify the two columns you want to compare. In this case, we are tallying two tables

Step 2 - Insert a new column next to the columns you want to compare. This column will display the comparison results.

Step 3 - Click on the first cell of the new column (e.g., in our case, it is Column C; click on C3).

Step 4 - Enter the EQUAL formula to compare the values in the corresponding cells in the two tables with data in Column B. We will use the below formula -

=B3=B16

Step 5 - Press Enter to apply the formula to the cell.

Step 6 - Drag the fill handle down the column to apply the formula to all rows you want to compare.

The new column will display TRUE if the values in the corresponding cells in Column B are identical and FALSE if they differ.

Compare Two Columns In Excel Using the “EXACT” Function

You can compare two columns using the EXACT function to determine if the values in corresponding cells are exactly the same. 

Step 1 - Ensure your data is organised with the two columns you want to compare. Let's say Column C contains the values you want to search for in Column G.

Step 2 - Insert a new column where you want the comparison results to appear.  

Step 3 - In the first cell of the new column, enter the following formula:

=EXACT(C3,G3)

This formula searches for the value in cell C3 in the entire Column G. If it finds a match, it returns "True"; otherwise, it returns "False".

Step 4 - Drag down the fill handle to copy the formula to all cells in the new column corresponding to the data in Column C.

The new column will now display "True" for values in Column C found in Column G and "False" for those not found.

How to Compare Two Columns In Excel Using the “IF” Function?

The IF function in Excel allows you to perform conditional checks and display specific outputs based on whether the values in corresponding cells match. Here are the steps involved:

Step 1 - Determine which two columns you want to compare. In our example, we want to compare Column C and Column G.

Step 2 - Insert a new column next to the columns you want to compare. This column will display the comparison results.

Step 3 - Click on the first cell of the new column (e.g., if it's Column I, click on I3).

Step 4 - Enter the IF formula to compare the values in the corresponding cells in Columns C and G.

=IF(C3=G3, "Match", "No Match")

This formula checks if the value in cell C3 equals that in cell G3. If they match, it returns "Match"; otherwise, it returns "No Match".

Step 5 - Apply the Formula and press Enter to apply the formula to the cell.

Step 6 - Drag the fill handle down the column to apply the formula to all rows you want to compare.

The new column will display "Match" if the values in the corresponding cells in Columns C and G are identical and "No Match" if they differ.

Compare Two Columns In Excel Using “VLOOKUP”

You can compare two columns in Excel using the VLOOKUP function to check if the values in one column exist in the other. Here's the process to do it:

Step 1 - Ensure your data is organised with the two columns you want to compare. For example, Column C contains the values you want to search for in Column G.

Step 2 - Insert a new column where you want the comparison results to appear.  

Step 3 - In the first cell of the new column, enter the following formula:

=IF(ISNUMBER(VLOOKUP(C3, G:G, 1, FALSE)), "Match", "No Match")

This formula searches for the value in cell C3 in the entire Column G. If it finds a match, it returns "Match"; otherwise, it returns "No Match".

Step 4 - Drag down the fill handle to copy the formula to all cells in the new column corresponding to the data in Column C.

The new column will now display "Match" for values in Column C found in Column G and "No Match" for those not found.

Compare Two Columns In Excel Using "Conditional Formatting” 

Listed below are the steps to create  conditional formatting to highlight unique values -

Step 1: Select the data range: Select the columns or the range where you want to highlight unique values.

Step 2: Go to Conditional Formatting: Click on the "Home" tab in the Excel ribbon and locate "Conditional Formatting" in the "Styles" group.

Step 3: Highlight Cell Rules: In the drop-down menu, select "Highlight Cells Rules" and then choose "Duplicate Values". Look for a unique rule under a duplicate rule.

Step 4: Set up the rule: In the "Duplicate Values" dialogue box, select "Unique" from the drop-down menu.

Step 5: Choose formatting style: Select the style you want to apply to the unique values, such as a specific fill color or font color.

Step 6: Click OK to apply the rule: Excel will now apply the formatting of your choice and highlight unique values in your selected range.

What is the Purpose of Comparing Two Columns In Excel?

Comparing two columns in Excel serves several purposes:

1. Identifying Common Information: When you have data in two columns, comparing them helps you identify common points between the two data sets. It comes in handy for finding duplicates, matching records, or consolidating information.
2. Finding Differences: Besides finding similarities, comparing two columns also helps identify differences. It allows you to pinpoint inconsistencies or missing information.
3. Data Validation: Comparing columns can be part of a data validation process. You can check if the data in one column matches the expected values in another, ensuring data integrity and accuracy.
4. Data Cleaning and Formatting: Comparing columns can help in data cleaning tasks, such as standardising formats, correcting errors, or removing redundant information.
5. Analysing Data Relationships: Comparing columns can reveal relationships between different datasets. For example, you might compare sales data with customer demographics to analyse buying patterns or compare employee performance metrics with training records to assess their performance.

Limitations of Comparing Two Columns in Excel

Below are some of the noteworthy limitations of the process of comparing two columns in Excel -

• Only Row-by-Row Comparison by Default: Basic Excel formulas (=A1=B1, IF, EXACT) compare data row by row. They don't compare one column's values to the entire second column unless advanced logic is used, which limits real-world use cases like cross-list checks.
• False Positives/Negatives Due to Extra Spaces or Formatting: Small differences in formatting (like an extra space or different date format) often cause incorrect mismatch results, even when the values seem identical.
• Manual Setup Can Be Time-Consuming: Setting up comparisons and formulas in large datasets takes effort and is prone to human error, especially without automation or structured tables.
• VLOOKUP Limitations: VLOOKUP can only search in one direction (left to right) and is not flexible for all comparison tasks. It fails if the data structure is not consistent.
• Conditional Formatting is Visual Only: While helpful for highlighting, conditional formatting does not allow data filtering, sorting, or exporting based on results. It’s not useful for deeper analysis.
• Performance Issues on Large Datasets: Using many formulas or conditional formatting rules in large spreadsheets can slow down Excel, causing delays or crashing in some cases.
• Case Sensitivity Issues: Different functions behave differently with case sensitivity (EXACT is case-sensitive; = is not), leading to confusing or incorrect outcomes.

Conclusion

Comparing two columns in Excel can help you identify differences, duplicates, or matching data within a dataset. You can quickly highlight or isolate similarities and discrepancies using the above discussed methods. Mastering these techniques can simplify data analysis, making it easier to spot errors, ensure consistency, or align information across different datasets.