Consolidating data from multiple sheets in Excel - Dashboard Guide

Dashboard Mode - Consolidating data from multiple sheets

Dashboard Goal

Show total sales from three different store sheets combined into one summary dashboard.

Sample Data

Each sheet has sales data for products sold in different stores.

Product	Sales
Apples	100
Bananas	150
Cherries	200

Sheet Names and Data:

• Store1: Apples 100, Bananas 150, Cherries 200
• Store2: Apples 120, Bananas 130, Cherries 210
• Store3: Apples 110, Bananas 140, Cherries 190

Dashboard Components

• Component 1: Consolidated Sales Table
  Shows total sales per product from all stores combined.
  Formula example for Apples total sales:
  =Store1!B2 + Store2!B2 + Store3!B2
  Result for Apples: 330
• Component 2: Total Sales KPI
  Sum of all sales from all products and stores.
  Formula:
  =SUM(Store1!B2:B4) + SUM(Store2!B2:B4) + SUM(Store3!B2:B4)
  Result: 1350
• Component 3: Bar Chart
  Visualizes total sales per product from the consolidated table.

Dashboard Layout

+-------------------------+------------------+
| Consolidated Sales Table | Total Sales KPI  |
| (Product and Total Sales)| (Big number)     |
+-------------------------+------------------+
|                         Bar Chart              |
|               (Sales per Product)              |
+-----------------------------------------------+

Interactivity

Add a filter to select a specific product. When a product is selected, the consolidated sales table and bar chart update to show only that product's sales. The total sales KPI updates to show the sum for the selected product across all stores.

Self Check

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

• Consolidated Sales Table: Shows only Bananas with total sales 150 + 130 + 140 = 420
• Total Sales KPI: Updates to 420
• Bar Chart: Shows a single bar for Bananas with height 420

Key Result

Dashboard consolidates sales data from three store sheets to show total sales per product and overall total.