5.4 Workbook hygiene and handing a file over

What you'll be able to do

Hand a workbook over so that its next owner understands it without phoning you. Documented, tidy, cleared of personal traces, and not mysteriously enormous.

Before you start

Pick the most important real workbook you own. Practising this on a toy teaches nothing.

The work

1. Same either way: add a documentation sheet. Make it the first sheet and name it README. Write what the workbook is for, where the data comes from, and who owns it. Write how to refresh it, in exact steps, even if that is "press refresh and Power Query does the rest". Write the assumptions a stranger could not guess, such as a financial year running April to March, or amounts that exclude tax. Fifteen minutes here is the difference between a useful file and a riddle.
2. Same either way: name things as though you mean it. Sheets called Data, Calc and Dashboard, not Sheet1 and Sheet7. Rename them from the Home → Format menu or by double-clicking the sheet tab. Give Tables and queries real names, and name the cells that hold constants. Names are free documentation, and formulas inherit them, so =Amount*TaxRate explains itself.
3. Same either way: delete the leftovers. The "Copy of Sheet2 (3)" experiments, the dead scratch columns, the formatting that stretches to row 40,000. A workbook you hand over holds what it needs and nothing else, because every stray sheet is a question its next owner has to investigate.
4. Same either way: bring the hidden into view. Unhide every sheet, row and column (Shift+F10 on the sheet tabs → Unhide) and decide about each one on purpose: keep it visible, or delete it. Hidden content that matters gets lost, and hidden content that does not is clutter with a cover on. Very hidden sheets and protected structure belong only where the README says so, and says why.
5. No documented keyboard shortcut for this — use the menu: before the file leaves your machine, go to File → Info → Check for Issues → Inspect Document. The Document Inspector finds what the sheet does not show: author names and other personal details, comments, hidden sheets and hidden rows. Remove whatever the recipient has no business receiving. A file sent outside your organisation carries everything you did not strip out.
6. Same either way: when a file is mysteriously huge, hunt in this order. First the used range. Press Ctrl+End on each sheet. Landing at XFD1048576 means formatting stretches far past the data, so delete the empty rows and columns below it and save. Second, images that were never compressed, which Picture Format → Compress Pictures fixes, as the PowerPoint module taught. Third, data copied across several sheets that one query should supply once. Most 60 MB workbooks are a 2 MB workbook plus one of those three.

Try it

Run the full checklist on your real workbook. README written, everything renamed, strays deleted, hidden content decided on, Document Inspector run and cleaned, and Ctrl+End checked on each sheet. Then the test: a colleague opens it cold and explains back what it does and how to refresh it.

Check yourself