How to Remove Duplicates in Excel: The Complete Guide

Duplicate data is one of the most common headaches when working with spreadsheets, whether you’re managing a customer list, tracking sales records, or cleaning up survey responses. A single duplicated row can throw off totals, skew averages, and lead to inaccurate reports if left unchecked. The good news is that Excel offers several built-in ways to find and remove duplicate entries, ranging from a simple one-click tool to more advanced formula-based approaches that give you finer control.

In this guide, we’ll walk through every major method for removing duplicates in Excel, covering the built-in Remove Duplicates feature, conditional formatting to visually spot duplicates before deleting them, formula-based approaches for more advanced control, and Power Query for handling larger or more complex datasets. We’ll also cover how the process differs slightly on Excel for Mac versus Excel for Windows, along with a look at Google Sheets as a free alternative if you don’t have an Excel license.

Why Duplicate Data Causes Problems

Before diving into the removal methods, it’s worth understanding why duplicates matter so much in the first place. Duplicate rows can silently inflate totals in a SUM formula, cause a COUNT function to overstate how many unique customers or transactions you actually have, and create confusion in pivot tables where the same entry appears to be counted multiple times. In mailing lists, duplicates can mean sending the same email twice, which looks unprofessional and can hurt engagement metrics. In financial tracking, a duplicated transaction row can make your numbers appear larger than reality, leading to inaccurate business decisions.

Because of this, cleaning duplicate data isn’t just a cosmetic exercise, it’s an essential step in ensuring your spreadsheet accurately reflects the data you’re actually working with.

Method 1: Using the Built-In Remove Duplicates Tool

This is the fastest and most straightforward method, built directly into Excel, and works well when you want to permanently delete duplicate rows based on one or more columns.

1. Select the range of cells containing your data, or click anywhere inside your data if it’s formatted as a table, since Excel can automatically detect the full range.
2. Click Remove Duplicates in the Data Tools group.
3. A dialog box will appear showing all the columns in your selected range. Check the boxes for the columns you want Excel to compare when identifying duplicates. If you want an entire row to be considered a duplicate only when every column matches, leave all columns checked.
4. Click OK.
5. Excel will display a summary message telling you how many duplicate values were found and removed, along with how many unique values remain.

This method is permanent, meaning the duplicate rows are deleted immediately once you click OK, so it’s a good idea to keep a backup copy of your original data before running it, just in case you need to reference the removed rows later.

Method 2: Highlighting Duplicates with Conditional Formatting (Before Deleting)

 

1. Select the range of data you want to check.
2. Click Duplicate Values.
3. In the dialog box, choose a formatting style (such as light red fill with dark red text) and click OK.
4. Excel will highlight every duplicate value in your selected range, letting you manually review, sort, or filter before deciding how to handle them.

This approach is particularly useful when you’re not entirely sure whether a “duplicate” is actually an error or a legitimate repeated value, such as two different customers who happen to share the same name.

Method 3: Using Formulas to Identify Duplicates

For more control over what counts as a duplicate, formulas give you flexibility that the built-in tool doesn’t offer, especially when you want to flag duplicates without deleting anything automatically.

Using COUNTIF to flag duplicates:

1. In a new column next to your data, enter the formula: =COUNTIF(A:A, A2)
2. Copy the formula down for all rows, then filter the column to show only values greater than 1 to isolate your duplicates.

Using COUNTIFS for multi-column duplicate checks:

If you need to check for duplicates based on more than one column, such as matching both a first name and last name together, use COUNTIFS instead:

=COUNTIFS(A:A, A2, B:B, B2)

This checks whether the combination of values in columns A and B appears more than once, which is especially useful for datasets where a single column alone wouldn’t reliably indicate a true duplicate.

Using IF combined with COUNTIF for a clear label:

=IF(COUNTIF(A:A, A2)>1, "Duplicate", "Unique")

This gives you a clean, readable label directly in your spreadsheet, making it easy to filter or sort based on the word “Duplicate” rather than interpreting raw numbers.

Method 4: Removing Duplicates with Power Query

Power Query is Excel’s more advanced data-cleaning tool, ideal for larger datasets, recurring cleanup tasks, or situations where you need to remove duplicates as part of a broader data transformation process.

