Absolute references ($A$1) in Excel - Dashboard Guide

Dashboard Mode - Absolute references ($A$1)

Goal

Find total sales for each product using a fixed tax rate cell with absolute reference.

Sample Data

Product	Price	Quantity	Tax Rate
Apple	2	10	0.10
Banana	1	20
Cherry	3	15
Date	4	5

Dashboard Components

• Total Sales (without tax): =B2*C2 copied down for each product (e.g., for Apple: 2*10=20)
• Tax Amount: =B2*C2*$D$2 copied down. The $D$2 is an absolute reference to the tax rate cell, so it stays fixed when copied.
• Total Sales (with tax): =B2*C2 + B2*C2*$D$2 or =B2*C2*(1+$D$2) copied down.
• Grand Total Sales (with tax): =SUM(E2:E5) sums all total sales with tax.

Dashboard Layout

+---------+-------+-----+---------------------+------------+-----------------------+
| Product | Price | Qty | Total Sales (no tax) | Tax Amount | Total Sales (with tax) |
+---------+-------+-----+---------------------+------------+-----------------------+
| Apple   | 2     | 10  | 20                  | 2          | 22                    |
| Banana  | 1     | 20  | 20                  | 2          | 22                    |
| Cherry  | 3     | 15  | 45                  | 4.5        | 49.5                  |
| Date    | 4     | 5   | 20                  | 2          | 22                    |
+---------+-------+-----+---------------------+------------+-----------------------+
|                      Grand Total Sales (with tax): 115.5                      |
+---------------------------------------------------------------------------------+
| Tax Rate (fixed cell): 0.10                                                      |
+---------------------------------------------------------------------------------+

Interactivity

Changing the tax rate in cell D2 updates all tax amount and total sales with tax values automatically because of the absolute reference $D$2. This shows how absolute references keep a formula linked to a fixed cell when copied.

Self Check

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

• Tax Amount column values update to reflect 15% tax.
• Total Sales (with tax) column updates accordingly.
• Grand Total Sales (with tax) updates to new sum.

Key Result

Dashboard shows product sales calculations using absolute reference for a fixed tax rate.