Ravindra BagaleCourses & study guides मराठी Track your progress

7. PivotTables and PivotCharts

7.7 Sorting, Filtering and Top 10

By Last updated:

Steps in Excel

  1. Sort by value: right-click any Sum of Amount cell › Sort › Sort Largest to Smallest.
  2. Manual order: drag an item label up/down, or type an item name over another label.
  3. Label filter: ▾ next to Row Labels › Label Filters › Begins With… Na.
  4. Top 10: ▾ › Value Filters › Top 10… › Top 5 Items by Sum of Amount › OK.
  5. 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).
  6. 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.

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.

Loading practice tasks...

  1. Distinct groups (Easy): How many different values in the "Platform" column?
  2. List groups (Easy): Unique list of the "Platform" column.
  3. Filter names (Easy): Names in the "Name" column where "Platform" is "Google".
  4. Sum of filtered (Medium): Sum of the "Clicks" column where "Platform" is "Meta", using FILTER.
  5. Count filtered (Medium): Rows where "Clicks" is above the average of the "Clicks" column.
  6. Sorted values (Medium): the "Clicks" column sorted largest first.
  7. Top value via SORT (Medium): The largest the "Spend (€)" column value using SORT and INDEX.
  8. Second largest (Hard): Second largest the "Clicks" column using SORT and INDEX.
  9. Numbers 1 to 5 (Hard): A spill of numbers 1 to 5.
  10. Two-condition filter (Hard): Sum of the "Clicks" column where "Platform" is "Google" or "Meta".
  11. Names joined (Hard): Names where "Platform" is "Google", joined with ", ".

See all practice tasks in the Task Hub →

Worked example — sorting filtering and top 10

Why

Rohan Patil in Pune and Sophie Martin abroad both need a clear, repeatable way to use sorting filtering and top 10 on a Pearl Textiles sheet without breaking totals.

What

Sorting Filtering And Top 10 is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for sorting filtering and top 10.
  2. Enter a small made-up value for Rohan Patil using ₹ where money appears.
  3. Add a second value for Sophie Martin using € so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. 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).
Result: The demo cell shows the expected result for Pearl Textiles's sample row.
Meaning: Once sorting filtering and top 10 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.

Revision card 1: What is Sorting Filtering And Top 10?
Card 1: What is Sorting Filtering And Top 10? · Download PNG
Revision card 2: Tiny example — Sorting Filtering And Top 10
Card 2: Tiny example — Sorting Filtering And Top 10 · Download PNG
Revision card 3: Checklist — Sorting Filtering And Top 10
Card 3: Checklist — Sorting Filtering And Top 10 · Download PNG