1. Select your data range and go to the Data tab.
2. Click From Table/Range (you may be prompted to confirm your data range and whether it has headers).
3. This opens the Power Query Editor in a new window.
4. Select the column or columns you want to check for duplicates by clicking on the column header (hold Ctrl to select multiple columns).
5. Right-click the selected column header(s) and choose Remove Duplicates.
6. Click Close & Load to bring the cleaned data back into your Excel worksheet.

The advantage of Power Query is that it keeps a record of your transformation steps, meaning if your source data changes or grows, you can simply refresh the query rather than repeating the entire manual process from scratch.

How to Remove Duplicates on Excel for Mac

The process on Excel for Mac is nearly identical to Windows, with only minor interface differences.

1. Select your data range.
2. Click Remove Duplicates.
3. Choose which columns to check in the dialog box that appears.
4. Click OK to confirm and remove the duplicate entries.

Conditional formatting and formula-based methods work exactly the same way on Mac as they do on Windows, since these features are part of Excel’s core functionality rather than platform-specific tools.

How to Remove Duplicates in Google Sheets (Free Alternative)

If you don’t have an Excel license or prefer a free, browser-based option, Google Sheets offers a very similar built-in tool.

1. Select the range of data you want to clean.
2. Click Data in the top menu.
3. Click Data cleanup, then Remove duplicates.
4. A dialog box will appear, allowing you to choose which columns to compare and whether your data has a header row.
5. Click Remove duplicates to confirm.

Google Sheets is completely free to use with a Google account, making it a solid option if you’re working with a smaller dataset and don’t need Excel’s more advanced features like Power Query or PivotTables built from complex data models.

Removing Duplicates Based on Specific Conditions

Sometimes you don’t want to remove every duplicate, only duplicates that meet certain criteria, such as keeping the most recent entry while deleting older duplicate records.

Sorting before removing duplicates:

1. Sort your data by a relevant column first, such as a date column, so the most recent or most relevant entry appears at the top of each duplicate group.
2. Run the Remove Duplicates tool as described in Method 1.
3. Since Excel keeps the first occurrence of each duplicate group by default, sorting beforehand ensures the “correct” version of the row is the one that gets retained.

Using helper columns for more complex logic: For situations requiring more nuanced rules, such as keeping the duplicate with the highest sales value rather than simply the first occurrence, a combination of MAXIFS or a helper column with ranking formulas can isolate the specific rows you want to keep before applying Remove Duplicates or a manual filter-and-delete process.

Removing Duplicates in Large Datasets or PivotTable Source Data

When working with very large datasets, thousands or even hundreds of thousands of rows, the standard Remove Duplicates tool can start to feel slow, and manual review becomes impractical. In these situations, a slightly different approach works better.

1. Use Power Query as your primary tool rather than the standard Remove Duplicates button, since Power Query is optimized to handle larger volumes of data more efficiently and keeps your cleanup process repeatable through refreshable queries.
2. Check your PivotTable source range before removing duplicates, since deleting rows directly from data that already feeds a PivotTable can sometimes cause reference errors. It’s safer to clean your source data first, then build or refresh your PivotTable afterward.
3. Consider using the Data Model in Excel for very large datasets, since Power Pivot (built into modern Excel versions) can handle duplicate removal and relationships across multiple tables more efficiently than working within a single flat worksheet.
4. Break large cleanup tasks into smaller batches if you’re working with an older version of Excel or a lower-spec computer, since processing an enormous single-pass duplicate removal can occasionally cause the application to freeze temporarily on very large files.

• Convert your data into an Excel Table (Ctrl+T) before cleaning, since tables automatically expand formulas and keep formatting consistent as your dataset grows or changes.
• Use TRIM and CLEAN functions first if your duplicates aren’t being detected properly, since hidden spaces or non-printing characters can cause Excel to treat two visually identical entries as different values. The formula =TRIM(CLEAN(A2)) removes both extra spaces and non-printable characters in one step.
• Standardize text case before checking for duplicates, since Excel’s Remove Duplicates tool is not case-sensitive by default, but formulas like COUNTIF also ignore case, which can occasionally cause unexpected matches if you need case-sensitive comparison (in which case, EXACT() combined with SUMPRODUCT is a more precise alternative).
• Use Power Query for recurring cleanup tasks, since refreshing a saved query is far faster than manually repeating the Remove Duplicates process every time new data comes in.
• Filter before deleting whenever you’re unsure about a dataset, using conditional formatting or a COUNTIF helper column to review duplicates visually before committing to permanent removal.

