Named ranges in Excel - Dashboard Guide

Dashboard Mode - Named ranges

Dashboard Goal

Help a small store manager quickly see total sales and average sales per product using named ranges for easy formula management.

Sample Data

Product	Sales
Apples	120
Bananas	80
Cherries	150
Dates	90
Elderberries	60

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUBTOTAL(109,Sales)
  Explanation: Adds all visible sales values using the named range Sales. Result: 500
• KPI Card: Average Sales per Product
  Formula: =SUBTOTAL(101,Sales)
  Explanation: Calculates average of visible sales using the named range Sales. Result: 100
• Table: Product Sales
  Shows product names and sales using named ranges Products and Sales.

Dashboard Layout

+----------------------+----------------------+
| Total Sales (500)    | Average Sales (110)  |
+----------------------+----------------------+
| Product     | Sales                  |
|------------------------------|
| Apples      | 120                    |
| Bananas     | 80                     |
| Cherries    | 150                    |
| Dates       | 90                     |
| Elderberries| 60                     |
+------------------------------+

Interactivity

Currently, this dashboard uses named ranges for formulas but has no filters. You can add filters later to select specific products or sales ranges, and the KPI cards and table will update automatically because formulas use named ranges.

Self Check

Add a filter to show only products with sales above 100. Which components update?

• The Total Sales and Average Sales KPI cards will update to reflect only the filtered sales.
• The Product Sales table will show only products with sales above 100.

Key Result

Dashboard shows total and average sales using named ranges for easy formula updates.