Pivot tables

01The problem

SUMIFS works, but a summary of revenue by region and month would need 6 × 18 = 108 formulas. The director will then ask for it by channel instead. Pivot tables build these summaries by dragging fields, and rebuild them in seconds when the question changes.

02The concept

A pivot table is a summary that Excel builds for you. You tell it which column to group by and which numbers to add up, and it does what dozens of SUMIFS formulas would do, in a second. Change your mind, drag a field somewhere else, and the summary rebuilds itself.

The name comes from pivoting: turning the same data round to look at it from another side. Revenue by region, then by month, then by channel, all from one table.

What a pivot table needs

The source must be a clean, tabular list:

• One header row, with a name in every column.
• One row per record (here, per order line).
• No blank rows or columns in the middle, and no subtotal rows.
• The columns you want to group by: for Kolanut, the revenue, region, channel and category columns you added in lessons 2 and 6.

Your Orders table already meets all of these. Build pivots on a Table, not a fixed range: when rows are added to the Table, a Refresh picks them up. A pivot built on A1:K4267 would miss them.

The four areas

You need at least one field in Values and usually one in Rows. Columns and Filters are optional. Put text fields (region, channel) in Rows, Columns or Filters; put number fields (revenue, quantity) in Values.

Summarise Values By: sum, count, average…

Numbers in Values are summed by default. Right-click any number in the pivot → Summarize Values By to change it.

With channel in Rows and revenue in Values three times, each summarised a different way:

Each summary answers a different question. Sum: how much did each channel bring in? Count: how many order lines? Average: how big is a typical line? Wholesale lines average five times the size of kiosk lines.

Show Values As: percentages and differences

Summarise Values By decides what to calculate. Show Values As (right-click a number) decides how to show it:

The same region pivot as % of Grand Total:

And with years across the top as % of Column Total, Lagos's share rises from 47.9% of 2025's revenue to 52.5% of 2026's so far. That's a story worth telling: the business is leaning more on Lagos, not less.

To show the amount and the percentage side by side, drag revenue into Values twice and set Show Values As only on the second one.

Grouping dates

A date field in Rows or Columns would give one row per day: 546 of them. Group it instead: right-click any date in the pivot → Group → choose Months, Quarters and Years (choose Years together with Months, or January 2025 and January 2026 are added together).

Recent versions of Excel group dates automatically when you add a date field; click the + next to a year to see its quarters and months.

By quarter, Kolanut's revenue grows almost every quarter:

December 2025 alone was ₦66,284,310, the biggest month by far: the festive season. That's why Q4 stands out.

Numbers can be grouped too: right-click a quantity in Rows → Group → Starting at 1, Ending at 30, By 10 gives bands 1–10, 11–20, 21–30.

Sorting and filtering inside a pivot

• Sort: right-click a number → Sort → Largest to Smallest. The pivot keeps that order when it refreshes.
• Filter a row field: click the drop-down on Row Labels. Value Filters → Top 10 shows the top N items by value: the top 5 customers, the top 3 products.
• Filters area: drag channel to Filters, and a drop-down above the pivot limits everything to one channel.

Slicers

A slicer is a set of buttons that filters the pivot with one click, which is clearer than a drop-down for anyone reading your report.

1. Click inside the pivot.
2. PivotTable Analyze → Insert Slicer, tick channel, OK.
3. Click Wholesale: the pivot shows wholesale only. Ctrl + click to choose several. The clear-filter icon in the corner shows everything again.

A timeline (PivotTable Analyze → Insert Timeline) is a slicer for dates: drag across months to filter a period.

Refreshing

A pivot table is a snapshot. It doesn't update by itself when the source data changes. After editing or adding rows: Data → Refresh All (Ctrl + Alt + F5), or right-click the pivot → Refresh.

GETPIVOTDATA

If you type = and click a cell inside a pivot, Excel writes a GETPIVOTDATA formula instead of a simple reference:

=GETPIVOTDATA("revenue", $A$3, "region", "Lagos")

That's deliberate: it finds "Lagos revenue" by name, so it still works when the pivot is re-sorted or reshaped. It's useful for a summary page that quotes pivot figures. If you'd rather have plain references, turn it off under PivotTable Analyze → Options ▾ → Generate GetPivotData.

03Example

Revenue by channel, with each channel's share. channel in Rows, revenue in Values twice, the second shown as % of Grand Total:

Wholesalers are 21 of Kolanut's 90 customers, fewer than a quarter, but bring in 70% of revenue. A director would want to know which wholesalers, and whether any are at risk: that's the next question, and with customer_name in Rows and a Top 10 filter, the next pivot.

This half-year against the same half last year. The data stops at June 2026, so comparing 2026 with all of 2025 isn't fair. Filter both years to January–June (group by Months and Years, and filter the months): the first half of 2025 brought in ₦244,163,070 and the first half of 2026 ₦290,730,455, 19.1% more. Show Values As → % Difference From, with Base field Years and Base item 2025, calculates that for you.

04Walkthrough

1. Click inside the Orders table (with its revenue, region, channel and category columns).
2. Insert → PivotTable → From Table/Range → New Worksheet, OK.
3. In the PivotTable Fields pane, drag region to Rows and revenue to Values. You get Sum of revenue by region.
4. Drag order_date to Columns. Excel groups it by year (click the + to see quarters and months). If it doesn't, right-click a date → Group → select Months and Years.
5. Right-click any revenue number → Number Format → Number, 0 decimals, with a thousands separator.
6. Sort: right-click a revenue number in the Grand Total column → Sort → Largest to Smallest.
7. Check the Grand Total is ₦830,541,245, the same as your SUM from lesson 4. If not, the pivot needs refreshing or its source range is wrong.
8. Insert Slicer for channel. Click Wholesale, then Kiosk, and watch the whole pivot change.
9. Drag order_date out of Columns, and drag revenue into Values a second time. Right-click the second one → Show Values As → % of Grand Total.

1. The pivot table: region in rows, sorted by revenue, with a second value column showing % of total. Lagos is 49.5% of all revenue.
2. Field list: every column of the source Table. Ticked fields are in use.
3. Areas: Filters, Columns, Rows and Values. Drag fields between them to reshape the summary.
4. Slicer for channel: click Wholesale and the pivot shows wholesale revenue only.

Shortcuts for pivot tables

Summary

05Practice

06Challenge

07More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

08Check your understanding