Getting data from sources in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

Excel

=[1]('Sales'!A1)

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

AINDIRECT

BSUM

CVLOOKUP

DAVERAGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM or AVERAGE instead of INDIRECT will not fetch data from another sheet.

VLOOKUP is for searching data, not directly referencing a cell.

The INDIRECT function lets you refer to a cell or range by text, so you can get data from another sheet by specifying its name and cell.

2fill in blank

medium

Complete the formula to import data from a CSV file located at 'C:\Data\report.csv'.

Excel

=[1]("C:\\Data\\report.csv")

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

AQUERY

BWEBSERVICE

CIMPORTDATA

DFILTER

Attempts:

3 left

💡 Hint

Common Mistakes

WEBSERVICE fetches data from web services, not CSV files.

FILTER and QUERY are for filtering data already in sheets.

IMPORTDATA is used to import data from CSV or TSV files by specifying the file path or URL.

3fill in blank

hard

Fix the error in the formula to import data from a web URL 'http://example.com/data.csv'.

Excel

=IMPORTDATA([1])

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

A'http://example.com/data.csv'

Bhttp://example.com/data.csv

Chttp://example.com/data.csv"

D"http://example.com/data.csv"

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving the URL without quotes causes a formula error.

Using mismatched or single quotes may not work in Excel formulas.

The URL must be enclosed in double quotes to be recognized as a text string in the formula.

4fill in blank

hard

Fill both blanks to create a formula that imports data from a named range 'DataRange' in another workbook 'Book1.xlsx'.

Excel

='[[1]]Sheet1'![2]

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

ABook1.xlsx

BBook2.xlsx

CDataRange

DA1:B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong workbook name or range will cause errors.

Forgetting the square brackets around the workbook name.

The formula references the workbook name in square brackets and the named range to get data from that range.

5fill in blank

hard

Fill all three blanks to create a formula that imports data from a web API URL stored in cell A1, then filters rows where column B is greater than 100.

Excel

=FILTER(IMPORTDATA([1]), [2] > [3])

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

AA1

BB:B

C100

D"http://api.example.com/data.csv"

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the URL as a text string instead of a cell reference.

Using the wrong column reference or comparison value.

The IMPORTDATA function uses the URL in cell A1. FILTER then selects rows where column B values are greater than 100.

Practice

1. Which Excel feature allows you to import data from an external file like a CSV or another workbook?

easy

A. Create a Pivot Table

B. Use the SUM function

C. Apply Conditional Formatting

D. Get Data from the Data tab

Solution

1. Step 1: Identify the feature for importing data

   Excel's Data tab includes a 'Get Data' option specifically for importing external data files.

2. Step 2: Understand other options

   SUM adds numbers, Conditional Formatting changes cell colors, and Pivot Tables summarize data, none import files.

3. Final Answer:

   Get Data from the Data tab -> Option D

4. Quick Check:

   Import external data = Get Data [OK]

Hint: Look for 'Get Data' under the Data tab to import files [OK]

Common Mistakes:

• Confusing SUM with data import
• Thinking Pivot Tables import data
• Using Conditional Formatting for importing

2. Which of these is the correct way to refresh imported data in Excel to get the latest updates?

easy

A. Press Ctrl + C to copy the data

B. Use the CONCATENATE function

C. Right-click the imported table and select Refresh

D. Delete and re-import the file manually

Solution

1. Step 1: Identify how to update imported data

   Right-clicking the imported data and choosing Refresh updates it with the latest source changes.

2. Step 2: Understand other options

   Ctrl + C copies data, CONCATENATE joins text, and deleting then re-importing is inefficient and unnecessary.

3. Final Answer:

   Right-click the imported table and select Refresh -> Option C

4. Quick Check:

   Refresh data = Right-click and Refresh [OK]

Hint: Right-click imported data and pick Refresh to update [OK]

Common Mistakes:

• Trying to copy data to refresh
• Using text functions like CONCATENATE
• Deleting data instead of refreshing

3. You imported a table from a web page using Get Data. After refreshing, which of these will happen if the web page data changed?

medium

A. The Excel table updates to show the new data

B. Excel shows an error and deletes the table

C. The table stays the same and does not change

D. Excel automatically saves a backup copy

Solution

1. Step 1: Understand the refresh behavior

   Refreshing imported data updates the Excel table to match the current source data, including any changes.

2. Step 2: Consider other options

   Excel does not delete the table or show errors just because data changed; it also does not auto-save backups on refresh.

3. Final Answer:

   The Excel table updates to show the new data -> Option A

4. Quick Check:

   Refresh updates data = Table changes [OK]

Hint: Refresh updates your table with new source data [OK]

Common Mistakes:

• Thinking refresh deletes data
• Assuming no change after refresh
• Expecting automatic backups on refresh

4. You tried to import data from a CSV file using Get Data, but the data looks wrong with extra columns. What is the likely cause?

medium

A. The delimiter setting is incorrect during import

B. The CSV file is empty

C. You forgot to save the Excel workbook

D. The SUM function was applied to the data

Solution

1. Step 1: Identify cause of extra columns

   CSV files use delimiters like commas or semicolons; if the wrong delimiter is chosen, data splits incorrectly into extra columns.

2. Step 2: Rule out other causes

   An empty CSV shows no data, saving Excel doesn't affect import, and SUM function does not change import layout.

3. Final Answer:

   The delimiter setting is incorrect during import -> Option A

4. Quick Check:

   Wrong delimiter = extra columns [OK]

Hint: Check delimiter settings if columns split oddly [OK]

Common Mistakes:

• Assuming file is empty without checking
• Confusing saving workbook with import issues
• Applying functions before fixing import

5. You want to combine data from two different Excel workbooks into one table that updates automatically when either source changes. Which method should you use?

hard

A. Copy and paste data manually from both workbooks

B. Use Get Data to import both tables and create a query to merge them

C. Use VLOOKUP with hardcoded file paths

D. Save both files as CSV and open them separately

Solution

1. Step 1: Identify method for combining and auto-updating data

   Get Data allows importing tables and merging them with queries that refresh automatically when sources change.

2. Step 2: Evaluate other options

   Copy-paste is manual and not automatic; VLOOKUP with hardcoded paths is fragile and not ideal; saving as CSV and opening separately does not combine data.

3. Final Answer:

   Use Get Data to import both tables and create a query to merge them -> Option B

4. Quick Check:

   Combine and auto-update = Get Data with merge query [OK]

Hint: Merge tables with Get Data queries for auto updates [OK]

Common Mistakes:

• Relying on manual copy-paste
• Using fragile VLOOKUP with file paths
• Opening CSVs separately without merging