Custom sorting rules in Excel - Dashboard Guide

Dashboard Mode - Custom sorting rules

Dashboard Goal

Sort a list of products by a custom priority order instead of alphabetical or numeric order.

Sample Data

Product	Category	Price
Banana	Fruit	0.5
Carrot	Vegetable	0.3
Apple	Fruit	0.7
Broccoli	Vegetable	1.2
Orange	Fruit	0.6
Potato	Vegetable	0.4
Strawberry	Fruit	1.5

Dashboard Components

• Custom Sort Order Table: A small table listing categories in the desired order.
  A10:A11 contains: Fruit, Vegetable
• Helper Column for Sort: In D2, formula:
  =MATCH(B2,$A$10:$A$11,0)
  This finds the category's position in the custom order list.
  Drag down to D8.
• Sorted Product List: Use SORTBY formula to sort by custom order and then by price ascending.
  In F2, formula:
  =SORTBY(A2:C8, D2:D8, 1, C2:C8, 1)
  This sorts products first by category order (Fruit first, then Vegetable), then by price low to high.

Dashboard Layout

+----------------------+-----------------------+
| Custom Sort Order     | Sorted Product List   |
| (A10:A11)            | (F2:H8)               |
+----------------------+-----------------------+
| Sample Data          | Helper Column (hidden) |
| (A1:C8)              | (D2:D8)                |
+----------------------+-----------------------+

Interactivity

Change the order of categories in the custom sort order table (cells A10:A11). The helper column updates automatically. The sorted product list updates to reflect the new category priority.

Self Check

If you change the custom sort order to have Vegetable first and Fruit second in A10:A11, which product category appears first in the sorted product list?

Key Result

Sort products by a custom category order and then by price ascending.