Why this matters
Lena (made up) runs sales at a small office-supply shop. On Friday the owner asks, "Who were our top customers last quarter?" The only source is the export from the sales system: 12 orders, one line each, and one order that is in it twice.
Adding it up by customer with a calculator takes an hour, and the repeated order quietly makes Cobalt Legal look $2,310 bigger than it is. In this module you clean the export, let a PivotTable do the adding and sort the biggest customers to the top in about ten minutes.
What you’ll build
A workbook called "Q3 sales export": the 12 orders on Sheet1 with the duplicate removed, and a PivotTable on Sheet2 with one row per customer, sorted biggest first and formatted as currency.
With the sample numbers, the quarter totals $16,170. Cobalt Legal ($4,440), Northside Realty ($4,030) and Maple Dental ($3,660) bring in $12,130, three quarters of it.
What you need
- Nothing to start the practiceThe practice is built from real Excel for the web screens and does not touch any account.
- A free Microsoft account, to do it for realSign in at microsoft365.com and open Excel. Excel for the web saves as you work.
- A sales exportAny list with one row per order and a customer and amount column: a CSV from your sales, invoicing or online shop system, or the sample in the practice.
Concepts
3 ideas you’ll use in the practice.
Clean before you count
Exports repeat rows more often than you would think: a retried payment, a re-synced order, a line saved twice. Every total built on them is then wrong, and nobody notices.
Data › Remove Duplicates with every column ticked deletes only rows that match in every column, and tells you how many it removed. Here: 1 duplicate found, 12 unique values remain.
A PivotTable groups and totals for you
Insert › PivotTable › New sheet opens the PivotTable Fields list. Tick the column to group by (Customer goes into Rows) and the column to add up (Amount goes into Values as Sum of Amount). There are no formulas to write or copy down.
The same two ticks work for 12 orders or 12,000. Swap Customer for Region and the same PivotTable answers "which region sold most?"
Sort so the answer is the first line
Click any total in the PivotTable and choose Data › Sort Descending. In a PivotTable that sorts the customers by their totals, so the biggest customer is the first row.
Then select the totals and pick Currency from the number format box on the Home tab, so the table can go straight into an email or a slide.
Practice
Do every step yourself in a practice copy of the real tool. It checks your work, and “Do it for me” finishes any step you get stuck on.
Do it for real
Now repeat it in your own account. Tick each step as you go.
Quick check
3 questions to check you’ve got it.
1. Why remove duplicates before you build the PivotTable?
- It makes the file smaller
- A repeated order would be counted twice in the totals
- PivotTables do not work with duplicates
Show the answer
A repeated order would be counted twice in the totals. The PivotTable adds every row it is given. The repeated order 1006 would have made Cobalt Legal look $2,310 bigger.
2. Which two fields give you one total per customer?
- Customer in Rows, Amount in Values
- Amount in Rows, Customer in Values
- Region in Filters, Date in Values
Show the answer
Customer in Rows, Amount in Values. The field you group by goes into Rows; the number you add up goes into Values as Sum of Amount.
3. With the sample numbers, who is the top customer after the duplicate is removed?
- Northside Realty, $4,030
- Cobalt Legal, $4,440
- Cobalt Legal, $6,750
Show the answer
Cobalt Legal, $4,440. Cobalt Legal is $860 + $2,310 + $1,270 = $4,440. With the duplicate still in, it would have shown $6,750.
Word for the Web: Turn Meeting Notes into a Client Update
Numbers are only half the job: next, turn rough meeting notes into a one-page update your client can read in a minute.
Start module 3 →