Apply This Method For A Better Life With Spreadsheets

If you are a regular user of Excel for calculations or model building, I bet you spend quite some time to prove the calculations are correct or locating the cell formula that is messing up your impeccable design. I had had my fair share of long hours in navigating from cell to cell and sheet to sheet in search of what-is-wrong. After my frustration had reached its critical mass I realized that the quality of my spreadsheets need to be built in the development process itself. Ever since I practice this belief and hours spent in debugging has been a very small part of the whole process. I will leave aside the errors related to the calculation or model design and I would like to focus on the ones typical for excel - wrong ranges, bad formulas and all others that have the potential to ruin otherwise brilliant spreadsheet.


The heart of the method is to use checkpoints as much possible and reasonable. I see two types of checkpoints - one checking for technical error and the other is checking for logical consistency.
Technical errors include wrong ranges, column indexes, wrong formula and so on. For example, a SUMIF produces wrong sub-total because of a wrong condition or VLOOKUP returning value for the wrong lookup value or rang not to mention the debugging difficulties coming from a formula composed by few nested formulas. Here are some tricks to spot problems faster:
- Use of control sums or values. Put these next to a block of data (or a row or a column) to compare the outcome with similar operation over the same data. For example, the sum of cells in a block that extracts sales figures by product type should be same as the same as the sum of block that extracts sales by department. Another example are the subtotals - sum of the subtotals should be same as the total. These are very simple examples but you get the idea. (You could rightfully argue that a perfect spreadsheet should not extract same piece of data twice but this is another discussion I will be covering soon).
- Use control formula. This is a formula that is same as the one used in the range but produces a result that could easily be checked against its parameters. Example: VLOOKUP with column index value of 1 to return the look-up value. Another example is keeping an eye on the SUMIF formula with calculating the COUNTIF with the same arguments.
- Break complex formulas into their parts where possible. Simpler formulas mean less mistakes and faster detection of problems with them. For example, when using VLOOKUP with nested MATCH,  calculate MATCH in a separate row/column (this will could also make the spreadsheet faster and smaller).
There are other controls that could be applied, I cannot exhaust all the possibilities here. But the message is to place these controls next to a calculation block and compare their results with other controls in the calculation flow and produce TRUE, FALSE or some other meaningful message to identify things are OK or not.

The other type of controls are the logical control. Their purposes is the perform sanity check on the calculations. The sanity check is versus the results of some simplified model or an external figure. Then built-in a control comparing the results of the model with the simplified model or external figures. For example, take calculation of market growth over the time. Before applying any rules we need to have a rough estimate of what we could expect. Can we expect growth in the ranges of 15% to 20%? If so, we could directly compare the outcomes. Another approach is to assume a constant growth or one based on a simple trend and compare with it. Again, simplification of calculations and steps is of great use for reducing the chances for error.


Building error-proof spreadsheets is matter that involves many tasks and I am just touching the surface of one of these. Placing well-thought controls on strategic places in your spreadsheet could make debugging a breeze if there is need of debugging at all. I know, it is an extra effort in the development process but effort that pays back well.