Day 29: Excel Best Practices & Professional Standards

Separate sheets by purpose: (1) Inputs/Assumptions — all changeable parameters in one place, clearly labelled, with units and descriptions. (2) Data — raw data tables, no calculations mixed in. (3) Calculations — formulas only, reference inputs and data, no hard-coded values. (4) Outputs — summary tables, charts, and the final report view for stakeholders. This separation makes the model auditable, reduces errors, and makes it easy for someone else to update inputs without touching the formulas.

Top mistakes: (1) Hard-coded values inside formulas (=A1*0.18 instead of =A1*TaxRate). (2) Mixing inputs with calculations in the same cell. (3) Not converting data to Tables before analysis — breaks Pivot Table refresh. (4) VLOOKUP with column index numbers that break when columns are inserted. (5) SUM over a range that includes a text-formatted number column — gives wrong total silently. (6) Using percentage formatting on a cell that already contains a percentage value (0.18 formatted as %) shows 18% correctly; 18 formatted as % shows 1800%). (7) Not saving with a version history for important shared workbooks.

Documentation approach: (1) Create a "README" sheet as the first tab — explains the purpose, how to use it, what inputs to change, refresh procedure, and key assumptions. (2) Name all Sheets, Tables, and named ranges descriptively. (3) Add cell comments (Insert → Comment/Note) to explain non-obvious assumptions or calculation logic. (4) Color-code: blue for inputs, white for calculations, grey for static reference data (no hard rule, but be consistent). (5) Lock formula cells, unlock input cells. (6) Add a "Last Updated" cell with =TODAY() so the recipient knows the data freshness.

Verification checklist: (1) Check totals against known benchmarks (last month's confirmed report). (2) Sample 10–20 random rows and verify formula output manually for each. (3) Check edge cases: what happens in the first row, last row, and rows with null values? (4) Check for silent errors — does SUM match manual addition? Does VLOOKUP return plausible values? (5) Use Evaluate Formula on complex formulas to step through logic. (6) Create a validation sheet with sanity checks: total rows should match source, grand total should match expected range.

For individual work: (1) File naming with date: RevenueReport_2026-08-01_v1.xlsx, never "final" or "final_final". (2) Save versions before major changes. (3) Excel's AutoSave versioning in OneDrive/SharePoint allows recovering prior versions. For team workbooks: (4) OneDrive or SharePoint with version history enabled — right-click the file to see and restore earlier versions. (5) For models that change frequently, some teams export to CSV and track changes in Git. (6) Never overwrite the only copy of a file — always work on a copy when making significant changes.