Learning Objectives

  • Reference cells on other worksheets (=Sheet2!B5)
  • Build a Dashboard that pulls Pivot and Employees totals
  • Create and manage external workbook links
  • Use 3D references across similarly structured sheets
  • Update or break links safely before submission

Same workbook:

=Employees!G12
=Pivot!B4
='Sales Data'!D10

If the sheet name has spaces, wrap it in single quotes.

Dashboard pattern (Model Lab Test Set B style)

  1. Sheet Dashboard.
  2. Label cells in column A (Total Gross, West Sales, …).
  3. In column B, pull live values from Employees / Pivot / Sales.
  4. Add a chart that uses Dashboard values or a dedicated chart sheet.
  1. Open source and destination files.
  2. In the destination, type = and click a cell in the source workbook.
  3. Excel inserts a path like:
='[Budget_2026.xlsx]Summary'!B10
  1. Save both files in the same project folder before distributing.

Data → Edit Links (Windows Excel):

  • Update Values
  • Change Source
  • Break Link (converts to static numbers — irreversible)

3) 3D References

When Jan–Dec sheets share the same layout:

=SUM(Jan:Dec!B2)

Useful for monthly department totals without listing twelve sheets.

4) Risk Management

Risk Mitigation
Moved/renamed source file Keep a stable folder; use Edit Links → Change Source
Broken #REF! after sheet rename Rename sheets carefully; update formulas
Sharing only the Dashboard Break links or send the whole folder
Circular references Dashboard should pull values, not push back into source

Practice Task

  1. Create Jan, Feb, Mar sheets with the same total cell B10.
  2. Summary sheet: =SUM(Jan:Mar!B10).
  3. Dashboard sheet linking Employees Net total and Pivot regional total.
  4. Optional: link one cell from a second workbook, then demonstrate Update and Break Link.

Step-by-Step Lab Exercise: Quarterly Summary

  1. Create sheets named Jan, Feb, and Mar with the same layout. In each, put total sales in B10: Jan = 120,000, Feb = 135,000, Mar = 150,000.
  2. Create a sheet named Summary:
    • A2 “January”, B2: =Jan!B10
    • A3 “February”, B3: =Feb!B10
    • A4 “March”, B4: =Mar!B10
    • A5 “Quarter total”, B5: =SUM(Jan:Mar!B10)
  3. Expected: B2:B4 show the three monthly values and B5 shows 405,000.
  4. Change Feb!B10 to 140,000. Expected: Summary B3 updates to 140,000 and B5 to 410,000.
  5. Optional external link: in a second workbook, type = and click Summary!B5 in the first workbook. Save both, close the source, then reopen the destination and choose Update when asked.

Key Takeaways

  • Use =SheetName!Cell to link sheets; wrap names with spaces in single quotes.
  • 3D references such as =SUM(Jan:Mar!B10) total the same cell across sheets.
  • External links depend on file locations; manage them with Edit Links.
  • Break links only when you need fixed values to share a file.

Exam-Style Questions

Viva (short answer)

  1. Write a formula that refers to cell B5 on a sheet named Sales.
  2. What is a 3D reference?
  3. What happens to an external link if the source file is moved?
  4. What does “Break Link” do?
  5. Why are single quotes needed in some sheet references?

Lab test tasks

  1. Create three monthly sheets and a summary sheet that totals them using a 3D reference.
  2. Build a dashboard sheet that pulls totals from two other sheets and displays them in a chart.
  3. Link a cell from another workbook, then demonstrate Update Values and Break Link.

📚 Practice: Unit 3 Practical Assignment