Be able to use external data sources within functions

Topic 20 – Spreadsheets: Using External Data Sources in Functions

Learning objective

Students will be able to reference, import and manipulate external data in spreadsheet functions, using correct cell‑referencing (absolute, relative and mixed), named ranges, the order of operations, and basic data‑manipulation tools (sort, filter, advanced filter, search). All skills are mapped to the Cambridge IGCSE ICT 0417 syllabus (sections 20.1, 20.2, 20.3).

1. What is an external data source?

An external data source is any set of data stored outside the current worksheet. Typical sources are:

• Another worksheet in the same workbook
• Another workbook (different file)
• A delimited text file (CSV, TSV)
• A database table (Microsoft Access, SQL Server, MySQL)
• Web data (HTML tables, XML, JSON via a web service or API)

Linking to external sources ensures the spreadsheet always works with the latest data and avoids duplication.

2. Key concepts for referencing external data

2.1 Cell‑referencing – absolute, relative and mixed

Reference type	Form	Effect when copied
Relative	B2	Both column and row change relative to the new position.
Absolute	$B$2	Neither column nor row changes.
Mixed (column locked)	$B2	Column stays B, row changes.
Mixed (row locked)	B$2	Row stays 2, column changes.

When linking to an external range you almost always want an absolute reference so the link remains correct if the formula is moved or copied:

=SUM([Budget.xlsx]Annual!$B$2:$B$12)

2.2 Named ranges

• Give a meaningful label to a range of cells.
• Make external references easier to read and maintain.

Use the name in a formula:

=SUM(Sales2023)

2.3 Order of operations

Spreadsheets follow the standard mathematical hierarchy:

1. Parentheses ()
2. Exponents ^
3. Multiplication ÷ and Division ÷
4. Addition + and Subtraction ‑

Use parentheses to force the required sequence, especially when several functions are combined.

=AVERAGEIF(Sales2023,">=2024-01-01",Sales2023) * 0.1

Here the AVERAGEIF is evaluated first, then the result is multiplied by 0.1.

3. How to refer to external data

1. Direct cell reference – type the workbook and sheet name in square brackets, then the range.
2. Named range – create the name in the source file and use it directly.
3. Function argument – supply the external range (or name) as the required argument.

4. Functions that accept external ranges

Function	Purpose	Example (external source)
SUM	Add numbers	=SUM([Budget.xlsx]Annual!$B$2:$B$12)
SUMIFS	Conditional total (multiple criteria)	=SUMIFS([Sales.xlsx]Data!$D:$D, [Sales.xlsx]Data!$B:$B, "East", [Sales.xlsx]Data!$C:$C, ">=2024-01-01")
COUNTIFS	Conditional count (multiple criteria)	=COUNTIFS([Orders.xlsx]Log!$E:$E, "Completed", [Orders.xlsx]Log!$F:$F, "<10")
VLOOKUP	Search a table for a matching value	=VLOOKUP(A2, [Products.xlsx]Sheet1!$A$1:$D$100, 3, FALSE)
INDEX / MATCH	Flexible two‑way lookup	=INDEX([Employees.xlsx]Data!$B$2:$B$500, MATCH(D2, [Employees.xlsx]Data!$A$2:$A$500, 0))
AVERAGEIF / AVERAGEIFS	Average with one or more conditions	=AVERAGEIFS([Sales.xlsx]Data!$D:$D, [Sales.xlsx]Data!$B:$B, "North", [Sales.xlsx]Data!$C:$C, ">=2024-01-01")
GETPIVOTDATA	Extract a value from a pivot table	=GETPIVOTDATA("Total Sales", '[Report.xlsx]Pivot'!$A$3)
WEBSERVICE	Retrieve data from a URL (web API)	=WEBSERVICE("https://api.exchangerate.host/latest?base=USD")
FILTER (Google Sheets)	Return rows that meet criteria	=FILTER(ImportRange("https://docs.google.com/spreadsheets/d/…","Sheet1!A2:D"), C:C="East")

5. Importing external data – menu paths (exam wording)

• Another workbook: Data → Get Data → From File → From Workbook
• CSV / Text file: Data → Get Data → From File → From Text/CSV
• Microsoft Access database: Data → Get Data → From Database → From Microsoft Access Database
• SQL Server / ODBC: Data → Get Data → From Database → From SQL Server Database
• Web data (API): Use the WEBSERVICE function directly in a cell; then parse with FILTERXML (XML) or a JSON add‑in.

6. Manipulating imported data (syllabus 20.2)

6.1 Sorting

Sorting helps you analyse data in a logical order.

1. Select any cell in the imported range.
2. Choose Data → Sort.
3. In the Sort dialog you can add multiple keys (e.g., first by “Region”, then by “Date”). Use Add Level to create additional keys.
4. Set the order (A‑Z, Z‑A, Oldest‑Newest, etc.) for each key and click OK.

6.2 Filtering

Two types of filter are required by the syllabus.

Standard filter

1. Select the header row of the imported table.
2. Click Data → Filter (Excel) or Data → Create a filter (Google Sheets).
3. Use the drop‑down arrows to choose values, text contains, dates, etc.

Advanced (custom) filter

1. Prepare a criteria range on the same sheet (e.g., cells H1:H2 with headings “Region” and “>1000”).
2. Go to Data → Advanced (Excel).
3. Set List range** to the imported data and **Criteria range** to the cells you just created.
4. Choose “Filter the list, in‑place” or “Copy to another location”. Click OK.

6.3 Searching & lookup

