It’s not very often we hear of fraudulent activities occurring in the science and medical fields, but as the industry is overflowing with research and data, the chances of it have become not just a possibility, but a reality.
Four years ago, doctors at Duke University revealed that they had discovered a method for using a genetic signature from a tumor to treat cancer. With the cancer-fighting community watching and waiting anxiously, two bio-statisticians out of the University of Texas were tasked with taking a closer look at the potential breakthrough.
Upon diving into Duke’s research, what they discovered was not a cure for cancer, but evidence that the Duke finding was, in fact, a fraud. On top of swapped sample labels and misleading evidence from the Duke doctors, further examination detected that underlying data had been manipulated within an Excel spreadsheet – an oversight ascribed to human error.
It’s no secret that the simplicity and usability of spreadsheets is what keeps business professionals – regardless of specialty or field – dependent on these tools. As Diane Robinette, president of Incisive, stated in a 2010 webinar hosted by the forensic accounting solutions company, “Spreadsheets are valuable; whether you create them, consume them or both. Everyone has access to them… But, they also cause us angst.”
According to Robinette, there are several problems related to this pervasive business application. First, unlike enterprise applications where users are often trained in their use before they are given access, Excel is provided sans training when employees receive their company computers. Secondly, complex formulas – which are typically out of sight from the user – can cause users to unknowingly make important business decisions based on inaccurate data. Lastly, a “looking good vs. being correct” battle habitually resonates among users, as they often lack the desire to fix a spreadsheet if it visually appears to be working. As today’s spreadsheet applications are short of error checking and debugging tools, it’s difficult to determine whether the user is truly developing a quality product.
In the 2010 webinar entitled “How to Avoid the Most Common Spreadsheet Errors,” Dan Vega, principal software engineer of Incisive gives detailed insight into the common types of spreadsheet errors, how they happen, and steps users can take to prevent them from occurring. According to Vega, spreadsheet inconsistencies usually present themselves as formula, risk, hidden, and informational errors.
Formula Errors
When users intermingle data and formula cells, it is nearly impossible to keep track of the big picture of the spreadsheet. To avoid overwriting formulas, Vega recommends separating data and formulas from the beginning. For the lookup validation tool, Vega advises to steer clear of duplicate entries and pay extra attention to the actual content included in the spreadsheet.
Risk Errors
For spreadsheet users, embedding a constant, or an entry with a specific fixed value, may make sense at the time, but applying an update to the constant can complicate usability for the next user, whom may not be aware of the constant. To bring more trust to spreadsheets, Vega says to pull out the actual constant, place it in the cell and then reference the cell.
Hidden Errors
Hiding sheets, columns and rows in spreadsheets can certainly be extremely valuable when it comes to maneuvering around extensive spreadsheets, since it presents to the user a document that focuses solely on the work area. However, the information within these hidden sheets, columns and rows will still remain active, meaning subsequent users of the document may not be aware of their existence and can, in turn, make uninformed business decisions. To avoid serious risks, users should look closely for each specific type of hidden content to understand what’s being displayed.
Another common obstacle involves conditional formatting, or when a user can set a condition to show which format is triggered. As observed by Vega, users have the tendency to reset the entire spreadsheet and re-apply conditional formatting to ensure it is active on the spreadsheet, making it extremely difficult to track. As Vega says, “If you take the approach of clearing out conditional formatting, you don’t know if you are erasing someone else’s conditional formatting. The best way is to know where it is, update those places that you want updated, and ensure they behave the way you want them to.”
Informational Errors
Finally, today’s spreadsheet applications lack the capability to confirm all related workbooks are grouped together, and this may trigger traps for users. Therefore, Vega recommends practicing caution when connecting two workbooks together and being sure that there is a solid purpose behind connecting them, and that they remain paired together. Otherwise, workbook references can end up in obscure places like in My Documents, temporary Internet files, within names, or even become referenced in two different directories.
So, what is it about spreadsheets that make it so easy for users to make errors?
“It comes from this idea of, ‘why do you need training if you can already do it?’ One of the dangers is that they’ve made [spreadsheets] so that you can use them, but it’s more like, ‘Use at your own risk,’” said Vega.
The bottom line is it’s near impossible for an employee to be so spreadsheet-proficient that he or she can avoid committing errors during usage. Given that most individuals are not prepared to completely relinquish spreadsheets, it’s pertinent that users find methods for identifying and avoiding spreadsheet errors and inconsistencies before they trigger large-scale predicaments.
To hear the complete webinar from Incisive, click here.
Edited by Jamie Epstein