Concept
1. Protection is about accidents, not security
Excel sheet and workbook passwords stop people from accidentally typing over formulas or deleting sheets. They are not security: they can be removed with free tools in minutes. Never rely on them to hide confidential data — remove the data from the file or use proper access control (SharePoint permissions, separate files).
2. Unlock what users may change
All cells are "Locked" by default; locking only takes effect when the sheet is protected.
- Select input cells (dropdown cells, linked cells of controls if on the same sheet) → Ctrl + 1 → Protection → untick Locked.
- For slicers/timelines: right-click → Size and Properties → Properties → untick Locked.
- For form controls: Format Control → Protection → untick Locked.
3. Protect the dashboard sheet
Review → Protect Sheet → allow:
- ☑ Select unlocked cells (and optionally locked cells)
- ☑ Use PivotTable & PivotChart (needed for slicers to filter pivots)
- ☑ Use AutoFilter if viewers filter tables
- ☐ Format cells / columns / rows, ☐ Insert/Delete rows, ☐ Edit objects (unless needed)
Test every slicer, dropdown, checkbox and button after protecting.
Pivots and protection: a slicer can filter a pivot on a protected sheet only if "Use PivotTable & PivotChart" is allowed; Refresh of pivots on a protected sheet fails — keep pivots on an unprotected (but hidden) PVT sheet, or unprotect/refresh/protect with a short macro.
4. Hide helper sheets
- Right-click tab → Hide for CALC, PVT, RAW (viewers can still Unhide).
- Very hidden (doesn't appear in the Unhide list): VBA editor (Alt + F11) → select the sheet → Properties (F4) → Visible = 2 - xlSheetVeryHidden. Use for technical sheets; note it on README so the next builder can find them.
5. Lock the workbook structure
Review → Protect Workbook → Structure. Now nobody can add, delete, rename, move, hide or unhide sheets. Combined with hidden helper sheets, this keeps the 3-layer design intact.
6. Keep a builder's way in
- Store the passwords in your team's password manager (not in the file, not in an email).
- A tiny "Builder mode" macro or documented steps: unprotect workbook → unhide sheets → unprotect DASH.
- Never protect the RAW sheets that Power Query writes to — refresh fails on protected sheets.
Common mistakes
Protecting the sheet and breaking slicers (Locked or pivot permission). Protecting sheets that queries load into. Using passwords as data security. Forgetting the password (keep it in a password manager).