In Excel, there are many instances when you need to compare two columns. It is simple to do when you deal with small tables, but it gets more complicated when you compare two columns in a large spreadsheet.
Without the relevant knowledge, you might have to manually compare data or highlight the matching or mismatching data in each column. This is a time-consuming approach that can be avoided with the application of some fundamental knowledge of Excel.
In this comprehensive guide, we unveil a step-by-step approach to comparing two columns in Excel, equipping you with the techniques and strategies necessary to streamline your data analysis workflow. From utilising built-in functions to employing conditional formatting, we’ll walk you through practical methods to efficiently compare columns and unlock hidden patterns within your data.
Whether you’re a seasoned Excel user or just starting your data analysis journey, this article will provide you with the knowledge and skills needed to navigate the intricacies of comparing columns in Excel. Join us as we dive into this invaluable process, empowering you to make informed decisions and gain deeper insights from your data.
There are many methods to streamline this process, and this article looks into each of these methods. We’ll cover how to confidently compare two columns and provide examples of where doing so might be especially useful.
Why Is It Useful to Compare Two Columns in Excel?
Excel spreadsheets are useful for data storage, manipulation, and decision-making. Excel is a versatile tool that conveys information to the user. Data analysts gather information that plays a crucial role in marketing and sales decisions.
Based on the tools or the formula, there is an impact if a cell does not contain information. Spreadsheets are huge, and multiple spreadsheets are linked to each other.
A data analyst must compare two columns across the same or different spreadsheets. Manually comparing columns is a tedious task. It might take hours or days to find the missing data.
Comparing two columns in Excel is imperative for the data analyst to find out whether the cell contains data or not. Excel displays it as TRUE/FALSE, Match/Not Match, or any other user-defined message.
How to Compare Two Columns in Excel
When you have data in two different columns, tables, or spreadsheets, you may often need to compare them to see what data is missing or present in both. Comparisons can happen in many different ways. You need to decide the method to compare depending on what you want from it.
You can compare two columns in Excel by:
- Highlighting the unique or duplicate values in each column using functions.
- Display unique or duplicate using conditional formatting or formulas.
- Row-by-row comparison
- Using LOOKUP formulas.
Comparing Two Columns in Excel with Equals Operator
You can compare two columns, row by row, and find the matching data by returning the result as Match or Not Match. The formula =A2=B2 is used in the example below to find the matching data and the result returns as True or False.
In cell C2, insert the formula and press Enter. Then, drag it down until the end of the table. The formula returns TRUE if the values in rows that are compared are the same and return FALSE if the values are different.
Compare Two Columns in Excel Using IF Condition
In Excel, you can compare two columns using the IF condition. The formula to compare two columns is =IF(A2=B2,”Match”,” ”). It returns the result as Match against the rows that contain matching values, and the remaining rows are left empty.
The same formula can identify and return the mismatching values, but an additional result can be shown when the IF condition proves false. The formula is =IF(A2=B2,”Match”,”Not a Match ”)
To compare two columns in Excel for differences, replace the equals sign with the non-equality sign (<>). The formula is =IF(A2<>B2,”Match”,”Not a Match ”).
Compare Two Columns in Excel Using EXACT() Function
Use the function EXACT() when you compare two columns in Excel, and you wish to find values that are case-sensitive.
The EXACT() function compares two text strings and returns TRUE if they are the same and FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. The syntax is =EXACT(text1, text2). It takes two arguments, text1 and text2, and both are required arguments.
Let’s take a simple example. The columns data1 and data2 contain two text strings, Nova Scotia, in columns A and B.
The formula, =IF(A2=B2, “Match”, “Mismatch”), when applied to the cell C2, returns a match as it is case insensitive.
Use the formula =IF(EXACT(A2, B2), “Match”, “Mismatch”) for the IF condition to be case sensitive.
The EXACT() returns values as true or false. The execution of the formula would be: first, the inner function would be executed, and the result returned. In the above example, the EXACT() function returns a false value to the outer function IF.
The general working of the IF condition is that if it returns true, the first argument in the function is returned; else, the second argument is returned.
Compare Two Columns in Excel Using Conditional Formatting
Click Home and then on Styles. Then, follow these steps Conditional Formatting → Highlight Cell Rules → Duplicate Values. You get a dialogue box, as shown below. From there, you must choose the values from the drop-down menu.
- Apply the formatting condition on the cells. You can choose any conditions: Duplicate or Unique.
- Format cells that contain: (options) values with (options)
In the example below, there are two data sets. The set of names is in columns Data1 and Data2. Not all the names in Data1 are present in Data2. Use Conditional Formatting to find and highlight the data that are present in both columns.
Before using Conditional Formatting, select the whole table and perform the above-mentioned steps. Choose Duplicate if you wish to find the names in both columns. To highlight it, choose any options: filling with colour, changing the text colour, or changing the cell border.
The last option is Custom Format. Choose this option if you wish to highlight the cell with a colour of your choice other than the ones specified in the drop-down menu.
There is another option that you can use is Unique. Use this option if you are interested in highlighting the cells that contain data that is not repeated. That is, you wish to highlight the cells that are unique.
Instead of selecting Duplicate, choose Unique from the drop-down list and apply any options, such as filling with colour, changing the text colour, or changing the cell border.
Tip: If you wish to clear the formatting that you performed on the cells, click Conditional Formatting → Clear Rules → Clear Rules from Selected Cells.
You can use conditional formatting when you don’t want a third column showing the results comparing the two columns. Here you can highlight duplicate (matching) and unique (different) data to show which rows have the same data or use an additional column to display values indicating whether the data matches. These are for smaller tables. For large spreadsheets, you need complex methods.
This is one of the methods in Excel to compare two columns and find the differences.
Using Lookup Function to Compare Two Columns
The LOOKUP function searches for a particular value in a single row or column and returns the corresponding value from another row or column. There are various lookup functions: viz, HLOOKUP, VLOOKUP, and XLOOKUP. H and V here stand for horizontal and vertical, and the XLOOKUP function is a combination of both LOOKUP and VLOOKUP.
The example below compares two columns in Excel to look for differences using VLOOKUP().
Column A contains the list of exams taken by a student, and column B is the list of subjects the student passed. The result sheet must contain a list of all the subjects. The VLOOKUP() is applied in cell C2 as =VLOOKUP(A2, $B$2:$B$5,1,0).
Drag the formula to apply it in all the cells below C2. You will find the result in column C with the subjects that are cleared and those that have not been cleared as #N/A. The formula in Excel to compare two columns using VLOOKUP is as follows.
- VLOOKUP(A2,..,..,..) – takes the value in cell A2.
- VLOOKUP(A2, $B$2:$B$5,..,..) – compares with all the values in cells from B2 to B5. That’s why the cells in the range B2:B5 are locked using absolute reference. The $ symbol before the cell reference is called an absolute reference.
- VLOOKUP(A2, $B$2:$B$5,1,..) – the third argument is the col_index_num which mentions the position of the column to compare from the lookup value A2. In the above example, the subjects list is in column A, and the column with which it has to compare is 1 column away. Hence, the value 1.
- VLOOKUP(A2, $B$2:$B$5,1,0) – this is the last argument that takes a logical value, either 0 or 1. If you wish to find the exact match, mention 0(zero). If you wish that VLOOKUP() returns a closet match sorted in ascending order, mention 1 in this argument.
Frequently Asked Questions
1. How to compare two columns in Excel?
When comparing two columns in Excel, one method is to select both columns of data, select Home → Find & Select → Go To Special → Row Differences, and click OK. The matching data cells across the columns’ rows are white, and unmatched cells appear in grey.
2. What are the other methods to compare two columns in Excel using the IF condition?
To find matches in all cells within the same row when the table has three or more columns when you want to find rows with the same values in all cells, use an IF formula with an AND statement. The formula is =IF(AND(A2=B2, A2=C2), “Full match”, “”).
And the formula to find matches in any two cells in the same row is =IF(OR(A2=B2, B2=C2, A2=C2), “Match”, “”).
3. Can you compare two columns in Excel using the Index-Match function?
Sometimes you may need to match two columns in two different tables and pull matching entries from the comparing table. Besides VLOOKUP, you can use the INDEX-MATCH function to compare and pull values from the other table.
In the example above, the MATCH() function takes all the values in column D starting from D2 and compares them with those in column A from A2 to A4. If it finds a match, it pulls the corresponding value from column B and displays it otherwise, it returns a value #N/A.
Conclusion
We often encounter situations where we must compare columns in Excel. Microsoft Excel offers options to compare and match data in a single column, multiple columns, and multiple spreadsheets. This tutorial shows several methods to in excel compare two columns for matches.
LOOKUP() functions are vital to learn in Excel as they’re so widely used. Check out our courses in Excel and Microsoft Office Applications to learn more about Excel functions and formulas.
