When I went to junior and senior high school, everyone had to take shop class – ladies too. Guys and girls also had to take home economics, as well.
Mr. Lapinski was my junior high school teacher for wood shop. When you used the wrong tool for a task or used it the wrong way, he would call you “Primitive Pete”. Everyone liked Mr. Lapinski. Calling you “Primitive Pete” was just his way to gently nudge you to think about how to do your work correctly and safely.
In the finance and accounting world today, “Primitive Pete” is lives on. I constantly see a screwdriver being used to fix everything. A lot of the time, a flat head screw driver is used when a Philips or a torx is needed. Even when the “right tool” is used, it is often used incorrectly. What is the figurative screw driver? It is the modern day spreadsheet. You have a problem? There is a spreadsheet to solve it.
Can you imagine, if you doctor prescribed the same medicine for everyone? That would be a disaster!
The misuse of a spreadsheet goes back to the days of one of the first spreadsheet packages – Lotus 123. The 123 meant it could be used as a spreadsheet (1), a word processor(2) or a database(3). While it was very effective with the relational concepts of the grid in a spreadsheet, it was completely ineffectual at word processing. The database idea was rudimentary at best in its application. Thus was born the idea that the spreadsheet could do everything. Sure I can get from Florida to Washington by walking, but a plane ticket is much more effective!
As a small example of the many issues with spreadsheets, let's look at one small item of many that was done in the past and that continues years later. An interesting point is that Lotus 123 didn’t have the border feature of modern spreadsheets. People would put a column or two in between data columns and place a “|” symbol in it to delineate columns.
Roll the clock forward 25 years and users are still using columns with “|” symbols for the same purpose although there is functionality to draw borders in cells now. New users are still learning that outdated method. Just let it die…..please. I wouldn’t mind it so much if that old technique didn’t cause problems. But, did you ever try to pivot a range of numbers and it wouldn’t create the pivot it because of the blank column header? What a pain in the butt. Clipping….ten yard penalty!
The “|” symbol is one of those “Primitive Pete’s” that just will not die and there are many more of them in the use of spreadsheets. If you have ever used the wrong tool or the right tool the wrong way to do a job, it takes a long time to fix something and there is often a question – is it done correctly? Sometimes the correct way to process something is to use a database or some other software tool. Unfortunately, the spreadsheet is the Leatherman of corporate life.
There are lots of issues with the over use of spreadsheets. Because of this, jobs that should take seconds often take hours. Here are just a few issues we have all seen.
- Spreadsheets are a work of art. Each person has a unique way of putting them together. I refer to this as “spreadsheet art”. Trying to understand how someone constructed an extensive spreadsheet can be daunting! To the person who created it, it is a “work of art”! Call them Michelangelo.
- While possible, a spreadsheet can be designed to flexibility handle changing data, but, more often than not, this is not done correctly. Formulas and ranges have to be manually changed when new data is added. This adds a significant amount of time and opens a huge opportunity for processing errors. Instead of programming it once, it is reprogrammed every single time. What a waste of time. It is like a Rube Goldberg model for someone new to the spreadsheet.
- The constant changing of ranges and formulas of inflexible designs eats up huge amounts of time. Talk to any CFO or controller and they groan at the thought of another spreadsheet!
- As a business grows, the amount of time “preparing and processing” spreadsheets grows exponentially. If the “artist” who designed a complex spreadsheet is hit by the proverbial “beer truck”, no one knows how to operate it without an extensive review.
- There are multiple copies of some spreadsheets on the network. Which one is the “latest and greatest? They are kept forever because no one knows what some of them do and they are afraid it may be needed some day. I was at one company where there was a network directory for a person that I knew had been gone for 5 years. What is in there? Does anyone know?
Spreadsheets can be a useful tool, but like a good program, they must not be full of spaghetti code.
Pivot tables are one of the most powerful features of spreadsheets, but they are often not used. If they are used, they are used rudimentarily. The amount of insight you can get by properly structuring a spreadsheet, applying a pivot table to the data and adding a pivot chart is off the charts! In five minutes, you can understand more about the subject represented by the spreadsheet's data then building "hardwired" formulas for hours. I call pivot tables "flexible reporting". I always say that "you don't have time NOT to learn pivot tables and charts!"
ODBC connections are also fantastic. ODBC connections allow the data to be kept in a database and easily accessed by the spreadsheet, but the “bible” of data is always the database.
VBA is a great tool for programming the spreadsheet for routine tasks, but in order to program, the spreadsheet has to have structure. Most users have very little knowledge about this feature.
I use spreadsheets like everyone else, but I also use databases for some jobs. The key is to know which tool to use when. Fill your toolbox with tools – not just a screwdriver. And… make sure you know when and how to use them!
Maybe schools should bring back shop class. Thank you Mr. Lapinski. Teachers do make a difference.
