UMass Amherst Professor Wins Microsoft Award for Tool that Finds Mistakes in Spreadsheets
April 8, 2013
Contact: Daniel J. Fitzgibbons 413/545-0444
AMHERST, Mass. – Professor Emery Berger at the University of Massachusetts Amherst has won a Software Engineering Innovation Foundation (SEIF) award, which includes a $25,000 grant, for his work on a system to automatically find errors in spreadsheets.
Berger’s CheckCell program, one of only 16 projects selected worldwide for Microsoft’s SEIF award, makes it possible for users of Microsoft Excel to find mistakes in spreadsheet data.
Because spreadsheets are widely used in businesses, Berger says, the impact of errors can be dramatic. “Errors in spreadsheet data have led to losses of millions of dollars,” says Berger. “CheckCell can automatically find mistakes like typos or other data entry errors.”
The CheckCell system that Berger and his graduate students have devised works by examining the interaction of the data in each cell in a spreadsheet with other parts of a spreadsheet like formulas or charts. When CheckCell finds cells that have an unusually high impact on a formula or a chart, such as making a pie wedge change dramatically in size, CheckCell marks the responsible cells in red. “The darker the red is, the ‘weirder’ the value is,” says Berger. “That means that either the value is extraordinarily important, or more likely, it is actually a mistake.”
Berger hopes that the technology in CheckCell will eventually become a part of standard spreadsheets. “When you make a spelling or grammatical error in a word processor, you immediately get a squiggle underneath the mistake. That makes it easy to find and fix it. We view CheckCell as a kind of spell checker for spreadsheets that will help users avoid costly mistakes.”
Berger and his graduate students, Daniel Barowy and Dimitar Gochev, designed CheckCell’s algorithms, which use a combination of statistical analysis and data flow analysis to flag inputs that have an unusual impact on the program’s output. To verify its effectiveness, they recruited workers online to enter data into a financial spreadsheet. Many of these users made typographical errors entering these numbers, some of which were over 10 digits long. CheckCell was able to find most of the errors that had a significant impact on the overall computation.
Microsoft Research established its SEIF awards in 2010 to encourage researchers to advance the state of the art in software engineering applications and tools. Its goals are to stimulate and advance software engineering practices in the development and application of devices and services, and to continue to support academic research in software engineering technologies, tools, practices and teaching methods.