Do you want to compare two columns in Excel to match data, identify differences, and find duplicate values? Are you searching for simple methods on how to compare 2 columns in Excel?? This tutorial will provide six different methods to do so. We will provide a step-by-step guide with graphical examples.
Part 1 - Compare Two Columns in Excel Using the Equal Operator
The first method is to use the Equal operator for comparing two columns in Excel. Here is a step-by-step guide on how to use the Equal operator to compare two columns in Excel.
Step 1: In the Excel sheet create a new column and label it as the “Result” column.
Step 2: Click the Result column and add a formula for comparing two columns as shown below.
Step 3: Excel will give TRUE for every successful comparison and FALSE for every unsuccessful comparison as shown below.
This is a simple and easy-to-use method for comparing two columns in Excel.
Part 2 - Compare Two Columns in Excel Using the IF Function
This method is an advanced version of the above-referred method. You can use the IF function If you want customized results in the “Result” column, For example, if you want the result for the above-referred example as follows:
“Found” if values in both columns match.
“Not Found” if values in both columns don’t match.
You can use the following steps to get the desired results.
Step 1: Click the “Result” Column and enter the following formula.
= IF(A2=B2, “Found”, “Not Found”)
Step 2: Apply the formula on all cells and you will get the following result.
This is an advanced method to compare two columns and get customized results of your choice in the “Result” column.
Part 3 - Compare Two Columns in Excel Using the EXACT Function
The third method to compare two columns in Excel is by using the EXACT function. The following steps can be followed to achieve the desired results.
Step 1: Click the “Result” column and type the following formula in the formula bar.
=EXACT(A2,B2)
Step 2: Apply the formula on cells and you will get TRUE if the values in both columns match and FALSE if the values in both columns are different.
The important point to remember here is that the EXACT function is case-sensitive. If there is a value of “CentOS” in the first column and another value of “centOS” in the corresponding second columns, the EXACT function will return FALSE as the cases are different in both columns.
This method is also simple and easy to use but due to case sensitivity it may not accurately compare two columns having the same values but with different cases.
Part 4 - Compare Two Columns in Excel Using Conditional Formatting
Perhaps this is the simplest method of how to compare 2 columns in Excel. Follow these steps to compare two columns in Excel for matches and differences using conditional formatting.
Step 1: Select all cells of both columns that you want to compare.
Step 2: Navigate to Home in the menu bar and select Conditional Formatting.
Step 3: Select Highlight Cell Rules and then select Duplicate Values in the Conditional Formatting option.
Step 4: A pop-up window will appear, click the OK button and you will find all duplicate values highlighted in red color.
Step 5: You can also change the option in the pop-up window to find and highlight the unique values in both columns as shown in the following figure.
This is the easiest and the simplest method to compare two columns in Excel. You can use Excel’s built-in feature to compare columns. There is no need to enter any formula or use any function. Conditional formatting is also used to highlight the greater value in Excel when comparing two columns as shown in the following figure. In this example, all values greater than 90 are highlighted in red.
.
WPS Office also comes with the feature of comparing two columns in Excel. You can use WPS Sheet to extract duplicate data using Conditional Formatting. WPS Sheet has a simple and user-friendly interface to perform comparisons and extract duplicate values. Follow these simple and easy-to-follow steps to compare two columns in the WPS Sheet.
Step 1: Select all cells of both columns and then select Formatting from the Home menu.
Step 2: Select Conditional Formatting from the Formatting option and then select Highlight Cell Rules and then select Duplicate Values as shown in the following figure.
Step 3: A pop-up window will appear, select OK from the pop-up window. All duplicate values will be highlighted in red.
Step 4: If you want to find unique values, select Unique from the pop-up window. The unique values will be highlighted in red.
Part 5 - Compare Two Columns in Excel Using VLOOKUP
This is an advanced and tricky method to compare columns in Excel containing large data. Follow these steps to compare two columns in Excel using the VLOOKUP function with the help of the following formula.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Step 1: As a first step create a new column, label it as “Result” and enter the VLOOKUP formula as shown in the figure.
Step 2: Drag the formula to all cells to compare all values in both columns.
Step 3: You will see some cells in the result column with a “#N/A” value. Don’t worry, we can modify the formula using the “IFERROR” clause to avoid this error.
Step 4: Drag the formula to apply on all cells. You will get the following result with no errors.
If the values in both of the columns don’t match, you will get “Not Found” in the corresponding “Result” column.
Part 6 - Compare Two Columns in Excel Using COUNTIF
We can also compare two lists or columns in Excel using the COUNTIF function. Follow these steps to get the desired comparison results.
Step 1: Create a new column and label it as “Result”. Enter the formula of COUNTIF as shown in the figure.
Step 2: Drag the formula to all cells to get the result of the comparison of all cells of both columns.
If a value in the first column is found anywhere in the second column, the “Result” column will give a “1” and a “0” otherwise.
If you want to compare a simple comparison of two columns with a small amount of data, this method is rarely used. However, this method is more practical to be used for complex comparisons.
Part 7 - Comparison of Methods
We have seen different methods on how to compare two lists in Excel. All these methods are used for comparing columns in Excel depending on the nature of the task. However, the easiest and simplest method for how to compare 2 columns in Excel is Conditional Formatting. You don’t need to type any formula or do some tweaks to the formula to get the desired results. All you need to do is, use the built-in option of Conditional Formatting and get the result of comparison.
Similarly, if you want a case-sensitive comparison of both columns, you can use the EXACT function. If you want to compare multiple columns, you can use the IFCOUNT or VLOOKUP functions. The VLOOKUP function can also be used to pull the duplicate values in the “Result” column. If you want the duplicate values to be highlighted only, use the Conditional Formatting method.
FAQs about Comparing Two Columns in Excel
Q1: Which method is best for case-sensitive comparisons?
For case-sensitive comparisons, you can use the EXACT function. The EXACT function comes with the feature of comparing values in both columns keeping in view the case sensitivity of all values in cells. For example, if we have a value “Microsoft” in the first column and another value “microsoft” in the second column, the EXACT function will return FALSE after making the comparison of both columns.
Q2: What is the fastest way to compare two columns in Excel?
The fastest way to compare two columns in Excel is by using the Equal Operator. You can also use the Conditional Formatting option to easily compare two columns.
Final Thoughts - How to Compare 2 Columns in Excel?
This article helped you enhance your skills in how to compare two lists in Excel. I have discussed six different ways to compare two columns in Excel with the help of examples. I have also tried to portray my analysis of using different methods for comparing two columns in Excel. You can also use the WPS Sheet for easy data comparison, analysis, and validation. WPS Sheet is a free Excel editor for creating, editing, and sharing Excel Sheets.
WPS Sheet has a lot of professional and stunning built-in templates to enhance user experience and create new projects effortlessly. It comes with different Excel functions e.g. merge and center cells, highlight duplicates, insert Checkbox, pivot charts, shapes, and lists, split and merge, invoice maker tool, easy formulas, and built-in Excel to PDF converter.
In summary, the WPS Sheet is a productive and free alternative to Microsoft Excel with all the features and functions of Excel. It comes with an AI-powered WPS Office Suite, which includes a Docs tool for Word files, a Slides tool for PowerPoint presentations, and a PDF tool for editing and viewing Adobe PDF files. You can easily download WPS Office for free and enjoy the seamless, faster, smarter, and easier experience of an AI-powered Office suite.
Result of Plagiarism with Paperrator
