IMPORTRANGE for cross-spreadsheet data in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to create a monthly sales summary by importing sales data from another Google Sheet.

📊 Data: You have access to a separate Google Sheet that contains daily sales data with columns Date, Product, Region, and Sales Amount.

🎯 Deliverable: Create a summary sheet that imports the sales data from the other spreadsheet and calculates total sales per month.

Sample Data

Date	Product	Region	Sales Amount
2024-01-01	Shirt	North	100
2024-01-02	Pants	South	150
2024-01-15	Hat	East	50
2024-02-01	Shirt	North	120
2024-02-10	Pants	West	200
2024-02-20	Hat	South	80
2024-03-05	Shirt	East	130
2024-03-15	Pants	North	170

1

Step 1: Open your summary Google Sheet where you want to import the sales data.

No formula needed for this step.

Expected Result

You have a blank sheet ready to import data.

2

Step 2: Use the IMPORTRANGE formula to import the sales data from the other spreadsheet. You need the URL of the source spreadsheet and the range of data.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-source-sheet-id/edit", "Sheet1!A2:D9")

Expected Result

The sales data from the source sheet appears in your summary sheet starting from the cell where you entered the formula.

3

Step 3: Allow access permission when prompted to connect the two sheets.

No formula needed.

Expected Result

Data loads successfully after permission is granted.

4

Step 4: Create a new column next to the imported data to extract the month from the Date column.

=TEXT(A2, "yyyy-MM")

Expected Result

The month in 'yyyy-MM' format appears for each date, e.g., '2024-01'.

5

Step 5: Create a summary table with unique months listed in one column.

Manually type or use =UNIQUE(E2:E9) where E column has the month values.

Expected Result

A list of unique months like 2024-01, 2024-02, 2024-03 appears.

6

Step 6: Calculate total sales for each month using SUMIF based on the month column.

=SUMIF(E2:E9, G2, D2:D9)

Expected Result

Total sales amount for the month in G2 appears, e.g., 300 for 2024-01.

7

Step 7: Copy the SUMIF formula down for all months in your summary table.

Drag the formula down from the first month cell.

Expected Result

Total sales for each month are calculated correctly.

Final Result

Month    | Total Sales
----------------------
2024-01  | 300
2024-02  | 400
2024-03  | 300

✓January 2024 had total sales of 300.

✓February 2024 had the highest sales with 400.

✓March 2024 sales were 300.

Bonus Challenge

Create a chart that shows monthly sales trends using the summary table.

Show Hint

Select the summary table and insert a line chart from the Insert menu.