Sorting data (single and multi-level) in Excel - Dashboard Guide

Dashboard Mode - Sorting data (single and multi-level)

Dashboard Goal

Help a small store manager quickly find top-selling products and organize sales data by category and sales amount.

Sample Data

Product	Category	Sales
Apples	Fruit	150
Bananas	Fruit	120
Carrots	Vegetable	90
Broccoli	Vegetable	110
Oranges	Fruit	200
Potatoes	Vegetable	130
Tomatoes	Vegetable	140

Dashboard Components

• Sorted by Sales (Single-level sort): Table sorted by Sales descending.
  Formula: Use Excel's Data > Sort feature or formula =SORT(A2:C8,3,-1)
  Result: Oranges (200) at top, Carrots (90) at bottom.
• Sorted by Category then Sales (Multi-level sort): Table sorted first by Category ascending, then by Sales descending.
  Formula: =SORT(A2:C8,{2,3},{1,-1})
  Result: Fruit category products listed first sorted by sales high to low, then Vegetable category sorted similarly.
• Top Selling Product: Shows product with highest sales.
  Formula: =INDEX(A2:A8,MATCH(MAX(C2:C8),C2:C8,0))
  Result: "Oranges"

Dashboard Layout

+---------------------------+---------------------------+
| Sorted by Sales (Single)  | Sorted by Category + Sales |
| (Table)                  | (Table)                   |
|                           |                           |
+---------------------------+---------------------------+
| Top Selling Product:       |                           |
| "Oranges"                |                           |
+---------------------------+---------------------------+

Interactivity

The manager can apply filters on Category to see only Fruit or Vegetable products. When filtered, both sorted tables update to show only filtered products. The Top Selling Product updates to show the highest sales product in the filtered list.

Self Check

If you add a filter to show only "Fruit" category, which components update and what changes?

• Both sorted tables show only Fruit products.
• Top Selling Product updates to the Fruit product with highest sales ("Oranges").

Key Result

Dashboard shows product sales sorted by sales and by category+sales, highlighting the top seller.