7. PivotTables and PivotCharts
7.4 Show Values As
Show Values As % of Grand Total with example — in 30 seconds
What: Show Values As changes how a pivot value is displayed, for example as a percentage of the grand total.
Why: "Solapur ₹1,299" means little alone. "Solapur is 45% of sales" tells the story at once.
Tiny example: Solapur ₹1,299 out of ₹2,870 = 45.26%.
Show Values As % of Grand Total with example — 30 सेकंदात
काय: Show Values As pivot मधली value कशी दिसते ते बदलतं, उदा. grand total च्या टक्केवारीत.
का: "Solapur ₹1,299" एकटं फार काही सांगत नाही. "Solapur म्हणजे sales च्या 45%" लगेच गोष्ट सांगतं.
छोटं उदाहरण: ₹2,870 पैकी Solapur ₹1,299 = 45.26%.
Show Values As % of Grand Total with example — 30 सेकंड में
क्या: Show Values As pivot की value कैसे दिखे यह बदलता है, जैसे grand total के प्रतिशत में.
क्यों: "Solapur ₹1,299" अकेले ज़्यादा कुछ नहीं बताता. "Solapur sales का 45% है" तुरंत कहानी बताता है.
छोटा उदाहरण: ₹2,870 में से Solapur ₹1,299 = 45.26%.
First look at the data (before → after):
Expected result — Same pivot as % of Grand Total
| Row Labels | Sum of Amount |
|---|---|
| Kolhapur | 8.08% |
| Nagpur | 8.36% |
| Nashik | 12.54% |
| Pune | 18.95% |
| Sambhaji Nagar | 6.79% |
| Solapur | 45.26% |
| Grand Total | 100.00% |
About this data: Teaching sample for a fictional shop “Summit Schools”. Figures and names (e.g. Meera Joshi) are made up for this lesson on show values as.
Before table and formula as text
Before — Pivot showing Sum of Amount
| 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: Demo table only — not real customer or company data. Built to practise show values as.
Formula / click path
Right-click a value › Show Values As › % of Grand Total
Do it step by step:
- Build the pivot: Rows = City, Values = Sum of Amount.
- Drag Amount into Values a second time, so you keep the ₹ column too.
- Right-click any number in the second column.
- Choose Show Values As › % of Grand Total.
- Rename the header to "Share of sales".
स्टेप बाय स्टेप करून बघा:
- Pivot बनवा: Rows = City, Values = Sum of Amount.
- Amount परत एकदा Values मध्ये drag करा, म्हणजे ₹ वाला column पण राहील.
- दुसऱ्या column मधल्या कोणत्याही number वर right-click करा.
- Show Values As › % of Grand Total निवडा.
- Header चं नाव "Share of sales" करा.
स्टेप बाय स्टेप करके देखें:
- Pivot बनाएँ: Rows = City, Values = Sum of Amount.
- Amount को दोबारा Values में drag करें, ताकि ₹ वाला column भी रहे.
- दूसरे column के किसी भी number पर right-click करें.
- Show Values As › % of Grand Total चुनें.
- Header का नाम "Share of sales" करें.
Right-click a value › Show Values As › choose a calculation. The underlying Sum stays; only the display changes.
| Option | Answers the question | Mini-data result |
|---|---|---|
| % of Grand Total | Share of each city in total sales | Solapur 45.3%, Pune 19.0%, Nashik 12.5% |
| % of Column Total | Share within each platform | Pune = 18.6% of FreshGo |
| % of Row Total | Platform mix within each city | Pune: MarketRush 20.2%, FreshGo 79.8% |
| Difference From (Base: previous item) | Change vs previous month/day | Nov vs Oct |
| % Difference From | Growth % vs previous | MoM growth |
| Running Total In (Base field: Date) | Cumulative sales | 02-11: 334 … 08-11: 2,870 |
| Rank Largest to Smallest | Rank of each city | Solapur 1, Pune 2 |
| % of Parent Row Total | Area's share within its city | Kothrud's share of Pune |
About this data: Made-up rows so you can copy the pattern for show values as. Currencies may use ₹, £, € or $.
Worked example – running total by date (Rows: Order Date by day; Values: Amount twice, second one as Running Total In › Order Date):
| Date | Sum of Amount | Running Total |
|---|---|---|
| 02-11-2026 | 334 | 334 |
| 03-11-2026 | 350 | 684 |
| 04-11-2026 | 382 | 1,066 |
| 05-11-2026 | 1,299 | 2,365 |
| 06-11-2026 | 195 | 2,560 |
| 07-11-2026 | 220 | 2,780 |
| 08-11-2026 | 90 | 2,870 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
Ravindra Bagale's Tip
For share %, many students write a formula like =B5/B$12 next to the pivot – when the layout changes, the formula goes wrong. Use Show Values As; it changes along with the pivot. And drag the same field into Values twice: one Sum, one % – then both show; rename them (for example "Sales" and "Share %") to make it clear.
Ravindra Bagale's Tip – मराठी
Share % साठी बरेच students pivot च्या बाजूला =B5/B$12 असा formula लिहितात – layout बदलला की formula चुकतो. Show Values As वापरा, ते pivot सोबत बदलतं. आणि same field दोनदा Values मध्ये ओढा: एक Sum, एक % – मग दोन्ही दिसतात; rename करून (उदाहरणार्थ "Sales" आणि "Share %") स्पष्ट करा.
Ravindra Bagale's Tip – हिंदी
Share % के लिए बहुत से students pivot के बगल में =B5/B$12 जैसा formula लिखते हैं – layout बदलते ही formula गलत हो जाता है. Show Values As इस्तेमाल करो, वह pivot के साथ बदलता है. और same field को दो बार Values में खींचो: एक Sum, एक % – फिर दोनों दिखेंगे; rename करके (जैसे "Sales" और "Share %") साफ़ कर दो.
Practice task
Show each city's % of Grand Total, the platform mix (% of Row Total) and a running total by date. Rename the value headers to meaningful names.
Loading practice tasks...
- Total amount (Easy): Add up Amount (₹) in E2:E11. (sheet "Data")
- Average units (Easy): Average of Units in D2:D11, rounded to 1 decimal. (sheet "Data")
- West rows (Easy): How many rows have Region "West"? (sheet "Data")
- Max amount (Easy): Largest Amount (₹) in E2:E11. (sheet "Data")
- Snacks count (Easy): How many rows have Category "Snacks"? (sheet "Data")
- West amount (Medium): Total of Amount (₹) where Region is "West". (sheet "Data")
- Drinks units (Medium): Total of Units where Category is "Drinks". (sheet "Data")
- North snacks amount (Medium): Amount where Region is "North" and Category is "Snacks". (sheet "Data")
- West share (Medium): West amount / all amount, 3 decimals (like % of Grand Total). (sheet "Data")
- Snacks share (Hard): Snacks amount / all amount, 3 decimals. (sheet "Data")
- Above avg amount? (Hard): "High" if E2 > average amount else "Low". (sheet "Data")
- High unit rows (Hard): How many rows have Units > 100? (sheet "Data")
- Amount of Asha West snacks (Hard): Amount for Rep "Asha". (sheet "Data")
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
