How to Compare Two Columns and Delete Duplicates in Excel?
Last Updated :
28 Dec, 2022
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 duplicates are present and for that we need to compare columns and rows to find the duplicate entry. After the duplicate values are found, we need to highlight them and remove them. The above process will be explained using excel commands.
Comparing two columns in MS Excel
Follow the following steps to compare two columns in excel:
Note: This tutorial is for MS Excel 2013. Steps can be different or the same for other versions of Excel.
Step 1: Open the Excel file on which you want to compare the columns. For Example, your data can look like this

Step 2: Select the columns and go to the Home tab > Style group> Conditional Formatting i.e first click on Home tab, then go to Style group and then select Conditional Formatting (as shown in the image below). Conditional formatting allows users to automatically apply formattings such as changing colors, data bars, and icons in the cells based on cell value.

Step 3: After clicking on Conditional Formatting, a drop-down menu will appear. From the menu ribbon select the Highlight Cells Rules

Step 4: After selecting Highlight Cell Rule another drop down will appear and, select Duplicate Values

Step 5: After selecting Duplicate Values, a dialogue box (given below) will appear. In the first space fill Duplicate and in the other space fill the color options that you want to duplicate values to be highlighted in. And click OK. The purpose of this step is to highlight the duplicate values.

Step 6: After clicking OK. The duplicated values are highlighted.

Removing Duplicate Values
Step 1: We'll be adding a filter to columns A and B and for that go to Home Tab> Editing Group> Sort & Filter. Here, Sort & Filter will help to apply various filters such as sort in ascending, descending order and sort by color, and much more.

Step 2: After clicking on Sort & Filter, a dialogue box will appear (as shown below). From the dialogue box, select Filter

Step 3: You'll see a downward arrow in the first cell of all the selected columns. After clicking on the downward arrow, a dialogue box will appear(as shown below). Select Filter by Color option.

Step 4: In the Filter by Color, select Filter by Cell Color. Remember, here the default color is taken, if you want to take a different color then choose that cell color. And then press OK.

The result will look like this. The duplicate values are highlighted in Column A because the filter is applied to Column A and if you want to highlight the duplicate values from column B, then apply the filter in Column B.

Step 5: Select the cells from Column A that are colored and right-click, a dialogue box will appear(as shown below) and from the dialogue box select Clear Contents

All the duplicate contents will be deleted.
Similar Reads
How to Compare Two Columns in Excel : Easy and Quick Methods
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
8 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
How to Delete Blank Columns in Excel
Empty columns in your Excel spreadsheet can make it look messy and also look unprofessional. Now, whether you are doing a budget analysis, sorting data, or making a report, removing blank columns is a way to make your report professional and cleaner. Now, if you are new to MS Excel and want to know
5 min read
How to Create a Column Chart for Comparing Data in Excel?
Excel is powerful data visualization and data management tool which can be used to store, analyze, and create reports on large data. It can be used to visualize and compare data using a graph plot. In excel we can plot different kinds of graphs like line graphs, bar graphs, etc. to visualize or anal
2 min read
How to Ungroup Columns in Excel: A Complete Guide
Excelâs grouping feature is a powerful tool for organizing complex datasets, but have you ever worked with grouped columns in Excel and needed to break them apart to simplify your view? Ungrouping columns in Excel helps you manage and view your data more efficiently, especially when working with gro
4 min read
How to Edit, Copy and Delete Drop Down List in Excel
Excel is a versatile tool used globally for various data management tasks, including the use of drop-down lists to simplify data entry and ensure consistency. However, there might be times when you need to modify these lists to keep up with changes in data or requirements. These essential techniques
11 min read
How to Find and Remove Duplicates in Excel
Inacurate data always ruin your data and that the reason removing duplicates in Excel because it is one of the reason for duplicate data in Excel. In this guide, we aer going to explain you the best ways to find and remove duplicates in Excel. How to Find and Remove Duplicates in ExcelIn the section
9 min read
SQL Query to Delete Duplicate Columns
Through this article, we will learn how to delete duplicate columns from a database table. As we know that duplicity in our database tends to be a waste of memory space. It records inaccurate data and is also unable to fetch the correct data from the database. To remove the duplicate columns we use
2 min read
How to Create a Line Chart for Comparing Data in Excel?
Excel is powerful data visualization and data management tool which can be used to store, analyze, and create reports on large data. It can be used to visualize data using a graph plot. In excel, we can plot different kinds of graphs like line graphs, bar graphs, etc., to visualize or analyze the tr
2 min read
How to Create Combination Charts for Comparing Data in Excel?
Excel is powerful data visualization and data management tool which can be used to store, analyze, and create reports on large data. It can be used to visualize and compare data using a graph plot. In excel we can plot different kinds of graphs like line graphs, bar graphs, etc. to visualize or anal
2 min read