7. PivotTables and PivotCharts
Practice and Answer Key: PivotTable by City and Platform
Goal of this practice: Build a City × Platform PivotTable, then show each city as a share of total sales.
या practice चं ध्येय: City × Platform PivotTable बनवा, मग प्रत्येक शहराचा total sales मधला share दाखवा.
इस practice का लक्ष्य: City × Platform PivotTable बनाएँ, फिर हर शहर का total sales में share दिखाएँ.
Suppose Amir wants one table that shows sales by city and by platform for the ten-row mini dataset of Module 3 (tblMini). All data is fictional.
Use tblMini from Module 3 (10 rows, total ₹2,870).
Exercise (one under the other):
- Click inside tblMini and choose Insert › PivotTable › New Worksheet.
- Put City in Rows and Platform in Columns.
- Put Amount in Values (Sum of Amount).
- Note the Grand Totals.
- Add Amount to Values again and set Show Values As › % of Grand Total.
- Sort the rows by Grand Total, largest first.
Expected result (check your numbers):
| Row Labels | Amazon Now | Blinkit | Grand Total |
|---|---|---|---|
| Kolhapur | 232 | — | 232 |
| Nagpur | — | 240 | 240 |
| Nashik | — | 360 | 360 |
| Pune | 110 | 434 | 544 |
| Sambhaji Nagar | 195 | — | 195 |
| Solapur | — | 1299 | 1299 |
| Grand Total | 537 | 2333 | 2870 |
Share of Grand Total: Kolhapur 8.08%, Nagpur 8.36%, Nashik 12.54%, Pune 18.95%, Sambhaji Nagar 6.79%, Solapur 45.26%. Blinkit is 81.29% of all sales.
Answer key (open after you try)
Insert › PivotTable › From Table/Range: tblMini › New Worksheet
Rows: City · Columns: Platform · Values: Sum of Amount
Values (2nd copy): Show Values As › % of Grand Total
Right-click Grand Total › Sort › Sort Largest to Smallest
Empty cells mean that city had no order on that platform. Right-click › PivotTable Options › "For empty cells show: 0" if the manager prefers zeros.
Common mistakes:
| What you see | Why | Fix |
|---|---|---|
| Count of Amount instead of Sum | A blank or text value in Amount | Fix the data, then Summarize Values By › Sum |
| New rows do not appear | The pivot was built on a fixed range | Build it on the Table and press Refresh |
| Percentages add up to more than 100% | % of Column Total was chosen | Choose % of Grand Total |
| Sorting by Grand Total does nothing | Sorted the City labels instead | Right-click a Grand Total number, then Sort |
Ravindra Bagale's rule: build every pivot on a Table, so Refresh picks up next month's rows.
Thodkyaat sangaycha tar (quick recap)
Clean Table var PivotTable; Rows/Columns/Values/Filters samja; Sum vs Count heading nakki bagha; % aani running total sathi Show Values As; dates group kara (Years sobat); calculated field sum var chalto; Top 10, slicers, timelines aani Report Connections ne interactive report; source badalla ki Refresh All; KPI sathi GETPIVOTDATA. Aata pudhe jaauya – charts!
Loading practice tasks...
- Total covers (Easy): Add up Covers in D2:D11.
- Average bill amount (Easy): Average of E2:E11, rounded to 2 decimals.
- Rows in Downtown (Easy): How many rows have Outlet "Downtown"?
- Largest covers (Easy): The largest value in D2:D11.
- Covers for Downtown (Medium): Total of D2:D11 where B is "Downtown".
- Downtown in Austin (Medium): Total of D2:D11 where B is "Downtown" and C is "Austin".
- Best in Downtown (Medium): Largest E2:E11 value where B is "Downtown".
- Value of row 2 (Medium): D2 times E2.
- Look up Liam O'Brien (Medium): The Covers for "Liam O'Brien" (use a lookup on column A).
- Total value (Hard): Sum of D times E for all rows.
- Share of Downtown (Hard): Share of D2:D11 that is "Downtown", rounded to 3 decimals.
- Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
- Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".