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.

Practice

1. Why is using external data important in Google Sheets analysis?

easy

A. It only works with Excel files, not Google Sheets.

B. It makes the sheet load slower without benefits.

C. It deletes existing data automatically.

D. It adds new information that can improve insights.

Solution

1. Step 1: Understand the role of external data

   External data brings additional information from other sources to your current sheet.

2. Step 2: Recognize the benefit of added information

   More data means better analysis and insights, improving decision-making.

3. Final Answer:

   It adds new information that can improve insights. -> Option D

4. Quick Check:

   External data = new info = better analysis [OK]

Hint: External data means more info for better decisions [OK]

Common Mistakes:

• Thinking external data slows analysis without benefits
• Believing external data deletes existing data
• Confusing Google Sheets with Excel file compatibility

2. Which formula correctly imports data from another Google Sheet using IMPORTRANGE?

easy

A. =IMPORTRANGE("spreadsheet_url", "Sheet1!A1:C10")

B. =IMPORTDATA("spreadsheet_url", "Sheet1!A1:C10")

C. =IMPORTRANGE(Sheet1!A1:C10)

D. =IMPORTXML("spreadsheet_url", "Sheet1!A1:C10")

Solution

1. Step 1: Identify correct function syntax

   IMPORTRANGE requires the spreadsheet URL and the range as text strings.

2. Step 2: Check each option's syntax

   =IMPORTRANGE("spreadsheet_url", "Sheet1!A1:C10") uses correct syntax: =IMPORTRANGE("spreadsheet_url", "Sheet1!A1:C10"). Others use wrong functions or missing parameters.

3. Final Answer:

   =IMPORTRANGE("spreadsheet_url", "Sheet1!A1:C10") -> Option A

4. Quick Check:

   IMPORTRANGE needs URL and range as strings [OK]

Hint: IMPORTRANGE needs URL and range in quotes [OK]

Common Mistakes:

• Using IMPORTDATA or IMPORTXML instead of IMPORTRANGE
• Not putting URL or range in quotes
• Missing one or both parameters

3. Given the formula =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Data!B2:B4") and the external sheet has values 10, 20, 30 in B2:B4, what will be the output?

medium

A. A single cell with text "10, 20, 30"

B. 10, 20, 30 in three separate cells

C. An error because of missing permissions

D. Empty cells because range is invalid

Solution

1. Step 1: Understand IMPORTRANGE output

   IMPORTRANGE imports the exact range values into the same shape in your sheet.

2. Step 2: Check the given range and values

   The range B2:B4 has three cells with values 10, 20, 30, so these will appear in three separate cells.

3. Final Answer:

   10, 20, 30 in three separate cells -> Option B

4. Quick Check:

   IMPORTRANGE outputs range values as is [OK]

Hint: IMPORTRANGE copies range shape and values exactly [OK]

Common Mistakes:

• Expecting all values in one cell as text
• Assuming error without checking permissions
• Thinking range is invalid without reason

4. You used =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sheet1!A1:A5") but see a #REF! error. What is the most likely fix?

medium

A. Grant permission to access the external sheet when prompted.

B. Change IMPORTRANGE to IMPORTDATA.

C. Remove quotes around the range.

D. Use a local file path instead of URL.

Solution

1. Step 1: Identify cause of #REF! error with IMPORTRANGE

   #REF! often appears if permission to access the external sheet is not granted yet.

2. Step 2: Fix by granting access

   Click the cell, then allow access to the external sheet to remove the error.

3. Final Answer:

   Grant permission to access the external sheet when prompted. -> Option A

4. Quick Check:

   #REF! = missing permission fix [OK]

Hint: Allow access prompt fixes IMPORTRANGE #REF! error [OK]

Common Mistakes:

• Changing function to IMPORTDATA incorrectly
• Removing quotes breaking syntax
• Using local file path which IMPORTRANGE does not support

5. You want to combine sales data from two different Google Sheets into one summary sheet. Which approach best uses external data to expand your analysis?

hard

A. Use VLOOKUP only on local data without importing external sheets.

B. Copy and paste data manually from both sheets every day.

C. Use IMPORTRANGE to import both sheets, then use QUERY to combine and analyze.

D. Create separate sheets and avoid combining data.

Solution

1. Step 1: Identify best method to combine external data

   IMPORTRANGE imports data from external sheets dynamically, keeping data updated.

2. Step 2: Use QUERY to combine and analyze imported data

   QUERY can filter, sort, and combine data from multiple imported ranges efficiently.

3. Final Answer:

   Use IMPORTRANGE to import both sheets, then use QUERY to combine and analyze. -> Option C

4. Quick Check:

   IMPORTRANGE + QUERY = dynamic combined analysis [OK]

Hint: Combine IMPORTRANGE with QUERY for dynamic analysis [OK]

Common Mistakes:

• Relying on manual copy-paste causing outdated data
• Using VLOOKUP without importing external data
• Avoiding data combination limits analysis power