DEV Community

Robert Breen
Robert Breen

Posted on Originally published at learn.ynteractive.com

Excel for the Web: Remove Duplicates Before the PivotTable, or Your Top Customer Is Overstated by $2,310

"Who are our top customers this quarter?" is one of the most common questions a small office asks of a sales export, and one of the easiest to get slightly wrong.

The export below has 12 orders. One of them is in the file twice, which happens all the time when a system exports overlapping date ranges or someone pastes two downloads together. If you pivot first, the duplicate silently inflates one customer's total and the grand total.

Here's the whole fix in Excel for the web, with the real numbers, in the order that keeps the answer right: dedupe, pivot, sort by value, format.

The data

Paste this into A1 of a blank workbook (or open your own CSV):

Order   Date    Customer    Region  Amount
1001    7/3/2026    Maple Dental    East    1250
1002    7/8/2026    Cobalt Legal    West    860
1003    7/15/2026   Northside Realty    East    2140
1004    7/22/2026   Maple Dental    East    980
1005    8/2/2026    Pine Street Clinic  West    1575
1006    8/9/2026    Cobalt Legal    West    2310
1006    8/9/2026    Cobalt Legal    West    2310
1007    8/18/2026   Harbor Accounting   East    640
1008    8/27/2026   Northside Realty    East    1890
1009    9/4/2026    Pine Street Clinic  West    720
1010    9/12/2026   Maple Dental    East    1430
1011    9/19/2026   Harbor Accounting   East    1105
1012    9/26/2026   Cobalt Legal    West    1270
Enter fullscreen mode Exit fullscreen mode

Order 1006 appears twice. That's 13 data rows for 12 real orders.

What the duplicate does if you skip step 1

With the duplicate After removing it
Cobalt Legal 6,750 4,440
Grand total 18,480 16,170

Cobalt Legal is still the top customer either way, which is exactly why this kind of error survives review. The ranking looks plausible. The number is 52% too high for that customer and the quarter is overstated by 14%.

Step 1: Remove the exact duplicate

  1. Click any cell in the data (A1 is fine).
  2. Data tab → Remove Duplicates.
  3. Leave Select all columns and My data has headers ticked, click OK.

Excel reports: "1 duplicate values found and removed; 12 unique values remain."

Why all columns? With every column ticked, a row only counts as a copy when every field matches. That's the safe default for an export: you remove true copies and nothing else. If your data has a reliable unique key (like Order here), ticking only that column also works, but think about it first: two different orders that share an ID are a data problem you want to see, not silently delete.

Do this on a copy of the raw export if you need an audit trail. Remove Duplicates edits the range in place.

Step 2: Total every customer with a PivotTable

  1. Insert → PivotTable. The panel shows the source as Sheet1!A1:E13 (headers plus 12 rows).
  2. Under Create your own PivotTable, click New sheet.
  3. In PivotTable Fields, tick Customer (goes to Rows) and Amount (goes to Values as Sum of Amount).

Grand total: 16,170.

Step 3: Sort customers by their totals

Click a total, not a customer name. For example B3 (Cobalt Legal, 4440). Then Data → Sort Descending.

This detail matters: in a PivotTable, sorting from a value cell sorts the row labels by that value. Sorting from a label cell sorts alphabetically. Result:

Cobalt Legal        4440
Northside Realty    4030
Maple Dental        3660
Pine Street Clinic  2295
Harbor Accounting   1745
Grand Total        16170
Enter fullscreen mode Exit fullscreen mode

Step 4: Make it read like money

Select B3:B8, Home → number format box (it says General) → Currency. You get $4,440.00 down to $1,745.00, and $16,170.00 total.

The same answer with formulas

If you'd rather have a result that updates as rows are added (and you've already removed duplicates), Excel's dynamic arrays do it in one cell. With the cleaned data in A1:E13:

=SORTBY(
  HSTACK(UNIQUE(C2:C13), SUMIFS(E2:E13, C2:C13, UNIQUE(C2:C13))),
  SUMIFS(E2:E13, C2:C13, UNIQUE(C2:C13)), -1
)
Enter fullscreen mode Exit fullscreen mode
  • UNIQUE(C2:C13) lists each customer once.
  • SUMIFS(...) totals the amount for each of those customers.
  • HSTACK puts names and totals side by side.
  • SORTBY(..., -1) sorts by the totals, largest first.

The PivotTable is easier to hand to a colleague. The formula is easier to drop into a dashboard sheet. Either way, the dedupe comes first.

Checks before you send the answer

  • Row count before and after dedupe matches what you expect (13 → 12 here).
  • The grand total matches the sum in the source system for the same period.
  • Sort was done from a value cell, not a label.
  • The date range in the export is the quarter you think it is.

I built this as a free interactive lesson that walks the 17 steps in a practice copy of Excel for the web, checking each click: Excel for the Web: Find Your Top Customers in a Sales Export.

Top comments (0)