Lesson 1, Topic 1
In Progress

Week 1 – Practice

Week 1 — Practice | The Linking Lab | MO-211 Expert
Week 1 of 9 · Practice · Framework Formative

Practice — The Linking Lab

Five Exercises That Build Toward the Roll-up

This is the framework formative for Week 1. Rather than locating commands one by one (you're past that), you'll do five short investigations: create a link, watch the Update Links prompt, deliberately break a link, repair it, and tour the Trust Center. Each exercise teaches a behavior the MO-211 exam tests directly — and each prepares you for one piece of Friday's Build project.

You'll record what you observe in a single workbook called the Linking Lab, which becomes Deliverable 1.

📘 Why this format: Sub-objective 1.1.2 can't be tested by recall alone — the exam asks you to do things to a workbook. These exercises rehearse the doing. By Friday you'll have repeated each motion 2–3 times, which is what muscle memory needs.

📦 Starter Files Needed

Your teacher provides two small starter workbooks:

budget-marketing.xlsx — a 2-sheet workbook with a Budget sheet (B5 contains a number like 42,500)

budget-engineering.xlsx — same structure, different number

Save both to a local folder you'll remember. You'll need to rename one of them mid-lab, so don't put them in OneDrive yet.

Setup — Create the Linking Lab Workbook

  1. Create a new blank workbook. Save it immediately as lastname-firstname_week01_linking-lab.xlsx in the same folder as the two starter files.
  2. Set Workbook Properties (File → Info → Properties → Show All Properties). Title: "Week 1 Linking Lab"; Author: your name. Same habit as Associate Week 1 — don't break it now.
  3. Add four worksheets and rename the tabs by double-clicking each tab name:
    • Roll-up — where your external reference formula will live
    • Observations — where you record what each exercise showed you
    • Edit Links Notes — short notes from Exercise D
    • Trust Center Notes — short notes from Exercise E
  4. Color the tabs (right-click → Tab Color). Carryover habit from Associate; helps the lab look organized.
  5. On the Observations sheet, in cell A1 type: "Exercise observations". In row 3 build column headers across A3:C3: Exercise | What I did | What I observed. Bold row 3 with a fill color. Freeze Top Row.

Exercise A — Create Your First External Reference

Lab A

Make Roll-up!B2 read the value of Marketing's Budget!B5

Goal: produce a correctly written external reference using the point-and-click method, then read it in the Formula Bar.

  1. Open all three workbooks: your linking-lab.xlsx, budget-marketing.xlsx, and budget-engineering.xlsx. Use the View → Window → Switch Windows menu to move between them.
  2. In linking-lab.xlsx, on the Roll-up sheet, click cell B2. Type = (just the equals sign).
  3. Switch to budget-marketing.xlsx (View → Switch Windows → budget-marketing.xlsx, or click it in the taskbar). Click cell B5 of the Budget sheet.
  4. Press Enter. Excel jumps back to linking-lab.xlsx with cell B2 now showing the value from Marketing's B5.
  5. Click B2 again. Look at the Formula Bar. You should see something like:
    ='[budget-marketing.xlsx]Budget'!$B$5
  6. Repeat for Roll-up!B3, this time pointing at Engineering's Budget!B5.
  7. In Roll-up!B5, type =B2+B3 (a regular formula, not external). This is your roll-up total.
  8. Save. Press Ctrl+S.

📝 Record on the Observations sheet (one row)

  • Exercise A — Created two external references; total in B5
  • What I observed: write one sentence about what the Formula Bar showed for B2 (with the source open)

Exercise B — Trigger the Update Links Prompt

Lab B

Close the sources; reopen the master; observe the prompt

Goal: see what changes in the Formula Bar when the source is closed, and respond to the Update Links prompt.

  1. Save and close all three workbooks. File → Close, or click the X on each window.
  2. Reopen ONLY linking-lab.xlsx. Do not open the budget files yet.
  3. Watch for the prompt. Depending on your Trust Center setting, you'll see one of two things:
    • The yellow "Security Warning" bar with an Enable Content button (Trust Center is "Disable automatic update")
    • A dialog asking "This workbook contains links to external sources..." with Update / Don't Update buttons (Trust Center is "Prompt user" — the default)
  4. Choose Don't Update (or just close the bar without enabling). Open the Roll-up sheet. Click B2. Look at the Formula Bar.
  5. You should now see the FULL PATH version of the formula, something like:
    ='C:\Users\...\[budget-marketing.xlsx]Budget'!$B$5
    Same link. Different display, because the source is closed.
  6. Now open budget-marketing.xlsx (File → Open). Return to linking-lab.xlsx. Click B2 again. The path collapses back to ='[budget-marketing.xlsx]Budget'!$B$5.

📝 Record on the Observations sheet

  • Exercise B — Closed sources, reopened master, observed prompt
  • What I observed: which prompt appeared (Security Warning bar vs Update Links dialog)? What does that tell me about my Trust Center setting?

Exercise C — Break a Link on Purpose, Then Repair It

Lab C

Rename a source file and use Edit Links → Change Source to fix it

Goal: experience a broken link, see Edit Links report the failure, and repair using Change Source.

  1. Close all workbooks.
  2. In File Explorer (not Excel), navigate to the folder with your three files. Rename budget-marketing.xlsx to budget-marketing-RENAMED.xlsx. (Right-click → Rename.) Leave the other files alone.
  3. Reopen linking-lab.xlsx. Choose Update if prompted. Cell B2 should show #REF! or a similar error — Excel couldn't find the source file. (Engineering's reference in B3 still works.)
  4. Open Edit Links: Data → Queries & Connections → Edit Links.
  5. Click on budget-marketing.xlsx in the list. Status column should read "Source not found." Click Change Source...
  6. In the file picker, navigate to your renamed file (budget-marketing-RENAMED.xlsx) and select it. Click OK.
  7. Click Check Status. Both links should now show OK. Cell B2 displays its value again.
  8. Save.
⚠️ Don't click Break Link! That would convert your B2 formula to a static value — you'd lose the dynamic link entirely. Change Source is what you want for "the file moved or was renamed." Break Link is for "I no longer want this to update, ever."

📝 Record on the Observations sheet

  • Exercise C — Renamed source, saw #REF!, repaired with Change Source
  • What I observed: how did the broken cell display before vs after the repair?

Exercise D — Tour the Edit Links Buttons

Lab D

Try each button (except Break Link) and note what it does

Goal: develop fluency with every button in the Edit Links dialog before the exam puts you in front of it under time pressure.

  1. With linking-lab.xlsx open, open Edit Links (Data → Queries & Connections → Edit Links).
  2. Click Update Values. Watch the Status column refresh. (Visual signal: nothing changes if values were already current.) Record: this is the button you use to "refresh now without reopening the file."
  3. Click Open Source. Excel opens the source workbook in a new window. Close that window to return to Edit Links.
  4. Click Check Status. Excel verifies each source. Useful when you suspect a source has moved but Excel hasn't noticed yet.
  5. Click Startup Prompt. A small dialog appears with three options for what this workbook should do at open time. Read them; choose "Let users choose to display the alert or not" (the default). Click OK.
  6. Do NOT click Break Link. That's irreversible after save and you'd lose your work.
  7. Close Edit Links.

📝 Record on the Edit Links Notes sheet

  • List each of the six buttons (one per row in column A)
  • In column B, write one sentence in your own words about what each does
  • Highlight Break Link in red fill — visual reminder that it's irreversible

Exercise E — Trust Center External Content Tour

Lab E

Visit the Trust Center, identify your current setting, do NOT change it

Goal: know where the setting lives and what each option does — without changing it (Trust Center changes affect every workbook on this machine).

  1. Open Trust Center: File → Options → Trust Center → Trust Center Settings... → External Content (in the left navigation).
  2. Find the section titled "Security settings for Workbook Links." You'll see three radio buttons. One is selected — that's your current setting.
  3. Note your current setting on the Trust Center Notes sheet (A1: "My current setting is _____").
  4. For each of the three options, write a one-sentence description of when it would be appropriate (A2:A4). Reference the Lesson chunk's table if you need a refresher.
  5. Click Cancel (not OK) to exit Trust Center without changing anything. Click Cancel again to exit Options.
⚠️ Trust Center settings are global. Changing them affects every Excel workbook on your machine — including ones you didn't write. Tour the settings, but don't change them unless you have a specific reason. For this lab, just observe.

📝 Record on the Trust Center Notes sheet

  • A1: My current setting is [X]
  • A2–A4: one sentence for each of the three options (Enable, Prompt user, Disable) — when each is appropriate

Submit Your Linking Lab

📤 How to Submit

Filename: lastname-firstname_week01_linking-lab.xlsx

Where to submit: Your LMS assignment area. Upload the .xlsx file to the Week 1 — Linking Lab assignment.

What your teacher will look at:

  • Roll-up sheet has working external references (B2, B3) and a total (B5)
  • Formula Bar inspection of B2/B3 confirms they're real external refs, not pasted values
  • Observations sheet has five rows (one per exercise) with both columns filled
  • Edit Links Notes sheet lists all six buttons with descriptions; Break Link visibly flagged
  • Trust Center Notes sheet identifies the student's current setting and describes the three options
  • Workbook Properties show your name as Author
  • Filename matches the convention
📌 What this practice unlocks for Build: the Multi-Workbook Budget Roll-up (Build chunk) is exactly Exercise A scaled up — four sources instead of one, with a dashboard and a PDF export. Every motion you rehearsed here repeats in the Build, just at scale. If any of the five exercises felt shaky, run them again before Friday.