No company is eager to hand out its spreadsheets for public analysis. However, a treasure trove of spreadsheets was made available that allows a new analysis of spreadsheet use by corporations.
It's not very often that all of a company's spreadsheets are made available for public scrutiny. But thanks to the efforts made by Felienne Hermans of the Delft University of Technology, all of the email attachments containing Excel files from Enron were made publicly available. (Her research is available here.)
Taking advantage of this rare opportunity, we ran the 15,922 spreadsheets through CIMCON's XLAudit tool and found some interesting results!
Of the 15,922 spreadsheets, 6,678 do not have any formulas. We left these out of the results presented below as these files were probably for record-keeping purposes rather than modeling or calculations. However, the fact that over 40% of spreadsheets do not calculate anything is potentially a problem unto itself. It is often postulated that companies should do everything possible to rid themselves of unnecessary spreadsheets. Record-keeping spreadsheets would seem the simplest of targets to move to a different system in order to improve information security. But given the huge numbers, identifying such files can seem insurmountable. And, in addition, given the many stories around sensitive data contained in spreadsheets being lost, even those spreadsheets need a minimum set of controls.
As measures of complexity, here is a look at the average spreadsheet that does contain formulas:
- Average Number of Formulas: 2254
- Average Number of Sheets: 5.4
- Percent of Files With Charts: 9.0%
Average Number of Charts For Those Files: 5.4
- Percent of Files With External Links: 24.8%
Average Number of External Links For Those Files: 5.8
- Percent of Files With Data Queries: 0.7%
Average Number of Data Queries For Those Files: 3.7
We also took a look at errors and issues within formulas. Note that previous studies have shown that over 90% of spreadsheets have errors:
We looked at formulas resulting in errors (#REF!, #VALUE!, etc.) and formulas that may have some logical issue (Formulas with Constants, Formulas Referring to Hidden Cells, Formulas Formatted as Text, etc.) and found that 86% of the spreadsheets had some sort of formula problem.
Additionally, there was an abundance of hidden information in the spreadsheets. While this does not necessarily indicate an error, hiding information can make the spreadsheet harder to understand or use by the next user and can also indicate attempts at fraud.
- 7.1% had hidden sheets
- 20.3 % had hidden columns
- 27.7% had hidden rows
- 6.3% had invisible cells (white text on white background)
While we can’t reveal spreadsheet analysis data for other large corporations, we can say that the results we found at Enron are quite typical.
Chances are that the spreadsheets used within your company are fraught with issues that could put your company at unnecessary risk. You can hope for the best & wait until a problem happens… or take proactive measures to implement more effective controls of your spreadsheets today!
Because who wants to end up like Enron?
To learn more about how to mitigate spreadsheet and other EUC risks download the white paper "Taming the Spreadsheet Menance."