Common Mistakes to Avoid

• Forgetting to select the entire dataset range. If you only select part of your data before running Remove Duplicates, Excel may misalign rows, since it only affects the selected columns and rows, not your entire sheet.
• Not accounting for header rows. If Excel doesn’t recognize your first row as a header, it may treat your column titles as data and either flag them as duplicates or fail to properly identify actual duplicate values beneath them.
• Relying on Remove Duplicates without sorting first, when you actually need to preserve a specific version of a duplicate row, such as the most recent transaction, rather than whichever one happens to appear first in the sheet.
• Ignoring hidden characters or extra spaces, which can cause Excel to treat two entries that look identical as unique values, leading to duplicates that go undetected.
• Applying Remove Duplicates across unrelated columns, which can accidentally merge unrelated records if your selected columns don’t accurately represent what should define a “duplicate” in your specific dataset.

Conclusion

Removing duplicates in Excel doesn’t have to be complicated, whether you need a quick one-click cleanup with the built-in Remove Duplicates tool, a more cautious visual review using conditional formatting, precise control through COUNTIF and COUNTIFS formulas, or a repeatable, scalable solution using Power Query. Each method has its place depending on how large your dataset is, how confident you are that a “duplicate” truly needs to be removed, and whether you’re working with a one-time cleanup or an ongoing dataset that gets updated regularly.

The most important habit to build is checking your data before deleting anything permanently. A quick pass with conditional formatting or a COUNTIF helper column can save you from accidentally removing rows you actually needed, especially in datasets where similar-looking entries might represent genuinely different records. With the methods covered in this guide, you should be well equipped to handle duplicate data cleanly, whether you’re working in Excel for Windows, Excel for Mac, or the free Google Sheets alternative.

Frequently Asked Questions

1. Does Remove Duplicates in Excel delete the entire row or just the duplicate cell? By default, Remove Duplicates deletes the entire row once a duplicate is identified based on your selected columns, not just the individual cell containing the duplicate value.

2. Can I undo Remove Duplicates if I made a mistake? Yes, but only immediately after running it, using Ctrl+Z within the same Excel session. Once you close and reopen the file, the undo history is lost, which is why keeping a backup copy beforehand is strongly recommended.

3. Is Excel’s Remove Duplicates tool case-sensitive? No, by default it treats “Apple” and “apple” as the same value. If you need case-sensitive duplicate detection, you’ll need to use a formula-based approach involving the EXACT function instead.

4. What’s the difference between Remove Duplicates and Conditional Formatting for duplicates? Remove Duplicates permanently deletes matching rows immediately, while Conditional Formatting only highlights duplicate values visually, allowing you to review and decide how to handle them manually before making any permanent changes.

5. Can I remove duplicates based on only one column while keeping other differing data intact? Yes, in the Remove Duplicates dialog box, you can choose to check only a single column. However, be aware this means Excel will delete the entire row even if other columns contain different data, so use this option carefully.

6. Does Google Sheets’ Remove Duplicates tool work the same way as Excel’s? Yes, the core functionality is nearly identical, allowing you to select which columns to compare and confirming whether your data includes a header row before removing matching entries.

7. How do I find duplicates without deleting anything at all? Use conditional formatting to visually highlight duplicates, or add a helper column with a COUNTIF formula like =COUNTIF(A:A, A2)>1 to flag duplicates as TRUE or FALSE without altering your original data.

8. Do I need a paid Microsoft 365 subscription to use these features? Remove Duplicates, Conditional Formatting, and standard formulas like COUNTIF are available in both one-time purchase versions of Excel and Microsoft 365 subscriptions, which start at around $70 USD/year (approximately ₹6,000/year) for a personal plan. Power Query is also included in these versions at no extra cost. If you don’t want to pay for Excel at all, Google Sheets offers equivalent core duplicate-removal features completely free with a Google account.