Relative references (A1) in Excel - Dashboard Guide

Dashboard Mode - Relative references (A1)

Dashboard Goal

See how sales totals change when copying formulas using relative references.

Sample Data

Product	Price	Quantity Sold
Apples	2	10
Bananas	1.5	20
Cherries	3	15
Dates	4	5
Elderberries	5	8

Dashboard Components

• KPI Card: Total Revenue
  Formula: =SUMPRODUCT(B2:B6,C2:C6) entered in cell E2
  Shows total money earned from all products.
• Table: Revenue per Product
  Formula in D2: =B2*C2 copied down to D6
  Shows revenue for each product using relative references.
• Chart: Bar chart of Revenue per Product
  Uses data from Product (A2:A6) and Revenue (D2:D6).

Dashboard Layout

+----------------------+---------------------+
|      Total Revenue    |                     |
|        (E2)          |   Revenue per Product|
|                      |       Table         |
+----------------------+---------------------+
|                      Bar Chart              |
|               (Products vs Revenue)         |
+---------------------------------------------+

Interactivity

When you change Quantity Sold or Price, the Revenue per Product and Total Revenue update automatically because the formulas use relative references. Copying the formula in D2 down to other rows adjusts the cell references for each product.

Self Check

If you change the Quantity Sold for Bananas from 20 to 25, which components update and what happens to Total Revenue?

Key Result

Dashboard shows product revenues calculated with relative references and total revenue summary.