IMPORTRANGE for cross-spreadsheet data in Google Sheets - Dashboard Guide

Dashboard Mode - IMPORTRANGE for cross-spreadsheet data

Goal

See sales data from another Google Sheet in this sheet to analyze total sales and average sales per region.

Sample Data

Source Spreadsheet (Sales Data):

Region	Sales
North	100
South	200
East	150
West	120
North	130
South	220

Destination Spreadsheet (this sheet):

Imported Data
Using IMPORTRANGE formula to get data from source sheet

Dashboard Components

• Imported Sales Data Table: Shows data imported from another spreadsheet.
  Formula: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123xyz456/edit#gid=0", "Sheet1!A2:B7")
  Output: Table with Region and Sales columns, 6 rows of data.
• Total Sales KPI: Sum of all sales from imported data.
  Formula: =SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123xyz456/edit#gid=0", "Sheet1!B2:B7"))
  Output: 920
• Average Sales per Region: Average sales grouped by region using QUERY on imported data.
  Formula: =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123xyz456/edit#gid=0", "Sheet1!A2:B7"), "select Col1, avg(Col2) group by Col1 label avg(Col2) 'Average Sales'", 0)
  Output: Table with Region and Average Sales columns.

Dashboard Layout

+----------------------+-----------------------+
| Imported Sales Data   | Total Sales KPI       |
| Table                | (Sum of Sales)        |
|                      |                       |
+----------------------+-----------------------+
| Average Sales per Region (Grouped Table)          |
+--------------------------------------------------+

Interactivity

Add a filter dropdown for Region. When you select a region, the Imported Sales Data Table, Total Sales KPI, and Average Sales per Region table update to show data only for that region.

This can be done by adding a filter formula or using a slicer connected to the imported data range.

Self Check

If you add a filter to show only the "South" region, which components update?

• The Imported Sales Data Table will show only rows with Region = South.
• The Total Sales KPI will sum only sales from the South region (200 + 220 = 420).
• The Average Sales per Region table will show only the South region with average sales 210.

Key Result

Dashboard imports sales data from another Google Sheet and shows total and average sales by region.