Why reference types affect formula behavior in Google Sheets - Dashboard Impact

Dashboard Mode - Why reference types affect formula behavior

Goal

Understand how different cell reference types (relative, absolute, mixed) change formula results when copied across cells.

Sample Data

Item	Price	Quantity	Relative Total	Absolute Total	Mixed Total
Apple	2	3	=B2*C2	=B2*$C$2	=B2*$C2
Banana	1	5	=B3*C3	=B3*$C$2	=B3*$C3
Cherry	3	2	=B4*C4	=B4*$C$2	=B4*$C4
Date	4	4	=B5*C5	=B5*$C$2	=B5*$C5
Elderberry	5	1	=B6*C6	=B6*$C$2	=B6*$C6

Note: Columns D, E, F show formulas copied down from row 2.

Dashboard Components

• KPI Cards: Show total sums for each total column:
  • Total Relative = =SUM(D2:D6)
  • Total Absolute = =SUM(E2:E6)
  • Total Mixed = =SUM(F2:F6)
• Explanation Table: Describes each reference type:
  

  Reference Type	Example	Behavior When Copied
  Relative	B2*C2	Both row and column change relative to new position
  Absolute	B2*$C$2	Row and column fixed; always refers to cell C2
  Mixed	B2*$C2	Column fixed (C), row changes relative to new position

Dashboard Layout

+----------------------+----------------------+----------------------+
|      KPI Cards       |      KPI Cards       |      KPI Cards       |
|  Total Relative      |  Total Absolute      |  Total Mixed         |
+----------------------+----------------------+----------------------+
|                      Explanation Table                      |
|  Reference Type | Example | Behavior When Copied           |
+-------------------------------------------------------------+

Interactivity

User can change quantities or prices in columns B and C. All total columns and KPI cards update automatically to show how formulas behave differently based on reference types.

Copying formulas in columns D, E, F to other rows demonstrates how references adjust or stay fixed.

Self Check

If you change the quantity in cell C4 from 2 to 10, which total column sums will change and why?

Answer: The Relative Total (column D) and Mixed Total (column F) sums will change because their formulas reference the quantity cell with relative or mixed references that adjust per row. The Absolute Total (column E) sum will not change because it always uses the fixed cell $C$2 for quantity.

Key Result

Dashboard shows how relative, absolute, and mixed references affect formula results when copied and how totals update accordingly.