How to Remove Repeated Data in Excel

Nothing brings your data analysis to a halt faster than duplicate entries. Whether it's the same customer listed twice or a product order that was accidentally recorded multiple times, repeated data can skew your reports, inflate your numbers, and lead to poor business decisions. This guide will walk you through several effective methods for finding and removing repeated data in Microsoft Excel, from one-click solutions to more advanced functions.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Do We Mean by "Repeated Data" or "Duplicates"?

Before we jump into the "how," let's clarify what a duplicate is. In Excel, a duplicate is a row where the values in one or more specified columns are identical to the values in another row. It's important to define this for yourself. Are you looking for:

• Entirely Duplicate Rows: Every single cell in Row 2 is identical to Row 10.
• Duplicates Based on a Key Identifier: The Email Address or Order ID in Row 5 is the same as in Row 25, even if other data like the date are different.

Understanding which cells define a duplicate record is the first step to cleaning your data effectively. Once you know what you're looking for, you can pick the best method below.

Method 1: The Easiest Fix - Excel’s Built-In “Remove Duplicates” Tool

This is the go-to method for a quick cleanup. Excel’s “Remove Duplicates” feature does exactly what it says: it finds duplicate rows based on the columns you select and permanently deletes them, keeping only the first unique instance it finds.

Heads-up: This method permanently deletes data. It's always a good idea to work on a copy of your spreadsheet or duplicate your worksheet before proceeding. Simply right-click on the sheet tab at the bottom and select "Move or Copy," then check the "Create a copy" box.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide to Use Remove Duplicates:

1. Click any single cell inside your data range. You don’t need to select the entire table as long as your data is in a continuous block with a header row.
2. Navigate to the Data tab on the Ribbon.
3. In the "Data Tools" group, click on the Remove Duplicates icon (it looks like a small table with a red 'X').

A dialog box will appear, showing you all the columns in your dataset.

1. Specify Which Columns to Check: By default, Excel selects all columns.
2. Ensure the "My data has headers" box is checked if your data has a header row. This prevents Excel from treating your titles as data.
3. Click OK.

After you click OK, a message will pop up telling you how many duplicate values were found and removed, and how many unique values remain. It’s that simple.

Method 2: Non-Destructive Cleanup Using "Advanced Filter"

What if you want to see the unique entries without deleting the original data? The Advanced Filter tool is perfect for this. It allows you to filter your list in place to show only unique records or, even better, copy the unique records to a completely new location on your worksheet.

How to Use Advanced Filter for Unique Records:

1. Start by selecting a single cell within your data area.
2. Go to the Data tab and click on Advanced in the "Sort & Filter" group.
3. The Advanced Filter dialog box opens up. Here are the key settings:
4. The most important step: Check the box for Unique records only.
5. Click OK.

Excel will instantly copy all the unique rows from your original dataset to the new location you specified. You now have a clean list and your original data is still safe and sound.

Method 3: Highlight Duplicates First with Conditional Formatting

Sometimes you don't want to remove duplicates just yet. Maybe you need to review them first to understand why they're happening. Conditional Formatting allows you to visually identify and flag duplicates by automatically changing their cell background color, font color, or more.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Set Up Conditional Formatting for Duplicates:

1. Select the column (or range of cells) where you want to check for duplicates. For example, to find duplicate email addresses, highlight the entire email column.
2. On the Home tab, click Conditional Formatting in the "Styles" group.
3. Go to Highlight Cells Rules > Duplicate Values...
4. In the dialog box that appears, make sure "Duplicate" is selected in the first dropdown menu.
5. Choose a formatting style from the second dropdown menu, such as "Light Red Fill with Dark Red Text," or create your own custom format.
6. Click OK.

Instantly, all the duplicate values in your selected range will be highlighted. This gives you a clear visual audit before you decide to filter them, delete them, or make corrections.

Method 4: Use Formulas to Identify Duplicates

For more control and flexibility, you can use formulas in a helper column to flag duplicates. This method is incredibly useful because you can customize what counts as a "duplicate" and even tag the first, second, or third instance of an entry.

Using the COUNTIF Formula:

The COUNTIF function counts how many times a certain value appears in a range. We can use it to build a simple but powerful duplicate detector.

1. Choose a blank column next to your data. In the top cell of that new column (let's say it's column E), type a header like "Duplicate Check."
2. In the cell just below the header (E2), enter the following formula. Let's assume you're checking for duplicate email addresses in column B:

=IF(COUNTIF($B$2:$B$100, B2)>1, "Duplicate", "Unique")

1. Let's break that down:
2. Press Enter, then drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the formula to the rest of the column.

You now have a helper column that clearly labels every single row as either a unique entry or a duplicate. From here, you can simply filter this column to show only the "Duplicate" rows and decide what to do with them.

Using the UNIQUE Function (Modern Excel Versions)

If you're using Microsoft 365 or a recent version of Excel, there's a fantastic function called UNIQUE that does exactly what you need with almost no effort. It dynamically spills a list of all unique values from a specified range.

Simply click on an empty cell and type:

=UNIQUE(A2:A100)

Just replace A2:A100 with the column or range you want to extract unique values from. Press Enter, and Excel will automatically create a list containing only the unique entries. It's fast, simple, and updates automatically if the original data changes.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 5: Automating the Process with Power Query

If you have to clean up the same type of data regularly (e.g., a weekly sales export), Power Query is your best friend. Power Query is Excel's data transformation tool, and it can record your cleanup steps and automate them for you next time.

A Quick Overview of Removing Duplicates in Power Query:

1. Select your data range and go to the Data tab. In the "Get & Transform Data" group, click From Table/Range.
2. Your data will open in the Power Query Editor. This is a separate window where you can transform data without affecting your original worksheet.
3. Select the column(s) you want to use to identify duplicates. To select multiple columns, hold down the Ctrl key while clicking their headers.
4. Right-click on one of the selected column headers. In the menu that appears, simply select Remove Duplicates.
5. Power Query will remove the duplicate rows, showing you a preview of the clean data.
6. When you're ready, click Close & Load in the top-left corner. Power Query will load the cleaned data into a new worksheet in your Excel file.

The best part? Next week, when you get a new data file, you can just paste the new data into your original source table and click "Refresh All" on the Data tab. Power Query will automatically run all your cleanup steps, including removing duplicates, in seconds.

Final Thoughts

Cleaning up duplicated data in Excel doesn't have to be a headache. Whether you need a quick, permanent deletion with the "Remove Duplicates" tool, a non-destructive copy using "Advanced Filter," or an automated workflow with Power Query, Excel provides a method that fits your needs.

Manually cleaning data in spreadsheets is often the first step, but it becomes repetitive when you’re constantly pulling reports from different sources like Google Analytics, Shopify, your CRM, or your ad platforms. At Graphed you built a tool to eliminate this manual reporting process completely. You can connect all your data sources in seconds, and then use simple natural language - just like chatting with a person - to create the exact dashboards and reports you need, all with real-time, clean data.