60-Second Skills #10: 3 Proven Ways To Check For Errors In Your Spreadsheet Models

by Bruce Kirsch on August 31, 2012

One of the downsides of working in Excel is that it is 100% faithful in carrying out your instructions perfectly. So this means that when there is a mistake in our model, it is always “operator error”! Unfortunately, while we can make light of errors, they can have serious consequences.

We’ve spent more than our fair share share of time in Excel, and have put together a list of best practices that can help you protect yourself from yourself. Here they are, in no particular order:

1. Verbalize the actions to be performed by the formula. If needed, write or type them out, and evaluate the math of the component parts of the formula.

As silly as it might seem, we have found that it is incredibly helpful to literally “talk out” the actions that you intend the formula to perform. Often times, before we even get to the end of the verbalization, the bug in our formula becomes apparent. Remember, almost every formula can be reduced to these standard components. If talking out the formula doesn’t work, write or type it out. This is a foolproof way to zero in on bugs.

2. Make sure that inputs are in fact inputs and not formulas. Use Ctrl+~ to expose the impostors.

We need to be honest and consistent with our formatting treatment of inputs vs. calculated values. If something is formatted as an input, then that cell should not have a formula in it. It should be a hard-keyed value. The existence of a formula in a supposed input cell could end up causing a lot of problems unbeknownst to us.

3. Trace dependents and precedents with the Audit tools.

The audit (“arrow”) tools are incredibly powerful ways to visualize the interconnected web of formulas in your spreadsheet. Use the audit tools to check to make sure the right inputs are being grabbed and the right elements are being subjected to the ripple effects of those formulas.

Have any other good ones? Let’s hear them! Happy modeling.

Posted in:

Previous post:

Next post: