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 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 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 Click any cell in the data (A1 is fine). Data tab → Remove Duplicates. 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 Insert → PivotTable. The panel shows the source as Sheet1!A1:E13 (headers plus 12 rows). Under Create your own PivotTable, click New sheet. 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 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 ) 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.