Grouping and outlining in Excel - Dashboard Guide

Dashboard Mode - Grouping and outlining

Dashboard Goal

See sales data grouped by region and category, and easily expand or collapse details to focus on summary or detailed sales.

Sample Data

Region	Category	Product	Sales
East	Furniture	Chair	120
East	Furniture	Table	250
East	Office Supplies	Pen	75
West	Furniture	Sofa	300
West	Office Supplies	Paper	150
West	Office Supplies	Stapler	90

Dashboard Components

• Summary Table: Shows total sales by Region and Category.
  Formula example for total sales per group:
  =SUMIFS(D2:D7, A2:A7, "East", B2:B7, "Furniture") sums sales where Region is East and Category is Furniture.
• Detail Table: Shows individual product sales rows. Grouped by Region and Category using Excel's Group feature to collapse/expand rows.
• KPI Card: Total Sales overall.
  Formula:
  =SUM(D2:D7) gives total sales of all products.

Dashboard Layout

+----------------------+----------------------+
|      KPI Card        |   Summary Table      |
|  (Total Sales)       | (Sales by Region &   |
|                      |    Category)         |
+----------------------+----------------------+
|                  Detail Table (Grouped Rows)                |
+-------------------------------------------------------------+

Interactivity

Use Excel's Group and Outline feature to collapse or expand the Detail Table rows by Region and Category. This lets you hide or show product-level details easily.

Click the plus (+) or minus (-) buttons on the left margin to expand or collapse groups.

Self Check

Try collapsing the "East" region group. Which components update?

• The Detail Table hides East region products.
• The Summary Table and KPI Card remain unchanged because they show totals.

Key Result

Dashboard showing total and detailed sales grouped by Region and Category with expandable groups.