Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 4, Live 2
PivotTables, PivotCharts, Slicers and Power Query
A thousand sales, summarised by zone in four drags. Then a chart that follows the table, a button that filters both, and a tool that remembers how you cleaned a file.
By the end of this class you can
- Build a PivotTable from Sales Data and explain its four areas
- Fix a PivotTable that counts when it should sum
- Sort it, format it and show each row as a share of the total
- Add a PivotChart whose title states the finding
- Filter the PivotTable and chart together with a slicer
- Say what Power Query is for and when it replaces cleaning by hand
In Week 3 you built a summary table by zone with four formulas, anchored ranges and a total row. A PivotTable builds the same table in about ten seconds, and rebuilds it by any other column with one drag. Start from your workbook with Revenue, Size and Zone in M, N and O.
1. PivotTables
Definition
A PivotTable is a summary table that Excel builds for you from a list of records. You choose which column to group by and which column to total, and Excel does the grouping and the arithmetic. Change your mind, and it rearranges itself: it "pivots".
Real-Life Scenario
Sorting the day's receipts into piles on a counter. One minute you pile them by branch and add each pile; the next you sweep them together and pile them by product instead. Same receipts, a different question, no retyping.
The Four Areas
| Area | What a field placed here does | Example |
|---|---|---|
| Rows | One row in the table for each different value | Zone: five rows |
| Columns | One column for each different value | Customer_Type: Corporate, Retail, Unknown and Wholesale side by side |
| Values | The numbers to total, count or average | Revenue |
| Filters | Limit the whole table to some records | Only Laptops |
Building It
Insert a PivotTable
When a PivotTable Counts Instead of Sums
Look at the label Excel puts in the Values box. On this sheet it says Count of Revenue, not Sum, and the numbers are how many sales each zone had, not how much money. The reason is the 25 sales whose quantity is unknown: their Revenue cell holds empty text, and when a column contains any text or blank cell, Excel guesses you want to count it. This is one of the most common PivotTable mistakes, and it is easy to miss because the numbers look like numbers.
Change Count to Sum, and format it
The Grand Total, 1,067,343,000, matches =SUM(M2:M1051) exactly, and every zone matches the SUMIFS table you built in Week 3. The difference is that this one took seconds and can be rearranged in seconds. Always read the Values label: it says exactly what the numbers are.
Sort It, and Show Shares
Sort, then add a share column
South West is first with 29.7% of all revenue. That one number, which took a right-click, is the headline of the chart in the next section.
Two Dimensions at Once
Drag Customer_Type to Columns, and the table splits each zone by the kind of customer:
One drag, and you can see each zone's customer mix: in South West the biggest is Wholesale, South South the biggest is Wholesale, North West the biggest is Retail, North Central the biggest is Wholesale, South East the biggest is Retail. The lead switches between Retail and Wholesale from zone to zone, and Corporate, never first, still brings in a large share everywhere: no zone can afford to ignore any kind of customer. The Unknown column is the fifteen sales Step 5 labelled: small, but honestly counted.
A PivotTable is a snapshot. If you change the data sheet, the PivotTable does not update until you right-click it and choose Refresh. Before you trust a PivotTable, refresh it.
Picture the receipts on the counter, swept into five piles by zone with one movement of your arm, then each pile split by customer type with another. No adding by hand, ever.
2. PivotCharts
Definition
A PivotChart is a chart drawn from a PivotTable. When the PivotTable changes (sorted, filtered, refreshed), the chart changes with it.
Real-Life Scenario
A shadow on a wall. Move your hand, and the shadow moves. You never redraw the shadow.
Remove the second Revenue field and Customer_Type, so the PivotTable is back to one total per zone, sorted largest to smallest. Then:
Insert a PivotChart
The Title Says the Finding
Excel's default title would be "Total". A better one says what the reader should notice: "South West brings in 30% of all revenue". Someone who reads only the title still gets the point. This rule matters so much that it is in your pod's dashboard brief.
A bar chart draws the first row of the table nearest the bottom. So a PivotTable sorted largest to smallest gives a chart with the largest bar at the bottom. Fix it on the chart: right-click the zone labels, Format Axis, tick Categories in reverse order. Now the biggest bar is on top, where the eye starts.
One colour is used for every bar except the leader, which is picked out in the accent colour. Colour should mean something; five different colours for five zones would mean nothing.
Picture the shadow on the wall, with a caption written underneath it that tells the viewer what shape to see.
3. Slicers
Definition
A slicer is a panel of buttons, one per value of a field, that filters a PivotTable (and its chart) when you click a button. It shows at a glance what the table is currently filtered to.
Real-Life Scenario
The channel buttons on a TV remote. Press one and the whole screen changes; the button stays lit so you know which channel you are on.
Insert a Category slicer
With Laptops selected, the table shows laptop revenue only: 344,720,000 in total, and the chart redraws to match. Hold Ctrl and click a second button to select two categories. Click the funnel with a red cross at the top of the slicer to clear it.
A slicer for every field turns a report into a control panel nobody understands. Pick the one or two questions the reader will actually ask. For the pod dashboard, that is Customer_Type.
Picture the TV remote with five buttons: whichever is lit is what the whole screen is about.
4. Power Query: What It Is and When to Use It
Definition
Power Query is Excel's tool for importing and cleaning data by recording steps. Each thing you do (remove duplicates, trim spaces, fix capitals) is saved as a step. Next month, when a new file arrives, you click Refresh and every step runs again by itself.
Real-Life Scenario
A recipe card versus cooking from memory. In Week 2 you followed the Cleaning Process tab by hand: helper columns, Paste Values, delete, ten steps. If TechPlug sends you April's sales next month, you do all of that again. Power Query writes the recipe down once.
A 15-Minute Look
Steps 3, 6 and 7 of the Cleaning Process, recorded
| Applied Steps (right of the editor) | Rows after it | What it recorded |
|---|---|---|
| Source | 1,070 | Read TechPlug_Sales_Data.csv |
| Promoted Headers | 1,070 | Used the first line as column names |
| Changed Type | 1,070 | Guessed a type for each column |
| Removed Duplicates | 1,050 | Took out the rows entered twice |
| Trimmed Text | 1,050 | Removed spaces at the start and end of Staff_Name |
| Capitalized Each Word | 1,050 | Fixed the capitals in Staff_Name |
Staff_Name goes from 101 spellings to 10, and the rows from 1,070 to 1,050, as they did by hand in Week 2, but this time the cleaning is saved. Drop next month's file in the same place and click Data, Refresh All: the new data is cleaned the same way, without anyone remembering how.
In Week 1 you saw that Excel's filters and SUMIF treat "LAPTOPS" and "Laptops" as the same. Power Query does not: to it they are two different values. That is one more reason the Cleaning Process fixes Category even though Excel was forgiving about it.
| Clean by hand (Week 2) | Clean with Power Query |
|---|---|
| Best for a one-off file | Best for a file that arrives again and again |
| Every step visible as a formula in the sheet | Every step listed in Applied Steps |
| Must be redone for a new file | Refresh reruns every step |
| Works in every Excel | Full editor in Windows Excel; limited elsewhere |
This was a first look. Power Query is taught in full in the SQL and Power BI tracks of Cohort 2, where it is the main way data is cleaned. Everything you learnt in Week 2 is exactly what you will be clicking there.
Picture the recipe card pinned above the stove. Next month, a new bag of the same ingredients arrives, and you follow the card without thinking.
Practical Exercises
Exercise 1: Very Easy
Build a PivotTable of Sum of Revenue by Category. Which category is first when sorted?
Exercise 2: Simple Application
Change the value to Count of Transaction_ID. Which category has the most sales? Is it the same one?
Exercise 3: Combination
Put Payment_Method in Columns. Which payment method brings in the most money?
Exercise 4: A Small Real-World Problem
Add a PivotChart to the Category PivotTable, give it a title that states the finding, and add a Branch slicer. Select Kano. Does the leading category change?
Answers to the exercises
Exercise 1: Laptops, with 344,720,000. Exercise 2: Appliances, with 222 sales, a different category: many sales is not the same as much money. Exercise 3: Transfer, with 282,368,000. Exercise 4: in Kano the leader is Televisions, not the company-wide leader.
Checkpoint
Q1. What are the four areas of a PivotTable?
Rows, Columns, Values and Filters.
Q2. Your PivotTable says "Count of Revenue". Why, and how do you fix it?
The column contains empty text on the sales with no quantity, so Excel defaulted to Count. Value Field Settings, Sum.
Q3. What should a chart title say?
The finding, for example "South West brings in 30% of all revenue".
Q4. Why is the biggest bar at the bottom of a bar chart from a sorted PivotTable, and how do you fix it?
Bar charts draw the first row at the bottom. Format Axis, Categories in reverse order.
Q5. When is Power Query better than cleaning with formulas?
When the same kind of file arrives again and again, because the recorded steps rerun on Refresh.
Tick Before You Move On
- ☐ I built a PivotTable and can name its four areas
- ☐ I changed Count of Revenue to Sum and formatted the numbers
- ☐ I sorted the PivotTable and added % of Grand Total
- ☐ I added a PivotChart with a title that states the finding
- ☐ I added a slicer and filtered the table and chart together
- ☐ I opened Power Query, recorded three cleaning steps and loaded the result
- ☐ I know to Refresh a PivotTable after the data changes
Quick Recap
A PivotTable groups and totals records by dragging fields into Rows, Columns, Values and Filters. Always read the Values label: a column with blanks or empty text defaults to Count. Sort, show % of Grand Total, and the headline writes itself. A PivotChart follows its PivotTable; its title should state the finding. A slicer filters the table and the chart together and shows what is selected. Power Query records cleaning steps so a new file can be cleaned with one Refresh.
Final Scenario
On a Monday morning TechPlug's managing director asks: "Which zone should get the new regional manager?" By 9:05 you have a PivotTable by zone, sorted, with % of Grand Total, and a bar chart titled "South West brings in 30% of all revenue".
She asks a follow-up: "And just laptops?" You click Laptops on the slicer. The table and chart change in front of her. She asks about corporate customers; you drag Customer_Type to Columns.
Three questions, three answers, no new formulas. That is what PivotTables are for.
Build the table once; let the questions rearrange it.
Next, Week 4, Virtual 2: two PivotTables from scratch on your own, by Branch and by Month, with a Customer_Type slicer. Then the Week 4 pod task: the Verdant Foods dashboard.