Array formulas basics in Excel - Dashboard Guide

Dashboard Mode - Array formulas basics

Dashboard Goal

Understand how to use array formulas to calculate multiple results at once in Excel. This dashboard shows how to sum, multiply, and filter data using array formulas.

Sample Data

Item	Price	Quantity	Category
Apple	1.2	10	Fruit
Banana	0.8	15	Fruit
Carrot	0.5	20	Vegetable
Tomato	0.9	12	Vegetable
Orange	1.1	8	Fruit

Dashboard Components

• Total Sales (All Items)
  Formula: =SUM(B2:B6*C2:C6)
  Explanation: Multiplies Price by Quantity for each row, then sums all results.
  Result: 1.2*10 + 0.8*15 + 0.5*20 + 0.9*12 + 1.1*8 = 12 + 12 + 10 + 10.8 + 8.8 = 53.6
• Sales per Item
  Formula (entered in E2 and spilled down): =B2:B6*C2:C6
  Explanation: Multiplies Price by Quantity for each item, showing sales per item.
  Results:
  Apple: 12
  Banana: 12
  Carrot: 10
  Tomato: 10.8
  Orange: 8.8
• Total Fruit Sales
  Formula: =SUM(IF(D2:D6="Fruit", B2:B6*C2:C6, 0))
  Explanation: Multiplies Price by Quantity only for items in the Fruit category, then sums.
  Result: Apple + Banana + Orange sales = 12 + 12 + 8.8 = 32.8

Dashboard Layout

+----------------------+---------------------+
| Total Sales (All)    | Total Fruit Sales   |
|        53.6          |        32.8         |
+----------------------+---------------------+
| Sales per Item (List)                      |
| Apple: 12                                 |
| Banana: 12                                |
| Carrot: 10                                |
| Tomato: 10.8                              |
| Orange: 8.8                               |
+------------------------------------------+

Interactivity

Add a filter dropdown for Category (Fruit, Vegetable). When a category is selected, the Sales per Item list and Total Sales update to show only items from that category. The Total Fruit Sales card updates only when Fruit is selected.

Self Check

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

• Total Sales (All Items) updates to sum only vegetables: Carrot (0.5*20=10) + Tomato (0.9*12=10.8) = 20.8
• Sales per Item shows only Carrot: 10 and Tomato: 10.8
• Total Fruit Sales updates to 0 because no fruits are shown

Key Result

Dashboard demonstrating basic array formulas to calculate total sales, sales per item, and filtered sales by category.