Formula structure and cell references in Google Sheets - Dashboard Guide

Dashboard Mode - Formula structure and cell references

Goal

Understand how formulas work in Google Sheets and how to use cell references to calculate values automatically.

Sample Data

Item	Price	Quantity	Total Cost
Apples	2	5
Bananas	1	8
Oranges	3	4
Grapes	4	3
Mangoes	5	2

Dashboard Components

• KPI Card: Total Items Sold
  Formula: =SUM(C2:C6)
  Shows the total quantity of all items sold.
• Calculated Column: Total Cost per Item
  Formula in D2: =B2*C2
  Drag down to D6 to calculate total cost for each item.
• KPI Card: Total Revenue
  Formula: =SUM(D2:D6)
  Shows the total revenue from all items.

Dashboard Layout

+----------------------+----------------------+
| Total Items Sold (KPI) | Total Revenue (KPI)  |
+----------------------+----------------------+
|                      Items Sold Table                    |
|  Item | Price | Quantity | Total Cost |                 |
+-----------------------------------------------+          

Interactivity

Adding a filter by Item name will update the Total Items Sold and Total Revenue KPIs as well as the Total Cost column in the table to reflect only the selected items.

Self Check

If you add a filter to show only "Apples" and "Oranges", which components update and what are their new values?

• Total Items Sold: SUM of quantities for Apples (5) + Oranges (4) = 9
• Total Revenue: SUM of total costs for Apples (2*5=10) + Oranges (3*4=12) = 22
• Total Cost column: Shows costs only for Apples and Oranges rows.

Key Result

A simple sales dashboard showing total items sold, total revenue, and item-wise total cost using formulas and cell references.