Susan Kruck, associate professor of computer information systems and management science, says good planning is one way to avoid common spreadsheet mistakes.

In an instant a perfectly good spreadsheet disintegrates, leaving a wasteland of #VALUE! error cells. We’ve all seen it happen. Most of us haven’t lost our job over it or even bounced a check — yet.

Everyday, money vanishes with an accidental cut-and-paste. A few years ago, several CEOs in Dallas were fired for a rounding miscalculation; exam scores were botched at a Canadian university because a plus sign was placed where a minus sign was needed; and votes were recounted in New Mexico after a row was deleted.

Spreadsheet errors are frighteningly common, research shows. Still, programs like Microsoft Excel and Corel Quattro Pro are used extensively. Researchers at IBM claim 44 percent of spreadsheets contain errors. Accounting giants PricewaterhouseCoopers and KPMG International estimate higher, saying 75 to 90 percent of corporate spreadsheets have material errors, costing companies up to $100,000 per error per month. That equals a $10 billion hit for corporate America each year.

“We have business professionals making major decisions based on these print outs. They’re colorful, organized, and the fonts are pretty, so people assume the information is correct. Often it’s not,” she said. “Everyone talks about frequent errors and how devastating they can be, but no one has suggested ways to fix the problems.”

That’s why she spent three years studying the most common spreadsheet bloopers and developing ways to avoid them.

Unfortunately, tweaking the software isn’t the answer. The mistakes are mostly human error, Kruck said. After distilling a glut of information, she found three main mistakes. “I looked at more than two hundred studies and was able to merge the data into three categories: design and planning, formula complexity, and testing and debugging.”

Design & Planning

Think before you type. “Too many spreadsheets, including some that manipulate millions of dollars, are developed using ad hoc approaches,” Kruck said. “Adequate time should be spent planning the spreadsheet before the computer is ever turned on.” In the planning stage, ask yourself if your plan will produce reliable results and if your work can be easily audited.

Formula Complexity

Keep it short and simple. “It takes just one small error — a simple misplaced decimal — to produce wildly erroneous results,” Kruck said. Simplicity is especially important if the spreadsheet will be used over time and by more than one person.

She also says to break down complex formulas into smaller steps. “A formula that adds and subtracts 50 different cells could be split into three straightforward formulas: one to add, one to subtract, and one that subtracts the second from the first. Systematically applying such a process should increase the accuracy of a spreadsheet.”

Testing & Debugging

Check your work. Start by running the “audit” features if you’re using Excel, but also apply some common sense. “Even the simplest tests can uncover errors. Look at the reasonableness of results,” Kruck said. “One easy way is to insert the number one into all the formulas and see if it works”

It sounds easy now, but Kruck knows the people who use spreadsheets are often very busy in their organizations. “Spreadsheets are such quick and dirty documents to prepare that we rush through them, but really Excel can do almost anything. There’s incredible power behind it, if your results are accurate.”

Subsequently, spreadsheet skills are among the most highly sought after skills by employers, said Dr. Brad Roof, an associate dean of JMU’s College of Business and chair of the Virginia Society of Certified Public Accountants. “Accounting firms are paperless today. There are no physical work papers anymore. They’re all done on computers often using spreadsheets. Private firms also use spreadsheets extensively in tracking and analyzing their business performance.”

JMU graduates are learning this, Roof said, and employers are noticing. “Being good with spreadsheets means increased efficiency and effectiveness.”

Kruck has designed three aids, or cheat sheets, that detail commons errors. “The temptation to take shortcuts is real,” she said, “but accurate results are worth a few extra minutes.”