MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Sorting, Filtering and Summarizing Pivot Data0%
Sorting, Filtering and Summarizing Pivot Data
Beginner12 min read•Updated: Jul 11, 2026
Sorting, Filtering and Summarizing Pivot Data
Once you have constructed a Pivot Table, the next step is transforming it into an executive-ready report. Excel provides powerful tools to sort data dynamically, filter using Label and Value filters, and customize numeric calculations to uncover high-impact insights.
1. Sorting Pivot Table Data
By default, Pivot Tables sort row labels alphabetically (A to Z). In financial and operational reports, you almost always want to sort by magnitude (e.g., highest revenue to lowest):
- Quick Right-Click Sort: Right-click any cell in the numeric Values column, choose Sort, and click Sort Largest to Smallest. The entire list of items re-orders according to their numeric performance.
- More Sort Options Dialog: Click the filter drop-down arrow on the Row Labels header, choose More Sort Options..., and select:
- Ascending (A to Z) by [Field Name]
- Descending (Z to A) by [Sum of Revenue]
Click to Zoom2. Advanced Pivot Filtering
Beyond basic checkbox filters, Pivot Tables offer two advanced filtering engines:
A. Label Filters (Filtering by Text/Names)
Click the Row Label dropdown > Label Filters:
- Equals / Does Not Equal: Filter specific categories.
- Begins With / Ends With: Locate SKUs or departments matching specific prefixes.
- Contains: Search for words like "Special" or "Premium" in product names.
B. Value Filters (Filtering by Aggregated Numbers)
Click the Row Label dropdown > Value Filters:
- Greater Than / Less Than: Show only sales reps whose total revenue exceeds $100,000.
- Top 10...: Keep only the Top 5 or Top 10 performers. You can also filter by percentage (e.g., top 20% of contributors) or sum.
3. Customizing Subtotals and Grand Totals
Under the contextual Design tab on the Ribbon (available when clicking inside the Pivot Table):
- Subtotals:
- Do Not Show Subtotals: Eliminates clutter in flat reports.
- Show all Subtotals at Bottom / Top of Group: Positions category subtotals.
- Grand Totals:
- Off for Rows and Columns: Removes totals when displaying percentages or ratios.
- On for Rows and Columns: Full enterprise summary.
- Report Layout:
- Compact Form (Default): Minimizes column width.
- Outline Form: Places nested fields in separate adjacent columns.
- Tabular Form: Traditional grid layout, ideal for copying into other models or databases (with Repeat All Item Labels option).
Interactive Quiz
Multiple Choice Questions
Question 1
How can you instantly sort a Pivot Table so the salesperson with the highest total revenue appears at the top?
Question 2
Which filter type in a Pivot Table allows you to display only departments whose total expenditure is greater than $500,000?
Question 3
Which Report Layout format places each nested row field in its own separate column, making it ideal for copying into other databases?
Question 4
Where on the Ribbon do you customize Subtotals, Grand Totals, and Report Layout for an active Pivot Table?
Question 5
What does the 'Top 10...' Value Filter allow you to do?
Next Lesson
Clustered Column and Bar Charts
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
Related Lessons
| Previous Lesson | Next Lesson |
|---|---|
| Pivot Table Fields (Rows, Columns, Values, Filters) | Clustered Column and Bar Charts |
Practice Quiz
Test your understanding of this lesson with 5 questions. Each question has one correct answer.