In a recent study of job postings Burning Glass Technologies, a labor market analysis firm, 78% of middle-skill jobs require spreadsheet skills. While they view that as growth in jobs requiring digital skills, I question the reason for such growth in relatively old technology. Many accounting software systems tout the ability to download data and reports to Excel. They sell this as a feature, but the reality is they are offering this as a work around for gathering data that the user needs. Intuit’s QuickBooks offers some ability to customize reports, but you are out of luck if you want data from different tables. You are relegated to downloading the data into spreadsheets and “copy and paste” it into one worksheet to get the data you want. This is quite cumbersome and prone to errors. A Forbes magazine article cited various studies over the past few years reporting 88% of all spreadsheets contain significant errors. WOW! There has to be a better way to easily slice and dice the data users need from the database.
In the late 1980’s, I was the Controller at a company that installed an ERP system utilizing the Pick database system. One feature of this system was the ability to easily mine the database through a simple “sentence”. If you wanted data from a different table, you merely created a pointer to that table in the database “dictionary”. You could use multiple data filters, multiple sorts, perform calculations, create subtotals and format the report all with a one sentence command. These queries could be saved and added to the menu within the ERP system for future use. The syntax was so simple and easy to learn that I trained all users to create their own queries. Users could quickly gather data they needed to perform their duties. Now, unless you have an onsite database programmer who can create custom queries, there are few options other than downloading data into Excel and manipulating it within the spreadsheet to get the information you need. Oh, and pray your spreadsheet has no errors in it. These spreadsheets are typically the documented data management system and generally residing on users’ workstations which is a huge internal control issue. Seems like a giant step backward in software technology. While there are a number of applications on the market that will assist users in creating queries, what is really needed are tools within the accounting/ERP systems to provide this capability to quickly and easily gather the information they need. Unless and until this occurs, here are my suggested rules for developing spreadsheets:
- Add a header and footer to every spreadsheet. The header should name the company, the report, the effective date of the data and any other descriptive data. The footer should display the location of the spreadsheet file, the author and the date created.
- Document the purpose of the spreadsheet. I recommend putting this in the comments section of the spreadsheet properties so it is visible when pointing at the file name in File Explorer. I also recommend adding it to the spreadsheet header so that it is displayed on printed reports.
- Document the data that appears in each column or line. Go beyond the descriptive heading and add information about the source of the data. You can either enter this directly in the heading or use the cell comment feature (right click on the cell and then left click on Insert Comment).
- Limit formulas to one step per cell. Avoid nesting calculations or functions in any one cell. This will make for easier checking of the logic. If you do not want all these cells to appear in your final report, simply hide them from view.
- Test, test and retest. Double check each step of the logic. If possible, perform this on the totals or results line. This will test flow both across and down the spreadsheet. Also, step back and review the spreadsheet to determine whether the results are reasonable and make sense.