Getting data from sources in Excel - Dashboard Guide

Dashboard Mode - Getting data from sources

Dashboard Goal

Understand how to import and link data from different sources into Excel to create a combined view for analysis.

Sample Data

Source	Product	Sales	Region
Sheet1	Apples	100	North
Sheet1	Bananas	150	South
CSV Import	Oranges	200	East
CSV Import	Grapes	120	West
Web Query	Mangoes	180	North
Web Query	Pineapples	160	South

Dashboard Components

• KPI Card - Total Sales: =SUM(C2:C7) sums all sales from combined data.
• Table - Combined Data: Shows all products and sales from different sources in one table.
• Formula - Sales from Sheet1: =SUMIF(A2:A7,"Sheet1",C2:C7) sums sales only from Sheet1.
• Formula - Sales from CSV Import: =SUMIF(A2:A7,"CSV Import",C2:C7) sums sales only from CSV Import.
• Formula - Sales from Web Query: =SUMIF(A2:A7,"Web Query",C2:C7) sums sales only from Web Query.

Dashboard Layout

+----------------------+---------------------+
|      Total Sales      |   Combined Data     |
|      (KPI Card)       |     (Table)         |
+----------------------+---------------------+
| Sheet1 Sales | CSV Import Sales | Web Query Sales |
|  (Formula)   |    (Formula)     |    (Formula)    |
+--------------+------------------+----------------+

Interactivity

Adding a filter by Source lets you see sales only from selected data sources. When you select a source, the combined data table and all sales formulas update to show only that source's data.

Self Check

If you add a filter for Source = CSV Import, which components update?

• The combined data table shows only rows where Source is CSV Import.
• The Total Sales KPI updates to sum only CSV Import sales.
• The sales formulas for Sheet1 and Web Query show zero or no data, while CSV Import sales formula shows the total sales from CSV Import.

Key Result

Dashboard showing combined sales data from multiple sources with total and source-specific sales summaries.