It’s not really possible to string together a more painfully boring term than “spreadsheet auditing”. Auditing is a painstaking activity that requires intense concentration, an obsessive attention to detail, and its only potential reward is that you find a mistake in the model that can embarrass you or a colleague, or make you or a colleague look careless, whether you or they were the original author of the spreadsheet or not. If you’re the “owner” of the spreadsheet, it’s on you, friend.
The conventional wisdom is that 95% of spreadsheets have at least one error in them. That’s pretty scary. The severity of the error can be anything from harmless to high-impact.
What is spreadsheet auditing?
Simply put, it’s checking every single cell in your spreadsheet to make sure that it’s doing what you want and expect it to do. Yes, it’s basically a modern form of torture. That said, it can save you from making big mistakes or presenting faulty analyses to potential stakeholders.
How do you audit a spreadsheet properly and efficiently?
(1) Unhide all columns (Select All, right-click on column header, select Unhide)
and unhide all rows (same process as for columns, but right-click on the row header instead of the column header)
(2) Ungroup any grouped rows and columns (click the number 2)
(3) Unhide all worksheets (right-click on any worksheet tab at the bottom and select Unhide Sheets — unfortunately each sheet needs to be unhidden individually)
(4) Systematically go tab by tab, Select All and “paint” the sheet a dark color and then visually inspect to ensure no cells contain inputs or outputs where the font was painted white, therefore making those cells not visible when the sheet is in its default white background state. When done inspecting, undo the painting with Ctrl+Z.
(5) Ensure all values formatted as inputs are in fact inputs (F5 key, then select Special, then set the settings shown below and Click OK, and all constants of that type will be highlighted simultaneously. Then do Ctrl+~ to reveal the sheet cell contents to make sure those constants are inputs and not formula-based.)
(6) Ensure all text labels are consistent with the associated data/calculations
(7) Ensure all calculations in every single cell are correct — nothing is missing, nothing extraneous is present, and the calculation methodology is consistent with convention for the property type and realities of its operation and financing
(8) Systematically make changes to each input, one at a time, to make sure that changing the inputs up, down, and to 0 make the outputs behave as you would expect them to
(9) Enable Error Checking to flag any instances of error types you wish to reveal (File > Options > Formulas)
and then use the Error Checking tool (Formulas > Error Checking).
Happy auditing!
(Incidentally, REFM offers this as a service — it can be some of the best money you ever spend. Contact [email protected] if you would like to learn more.)
