Up through Associate, every workbook was self-contained. You opened a file; everything you needed was inside it. That model works for solo projects — a budget, a tracker, a small dataset. It does not match how Excel actually gets used in workplaces.

In real companies, data lives across many files. Marketing maintains its own spend tracker. Engineering owns its headcount model. Finance pulls from both into a single roll-up dashboard. The files cross departments, computers, and time zones. Excel handles this with external references — formulas that pull values from other workbooks, refreshed on demand.

That capability comes with new failure modes the Associate exam doesn't test. Files move. Folders get renamed. Source workbooks close. Trust Center settings vary by machine. AutoSave saves a file you wish it hadn't. Each failure has a recovery path; learning those paths is most of this week.

By Friday, you will have built a Multi-Workbook Budget Roll-up — a master workbook that aggregates data from four department files into a single dashboard. The Roll-up becomes your reference exemplar for every multi-file project later in this course.

An external reference (also called a link) is a formula in one workbook that reads a value from a different workbook. The destination workbook holds the formula; the source workbook holds the data. When the source changes, the destination updates — automatically (when both are open) or on demand (when reopened).

The Syntax — Both Files Open

When both the source and destination workbooks are open in the same Excel session, the formula looks compact:

Breaking down each piece:

• The single quotes '…' wrap the filename + sheet so spaces and special characters work
• The square brackets […] contain the source workbook's filename, including the extension
• The sheet name follows the closing bracket, still inside the quotes
• An exclamation mark ! separates sheet from cell reference
• The cell address $B$5 — typically absolute (with dollar signs) so it doesn't shift if you copy the formula

The Syntax — Source File Closed

When you close the source workbook, Excel rewrites the formula to include the full path to the file so the link still works:

Notice: the formula didn't change behavior, only appearance. Open the source file again, and Excel collapses the path back to just [Marketing.xlsx]. The link is fundamentally the same object — Excel just shows you more or less of it depending on whether the source is reachable.

How to Create One — Two Methods

Method 1: Point-and-click (recommended for your first few). With both workbooks open, in the destination type =, then switch to the source workbook, click the cell you want, press Enter. Excel writes the syntax for you.

Method 2: Type by hand. Slower at first but unavoidable when you need to edit an existing link. Useful once you can read the syntax fluently.

When you open a workbook that contains external references, Excel checks whether the source files are still where they were. If so, Excel asks: do you want to update the links to the most current values from the sources?

Your Three Options

Once a workbook has external references, the Edit Links dialog is mission control. Every link in the workbook appears in a list with its current status; from there you can update, redirect, open the source, or break the link entirely.

(In some Excel builds it's Data → Connections → Edit Links. The button is grayed out if the workbook has no external references — which is itself a useful diagnostic.)

The Six Buttons

Reading the Status Column

• OK — source file located; last refresh succeeded
• Source open — source workbook is currently open in another window; values are live
• Source not found — Excel couldn't locate the file at its recorded path. Use Change Source to point it somewhere new, or open the source manually so Excel can see it.
• Warning: Values not updated — source was found but couldn't be read. Often a permissions issue or a corrupt source workbook. Check Status again after addressing.

AutoSave was a convenience in Associate. With external references, it becomes a safety net. A linked workbook can hit a confusing state — links suddenly broken, values changed unexpectedly, a formula overwritten — and the path back is Version History, not Ctrl+Z.

AutoSave Requirements

AutoSave only works when the file is stored in OneDrive or SharePoint. A workbook saved on the local C: drive cannot AutoSave — the toggle at the top of the window will be grayed out or show "Off." Move the file to OneDrive (File → Save As → OneDrive) and AutoSave becomes available.

Version History

Version History lists every saved revision of the file (typically dozens to hundreds for an active document). Click any version to open it as a read-only copy in a new window. From there you can copy formulas or values back to your live working file, or restore the entire version as the current state.

External content (linked workbooks, macros, web queries, ActiveX controls) is a security surface. A malicious linked file could pull data from somewhere unexpected; a malicious macro could do almost anything. The Trust Center is where you control how Excel handles each kind of external content.

The Three Settings for Workbook Links

You won't write macros this week; that comes in Week 5. But sub-objective 1.1.1 shows up in MO-211 Domain 1, so a quick orientation is worth it now:

• Macros live in modules inside a workbook's VBA project, opened with Alt+F11
• To copy a macro from workbook A to workbook B: open both, press Alt+F11, and in the Project Explorer pane, drag the module from A's project to B's project
• The destination workbook must be saved as .xlsm (Macro-Enabled Workbook) — copying a macro into an .xlsx destination silently fails on save

That's the entirety of the Week 1 macro knowledge required. Week 5 dives deep into recording, editing, debugging, and assigning macros.

Ten core MO-211 Domain 1.1 terms. Click each card to reveal its definition.

Six recognition checks. Click your answer; instant feedback.