Mixed references in Google Sheets - Dashboard Guide

Dashboard Mode - Mixed references

Dashboard Goal

See total sales for each product across different months and copy formulas easily without errors by using mixed references.

Sample Data

Product	Jan	Feb	Mar
Apples	100	120	130
Bananas	90	110	115
Cherries	80	95	100
Dates	70	85	90

Dashboard Components

• KPI Card: Total Sales per Product
  Formula in E2 (for Apples): =SUM(B2:D2)
  Shows total sales for each product across Jan to Mar.
• Summary Table: Monthly Sales Total
  Formula in B6 (for Jan): =SUM(B2:B5)
  Shows total sales for each month across all products.
• Formula Example Using Mixed References
  In E2, formula: =SUM($B2:$D2)
  Here, columns B to D are fixed with $ (absolute), row 2 is relative.
  When copied down, row changes but columns stay fixed.
  In F2, formula: =SUM(B$2:B$5)
  Here, rows 2 to 5 are fixed with $ (absolute), column B is relative.
  When copied across columns, rows stay fixed but column changes.

Dashboard Layout

+----------------------+---------------------+
| Product Sales Table   | Total Sales per Prod |
| (A1:D5)              | (E1:E5)             |
+----------------------+---------------------+
| Monthly Sales Total   |                     |
| (B6:D6)              |                     |
+----------------------+---------------------+

Interactivity

Copy the formula with mixed references down or across to calculate totals without changing fixed columns or rows incorrectly. This helps when adding new products or months.

Self Check

If you copy the formula =SUM($B2:$D2) from E2 down to E3, what will the formula in E3 be and what will it calculate?

Key Result

Dashboard shows total sales per product and total sales per month using mixed references for easy formula copying.