Y

Excel · Beginner

Pivot tables on half a million sales lines

3 min read

Why it matters

In the 50 Data Analyst job descriptions we read, 44% named Excel, rising to 57% of junior postings, and in India the phrase is usually "advanced Excel". For many operations, finance and business analyst roles, the first-round assignment is an Excel file with a question attached. Pivot tables are how you answer it quickly.

The concept

A pivot table groups rows and aggregates values, like group by in SQL, with a drag-and-drop interface:

  • Rows: what you group by (Country)

  • Columns: a second grouping spread across the top (Month)

  • Values: what you aggregate (Sum of Revenue)

  • Filters: rows to include or exclude (Invoice does not start with C)

Pivot tables read from a range or, better, from an Excel Table (Ctrl+T). Tables grow automatically when you paste new rows, so the pivot does not miss data after a refresh.

Worked example: revenue by country

Download the UCI Online Retail II workbook and open the sheet Year 2010-2011. It has 541,910 rows, one per invoice line.

  1. Click inside the data and press Ctrl+T to make it a Table. Name it sales.

  2. Add a column Revenue with the formula below. In a Table, the formula fills down by itself.

revenue-column
=[@Quantity]*[@Price]

  1. Add a helper column IsClean that flags the rows to keep: not a cancellation, positive quantity, known customer.

is-clean-column
=AND(LEFT([@Invoice],1)<>"C", [@Quantity]>0, [@[Customer ID]]<>"")

  1. Insert, PivotTable. Put Country in Rows, Revenue in Values, IsClean in Filters set to TRUE. Sort descending.

You should see the United Kingdom at about 7.31 million, then the Netherlands (285,446), EIRE (265,546), Germany (228,867) and France (209,042). The total of clean revenue is about 8.91 million.

  1. Add Customer ID to Values as a Distinct Count (tick "Add this data to the Data Model" when creating the pivot, otherwise distinct count is not offered). The Netherlands has only 9 customers and EIRE 3. Revenue per customer there is more than 30,000, against about 1,860 in the UK. These are wholesale buyers, and a single customer leaving would change the ranking. That is a finding worth one line in your summary.

Now look at products. Put Description in Rows: the top product by revenue is "PAPER CRAFT , LITTLE BIRDIE" at 168,470. Drill into it (double-click the value) and you find one invoice line of 80,995 units on 9 December 2011, which was cancelled by a separate C invoice. Excluding cancellation lines kept the original order but dropped its reversal. A pivot table will never tell you this. Checking the top rows will.

Common mistakes

  • Pivoting a range instead of a Table. New rows fall outside the range and silently disappear from the report.

  • Summing a column stored as text. Numbers imported as text show as a Count, not a Sum. Fix the type first.

  • Reporting partial periods. The data ends on 9 December 2011, so December looks like a collapse. Mark partial months.

  • Trusting the top of the list. One huge order, one test account or one refund often sits at the top. Always drill into the first few rows.

Interview angle

Assessments often give a raw export and ask for "top 5 X by Y, with insights". Deliver the pivot, then two observations with numbers, then the caveats (partial months, cancellations, missing customer ids). The caveats show judgement, which is what they are really testing.

Practice task

  1. A pivot of monthly clean revenue (group InvoiceDate by Months and Years). Which month is highest?

  2. A second pivot: revenue per country as a % of grand total.

  3. Find the three largest single invoice lines and decide whether each should be in the report.

Further reading