Excel Pivot Table Remove Duplicates: What Works

Charts & pivot tables · Updated November 21, 2026

A PivotTable does not delete duplicate rows from its source data: it groups matching field values into one displayed item and summarizes the related values. To remove duplicate records from the data itself, use Data > Remove Duplicates; for a non-destructive list or repeatable cleanup, use UNIQUE or Power Query instead.

What “remove duplicates” means in Excel

The phrase can describe two different tasks. You might want repeated labels to appear only once in a PivotTable, or you might want to delete repeated records from the worksheet. Those are not the same operation. A PivotTable changes how information is arranged and summarized; it does not clean or rewrite the source range.

For example, a sales list might contain five rows for the product Mug. In a PivotTable with Product in Rows and Sales in Values, Mug normally appears as one row, with its sales figures combined. The five original records remain in the source. That is useful for analysis, but it is not proof that the underlying records were duplicates or have been removed.

Before acting, decide what counts as a duplicate. It could mean identical values across every column, a repeated customer ID, or a repeated email address even when other columns differ. The right method depends on that definition.

How a PivotTable handles repeated values

A PivotTable creates groups from the fields you place in its layout. If Product is in Rows, records with the same product value are generally represented by one item in the row labels. If Sales is in Values, Excel applies a summary such as Sum, Count, or Average to the records in each group. The source rows are still present and can be inspected by returning to the source data.

This grouping can make a PivotTable look like a unique list. Drag a field such as Customer into Rows and the PivotTable can show each customer label once, rather than repeating it on every source record. If you add a second field, such as Order Date, the same customer may appear under multiple date groups because the combination of fields creates the groups.

Repeated labels are not necessarily duplicate records. Two orders with the same customer and product may be separate legitimate purchases. Removing one from the source would change the underlying facts, while summarizing both in a PivotTable may be exactly what you want.

Remove duplicate rows from the source

In Excel for Microsoft 365 on Windows, make a backup or copy of the data first if there is any chance you will need the original records. Then use the built-in command:

  1. Select a cell in the data range or Excel table.
  2. Choose Data > Remove Duplicates.
  3. In the dialog, confirm whether the first row contains headers.
  4. Select the columns Excel should compare. Select every column to identify completely identical rows, or select only the key column or columns that define a duplicate for your task.
  5. Choose OK and review Excel’s report of how many duplicate values were removed and how many unique values remain.

Excel keeps the first occurrence it encounters and removes later rows that match the selected comparison columns. The command changes the selected source data; it is not merely a filter. If the PivotTable uses that data, refresh the PivotTable afterward with PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh.

On Windows, Ctrl+Z can undo the removal if it is still the most recent action. On Mac, use Command+Z. Do not rely on Undo as your only safeguard: later actions, closing the workbook, or other changes can make recovery inconvenient. A separate copy is safer when the original records matter.

Choose the comparison columns carefully

The checkboxes in Remove Duplicates define what Excel considers a match. Suppose a table has Customer ID, Name, and City. If you select only Customer ID, Excel keeps one row per ID, even if two rows for that ID have different names or cities. It keeps the first row and discards the other selected matches; it does not decide which address is newer or which record is more complete.

If you select all three columns, two rows are removed as duplicates only when the values in all three selected columns match. This distinction is important when a repeated email, product name, or customer ID is the key, but other columns contain meaningful differences. Resolve conflicting records with a deliberate rule—such as retaining the latest dated row—before deleting anything.

For example, if you want a customer list with one row per email address, sort or otherwise determine which record should be retained, then select only Email in the dialog. If the goal is to remove repeated, identical transaction lines, select all columns that make up the transaction record. In either case, inspect a sample of the results before using the cleaned data for reporting.

Count distinct items without deleting records

If the real question is “How many different customers or products are there?”, keep the source intact and use a distinct count. In Excel for Microsoft 365 on Windows, create a PivotTable with Insert > PivotTable and, in the creation dialog, select Add this data to the Data Model. Add the field to Values, open its Value Field Settings, and choose Distinct Count under Summarize Values By.

