Regional workbook: print setup, external links, and structure protection
Your team maintains a workbook named RegionalSales.xlsx with 14 worksheets: a Summary sheet, 12 regional sheets (one per sales region), and a Notes sheet. The regional sheets were created by copying a template, so they share the same layout, and the Summary sheet uses 3D references such as =SUM(East:West!D10). Two regional sheets are hidden because those regions were discontinued. The workbook opens with a security warning because it links to an external price list on a network share that is often offline, and opening now takes over a minute. Only the Summary sheet has a defined print area and landscape orientation. Your manager wants a single PDF containing every visible regional sheet, with row 1 repeated at the top of each printed page and each sheet scaled to one page wide, while the Summary sheet must keep its own landscape layout and scaling. A colleague also needs to enter data in the regional sheets but must not be able to delete or rename worksheets.
1. How should you apply the repeated header row and one-page-wide scaling to the 12 regional sheets without changing the Summary sheet's page setup? Explain the steps and the risk.
Read the worked answer
Group the 12 regional sheets before changing page setup: click the first regional tab, then Shift-click the last. Confirm the Summary and Notes tabs are not selected; Ctrl-click any accidental selection to remove it. With the group active, go to Page Layout > Print Titles and set Rows to repeat at top to $1:$1, then Page Setup > Fit to 1 page wide by (leave tall blank). Because page setup changes apply to every selected sheet, this avoids repeating the work 12 times. Right-click a selected tab and choose Ungroup Sheets, then set the Summary sheet's orientation and scaling separately. Risk: if Summary is left in the group, it inherits the fit-to-width and print titles, overwriting its landscape layout. Also verify hidden sheets stay hidden; they are not printed in a whole-workbook PDF. Export to PDF and check the page count.
2. The workbook prompts about links and is slow to open. The price list is no longer maintained, and current prices are already stored in column F. What do you do, and what should you check first?
Read the worked answer
First use Data > Edit Links to see the source name and which cells depend on it, and search for the link in formulas (Ctrl+F for '[', or Find All). If column F already stores the current prices and no formulas need live updates, break the link: Edit Links > Break Link. Excel replaces external references with their last cached values, so the numbers remain but stop updating. This is irreversible for those formulas, so save a backup and confirm no other workbook depends on the link. If only a few cells are affected, copy them and use Paste Special > Values instead, which gives more control. Do not simply ignore the prompt: the cached values can be stale and the security warning will keep appearing. After breaking, save and reopen to confirm the warning is gone.
3. The colleague needs to edit cells but must not delete or rename sheets. Which protection option should you use, and why not protect each sheet?
Read the worked answer
Use Review > Protect Workbook and check Structure, not Protect Sheet. Structure protection prevents inserting, deleting, renaming, moving, hiding, or unhiding worksheets and is enforced for the whole workbook with one password. Cell editing remains allowed, which is what the colleague needs. Protecting each sheet would require unlocking every input cell first, and any missed locked cell would block data entry. Before applying protection, make sure all required sheets exist, because you cannot add or delete sheets while structure is protected. Tell the colleague the password or store it securely; Excel protection deters accidental changes but is not encryption and can be bypassed by a determined user. If the workbook contains sensitive data, use file encryption or a protected view/IRM solution instead.