How to Compare Two Columns in Excel : Easy and Quick Methods
Last Updated :
04 Jul, 2024
In the world of data analysis, knowing how to compare two columns in Excel is a fundamental skill that can unveil valuable insights and ensure data accuracy. If you're looking to spot differences, find duplicates, or identify similarities, mastering Excel's compare columns functionality is key.
This article will walk you through effective methods on how to compare two columns in Excel, exploring techniques to streamline your workflow and make informed decisions. By the end, you'll be equipped with the knowledge to confidently manage and analyze your data using Excel compare two columns features. Let's explore and learn the power of data comparison!
How to Compare Two Columns in ExcelWhy Compare Two Columns in Excel
Excel is a versatile tool that stores data and manipulates data for better decision-making. We are required to compare Excel columns data for the following reasons:
- To find the unique value in the dataset.
- To remove the identical data values from the dataset.
- To reduce the size of the dataset.
- To speed up the processing time for other Excel operations by reducing its size and removing identical data values.
How to Compare Two Columns in Excel
There are many different ways to compare your data when it is present in two different columns, tables, or spreadsheets to make sure that there shouldn't be any missing or duplicate data present in the columns.
Below are some methods to compare two columns :
- Highlighting the unique or duplicate values in each column using Functions
- Display Unique or duplicate using conditional formatting or formulas.
- Comparison through row by row
- Using LOOKUP formulas.
In this example, we will be using random cities' names in two different columns and comparing them to find duplicate or unique cities' names. For this example, we will create the following dataset. The dataset will contain two columns as Cities A and Cities B, which will contain random names of cities. Below is the screenshot of the dataset attached.
DatasetIn this method, we will the Conditional Formatting rules provided by Excel to compare the two columns and highlight the DUPLICATE DATA between the two columns.
Step 1: Select the data
First, we will select the data cells of Excel for which we want to compare the data.
Select the DataIn this step, we will do the conditional formatting for our selected cell's data and set the rules for highlighting the duplicate values. For this go to Home > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Conditional FormattingOnce we click on Duplicate Values, excel will pop up a tab asking for a formatting rule. We can select the according to our requirements.
Select DuplicateAfter setting the rule(Here, Light Red color for cells having duplicate data values) we need to click on the OK button.
Step 3: Output
Once we set the formatting rules and click on the OK button, excel will automatically Highlight the cells according to the formatting rule.
OutputHighlight Unique Data
In this method, we will use the Conditional Formatting rules provided by Excel to compare the two columns and highlight the UNIQUE DATA between the two columns.
We are required to repeat the same steps as we did in Method 1. But we need to make sure, to change the formatting rule. This time we need to format the cells that contain unique elements, not duplicate elements.
Note: Keep in mind to change the formatting rule for unique data.
Change formatting to Unique
Output
Once we will change the rule for unique element comparison and click on the OK button, Excel will automatically update the sheet according to the unique element.
Compare Two Columns in Excel Using Equals Operator
In this method, we will create one more column in our existing dataset as Result which will be used for storing the matched and unmatched responses.
Step 1: Add an Excel New Column
In this step, we will add a new column as "Result" for adding the response as TRUE or FALSE.
Add New Column in ExcelIn this step, we will add a formula in our Result column to compare whether the two columns contain equal data or not.
=A2=B2(We are using A2 and B2 cells for comparison, you need to change according to your own cells).
Add FormulaAfter using formula in one row, we need to fill the same formula for all the rows. To use the same formula in all rows, Select the bottom right corner of the cell(Here, C2) and pull it down to the cell. Excel will fill the same formula in all the rows.
Select the Bottom right cornerStep 3: Output
Once we have our comparison formula filled in all the rows, excel will automatically fill TRUE for matched and FALSE for unmatched entities.
OutputCompare Two Columns in Excel Using the IF Condition
You can also use the IF condition to compare two columns in Excel. The Formula that can be used is =IF(A2=B2,"MATCH"," "). It will return the result as MATCH against the rows that contain, matching values, and the remaining rows will show Not Match.
.webp)
ResultCompare Two Columns in Excel Using the EXACT() Function
The EXACT() Function is used to compare two text strings and returns TRUE if they are the same and FALSE if they are not the same. EXACT is case-sensitive but it ignores formatting differences.
Syntax: =EXACT(text1,text2)
It takes two arguments text1, and text2.
Let's consider the below Example. There are two columns Cities1 and Cities2. The formula =IF(EXACT(A2,B2),"Match","Not match")
-(1).webp)
Enter FormulaThe 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.
Using the VLOOKUP Function to Compare Two Columns
The LOOKUP function is used to search for a particular value in a particular row or column and return the result from another row or column. Excel has various lookups such as VLOOKUP (V stands for vertical), HLOOKUP (H stands for horizontal), and XLOOKUP (combination of both VLOOKUP and HLOOKUP).
Let's see the below example to compare two columns using VLOOKUP.
.webp)
Using VLOOKUPWhere Column A consists of a list of exams given by a student and Column B is a list of
subjects that students passed. The result sheet should contain a list of all the subjects. The VLOOKUP() is applied in cell C2 as =VLOOKUP(A2, $B$2:$B$4,1,0).
Drag the formula to apply it in the cell below C2. You will find the result in column C with the subjects that are cleared and those that have not been passed as N/A.
Conclusion
In conclusion, understanding how to compare two columns in Excel is essential for ensuring data integrity and making informed decisions in your analysis. Whether you're using Excel to identify discrepancies, locate matches, or streamline data cleansing processes, the ability to compare columns in Excel effectively enhances your productivity and accuracy. By leveraging the techniques and tools discussed in this article, such as Excel compare two columns features, you can confidently handle data comparisons and achieve reliable results. Start harnessing the power of Excel's data comparison capabilities today to streamline your workflows and elevate your data analysis skills.
Similar Reads
How to Compare Two Lists in Excel: 5 Easy Methods
Comparing two lists in Excel is a powerful way to identify matches, differences, or duplicates within your data. Whether you’re working with inventory records, customer databases, or project tasks, knowing how to compare two lists in Excel can save time and improve accuracy. Excel offers several too
7 min read
How to Compare Two Columns and Delete Duplicates in Excel?
Many a time while working with Microsoft Excel or MS Excel or Excel, we came up with a situation where we have duplicate values in columns and rows and the first task is to remove all the duplicates before proceeding to the next step. But before removing duplicates, we need to find out where these d
3 min read
How to Combine Two Columns in Excel (6 Methods Explained )
Combining columns in Excel is a vital skill for organizing and analyzing data effectively. Whether you're merging first and last names, combining data points, or creating seamless datasets, there are several techniques to get the job done. This guide explores six efficient methods, including using t
8 min read
How to Sum a Column in Excel: 3 Methods
Knowing how to sum a column in Excel is essential for managing budgets, financial reports, and large datasets. Whether you’re calculating sales totals, adding expenses, or analyzing business data, Excel provides multiple ways to add up a column efficiently. Many users wonder "how to add columns in E
5 min read
How to Count Duplicates in a Column in Excel (All Methods)
Counting duplicates in Excel is a crucial task when analyzing data, managing large datasets, or spotting repetitive entries. Whether you're working on sales reports, attendance sheets, or large-scale inventory lists, identifying and counting duplicates helps maintain data accuracy and improve effici
7 min read
Excel Transpose: Convert Rows to Columns (4 Easy Methods)
If you want to flip rows to columns or columns to rows but you don't want to retype all the data, then you need to use the Excel Transpose function, and this guide will help you to learn how to use Excel Transpose.Here in this guide you will learn formulas like INDIRECT + ADDRESS to save you time al
8 min read
How to Compare Two Columns For Equality in SQL Server?
In SQL, problems require us to compare two columns for equality to achieve certain desired results. This can be achieved through the use of the =(equal to) operator between 2 columns names to be compared. For this article, we will be using the Microsoft SQL Server as our database. Syntax: SELECT * F
2 min read
How to Create a Drop-Down List in Excel: 2 Easy Methods
How to Build Drop Down Menu in Excel: Quick StepsOpen MS Excel Spreadsheet >> Enter List Items Select Cell >> Go to Data Validation Choose List Option Set the Source >>Click OKHave you ever typed the same data repeatedly in Excel, only to make typos or inconsistencies? A drop-down
9 min read
How to Split Cells in Excel: Easy Methods Explained
How to Separate a Cell in Excel- Quick StepsOpen MS Excel>>Select Data Go to the Data tab>>Click Text to Columns.Choose Delimited and click NextSplitting cells in Excel can simplify data management, especially when dealing with combined information in a single column or cell. Whether you
10 min read
How to Swap Columns in Excel: 3 Methods Explained
To Swap Columns in Excel - Quick StepsDrag and Drop: Select a column, drag it to a new position, and release.Cut and Paste: Cut a column (Ctrl + X) for Windows and (Cmd + X) for Mac, then paste it in the new location using Insert Cut Cells.Copy and Paste: Copy a column, then insert the copied cells
8 min read