Just about every computer in the world comes pre-loaded with spreadsheet software, the king being Microsoft Excel. That doesn’t mean, however, every business user who has it knows how to use it. It’s very easy to make small mistakes within a spreadsheet. And as some businesses have found out the hard way, those little mistakes can translate into devastating results.
Consider the cautionary tale of the Fisher Auction Company based in Pompano Beach, Fla. A couple of years ago, the company was hired to handle Sarasota County’s online public auction of abandoned lots. In a rush to update the minimum bids, overnight some of the lots tripled in price on the county website. Bidders and real estate agents complained, local politicians were blamed, and the auction process broke down into total confusion.
For once, it wasn’t a politician’s fault.
The culprit: an input error in the spreadsheet listing the minimum bids. When revised, the unwitting Fisher Auction employee forgot to sort the value column and all the bids were thrown off from the lot numbers and auction ID numbers.
“These spreadsheets are approaching the complexity of enterprise applications, and yet they are absent the IT controls you would expect to have with those enterprise applications,” points out Eric Perry, vice president of product management at Prodiance, a spreadsheet risk management company based in San Ramon, Calif.
Perry has seen the gamut of typical mistakes from his clients, which primarily fall within the following categories:
Why errors go undetected
Perhaps one of the most basic mistakes is not realizing there is a mistake in the spreadsheet. “A lot of spreadsheet developers tend to assume that an answer within the spreadsheet is the answer. The system does not naturally alert the user there’s an error. That’s probably the most insidious problem,” says Mark Simkin, a professor of information systems at the University of Nevada, at Reno. In other words, flawed or not, when the data is inputted and calculated, it doesn’t leave any cells blank.
Simkin offers the following tips to catch mistakes:
A splash of color helps
One last tip offered by Perry: “Color coding a spreadsheet has tremendous value. Anything that makes it easier for users to find the areas they are updating, like assigning unique backgrounds, colors or fonts to specific inputs or outputs, makes it a lot easier to avoid confusion. Color can really bring a spreadsheet to life.”
Like all the other applications in the Microsoft Office suite, Excel has a new and improved 2007 edition. Among the upgrades: the capacity for more rows and columns. Specifically, one single document can now build out to as much as a staggering one million rows and sixteen thousand columns.
That’s a lot of room for error.