Spreadsheets are incredibly popular, but they fall short when it comes to critical tasks like managing a school district’s student data.

On September 15, 2008, investment banking giant Lehman Brothers filed for bankruptcy, becoming one of the first dominoes to fall in what would become the Great Recession. In the aftermath of its collapse, Lehman Brothers agreed to sell many of its remaining assets to other firms, including London-based Barclays. Racing against a midnight deadline set by a Bankruptcy Court, Lehman Brothers sent a massive 1,000-row, 24,000-cell Excel spreadsheet outlining the details of the asset transfers to Barclays’ lawyers on the eve of September 19.

Unfortunately, in the process of converting the spreadsheet to a PDF — the file format required for submission to the Court — the lawyers accidentally opened (and included) nearly 200 additional cells that Lehman Brothers had “hidden” instead of “deleted.” As a result, Barclays accidentally agreed to an additional 179 contracts for the purchase of “toxic” assets — all because its lawyers weren’t proficient in Excel!

As shocking as this story may seem, it all comes down to problems that practically everyone who’s used a spreadsheet has experienced. In fact, research from the University of Hawaii’s College of Business Administration suggests that an astounding 88% of spreadsheets used in professional settings contain errors.

Far From Excel-lence

According to the study, errors can typically be found in a small percentage of all spreadsheet cells. While this may seem insignificant, in the context of a large spreadsheet with thousands of cells, even such a modest error rate can result in dozens of costly mistakes.

As the study’s author, Raymond R. Panko, points out, the frequency of such mistakes is generally in-line with what behavioral scientists would expect: “Broadly speaking, when humans do simple mechanical tasks such as typing, they make undetected errors in about 0.5% of all actions. When they do more complex logical activities such as writing programs, the error rate rises to about 5%.”

When it comes to working in a spreadsheet, there are an almost infinite number of ways that you can mess things up. Just about any user can make a mechanical error — mistyping a number or clicking on an incorrect cell — at any time, and unless you’re well-versed in the system you’re using, it’s very possible that you’ll make a logical error like choosing the wrong formula. Omission errors are also a problem, and they’re often the most difficult kind of error to correct, since spreadsheet tools aren’t designed to detect when critical information has been left out.

The Problems with Using Spreadsheets in Education

In a highly collaborative industry like education, the fact that spreadsheets are full or errors is just one of several marks against it. As we’ve written about before, spreadsheets also tend to fall short when it comes to helping educators differentiate valuable data-driven insights from distracting “noise” data. For anyone who is not a trained data analyst, a massive, unorganized repository of student data is likely going to be more overwhelming than illuminating.

Similarly, spreadsheets’ limited data visualization functions and inability to collate and compare data longitudinally represent significant obstacles to effective management of things like chronic absenteeism and IEPs.

These spreadsheet pitfalls can have serious repercussions for schools. In 2012, for instance, a miscalculation at the Utah State Board of Education (USBE) led officials to severely underestimate the number of students enrolled in the state’s public schools, resulting in a $25 million education budget shortfall. The mistake, said State Superintendent Larry Shumway, was attributable to “a faulty reference in a spreadsheet.” Both the Associate State Superintendent and the State Director of School Finance were forced to resign because of the error.

A Better Data Management Solution

Fortunately, by adopting a more sophisticated education management information system (EMIS) like Vinson’s CheckPoint platform, districts can ensure that they don’t fall victim to costly spreadsheet errors like Barclays’ and USBE’s.

CheckPoint empowers district treasurers, superintendents, and EMIS coordinators to double-check the data teachers and administrators input on a continual basis, streamlining the data validation process and reducing the likelihood of errors. What’s more, CheckPoint automatically generates an audit trail that makes it easy for district stakeholders to view which datasets have been verified by which educators at which times, helping build a culture of shared accountability that guarantees the district doesn’t leave a single dollar of funding on the table.