• VLOOKUP – simple vertical search.
• HLOOKUP – horizontal search (less common in IGCSE).
• INDEX / MATCH – two‑way lookup; more flexible when the lookup column is not the first column.
• FILTER (Google Sheets) – returns an array of rows that meet a condition.

Example – find the department for an employee ID entered in D2:

=INDEX([Employees.xlsx]Data!$B$2:$B$500, MATCH(D2, [Employees.xlsx]Data!$A$2:$A$500, 0))

7. Presenting data (syllabus 20.3)

After analysis, the spreadsheet must be formatted for clear presentation and printing.

• Column widths & row heights – adjust so all data is visible.
• Number formats – currency, percentages, dates, and thousands separators.
• Cell styles – headings bold, centre‑aligned; totals highlighted with a different fill colour.
• Headers & footers – insert file name, page number, and date via Insert → Header & Footer.
• Print area – select the range and choose Page Layout → Print Area → Set Print Area.
• Page orientation – Portrait or Landscape as appropriate.
• Scaling – “Fit Sheet on One Page” or “Adjust to 100 %” to avoid cut‑offs.
• Print preview – always check File → Print → Print Preview before printing.

Below is a simple checklist that students can copy onto a “Print Setup” sheet:

□ Set column widths
□ Apply number formats (currency, date, %)
□ Add bold headings & colour‑coded totals
□ Define Print Area
□ Insert Header (File name) and Footer (Page 1 of n, date)
□ Choose Portrait/Landscape
□ Set scaling (Fit to 1 page wide)
□ Review Print Preview

8. Updating links and refreshing data

• When the source file changes, use Data → Refresh All (Excel) or Data → Refresh (Google Sheets) to pull the latest values.
• If a source file is moved or renamed, the link will return #REF!. Fix the path by editing the formula or using Edit Links → Change Source.
• Keep all source files in a dedicated folder and avoid renaming them after links are created.

9. Best practices (exam‑level)

• Store source files in a single folder; use relative paths where possible.
• Use absolute references for external ranges ($ locks).
• Prefer named ranges – they make formulas readable and easier to maintain.
• Document each source on a “Data Sources” sheet (file name, path, purpose, last‑updated date).
• Keep the source workbook open while creating or editing formulas to avoid #REF! errors.
• Apply sorting and filtering before any summary calculation to ensure you are analysing the intended rows.
• Wrap volatile formulas in IFERROR(...,"") or IFNA(...,"Not found") to produce clean output for the exam.

10. Common errors and troubleshooting

Error	Cause	Solution
#REF!	Source file closed, moved, renamed, or path broken.	Open the source file, correct the path via Edit Links → Change Source, or re‑create the reference.
#VALUE!	Incompatible data type (e.g., text in a numeric range) or improper parsing of web‑service result.	Clean the source data; use VALUE() or NUMBERVALUE() to coerce text to numbers.
#NAME?	Misspelled function name or missing add‑in (e.g., IMPORTJSON).	Check spelling; enable or install the required add‑in.
#N/A	Lookup value not found.	Verify the key exists; use IFERROR(...,"Not found") to handle gracefully.
#SPILL!	Dynamic array result cannot fit because of blocked cells.	Clear the cells below the formula or move the formula to an empty area.

11. Practice activities (aligned with syllabus 20.1 & 20.2 & 20.3)

1. Link and sum – Open Sales2023.xlsx and Targets.xlsx. In Targets.xlsx enter =SUM([Sales2023.xlsx]Sheet1!$B$2:$B$31). Convert the range to a named range Sales2023 in the source workbook and repeat the calculation using =SUM(Sales2023). Apply currency formatting and set the print area to show only the total.
2. Import & lookup – Import Employees.csv via Data → Get Data → From Text/CSV. Define the named range EmpData. In a new sheet, enter an employee ID in A2 and use =VLOOKUP(A2, EmpData, 3, FALSE) to return the department. Before the lookup, sort the imported list by “Surname” (Data → Sort → add level “First name”).
3. Database average with criteria – Connect to Inventory.accdb (Data → Get Data → From Access). Load the Products table. Calculate the average stock for “Electronics” with =AVERAGEIFS(Products!$D:$D, Products!$C:$C, "Electronics"). Then apply an Advanced Filter to display only rows where stock < 10 and print the filtered view.
4. Web‑service currency conversion – In cell A2 enter a USD amount. In B2 use =WEBSERVICE("https://api.exchangerate.host/latest?base=USD"). Extract the EUR rate with =FILTERXML(B2,"//EUR"). Convert the amount with =A2*B2 and format column C as Euro currency. Wrap the conversion in =IFERROR(A2*B2, "Rate unavailable").
5. Conditional total with SUMIFS – Using the imported sales CSV, create a summary that totals sales for the “North” region in 2024 only: =SUMIFS(Sales!$D:$D, Sales!$B:$B, "North", Sales!$C:$C, ">=2024-01-01", Sales!$C:$C, "<=2024-12-31"). Apply a filter to show only the “North” rows, then print the summary table.

12. Summary

External data sources dramatically increase the power and relevance of spreadsheets. Mastery of:

• Absolute, relative and mixed cell references
• Named ranges (created in the source file)
• The order of operations
• Functions that accept external ranges (including conditional aggregates such as SUMIFS and COUNTIFS)
• Data‑manipulation tools – multi‑key sort, standard & advanced filter, lookup functions
• Presentation techniques – formatting, page layout, print‑area setup
• Link maintenance – refreshing, updating paths, error handling with IFERROR

These skills satisfy the Cambridge IGCSE ICT 0417 requirements and equip students for real‑world spreadsheet tasks.