Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 4, Virtual 2
PivotTable Practice
Two PivotTables from a blank sheet, on your own: one by branch, one by time. Then one slicer that filters both at once.
By the end of this class you can
- Build a PivotTable from scratch without help
- Show revenue and number of sales side by side
- Group dates into months, and into weeks when months are too few
- Chart a trend with a line
- Connect one slicer to two PivotTables
In Live 2 you followed along. Today, start from your Sales Data tab and a blank mind. If you get stuck, the click paths are here, but try each step before you read it.
What you do in this class
- PivotTable 1: revenue and number of sales by Branch, sorted.
- PivotTable 2: revenue by month, then by week, with a line chart.
- A Customer_Type slicer connected to both.
- Then read the Week 4 pod task: the Verdant Foods dashboard.
Hand in: A sheet called Pivots with both PivotTables, the line chart and the slicer.
PivotTable 1: By Branch
Revenue and sales by branch
Transaction_ID is text, so Excel counts it automatically: Count of Transaction_ID is the number of sales. The Grand Totals must be the company figures, 1,067,343,000 and 1,050.
Which branch has the highest revenue per sale? Divide each branch's revenue by its sales in a column next to the PivotTable.
Check your answer
Port Harcourt. Compare it with the branch that made the most sales, Kano: a different branch. Revenue and number of sales tell different stories, which is why it helps to see both.
PivotTable 2: By Time
Put a second PivotTable on the same Pivots sheet, a few columns to the right of the first.
Revenue over time
When you drop a date field in Rows, Microsoft 365 groups it for you: it adds Months (and sometimes Years and Quarters) to the Rows box and shows Jan, Feb, Mar. If yours lists every single date instead, group it yourself:
If the dates are not grouped
Three rows. You already know the story they tell from Week 1: February is lowest because it is shortest. Three points make a poor trend line, though: a line through three dots says almost nothing. When the months are too few, group by week instead.
Grouping by Week
Seven-day groups
Do not sort this one by value. Time has a natural order, and a trend only makes sense in that order.
A line for a trend
The best week was 29 January to 4 February, at 96,977,000. The lowest full week was 19 February to 25 February, at 57,072,000. The rest move up and down around them with no steady climb or fall: a business holding level through its first quarter. Use a line for things that change over time and bars for comparing separate groups: that choice is part of your pod's dashboard brief too.
One Slicer, Two PivotTables
A slicer for both tables
To see it on a short table, regroup the time PivotTable by Months for a moment:
Wholesale brought in 374,850,000 over the three months, 35% of everything. Click Retail, then Corporate, and compare: the same two tables answer three questions in three clicks.
A new slicer only controls the PivotTable you inserted it from. Report Connections is how one slicer drives every PivotTable built from the same data. Your pod dashboard needs this.
Picture one light switch wired to every lamp in the room. Report Connections is the wiring.
Checkpoint
Q1. Why does Transaction_ID in Values show a count, not a sum?
It is text. Excel can only count it.
Q2. Your time PivotTable lists every single date. What do you do?
Right-click a date, Group, and choose Months, or Days with a number of days such as 7.
Q3. Why group TechPlug's sales by week rather than month for a line chart?
The workbook covers only three months; three points make a poor trend line. Thirteen weeks show the shape.
Q4. A slicer filters one PivotTable but not the other. Why?
It is not connected to the second one. Use Report Connections.
Tick Before You Move On
- ☐ I built the branch PivotTable with revenue and number of sales, sorted
- ☐ I grouped the dates by month, then by 7-day weeks
- ☐ I added a line chart whose title states the finding
- ☐ One Customer_Type slicer filters both PivotTables
Quick Recap
Text fields in Values are counted; number fields are summed. Dates in Rows group into months, or into weeks with Group, Days, 7; keep them in time order. Lines for trends over time, bars for comparing groups. Report Connections lets one slicer filter several PivotTables.
Next: Pod Task Week 4. Your pod builds the Verdant Foods dashboard: a KPI row, three charts, a slicer and three written insights.