Formula structure (=, cell references) in Excel - Dashboard Guide

Dashboard Mode - Formula structure (=, cell references)

Goal

Understand how to write simple formulas using the equal sign and cell references to calculate totals and sums in Excel.

Sample Data

Item	Price	Quantity
Apples	2	5
Bananas	1	8
Oranges	3	4
Grapes	4	3
Pears	2	6

Dashboard Components

• KPI Card: Total Items Sold
  Formula: =SUM(C2:C6)
  Result: 26
• KPI Card: Total Sales Amount
  Formula: =SUMPRODUCT(B2:B6,C2:C6)
  Result: 56
• Table: Sales per Item
  Formula in D2: =B2*C2 copied down to D6
  Results:
  Apples: 10
  Bananas: 8
  Oranges: 12
  Grapes: 12
  Pears: 12

Dashboard Layout

+----------------------+----------------------+
| Total Items Sold (26) | Total Sales (56)     |
+----------------------+----------------------+
| Sales per Item Table                      |
| +---------+-------+----------+-----------+|
| | Item    | Price | Quantity | Sales ($) ||
| +---------+-------+----------+-----------+|
| | Apples  | 2     | 5        | 10        ||
| | Bananas | 1     | 8        | 8         ||
| | Oranges | 3     | 4        | 12        ||
| | Grapes  | 4     | 3        | 12        ||
| | Pears   | 2     | 6        | 12        ||
| +---------+-------+----------+-----------+|
+------------------------------------------+

Interactivity

Add a filter to select specific items (e.g., only Apples and Bananas). When you select items, the Total Items Sold, Total Sales, and Sales per Item Table update to show only the selected items.

Self Check

If you filter the data to show only "Oranges" and "Grapes", what will the Total Items Sold and Total Sales show?

Answer: Total Items Sold = 7 (4 + 3), Total Sales = 24 (12 + 12)

Key Result

A simple sales dashboard showing total items sold, total sales amount, and sales per item using basic formulas with equal sign and cell references.