Be able to use external data sources within functions

Published by Patrick Mutisya · 14 days ago

Topic 20: Spreadsheets – Using External Data Sources in Functions

Learning Objective

Be able to use external data sources within spreadsheet functions.

What Is an External Data Source?

An external data source is any set of data that resides outside the current worksheet or workbook. It can be another worksheet, another workbook, a text file, a database, or data retrieved from the web. Functions can refer to these sources to perform calculations without manually copying the data.

Common Types of External Data Sources

  • Another worksheet in the same workbook

  • Another workbook (different file)

  • CS \cdot or other delimited text files

  • Database tables (e.g., Microsoft Access, SQL Server)

  • Web data (HTML tables, XML, JSON via web services)

Why Use External Data in Functions?

  • Ensures calculations are always based on the latest data.

  • Reduces duplication and the risk of inconsistent data.

  • Allows complex analysis that combines information from multiple sources.

  • Supports real‑time reporting and dashboards.

How to Refer to External Data

  1. Cell references – Directly reference a cell or range in another file.

  2. Named ranges – Define a name for a range in the external source and use that name in the function.

  3. Functions that accept a range argument – Supply the external range as the argument (e.g., SUM, VLOOKUP).

Examples of Functions Using External Data

FunctionPurposeExample Using External Data
SUMAdd numbers\$\text{=SUM([Budget.xlsx]Annual!B2:B12)}\$
VLOOKUPFind a value in a table\$\text{=VLOOKUP(A2, [Products.csv]Sheet1!\$A\$1:\$D\$100, 3, FALSE)}\$
A \cdot ERAGEIFAverage with criteria\$\text{=A \cdot ERAGEIF([Sales.db]Orders!C:C, ">=2024-01-01", [Sales.db]Orders!D:D)}\$
GETPI \cdot OTDATAExtract data from a pivot table\$\text{=GETPI \cdot OTDATA("Total Sales", '[Report.xlsx]Pivot'!\$A\$3)}\$
WEBSER \cdot ICERetrieve data from a URL\$\text{=WEBSER \cdot ICE("https://example.com/api/sales?year=2024")}\$

Step‑by‑Step: Linking to an External Workbook

  1. Open both the current workbook and the external workbook.

  2. Select the cell where the result will appear.

  3. Begin typing the function, for example =SUM(.

  4. Switch to the external workbook and select the required range.

  5. Press Enter. Excel inserts a reference such as [Budget.xlsx]Annual!\$B\$2:\$B\$12.

Step‑by‑Step: Importing Data from a CS \cdot File

  1. Choose Data → Get External Data → From Text (or the equivalent menu).

  2. Select the CS \cdot file and specify the delimiter (comma, semicolon, etc.).

  3. Complete the import wizard; the data appears on a new worksheet.

  4. Reference the imported range in a function, e.g., =A \cdot ERAGE(A2:A100).

Best Practices

  • Store external files in a stable folder and avoid moving them after linking.

  • Use absolute references (with $) when the range must remain fixed.

  • Assign meaningful names to external ranges for readability.

  • If a function returns #REF!, ensure the source file is open or the path is correct.

  • Document each external source on a dedicated “Data Sources” sheet.

Common Errors and Troubleshooting

ErrorCauseSolution
#REF!Source workbook is closed, moved, or renamed.Open the workbook or update the reference path.
#VALUE!Incompatible data type (e.g., text in a numeric range).Clean the source data or wrap the reference in VALUE().
#NAME?Misspelled function name or missing add‑in for web queries.Check spelling and enable any required add‑ins.

Practice Activities

  1. Open Sales2023.xlsx and Targets.xlsx. Use SUM in Targets.xlsx to total the sales figures from Sales2023.xlsx.

  2. Import Employees.csv and use VLOOKUP to retrieve each employee’s department based on their ID.

  3. Connect to the sample Access database Inventory.accdb and calculate the average stock level for a chosen product category using A \cdot ERAGEIF.

  4. Use the WEBSER \cdot ICE function to fetch the current exchange rate from a public API and convert a list of prices from USD to EUR.

Suggested diagram: Flowchart showing the steps to link an external workbook, import a CS \cdot file, and reference the data in a function.

Summary

External data sources greatly enhance the capability of spreadsheets by providing up‑to‑date information, reducing duplication, and enabling sophisticated analysis. Mastery of referencing external ranges, importing data, and handling common errors is essential for success in the IGCSE ICT examination.