Lesson 38: IMPORTRANGE Function Exercises

IMPORTRANGE Function

Practice IMPORTRANGE in Google Sheets with a simulated second spreadsheet: import a table, pick columns, choose a tab, and summarise imported data with QUERY.

Task 1 of 4

The URL of the Regional Orders spreadsheet is in A2. Import its whole Orders tab (A1:D9) into this sheet, starting in C1.

Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.

Pulling data in from another spreadsheet

IMPORTRANGE copies a range from a different Google Sheets file and keeps it in sync. It's how teams build one summary file on top of several others.

=IMPORTRANGE(spreadsheet_url, range_string)

spreadsheet_url: the full URL of the other file from your browser's address bar, or just its key (the long ID between /d/ and /edit). Keeping it in a cell, as these exercises do, makes formulas shorter and easier to update.
range_string: the tab name and range as text in quotes, such as "Orders!A1:D9". If you leave out the tab name, Sheets uses the first tab.

The first-time #REF! and "Allow access"

The first time a file imports from another file, the cell shows #REF!. Hover over it and click Allow access. You have to be able to open the source file yourself. After that, every IMPORTRANGE from that source works.

Combining it with QUERY

Imported data usually needs filtering or summarising: =QUERY(IMPORTRANGE(A2, "Orders!A1:D9"), "select Col2, sum(Col3) group by Col2", 1). Because the data comes from a function rather than a range in this sheet, refer to columns as Col1, Col2… and not by letter.

About these exercises: the practice grid can't reach real Google files, so each exercise comes with a simulated source spreadsheet, shown under the grid. The formulas are exactly what you'd type in Sheets.