Why reference types matter in Excel - Dashboard Impact

Dashboard Mode - Why reference types matter

Dashboard Goal

Understand how relative and absolute cell references affect formulas when copying them across cells.

Sample Data

Item	Price	Quantity	Tax Rate
Pen	1.50	10	0.07
Notebook	3.00	5	0.07
Eraser	0.50	20	0.07
Marker	2.00	7	0.07
Ruler	1.00	15	0.07

Dashboard Components

• Total Price (Relative Reference): Calculates price times quantity using formula =B2*C2 copied down. Shows how relative references change per row.
• Total Price with Tax (Absolute Reference): Calculates total price plus tax using formula =B2*C2*(1+D2) but tax rate is fixed with absolute reference $D$2 as =B2*C2*(1+$D$2). Copying this formula down keeps tax rate fixed.
• Tax Rate Cell: Shows tax rate in cell D2 used in formulas.
• Summary Table: Shows total price and total price with tax for each item.

Dashboard Layout

+----------------------+--------------------------+
|      Sample Data      |    Summary Table          |
|  (Items, Price, Qty)  |  (Total Price, Price+Tax) |
+----------------------+--------------------------+
|                      |                          |
|                      |                          |
+----------------------+--------------------------+

Interactivity

Changing the tax rate in cell D2 updates all total price with tax calculations automatically because of the absolute reference $D$2 in formulas.

Copying formulas down shows how relative references adjust for each row, while absolute references stay fixed.

Self Check

If you change the tax rate in cell D2 from 0.07 to 0.10, which components update?

• The Total Price with Tax column updates to reflect the new tax rate.
• The Total Price column does not change because it does not use the tax rate.

Key Result

Shows how relative and absolute references affect formulas when copied, using price, quantity, and tax rate data.