Protecting, printing and sharing workbooks

Advanced

Introduction

A workbook that other people will use needs finishing: the formulas must not be overwritten by accident, private figures must stay private, printouts must be readable, and colleagues need the latest version rather than copies attached to emails. This last lesson covers protection, printing and sharing, and gives an honest overview of automation with macros and Office Scripts.

How it works

Locking and protecting. Every cell is locked by default, but locking does nothing until the sheet is protected. So first select the input cells users should edit, open Format Cells (Ctrl+1), and on the Protection tab clear Locked. Then choose Review, Protect Sheet, optionally with a password, and tick what users may still do, such as select cells, sort or use filters. Now the inputs can be typed in, and every formula cell is safe. Review, Protect Workbook protects the structure: sheets cannot be added, deleted, renamed or unhidden.

These protections stop accidents, not determined people: their passwords are not strong security. To keep a file private, use File, Info, Protect Workbook, Encrypt with Password: the file is then encrypted and cannot be opened without the password. If that password is lost, the file cannot be recovered, so store it safely.

Printing. Excel sheets are often wider than a page. In Page Layout:

  • Print Area sets exactly which range prints.
  • Print Titles repeats rows (such as the header row) or columns on every page.
  • Scale to Fit: set Width to 1 page and Height to Automatic to fit all columns across one page width.
  • Orientation, margins and headers and footers with page numbers finish it.

Check everything in File, Print, the preview, or in Page Break Preview on the View tab, where you can drag the page breaks.

Sharing. Save the workbook to OneDrive or SharePoint, then use Share to invite people or create a link that can view or edit. With AutoSave on, several people can co-author: they edit at the same time and see each other's changes. Comments (threaded, with @mentions) carry discussions; notes are the older yellow sticky notes. Version History restores earlier versions.

Automation. Macros are programs in VBA (Visual Basic for Applications), recorded with the macro recorder or written in the editor (Alt+F11 on Windows). They run in desktop Excel on Windows and Mac, need the .xlsm format, and do not run in Excel for the web. Macros from unknown sources can be dangerous; Excel blocks them in files from the internet by default. Office Scripts, written in TypeScript, automate Excel for the web in many business and education Microsoft 365 plans, and can be run from Power Automate.

Example

A price calculator has inputs in B1:B3 and formulas in B5:B8 that must not be changed.

AB
1Quantity10
2Unit price250
3Discount0.05
5Total=B1*B2*(1-B3)

Line by line

  1. Select B1:B3, press Ctrl+1, open the Protection tab and clear Locked.
  2. Review, Protect Sheet, keep "Select unlocked cells" and "Select locked cells" ticked, and click OK (a password is optional).
  3. Type 20 in B1, then try to type in B5.
  4. Page Layout, Print Titles: set Rows to repeat at top to $1:$1 for longer lists, and Scale to Fit width to 1 page.

Expected result

After step 3, B1 accepts 20 and B5 shows 4,750 (20 x 250 x 0.95), but typing in B5 shows a message that the cell is on a protected sheet. The calculator can be used but not broken. Printed lists repeat their header row on every page and fit the width of the paper.

In the real world

Templates for expense claims, quotes and budgets are protected so that hundreds of people can fill them in without breaking the formulas. Teams co-author shared trackers in OneDrive or SharePoint instead of emailing copies, and finance files with salaries or forecasts are encrypted with a password to open.

Common mistakes

  • Protecting the sheet without unlocking input cells, so nobody can type anything.
  • Relying on sheet protection passwords for confidentiality; they are easy to remove.
  • Losing the password to open, which makes an encrypted file unrecoverable.
  • Emailing copies and ending up with several versions instead of one shared file.

Best practices

  • Colour input cells, unlock exactly those, and protect the rest.
  • Use encryption for confidential workbooks and store the password in a password manager.
  • Share one copy in OneDrive or SharePoint, and use comments for discussion.

Keyboard shortcuts

ActionWindowsMac
Print or print previewCtrl+PCommand+P
Open Format Cells, Protection tabCtrl+1Command+1
Protect Sheet by key tipsAlt, R, P, S—
Insert a noteShift+F2Shift+F2
Open the VBA editorAlt+F11Option+F11
SaveCtrl+SCommand+S

Summary

Unlock input cells and protect the sheet to stop formulas being overwritten, and protect the workbook structure to stop sheets being changed; only a password to open encrypts the contents. Print areas, print titles and scaling make printouts readable. Sharing from OneDrive or SharePoint allows co-authoring with comments and version history, macros automate desktop Excel in .xlsm files, and Office Scripts automate Excel for the web.

Quick check

1. What must you do before protecting a sheet so users can still type inputs?
2. Which protection keeps the contents private from someone with the file?
3. Which setting repeats the header row on every printed page?
4. Where do VBA macros not run?

Key takeaways

  • Unlock input cells, then protect the sheet: users can type inputs but not break formulas.
  • Sheet protection prevents accidents; only a password to open (encryption) keeps the contents private.
  • Set print titles and scaling before printing, and share through OneDrive or SharePoint to co-author.
My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.