7. PivotTables and PivotCharts
7.1 Creating a PivotTable
Quick answer: the short, self-contained version of this lesson is the guide How to Create a PivotTable in Excel from Scratch.
Create a PivotTable with example — in 30 seconds
What: A PivotTable groups and totals your rows by dragging fields. No formulas needed.
Why: Sales by city for ten rows is easy by hand. For 50,000 rows it is a pivot or nothing.
Tiny example: Rows = City, Values = Sum of Amount. Six city totals appear, Grand Total ₹2,870.
Create a PivotTable with example — 30 सेकंदात
काय: PivotTable fields drag करून rows group करतो आणि totals काढतो. Formulas लागत नाहीत.
का: दहा rows साठी city-wise sales हाताने सोपे आहेत. 50,000 rows साठी pivot शिवाय पर्याय नाही.
छोटं उदाहरण: Rows = City, Values = Sum of Amount. सहा शहरांचे totals येतात, Grand Total ₹2,870.
Create a PivotTable with example — 30 सेकंड में
क्या: PivotTable fields drag करके rows को group करता है और totals निकालता है. Formulas नहीं चाहिए.
क्यों: दस rows के लिए city-wise sales हाथ से आसान है. 50,000 rows के लिए pivot के सिवा कोई रास्ता नहीं.
छोटा उदाहरण: Rows = City, Values = Sum of Amount. छह शहरों के totals आते हैं, Grand Total ₹2,870.
First look at the data (before → after):
Expected result — PivotTable result
| Row Labels | Sum of Amount |
|---|---|
| Kolhapur | 232 |
| Nagpur | 240 |
| Nashik | 360 |
| Pune | 544 |
| Sambhaji Nagar | 195 |
| Solapur | 1,299 |
| Grand Total | 2,870 |
About this data: Teaching sample for a fictional shop “Maple Bakery Co”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on creating a pivottable.
Before table and formula as text
Before — tblMini (first 5 of 10 rows shown)
| Order ID | City | Amount |
|---|---|---|
| BLK-1001 | Pune | 64 |
| BLK-1002 | Nashik | 270 |
| AMN-1003 | Pune | 110 |
| BLK-1004 | Nagpur | 240 |
| AMN-1005 | Kolhapur | 232 |
About this data: Demo table only — not real customer or company data. Built to practise creating a pivottable.
Formula / click path
Insert › PivotTable › From Table/Range › New Worksheet
Rows: City · Values: Sum of Amount
Do it step by step:
- Click any cell in the data.
- Click Insert › PivotTable › From Table/Range. Choose New Worksheet and click OK.
- In the field list, tick City. It goes to Rows.
- Tick Amount. It goes to Values as Sum of Amount.
- Check the Grand Total: 2,870.
स्टेप बाय स्टेप करून बघा:
- Data मधल्या कोणत्याही cell वर click करा.
- Insert › PivotTable › From Table/Range वर click करा. New Worksheet निवडा आणि OK करा.
- Field list मध्ये City tick करा. ते Rows मध्ये जातं.
- Amount tick करा. ते Values मध्ये Sum of Amount म्हणून जातं.
- Grand Total check करा: 2,870.
स्टेप बाय स्टेप करके देखें:
- Data की किसी भी cell पर click करें.
- Insert › PivotTable › From Table/Range पर click करें. New Worksheet चुनें और OK करें.
- Field list में City tick करें. यह Rows में जाता है.
- Amount tick करें. यह Values में Sum of Amount बनकर जाता है.
- Grand Total check करें: 2,870.
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 |
About this data: Made-up rows so you can copy the pattern for creating a pivottable. Currencies may use ₹, £, € or $.
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.
Loading practice tasks...
- Total units sold (Easy): Add up Units sold in the "Units sold" column.
- Average price (Easy): Average of the Price (₹) column, rounded to 2 decimals.
- Rows in Snacks (Easy): How many rows have Category "Snacks"?
- Largest units sold (Easy): The largest value in the "Units sold" column.
- Units sold for Snacks (Medium): Total of the Units sold column where Category is "Snacks".
- Snacks in Bengaluru (Medium): Total of the Units sold column where Category is "Snacks" and City is "Bengaluru".
- Best in Snacks (Medium): Largest value in the "Price (₹)" column where "Category" is "Snacks".
- Value of row 2 (Medium): Multiply the first-row Units sold by the first-row Price (₹).
- Look up Ananya Nair (Medium): The Units sold for "Ananya Nair" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Units sold" times "Price (₹)" for all rows.
- Share of Snacks (Hard): Share of the "Units sold" column that is "Snacks", rounded to 3 decimals.
- Total "Units sold" for Snacks (Hard): Add up the "Units sold" column for the rows where "Category" is "Snacks".
- Above average? (Hard): "Above" if the "Price (₹)" value in the first row is above the average of the "Price (₹)" column, else "Below".
Worked example — creating a pivottable
Why
Vikram Rao in Pune and Oliver Bennett abroad both need a clear, repeatable way to use creating a pivottable on a CityBasket Retail sheet without breaking totals.
What
Creating A Pivottable 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 creating a pivottable.
- Enter a small made-up value for Vikram Rao using € where money appears.
- Add a second value for Oliver Bennett 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 creating a pivottable 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.
