7. PivotTables and PivotCharts
7.2 Field Areas: Rows, Columns, Values and Filters
| Area | Holds | Example |
|---|---|---|
| Rows | Categories listed down | City, Area |
| Columns | Categories across | Platform |
| Values | Numbers to summarise | Sum of Amount, Count of Order ID |
| Filters | A filter for the whole pivot | Status = Delivered |
Worked example – City × Platform (mini dataset). Rows: City; Columns: Platform; Values: Amount.
| City | Amazon Now | Blinkit | Grand Total |
|---|---|---|---|
| Kolhapur | 232 | 232 | |
| Nagpur | 240 | 240 | |
| Nashik | 360 | 360 | |
| Pune | 110 | 434 | 544 |
| Sambhaji Nagar | 195 | 195 | |
| Solapur | 1,299 | 1,299 | |
| Grand Total | 537 | 2,333 | 2,870 |
Steps in Excel – layout options
- Design › Layout › Report Layout › Show in Tabular Form and Repeat All Item Labels – looks like a normal table (good for copying).
- Design › Layout › Grand Totals / Subtotals – on/off.
- Design › Layout › Blank Rows and PivotTable Styles for appearance.
- Right-click a value › Number Format… › apply the Indian rupee format (1.5) – it stays after refresh.
- Show empty cells as 0: PivotTable Analyze › PivotTable › Options › Layout & Format › For empty cells show:
0.
Ravindra Bagale's Tip
When formatting numbers in Values, many students select the cells and use Home › Number – and the format is lost on refresh. Use right-click on a value › Number Format or Value Field Settings › Number Format; then the format stays. Tabular Form + Repeat Labels make the pivot much easier to read.
Ravindra Bagale's Tip – मराठी
Values मध्ये number format लावताना बरेच students cells select करून Home › Number वापरतात – refresh केल्यावर format जातो. Value वर right-click › Number Format किंवा Value Field Settings › Number Format वापरा, मग format कायम राहतो. Tabular Form + Repeat Labels ने pivot वाचायला खूप सोपा होतो.
Ravindra Bagale's Tip – हिंदी
Values में number format लगाते समय बहुत से students cells select करके Home › Number इस्तेमाल करते हैं – refresh करते ही format चला जाता है. Value पर right-click › Number Format या Value Field Settings › Number Format इस्तेमाल करो, फिर format हमेशा रहता है. Tabular Form + Repeat Labels से pivot पढ़ना बहुत आसान हो जाता है.
Practice task
Build Area (rows) × Platform (columns) with Sum of Amount, Tabular Form, repeated labels, empty cells shown as 0 and Indian rupee format.