Tip: Test It Yourself
How many people actually test their spreadsheets? With the formulas hidden, it’s so easy to introduce a mistake and not even know it. With calculations spanning data on multiple worksheets, there are lots of moving parts and subtle linkages that are not apparent to the naked eye.
Case in point: we received a multi-page, long-range planning worksheet from a [venture capitalist.] In using it, I found and debugged errors introduced when the spreadsheet was passed on to someone else to “enhance.” This is a common pitfall with complex spreadsheets: expecting the next person updating it to understand the thinking of the creator.
Many of us, myself included, use spreadsheets in lieu of IT support. Why? Their lengthy “process,” time investment, control of product, and ability to make changes on the fly. In reality, some spreadsheets probably should be replaced by programs (using the spreadsheet as a requirements statement.)
My real point: we need to validate the results in our worksheets — applying common sense and comparing to our expectations, and making sure it is fit for purpose. This is part of the IT overhead that we need to adopt. Think about this: spreadsheets are, hands down, the most widely used analytic tool for business. Scary, because they are the least controlled. —Michelle Wallace
From Janice Bell: When I create a complex spreadsheet, I test it by using numbers that should be reasonable, and then calculate the results by hand to compare to the spreadsheet results. If it works, I then generate data that should be out of the range of normal inputs, for instance, negative inventory, to see how the spreadsheet handles exceptions.
From Richard Block: Michelle, you are spot on. The key is to validate the results of a spreadsheet you create by inputting sample data to confirm the results you have predetermined to be correct. Jan’s suggestion further emphasizes this point by inputting data that knowingly will produce a “bad” result to test whether or how the spreadsheet logic will handle “bad data.” These steps are critical to appropriately developing a spreadsheet.
Trap: When Rows Don’t Foot
I have found the most basic, often-forgotten rule in building spreadsheets — the ones populated with complex formulas — is the cross check. It is very easy to get deep with your lovely data design, only to find the rows and columns do not foot. This is so common that I now refuse to review spreadsheet work where the footing cell is not visible. I no longer waste time on work that is fatally flawed. —Patricia Chapman
From Janice Bell: We’ve commented on this before, but it bears repeating.
From Richard Block: Ditto!