How to compare two columns and delete matches in Excel?
When managing large data sets in Excel, it's a common requirement to compare two columns or lists in order to identify and remove duplicate entries. For instance, you may have a master list of products or employees and a secondary list containing updates or new entries. Frankly, manually checking for duplicates is time-consuming and can result in errors, especially as the size of your lists grows. This article outlines several practical methods to compare two columns, find their matches, and then efficiently delete those duplicate items. We'll cover both formula-based approaches and the use of Kutools for Excel, each suitable for different scenarios and user preferences.
Compare two columns and delete matches with formula
Compare two columns and delete matches with Kutools for Excel 
Compare two columns and delete matches with formula
In Excel, one of the most straightforward methods to compare two columns and identify matching values is to use formulas. This method is particularly useful if you want a dynamic solution that updates automatically as your data changes or for situations where you want to visually audit matches before deleting them. However, it's best suited for users familiar with formulas and handling filter operations.
To illustrate, suppose you have two columns: Name1 in column A and Name2 in column C. You want to highlight the rows in Name1 that also appear in Name2.
1. Select a blank cell next to the first column you want to compare (here, cell B1 next to Name1). Enter the following formula, which will check if the value in A1 exists anywhere in C1:C10000:
=IF(ISERROR(MATCH(A1,$C$1:$C$10000,0)),"Unique","Duplicate")Press Enter to apply the formula, then drag the fill handle down to copy the formula for all cells in your data range. Adjust the range $C$1:$C$10000 if your data extends beyond 10,000 rows or is in a different column.
Note: In the formula, A1 represents the first cell in your main list. C1:C10000 refers to the range you are comparing against. Adjust these references to fit your actual data layout.
2. Next, select the helper column with the formulas you just entered. On the ribbon, click Data > Filter to enable the filter feature for this column.
3. Click the drop-down arrow in the helper column header, then select only the Duplicate option. This will filter your list to show only the rows where the value in Name1 exists in Name2.
4. After you confirm the filter, only matching entries will be displayed. Select the filtered rows in the Name1 column (you may click and drag, or use keyboard shortcuts), and press the Delete key to remove these duplicates.
5. Once you've deleted the visible entries, click Data > Filter again to turn off the filter. If you no longer need the formulas in the helper column, you can safely delete that column to tidy up your worksheet.
After these steps, all entries in your main list (Name1) that matched entries in the comparison list (Name2) will have been removed.
This formula approach is flexible and transparent, allowing you to double-check which items were marked as duplicates before deletion. However, for very large data sets, filtering and manual deletion can be time-consuming, and you should be careful not to inadvertently delete items still hidden by the filter or to miss highlighting the correct range.
Compare two columns and delete matches with Kutools for Excel
While the formula method offers precision, it involves several steps and manual operations. For a more interactive solution, you can use the Kutools for Excel add-in, which provides specialized tools for data comparison and management. Kutools simplifies the process of identifying and deleting duplicates—especially helpful for users who prefer minimizing manual intervention or want a more automated approach. This method is particularly useful when you need to process large lists, quickly visualize the matches, or highlight results with formatting options.
After free installing Kutools for Excel, follow these steps:
1. Hold down the Ctrl key to select both ranges you want to compare. Then go to the Kutools tab, click Select > Select Same & Different Cells.
2. In the Select Same & Different Cells dialog box, check My Data has headers if your selection includes headers (headers must be identical in both columns if you enable this option). Then select Each row and Same Values to specify you want to find rows with exact matches.
3. Click OK. A dialog will show you how many matching cells have been selected. This gives you instant feedback and helps you verify if the operation went as expected.
4. Click OK to close all dialogs. The matched (duplicate) cells in the first column will now be selected. Simply press Delete on your keyboard to remove them from your data set.
For added flexibility, if instead of deleting you want to highlight duplicate values to review them before removal, you can use the Fill backcolor or Fill font color options in the dialog and select a color. This helps to visually distinguish matching items and is particularly valuable when you want to audit affected data or present results to others. 
The Kutools approach offers a rapid, user-friendly workflow and reduces risk of manual errors. It is well-suited for regular data cleaning tasks and for users who handle several simultaneous comparisons. However, as with all data-altering operations, ensure that you have backed up your data or are prepared to use the Undo feature if you accidentally remove wrong records.
Select And Remove Matches
You may be interested in these articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in