7. PivotTables and PivotCharts
7.7 Sorting, Filtering and Top 10
Steps in Excel
- Sort by value: right-click any Sum of Amount cell › Sort › Sort Largest to Smallest.
- Manual order: drag an item label up/down, or type an item name over another label.
- Label filter: ▾ next to Row Labels › Label Filters › Begins With…
Na. - Top 10: ▾ › Value Filters › Top 10… ›
Top5ItemsbySum of Amount› OK. - Report filter: drag Status to Filters › choose Delivered. PivotTable Analyze › PivotTable › Options ▾ › Show Report Filter Pages… creates one sheet per filter item (e.g. one per city).
- Clear: PivotTable Analyze › Actions › Clear › Clear Filters.
Worked example. "Top 5 products by sales" (a question reported in interviews – see Module 18): Rows = Product, Values = Sum of Amount, Value Filter Top 5, sorted largest to smallest. Nashik grapes and Nagpur oranges usually lead in season.
Ravindra Bagale's Tip
After applying a Top 10 filter, is the Grand Total for only the 5 shown, or for everything? By default it is for the filtered items – many students report this total as "all sales". Label the grand total clearly or show a separate total. And for Top N, sort as well, otherwise the list appears in alphabetical order.
Ravindra Bagale's Tip – मराठी
Top 10 filter लावल्यावर Grand Total फक्त दाखवलेल्या 5 चा असतो की सगळ्यांचा? Default मध्ये तो filtered items चा असतो – बरेच students हा total "सगळा sales" म्हणून report करतात. Grand total चं label स्पष्ट लिहा किंवा वेगळा total दाखवा. आणि Top N साठी आधी sort पण करा, नाहीतर list alphabetical दिसते.
Ravindra Bagale's Tip – हिंदी
Top 10 filter लगाने के बाद Grand Total सिर्फ़ दिखाए गए 5 का होता है या सबका? Default में वह filtered items का होता है – बहुत से students इस total को "सारी sales" बताकर report कर देते हैं. Grand total का label साफ़ लिखो या अलग total दिखाओ. और Top N के लिए sort भी करो, वरना list alphabetical दिखती है.
Practice task
Show the top 3 areas by delivered sales for each platform, sorted largest first. Use Show Report Filter Pages to create one sheet per city.