Why external data expands analysis in Google Sheets - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to import data from another sheet named 'Sales'.

Google Sheets

=IMPORTRANGE("[1]", "Sales!A1:C10")

Drag options to blanks, or click blank then click option'

ASheet1

Bhttps://docs.google.com/spreadsheets/d/abc123xyz

CA1:C10

DSales

Attempts:

3 left

💡 Hint

Common Mistakes

Using the sheet name instead of the URL as the first argument.

Forgetting to put the URL in quotes.

The IMPORTRANGE function needs the URL of the external spreadsheet as the first argument to import data.

2fill in blank

medium

Complete the formula to filter imported data where sales are greater than 1000.

Google Sheets

=FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123xyz", "Sales!A2:B100"), [1] > 1000)

Drag options to blanks, or click blank then click option'

AB2:B100

BA2:A100

CIMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123xyz", "Sales!B2:B100")

DSales!B2:B100

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column range for the condition.

Using a local range like B2:B100 for the condition.

The FILTER function needs the imported range of sales values to compare with 1000. Column B contains sales, so repeat IMPORTRANGE for Sales!B2:B100.

3fill in blank

hard

Fix the error in this formula that tries to sum imported sales data.

Google Sheets

=SUM([1]("https://docs.google.com/spreadsheets/d/abc123xyz", "Sales!B2:B100"))

Drag options to blanks, or click blank then click option'

AIMPORTRANGE

BIMPORTDATA

CIMPORTHTML

DIMPORTXML

Attempts:

3 left

💡 Hint

Common Mistakes

Using IMPORTDATA or IMPORTXML which don't work for ranges.

Not wrapping IMPORTRANGE inside SUM.

To sum imported data from another sheet, use IMPORTRANGE to get the range, then SUM to add it up.

4fill in blank

hard

Fill both blanks to create a formula that imports data and returns only unique customer names.

Google Sheets

=UNIQUE([1]("https://docs.google.com/spreadsheets/d/abc123xyz", [2]))

Drag options to blanks, or click blank then click option'

AIMPORTRANGE

B"Sales!A2:A100"

C"Customers!A2:A100"

DFILTER

Attempts:

3 left

💡 Hint

Common Mistakes

Using FILTER instead of IMPORTRANGE.

Choosing the wrong sheet or range for customer names.

Use IMPORTRANGE to import the customer names from the 'Customers' sheet range, then UNIQUE to get distinct names.

5fill in blank

hard

Fill all three blanks to create a formula that imports sales data, filters sales over 500, and sums them.

Google Sheets

=SUM(FILTER([1]("https://docs.google.com/spreadsheets/d/abc123xyz", [2]), [3] > 500))

Drag options to blanks, or click blank then click option'

AIMPORTRANGE

B"Sales!B2:B100"

CIMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123xyz", "Sales!B2:B100")

DB2:B100

Attempts:

3 left

💡 Hint

Common Mistakes

Using a local range like B2:B100 for the condition.

Using a string instead of a range for the condition.

IMPORTRANGE imports the sales range, FILTER selects sales over 500 using the same range, and SUM adds them up.