16. Practice Exercises with Answer Hints
16.6 Module 7: PivotTables
- Pivot of sales by City and Platform. Hint: Rows = City, Columns = Platform, Values = Sum of Amount → total 2,870 on the mini dataset.
- Show each city's sales as a % of the grand total. Hint: Value Field Settings › Show Values As › % of Grand Total.
- Group order dates by month and quarter. Hint: right-click a date › Group › Months, Quarters.
- Count distinct customers per city. Hint: tick Add this data to the Data Model › Value Field Settings › Distinct Count.
- Add a calculated field for 5% commission.
Hint: PivotTable Analyze › Calculations › Fields, Items & Sets › Calculated Field
=Amount*0.05. - Show the top 3 cities by sales. Hint: Row Labels filter › Value Filters › Top 10 › 3 Items.
- Connect one City slicer to two pivots. Hint: right-click slicer › Report Connections.
Ravindra Bagale's Tip
Many students add new rows to the source data and they don't show up even after refreshing the pivot – because the pivot was built on a range, not a Table. Always build the pivot on a Table and use Data › Refresh All. If the pivot shows Count, the column has blanks or text numbers – clean those first.
Ravindra Bagale's Tip – मराठी
बरेच students source data मध्ये नवीन rows add करतात आणि pivot refresh केला तरी त्या दिसत नाहीत – कारण pivot range वर बनवला होता, Table वर नाही. नेहमी Table वर pivot बनवा आणि Data › Refresh All वापरा. Pivot मध्ये Count आला तर column मध्ये blanks किंवा text numbers आहेत – आधी ते clean करा.
Ravindra Bagale's Tip – हिंदी
बहुत से students source data में नई rows जोड़ते हैं और pivot refresh करने पर भी वे नहीं दिखतीं – क्योंकि pivot range पर बना था, Table पर नहीं. हमेशा Table पर pivot बनाओ और Data › Refresh All इस्तेमाल करो. Pivot में Count आए तो column में blanks या text numbers हैं – पहले उन्हें clean करो.
Loading practice tasks...
- Total billable hours (Easy): Add up Billable hours in the "Billable hours" column.
- Average rate per hour (Easy): Average of the Rate per hour ($) column, rounded to 2 decimals.
- Rows in Cloud (Easy): How many rows have Practice "Cloud"?
- Largest billable hours (Easy): The largest value in the "Billable hours" column.
- Billable hours for Cloud (Medium): Total of the Billable hours column where Practice is "Cloud".
- Cloud in Denver (Medium): Total of the Billable hours column where Practice is "Cloud" and City is "Denver".
- Best in Cloud (Medium): Largest value in the "Rate per hour ($)" column where "Practice" is "Cloud".
- Value of row 2 (Medium): Multiply the first-row Billable hours by the first-row Rate per hour ($).
- Look up Vikram Singh (Medium): The Billable hours for "Vikram Singh" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Billable hours" times "Rate per hour ($)" for all rows.
- Share of Cloud (Hard): Share of the "Billable hours" column that is "Cloud", rounded to 3 decimals.
- Total "Billable hours" for Cloud (Hard): Add up the "Billable hours" column for the rows where "Practice" is "Cloud".
- Above average? (Hard): "Above" if the "Rate per hour ($)" value in the first row is above the average of the "Rate per hour ($)" column, else "Below".
Worked example — module 7 pivottables
Why
Aarti Kulkarni in Pune and Anna Kowalski abroad both need a clear, repeatable way to use module 7 pivottables on a FreshGo Mart sheet without breaking totals.
What
Module 7 Pivottables is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for module 7 pivottables.
- Enter a small made-up value for Aarti Kulkarni using £ where money appears.
- Add a second value for Anna Kowalski using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once module 7 pivottables is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
