7. PivotTables and PivotCharts
7.1 Creating a PivotTable
Steps in Excel
- Make sure the source is a clean Table (
tblOrders): one header row, no blank rows, no merged cells, one type of data per column. - Click inside it › Insert › Tables › PivotTable (in Microsoft 365 the button may show From Table/Range – may vary by version).
- Table/Range:
tblOrders› choose New Worksheet › (optional) tick Add this data to the Data Model if you need Distinct Count or multiple tables › OK. - The PivotTable Fields pane opens on the right. Tick or drag fields into the areas.
- Two new ribbon tabs appear when a cell of the PivotTable is selected: PivotTable Analyze and Design.
- Recommended PivotTables (Insert › Tables) suggests ready layouts – useful for beginners.
Worked example – mini dataset of Module 3. Drag City to Rows and Amount to Values:
| Row Labels | Sum of Amount |
|---|---|
| Kolhapur | 232 |
| Nagpur | 240 |
| Nashik | 360 |
| Pune | 544 |
| Sambhaji Nagar | 195 |
| Solapur | 1,299 |
| Grand Total | 2,870 |
Ravindra Bagale's Tip
Many students select a range (A1:I500) for a PivotTable – then, when new rows are added, they don't show up even after a refresh. Always make a Table (Ctrl + T) first and build the PivotTable on that Table. If the source has an empty header or merged cells, the PivotTable won't be created – clean the data first.
Ravindra Bagale's Tip – मराठी
बरेच students PivotTable साठी range (A1:I500) select करतात – मग नवीन rows add केल्यावर refresh करूनही त्या येत नाहीत. नेहमी आधी Table (Ctrl + T) बनवा आणि PivotTable त्या Table वर बनवा. Source मध्ये रिकामा header किंवा merged cells असतील तर PivotTable बनतच नाही – आधी data clean करा.
Ravindra Bagale's Tip – हिंदी
बहुत से students PivotTable के लिए range (A1:I500) select करते हैं – फिर नई rows जोड़ने पर refresh करने के बाद भी वे नहीं आतीं. हमेशा पहले Table (Ctrl + T) बनाओ और PivotTable उसी Table पर बनाओ. Source में खाली header या merged cells हों तो PivotTable बनती ही नहीं – पहले data clean करो.
Practice task
Create a PivotTable of sales by City from tblOrders on a new sheet named Pivot_City. Add five new orders to the Table and refresh to see them included.