IMPORTRANGE for other spreadsheets in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a consolidated sales report that combines data from multiple regional spreadsheets into one master sheet.

📊 Data: You have separate Google Sheets for each region (East, West, North) with sales data including Date, Product, Units Sold, and Revenue.

🎯 Deliverable: Create a master Google Sheet that imports sales data from each regional spreadsheet using IMPORTRANGE, then summarize total revenue by region.

Sample Data

Date	Product	Units Sold	Revenue
2024-05-01	Widget A	10	200
2024-05-02	Widget B	5	150
2024-05-03	Widget C	8	240
2024-05-04	Widget A	7	140
2024-05-05	Widget B	6	180

1

Step 1: Open your master Google Sheet where you want to combine data from all regions.

No formula needed for this step.

Expected Result

You have a blank sheet ready to import data.

2

Step 2: In cell A1, import data from the East region spreadsheet using IMPORTRANGE. Use the spreadsheet URL and specify the range with headers.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/EAST_SPREADSHEET_ID/edit", "Sheet1!A1:D6")

Expected Result

East region sales data appears in the master sheet starting at A1.

3

Step 3: In cell F1, import data from the West region spreadsheet using IMPORTRANGE with the correct URL and range.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/WEST_SPREADSHEET_ID/edit", "Sheet1!A1:D6")

Expected Result

West region sales data appears in the master sheet starting at F1.

4

Step 4: In cell K1, import data from the North region spreadsheet using IMPORTRANGE with the correct URL and range.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/NORTH_SPREADSHEET_ID/edit", "Sheet1!A1:D6")

Expected Result

North region sales data appears in the master sheet starting at K1.

5

Step 5: Create a summary table below the imported data to calculate total revenue by region. In cell A10, type 'Region'. In B10, type 'Total Revenue'.

No formula needed for this step.

Expected Result

Headers for summary table appear in A10 and B10.

6

Step 6: In cell A11, type 'East'. In B11, calculate total revenue for East region by summing the Revenue column from the imported East data.

=SUM(D2:D6)

Expected Result

Total revenue for East region is calculated as 910.

7

Step 7: In cell A12, type 'West'. In B12, calculate total revenue for West region by summing the Revenue column from the imported West data (starting at column J).

=SUM(J2:J6)

Expected Result

Total revenue for West region is calculated correctly based on imported data.

8

Step 8: In cell A13, type 'North'. In B13, calculate total revenue for North region by summing the Revenue column from the imported North data (starting at column N).

=SUM(N2:N6)

Expected Result

Total revenue for North region is calculated correctly based on imported data.

9

Step 9: Format the summary table with bold headers and currency format for total revenue.

No formula needed for this step.

Expected Result

Summary table looks clear and professional.

Final Result

West_Total

↓

North_Total

✓The East region generated $910 in revenue for the period.

✓The West and North regions' total revenues are visible and updated automatically from their spreadsheets.

✓Using IMPORTRANGE allows the master sheet to stay updated when regional data changes.

Bonus Challenge

Create a combined list of all sales from all regions in one continuous table in the master sheet.

Show Hint

Use the ARRAYFORMULA and QUERY functions along with IMPORTRANGE to stack data vertically.