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
Khup students PivotTable sathi range (A1:I500) select kartat – mag navin rows add kelyavar refresh karun pan te yet nahit. Nehmi aadhi Table (Ctrl + T) banva aani PivotTable tya Table var banva. Source madhe rikami header kiwa merged cells asle tar PivotTable banatach nahi – aadhi data clean kara.
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.