0
0
Google Sheetsspreadsheet~8 mins

ARRAYFORMULA function in Google Sheets - Dashboard Guide

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - ARRAYFORMULA function
Dashboard Goal

Calculate total sales for each product by multiplying quantity and price for all rows automatically using ARRAYFORMULA.

Sample Data
ProductQuantityPrice
Apples102
Bananas51.5
Cherries203
Dates74
Elderberries35
Dashboard Components
  • KPI Card: Total Revenue
    Formula: =SUM(D2:D6)
    Shows total revenue from all products.
  • Table with Calculated Column: Sales per Product
    Formula in D2: =ARRAYFORMULA(IF(A2:A6="","",B2:B6*C2:C6))
    This multiplies Quantity by Price for each product automatically.
  • Bar Chart: Sales by Product
    Uses Product names (A2:A6) and Sales per Product (D2:D6) to visualize revenue per product.
Dashboard Layout
+----------------------+---------------------+
|      Total Revenue    |   Sales by Product  |
|        (KPI)         |      (Bar Chart)    |
+----------------------+---------------------+
|              Sales per Product Table             |
|               (Product, Quantity, Price, Sales) |
+-------------------------------------------------+
Interactivity

Add a filter for Product name. When you select a product, the Sales per Product table and Bar Chart update to show only that product's data. The Total Revenue KPI updates to show the sum for the filtered product(s).

Self Check

If you add a filter to show only "Bananas" and "Dates", which components update and what values do they show?

  • Sales per Product Table: Shows only rows for Bananas and Dates with their sales calculated.
  • Bar Chart: Displays bars only for Bananas and Dates.
  • Total Revenue KPI: Shows sum of sales for Bananas and Dates (Bananas: 5*1.5=7.5, Dates: 7*4=28, Total=35.5).
Key Result
Dashboard calculates and visualizes total sales per product using ARRAYFORMULA in Google Sheets.