Tech Tip: 3 Ways to Remove Duplicates in Excel
Use Case: I had a situation where I was asked to find all of the customers in our system that were active in the last 24 months, so I started with exporting a list of all invoices from all customers over the past 24 months. The list included duplicate customer names for each invoice so I needed a way to get rid of all the duplicate customer names. I discovered there are several ways to do this. Here are three ways you can use to get rid of duplicates in a column:
Method 1: Remove Duplicates Tool
- Select the column with customer names.
- Go to the Data tab.
- Click Remove Duplicates.
- Confirm the column and click OK.
Fast and simple. Great for one-time cleanup.
Method 2: Pivot Table
- Select your full dataset.
- Go to Insert → PivotTable.
- Drag Customer Name to the Rows area.
This gives you a unique list and lets you analyze customer activity too.
Method 3: UNIQUE Formula
If you’re using Excel 365 or Excel 2019+:
=UNIQUE(A2:A100)Replace A2:A100 with your actual range. This formula returns a dynamic list of unique customer names.
Ideal for live reports—automatically updates when data changes.
Ariel IT helps businesses simplify technology, strengthen cybersecurity, and stay compliant with evolving standards. As Business Professionals - Technical Experts, we partner with organizations to deliver reliable IT solutions that support growth and peace of mind. Contact us to learn more