28.1 Linking Cells Within the Same Workbook

A cross-sheet reference is a formula that reads a value from a cell on a different sheet in the same workbook. The syntax uses an exclamation mark to separate the sheet name from the cell address.

Cross-Sheet Reference Syntax

Creating a Cross-Sheet Reference by Pointing

The easiest and most accurate way — let Excel write the syntax for you:

1. Click the cell where you want the linked value
2. Type = (or start your formula: e.g., =SUM()
3. Click the sheet tab of the source sheet
4. Click the source cell (or drag to select a range)
5. Press Enter — Excel inserts the correct cross-sheet reference automatically

Common Cross-Sheet Reference Examples

Absolute References in Cross-Sheet Formulas

The same absolute/relative reference rules from Module 17 apply fully to cross-sheet references. If you copy a cross-sheet formula down a column:

28.2 3D References — The Same Cell Across Multiple Sheets

A 3D reference performs a calculation across the same cell or range on multiple consecutive sheets simultaneously. This is the most efficient way to consolidate monthly sheets, regional sheets, or department sheets into a single summary.

3D Reference Syntax

Rules for 3D References

• The sheets must be consecutive in the sheet tab order — the formula includes all sheets between FirstSheet and LastSheet (inclusive)
• If you insert a new sheet between FirstSheet and LastSheet, the 3D formula automatically includes it
• If you move a sheet outside the range, it is automatically excluded
• Supported functions: SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, VAR, and others. NOT supported by VLOOKUP, INDEX, MATCH, or most other functions.

3D Reference Example — Monthly Consolidation

Creating a 3D Reference by Pointing

1. In the Summary sheet, type =SUM(
2. Click the first sheet tab (Jan)
3. Hold Shift and click the last sheet tab (Dec)
4. Click the cell to reference (B5)
5. Type ) then press Enter
6. Excel inserts =SUM(Jan:Dec!B5) automatically

28.3 Linking to External Workbooks

An external reference (also called an external link) reads a value from a cell in a different workbook file. This connects separate files so that data entered in one flows automatically into another.

External Reference Syntax

Creating an External Link by Pointing

1. Open both workbooks (the source and destination)
2. In the destination workbook, click the cell where you want the link
3. Type =
4. Switch to the source workbook (View → Switch Windows, or click its taskbar button)
5. Click the source cell
6. Press Enter — Excel creates the external reference and returns to the destination workbook

Updating External Links

• When you open a workbook with external links, Excel shows a security bar asking whether to update links: click Update to fetch current values from the source files
• If source files have moved or been renamed, the links break and return #REF! errors
• To manage links: Data tab → Queries & Connections → Edit Links

Edit Links Dialog

28.4 Consolidating Data from Multiple Sheets

Excel's Consolidate feature combines data from multiple ranges (on the same or different sheets) into a single summary — summing, averaging, or counting matching rows by label or by position.

Using the Consolidate Feature

1. On the summary sheet, click the top-left cell of where the consolidated data should appear
2. Data tab → Data Tools → Consolidate
3. In the Consolidate dialog:
   • Function: choose Sum, Average, Count, Max, Min, etc.
   • Reference: type or select each source range → click Add for each one
   • Use labels in: Top row (if ranges have column headers), Left column (if ranges have row labels), or both
   • Create links to source data: tick this to create live linked formulas (instead of static values) — updates automatically when source data changes
4. Click OK

Consolidate vs 3D Reference

28.5 Hyperlinks — Clickable Navigation

A hyperlink in Excel is a clickable cell or object that navigates to a location — another cell, another sheet, another file, a website, or an email address — with a single click. Hyperlinks are the navigation system of multi-sheet workbooks and dashboards.

Types of Hyperlinks

28.6 Inserting Hyperlinks

Method 1 — Insert Hyperlink Dialog

1. Click the cell where you want the hyperlink (or select a shape/image)
2. Insert tab → Links group → Link — or — right-click → Link… — or — Ctrl+K
3. The Insert Hyperlink dialog opens with the four link types on the left
4. Set the display text in "Text to display" — this is what the user sees in the cell
5. Configure the destination based on link type (see below)
6. Click OK

Configuring "Place in This Document" (Sheet Navigation)

1. In the Insert Hyperlink dialog: click Place in This Document
2. The panel shows all sheet names in the workbook
3. Click the target sheet name
4. In "Type the cell reference": enter the cell to navigate to (e.g., A1 to jump to the top of the sheet)
5. Or: use a Named Range from the list for more robust navigation (named ranges survive sheet reorganisation better than cell addresses)
6. Click OK

Configuring "Existing File or Web Page"

1. Click Existing File or Web Page
2. Browse to a file — or — type or paste a URL in the Address box
3. Use the ScreenTip… button to add hover tooltip text (e.g., "Click to open the Policy PDF")
4. Click OK

Configuring "Email Address"

1. Click E-mail Address
2. Enter the email address (e.g., hr@company.co.za)
3. Optionally enter a default Subject line (e.g., "Leave Request — [Your Name]")
4. The display text auto-fills as mailto:hr@company.co.za — change it to something friendlier (e.g., "Email HR")
5. Click OK

Method 2 — HYPERLINK Function

The HYPERLINK function creates a clickable link using a formula, which allows the destination to be dynamic (calculated from other cells):

Hyperlinks on Shapes and Images

Hyperlinks can be attached to any shape, button, or image for a more visual dashboard navigation experience:

1. Insert → Shapes → draw a shape (e.g., a Rounded Rectangle) → type button text (e.g., "⬅ Back to Dashboard")
2. Right-click the shape → Link… (or Ctrl+K)
3. Configure the hyperlink destination as normal
4. Click OK — the shape becomes a clickable navigation button

28.7 Editing, Managing & Removing Hyperlinks

Editing an Existing Hyperlink

• Right-click the hyperlink cell → Edit Hyperlink… — or — select the cell and press Ctrl+K
• Change the display text, destination, or ScreenTip → OK

Selecting a Hyperlink Cell Without Activating It

• Click and hold the mouse button briefly before releasing — or —
• Navigate to the cell using the arrow keys (keyboard navigation never triggers hyperlinks)

Removing a Hyperlink

• Right-click the hyperlink cell → Remove Hyperlink — removes the link but keeps the cell text
• To also remove the text: press Delete after removing the hyperlink
• To remove hyperlinks from multiple cells at once: select all cells → right-click → Remove Hyperlink

Hyperlink Appearance

• Hyperlinks display in blue underlined text by default (the "Hyperlink" cell style)
• After clicking, they turn purple (the "Followed Hyperlink" cell style)
• To change hyperlink colour: Home → Cell Styles → right-click Hyperlink → Modify → Format → Font colour
• To remove underline: same process, uncheck Underline in Font formatting

28.8 Practical SA Linking & Hyperlink Scenarios

Scenario 1: Multi-Sheet Annual Report with a Navigation Index

Scenario 2: Regional Budget Consolidation

Scenario 3: Supplier Contact List with One-Click Email

28.9 Quick Self-Check

Q1: You need a formula on the Summary sheet that totals cell B10 from every sheet named Jan through Dec. Write the formula and explain how new sheets are handled.

✓ =SUM(Jan:Dec!B10). This 3D reference sums cell B10 across every sheet from Jan to Dec (inclusive) in the sheet tab order. If a new sheet is inserted between Jan and Dec (e.g., a "JanRevised" sheet inserted between Jan and Feb), Excel automatically includes it in the 3D sum — no formula change needed. If a sheet is moved outside the Jan–Dec range (e.g., Dec is dragged to after the Summary tab), it is automatically excluded from the calculation. This makes 3D references self-maintaining as long as sheets remain within the defined tab range. Note: the Summary sheet itself must be outside the Jan:Dec range to avoid circular references.

Q2: A workbook contains external links to Budget.xlsx. You receive an email saying Budget.xlsx has been moved to a new network folder. The workbook now shows #REF! errors in the linked cells. How do you fix this?

✓ Data tab → Queries & Connections → Edit Links → select the broken link to Budget.xlsx → click Change Source… → browse to the new location of Budget.xlsx → select it → OK. Excel updates all formulas referencing that workbook to the new path, and the #REF! errors resolve immediately. If the file has not just moved but also been renamed, select the new file by its new name in the Change Source dialog. After fixing: click Check Status to confirm the link is showing "OK" before closing the dialog.

Q3: Write a HYPERLINK formula that creates a dynamic clickable link. The sheet name is in cell A2. Clicking the link should navigate to cell A1 on whatever sheet is named in A2. If A2 contains "March", the link should say "Go to March" and navigate to March!A1.

✓ =HYPERLINK("#'"&A2&"'!A1","Go to "&A2). Breaking this down: the # prefix tells Excel this is an internal document link. The single quotes around the sheet name (''" & A2 & "'') handle sheet names that may contain spaces. !A1 sets the destination cell. "Go to "&A2 builds the display text dynamically (e.g., "Go to March"). If A2 = "March", the formula evaluates to =HYPERLINK("#'March'!A1","Go to March"). Copy this formula down a column paired with a list of sheet names to create a complete dynamic table of contents that always reflects the current sheet names.

Q4: What happens when you use "Break Link" in the Edit Links dialog, and when is it appropriate to use it?

✓ Break Link permanently replaces all external reference formulas with their last calculated static values. For example, =[Budget.xlsx]Summary!$B$5 which was returning 250000 becomes the plain value 250000 with no formula. This cannot be undone. The live connection to the source workbook is severed. Appropriate situations: (1) Before distributing a workbook to an external party (client, auditor, SARS) who does not have access to the source files — without breaking links, they see #REF! errors or prompts to update files they cannot access. (2) When the source workbook is being retired or archived and the values should be preserved as a historical snapshot. (3) When the linked workbook is on a network drive that is being decommissioned. Always save a copy of the workbook before breaking links, as the operation is irreversible.

Q5: You want to add a "Back to Dashboard" navigation button on each report sheet. Describe how to create this button using a shape, and why using a shape is better than a hyperlink in a cell for this purpose.

✓ Insert → Shapes → choose Rounded Rectangle (or any shape) → draw it in a consistent position on the sheet (e.g., top-right corner) → type "◁ Back to Dashboard" inside the shape → format with a colour matching the dashboard theme. Then: right-click the shape → Link (or Ctrl+K) → Place in This Document → select "Dashboard" sheet → Cell reference: A1 → OK. A shape hyperlink is better than a cell hyperlink for navigation buttons because: (1) it looks like a button, providing a clear visual cue to the user; (2) it can be positioned precisely anywhere on the sheet regardless of column/row structure; (3) it can be formatted with any colour, border, and text style; (4) it does not clutter the spreadsheet's cell data; (5) it can be resized proportionally without affecting any cell content. Copy the shape to each report sheet using Ctrl+C → navigate to the target sheet → Ctrl+V to paste the same button consistently across all sheets.

Q6: You have a supplier contact list with supplier names in column A and email addresses in column B. Write a HYPERLINK formula in column C that creates a clickable email link for each supplier, with the subject line pre-filled as "Purchase Order Request".

✓ In C2 (copy down): =HYPERLINK("mailto:"&B2&"?subject=Purchase%20Order%20Request","Email "&A2). The mailto: prefix creates an email link. &B2 appends the email address from column B. ?subject= adds a pre-filled subject line. "Purchase%20Order%20Request" is the subject with spaces encoded as %20 (URL encoding required for the subject parameter). "Email "&A2 creates the display text showing "Email [Supplier Name]". Clicking C2 opens the default email client (Outlook, Gmail web, etc.) with the To: field pre-filled with the supplier's email and the Subject: field pre-filled with "Purchase Order Request" — ready to compose and send without any manual entry.

✓ Module 28 Complete — You Have Learned:

• Cross-sheet references — syntax (=SheetName!CellAddress); single quotes required for sheet names with spaces (='Sheet Name'!A1); creating by pointing (type =, click sheet tab, click cell, Enter); 6 practical formula examples; absolute vs relative references apply normally to cross-sheet references
• 3D references — syntax (=SUM(Jan:Dec!B5)); calculates across consecutive sheets simultaneously; auto-includes inserted sheets, auto-excludes moved sheets; supported functions (SUM, AVERAGE, COUNT, MAX, MIN); creating by pointing (click first tab, Shift+click last tab, click cell); monthly consolidation example with SA regions
• External workbook links — syntax when open ([File.xlsx]Sheet!$A$1) vs closed (full path); creating by pointing across open workbooks; update prompt on open; Edit Links dialog (Update Values, Change Source, Open Source, Break Link, Check Status, Startup Prompt); Open vs Closed format conversion; breaking links before distribution (irreversible — save copy first)
• Consolidate feature — Data → Consolidate; Function, Reference, Add, Use labels (Top row / Left column), Create links; Consolidate vs 3D reference comparison (label matching, external workbooks, live vs static)
• Hyperlink types — 4 types (Place in This Document, Existing File or Web Page, Create New Document, Email Address); practical SA use cases for each
• Inserting hyperlinks — Insert → Link or Ctrl+K; Insert Hyperlink dialog (Text to display, ScreenTip); configuring Place in This Document (sheet + cell, or Named Range); configuring URL links; configuring Email Address (mailto: + ?subject=); hyperlinks on shapes and images for visual navigation buttons
• HYPERLINK function — =HYPERLINK(link_location, friendly_name); # prefix for internal links; dynamic links built from cell values; email links with URL-encoded subjects; table of contents with dynamic sheet names
• Managing hyperlinks — right-click → Edit Hyperlink (Ctrl+K); selecting without activating (click-hold or arrow keys); removing (right-click → Remove Hyperlink); changing appearance via Cell Styles → Hyperlink → Modify
• SA scenarios — navigation index with month links and Back buttons; regional budget consolidation with external links and break-before-distribute workflow; supplier contact list with dynamic one-click mailto links including pre-filled subjects