Lesson Plan

Resources → Subject Notes → Information Communication Technology ICT → Notes
Lesson Plan
Grade: Date: 01/12/2025
Subject: Information Communication Technology ICT
Lesson Topic: Be able to use external data sources within functions
Learning Objective/s:
  • Describe what constitutes an external data source for spreadsheets.
  • Demonstrate how to reference external worksheets, workbooks, CSV files, databases and web services within functions.
  • Apply functions such as SUM, VLOOKUP, AVERAGEIF, GETPIVOTDATA and WEBSERVICE to calculate using external data.
  • Diagnose and correct common reference errors (#REF!, #VALUE!, #NAME?).
Materials Needed:
  • Computer lab with Windows PCs and Excel installed
  • Projector and screen
  • Sample workbooks: Sales2023.xlsx, Targets.xlsx, Budget.xlsx
  • CSV file: Employees.csv
  • Access database file: Inventory.accdb
  • Internet access for WEBSERVICE demonstration
  • Handout summarising reference syntax and troubleshooting tips
 Introduction:

Begin with a quick question: where does the data we analyse in Excel come from? Review prior learning on cell references and basic functions. Explain that today students will learn to pull data from other files and the web, and they will demonstrate mastery through practical tasks.
Lesson Structure:
  1. Do‑Now (5'): Students list examples of data that might live outside the current workbook and share answers.
  2. Mini‑lecture (10'): Define external data sources and show syntax for referencing another workbook (live demo); highlight absolute vs. relative references.
  3. Guided practice (15'): Step‑by‑step linking to an external workbook using SUM; students follow along with provided files.
  4. Importing CSV (10'): Demonstrate Data → Get External Data → From Text; students import Employees.csv and use VLOOKUP.
  5. Database & web service (15'): Connect to Inventory.accdb, calculate AVERAGEIF; then use WEBSERVICE to retrieve an exchange rate and apply it.
  6. Troubleshooting clinic (10'): Present common errors (#REF!, #VALUE!, #NAME?) and have pairs fix a deliberately broken formula.
  7. Exit ticket (5'): Students write one correct external reference and one tip for avoiding errors.
Conclusion:

We reviewed how to link worksheets, import CSV files, query a database and call web services within Excel functions. The exit ticket confirmed each learner can produce a correct external reference and articulate a best‑practice tip. For homework, create a small workbook that pulls sales data from a CSV file and uses VLOOKUP to display product names.