Tables (Insert Table) and benefits in Excel - Dashboard Guide

Dashboard Mode - Tables (Insert Table) and benefits

Goal

See how using Excel Tables helps organize data and makes calculations easier.

Sample Data

Order ID	Product	Quantity	Price per Unit
1001	Apples	10	0.5
1002	Bananas	5	0.3
1003	Cherries	20	1.2
1004	Dates	7	1.5
1005	Elderberries	3	2.0

Dashboard Components

• KPI Card: Total Quantity Sold
  Formula: =SUBTOTAL(109,Table1[Quantity])
  Shows total items sold across all orders.
• KPI Card: Total Sales Amount
  Formula: =SUBTOTAL(109,Table1[Total Price])
  Calculates total money earned from all sales.
• Table: Sales Data
  Shows the data as an Excel Table named Table1. This allows easy filtering and structured references.
• Calculated Column: Total Price
  Formula in Table1, new column: =[@Quantity]*[@[Price per Unit]]
  Automatically calculates total price per order.

Dashboard Layout

+----------------------+----------------------+
| Total Quantity Sold   | Total Sales Amount   |
|       [KPI]           |        [KPI]          |
+----------------------+----------------------+
|                      Sales Data Table                    |
|                    (with Total Price column)             |
+----------------------------------------------------------+

Interactivity

The Excel Table allows filtering by Product or Order ID. When you filter the table, the KPI cards update automatically because their formulas use the Table's filtered data.

Self Check

If you filter the table to show only orders with Quantity greater than 5, which components update?

• The Sales Data Table shows only filtered rows.
• The Total Quantity Sold KPI updates to sum only visible quantities.
• The Total Sales Amount KPI updates to sum only visible sales.

Key Result

Dashboard showing sales data in an Excel Table with total quantity and sales KPIs that update with filters.