For instance, put Region in Rows and Customer ID in Values, then set the value calculation to Distinct Count. The result counts each customer ID once within each region, even if that customer has many source rows. It does not remove those rows. Distinct Count is available for PivotTables using the Data Model; if the option is missing, the PivotTable may not have been created with that setting.

Data Model and Power Pivot capabilities differ by platform. The full Data Model-based distinct-count workflow is a Windows desktop feature; it may not be available in Excel for Mac or Excel for the web. If you do not see Distinct Count, use a suitable Windows desktop workbook or calculate a distinct list with another method rather than assuming ordinary Count gives the same result.

Use Power Query for repeatable cleanup

Power Query is useful when you receive a fresh copy of a file regularly and want to apply the same duplicate rule each time. In Excel for Microsoft 365 on Windows, select a cell in the source data and choose Data > From Table/Range. In Power Query Editor, select the column or columns that define a duplicate, then choose Home > Remove Rows > Remove Duplicates. Finish with Home > Close & Load to return the query result to Excel.

Unlike the worksheet Remove Duplicates command, a Power Query operation builds a transformed output and leaves the original source data unchanged. When the source changes, use Data > Refresh All to rerun the query. This makes it a good option for recurring imports, while the worksheet command is often simpler for a one-time cleanup.

Choose the key columns before removing duplicates, just as you would in the worksheet dialog. If other columns contain conflicting values for matching keys, do not assume that the row Power Query retains represents the newest or best record. Define a sorting or grouping rule when the retained row matters, and check the query output before replacing any report or downstream data.

Make a unique list with the UNIQUE function

For a live list that updates when the source range changes, use UNIQUE in Excel for Microsoft 365 or Excel 2021 and later. With the values East, West, East, North, and West in A2:A6, enter this formula in an empty cell:

=UNIQUE(A2:A6)

The spilled result is East, West, North, in the order each value first appears. The formula does not delete or alter the original cells. Leave empty cells below the formula’s location so Excel can display the whole result. If the source is an Excel table, a structured reference can make the formula expand as table rows are added, but the output still depends on the formula’s source range or reference.

UNIQUE can also return distinct rows when given a multi-column range, which is helpful when duplicate means that every field in a record matches. A unique list is not a replacement for cleaning conflicting records: it reports distinct values or rows according to the input, not which version should be trusted. UNIQUE is unavailable in older perpetual Excel versions such as Excel 2019, where Remove Duplicates, a PivotTable, or Power Query may be alternatives.

Common problems and checks

  • The PivotTable still shows old results: Refresh it after changing the source. If rows were added outside the original source range, update the PivotTable’s data source or use an Excel table that can expand with new rows.
  • Values that look identical are treated differently: Extra spaces, inconsistent spelling, or different underlying values can prevent records from matching. Clean or standardize the source first; do not remove rows based only on visual similarity.
  • A row disappeared even though another column differed: Check which columns were selected in Remove Duplicates. Excel compares only those selected columns, then retains the first matching row.
  • A PivotTable shows a count rather than a sum: Check the field’s Value Field Settings. A field with text or mixed data may default to Count; choose the appropriate summary when the data type and reporting goal support it.
  • Repeated labels remain in the PivotTable: Check whether another row field creates separate groups, or whether the labels are not actually identical. A PivotTable groups based on its fields; it does not standardize inconsistent source text.

Frequently Asked Questions

Can a PivotTable remove duplicates from the original data?
No. It summarizes or groups records for display, but the source rows remain. Use Data > Remove Duplicates to delete matching records, or Power Query to create a cleaned output while leaving the source unchanged.

How do I show each name only once in a PivotTable?
Place the name field in the Rows area. Excel generally displays each distinct label once within that grouping. This changes the report layout, not the source list.

Does Remove Duplicates keep the first or last row?
It keeps the first matching row in the current order and removes later matches. If the row to retain matters, sort or otherwise identify the preferred record before removing duplicates, and save a copy first.

How can I count unique customers in a PivotTable?
Create the PivotTable with Add this data to the Data Model selected, add Customer ID to Values, and choose Distinct Count in Value Field Settings. The source records remain unchanged.

Will a PivotTable update after I remove duplicates?
Refresh it after changing its source. If the PivotTable does not include newly added rows, check its data source range; an Excel table is often easier to maintain as the source grows.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.