Getting data from sources in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula importing data from a web source?

Given the formula =WEBSERVICE("https://api.example.com/data") in cell A1, what will be the output if the URL returns the text "Success"?

Excel

=WEBSERVICE("https://api.example.com/data")

A 0

B Error: #VALUE!

C TRUE

D Success

💡 Hint

The WEBSERVICE function returns the exact text response from the URL.

The WEBSERVICE function fetches the content from the given URL and returns it as text. If the URL returns "Success", that exact text appears in the cell.

❓ Function Choice

intermediate

Which function imports data from another Excel workbook?

You want to get data from a different Excel file named Data.xlsx. Which function will correctly import the value from cell B2 in that file?

A =GETDATA('Data.xlsx', 'Sheet1!B2')

B ='[Data.xlsx]Sheet1'!B2

C =IMPORTDATA('Data.xlsx'!B2)

D =VLOOKUP(B2, '[Data.xlsx]Sheet1'!A1:C10, 2, FALSE)

💡 Hint

Referencing external workbooks uses a specific syntax with square brackets and sheet names.

To get a value from another workbook, use the syntax: ='[WorkbookName]SheetName'!CellReference. Option B correctly uses this.

🎯 Scenario

advanced

You want to import a CSV file into Excel and refresh it automatically. What is the best approach?

You have a CSV file that updates daily. You want Excel to import this file and refresh the data automatically when you open the workbook. Which method should you use?

A Use the Data tab > Get Data > From Text/CSV and set the query to refresh on open.

B Open the CSV file manually and copy-paste the data into Excel each day.

C Use the formula =IMPORTDATA("file.csv") in a cell.

D Use VBA code to open the CSV file and copy data each time.

💡 Hint

Excel's Get & Transform tools can automate importing and refreshing external data.

Using Get Data from Text/CSV creates a query that can be set to refresh automatically, making it the best method for daily updated CSV files.

❓ data_analysis

advanced

Analyzing data imported from a web API in Excel

You imported JSON data from a web API using Power Query. The data contains a list of products with prices. How can you calculate the average price of all products inside Excel after loading the data?

A Use the AVERAGE function on the column with prices in the loaded table.

B Use the WEBSERVICE function again to calculate average.

C Use the SUM function on the price column and divide by the number of rows manually.

D Use the VLOOKUP function to find the average price.

💡 Hint

Once data is loaded into Excel as a table, normal Excel functions work on it.

After importing data with Power Query, the data is in a table. You can use =AVERAGE(TableName[Price]) to get the average price easily.

🧠 Conceptual

expert

What error occurs when trying to import data from a non-existent external source?

You use the formula =WEBSERVICE("https://invalid-url.example.com/data") to import data. What error will Excel show in the cell?

Excel

=WEBSERVICE("https://invalid-url.example.com/data")

A #REF!

B #NAME?

C #VALUE!

D #DIV/0!

💡 Hint

WEBSERVICE returns #VALUE! when it cannot get data from the URL.

If the URL is invalid or unreachable, WEBSERVICE returns the #VALUE! error indicating it cannot retrieve data.