Absolute references ($) in Google Sheets - Dashboard Guide

Dashboard Mode - Absolute references ($)

Goal

Calculate total sales for multiple products with a fixed tax rate using absolute references to keep the tax rate constant in formulas.

Sample Data

Product	Price	Quantity Sold	Tax Rate
Apples	2	30	0.07
Bananas	1.5	50	0.07
Cherries	3	20	0.07
Dates	4	15	0.07
Elderberries	5	10	0.07

Dashboard Components

• Total Sales Before Tax: Sum of Price * Quantity Sold
  Formula: =SUMPRODUCT(B2:B6,C2:C6)
  Result: 2*30+1.5*50+3*20+4*15+5*10 = 60+75+60+60+50 = 305
• Tax Rate: Fixed tax rate in cell D2 (0.07)
  Note: This cell is used with absolute reference in formulas.
• Total Sales After Tax per Product: Calculate total price per product including tax.
  Formula in E2: =B2*C2*(1+$D$2)
  Copy down to E6.
  Example for Apples: 2*30*(1+0.07) = 60*1.07 = 64.2
• Total Sales After Tax: Sum of all sales including tax.
  Formula: =SUM(E2:E6)
  Result: 64.2 + 80.25 + 64.2 + 64.2 + 53.5 = 326.35

Dashboard Layout

+----------------------+-----------------------+
| Total Sales Before Tax| Total Sales After Tax  |
|        305           |        326.35          |
+----------------------+-----------------------+
| Tax Rate (fixed) = 7%                         |
+----------------------------------------------+
| Product | Price | Quantity | Total w/ Tax     |
| Apples  |  2    |   30     | 64.2            |
| Bananas | 1.5   |   50     | 80.25           |
| Cherries|  3    |   20     | 64.2            |
| Dates   |  4    |   15     | 64.2            |
| Elderb. |  5    |   10     | 53.5            |
+----------------------------------------------+

Interactivity

If you change the tax rate in cell D2, all formulas using $D$2 update automatically. This keeps the tax rate fixed in calculations even when formulas are copied down rows.

Changing quantities or prices updates total sales and total sales after tax instantly.

Self Check

Try changing the tax rate in D2 to 0.1 (10%). Which components update?

• Total Sales After Tax per Product (column E)
• Total Sales After Tax (summary)

The Total Sales Before Tax does not change because it does not use the tax rate.

Key Result

Dashboard shows total sales before and after tax using absolute references to keep tax rate fixed.