When I started my new job 18 months ago, the spreadsheets I found were not exactly the kind of spreadsheets that I would make. They were, I would guess, pretty typical of spreadsheets in small (and large) business around the world. They were used to collect and track data and they did their job for the most part. Here are some of the problems I noted:
- Most of the data was screaming to be in a relational database. “Normalization” was a word that didn’t exist in anyone’s vocabulary. We had one customer whose name was spelled at least three different ways. The same data that existed in the accounting system was repeated in the spreadsheet and not always reconciled.
- The spreadsheets lived wherever the creator wanted them to live. Not everyone knew where this was and two people could be tracking similar information in two different places.
- Data was copied and pasted between spreadsheets. A change in a downstream spreadsheet would be manually input in the source.
- Some of the workbooks were down-right huge. I’ve seen enough corrupted workbooks to be worried about five-year-old workbooks with 30 worksheets.
- No VBA code. This, of course, is not a problem, but a characteristic. I only mention it because it’s the point of this post.
Let me say a few nice things about these spreadsheets. None of them were shared. They used “Read-Only Recommended” quite well so that users only opened them in write-mode when they intended on entering data. The spreadsheets were complex and hard to navigate. But the users were adept at using and navigating them. They got the job done, albeit less efficiently than they could have.
My idea of reconciliation is looking at two totals and noting that they match. In order to achieve this, I have to create systems that ensure perfect data entry. When data is entered completely and accurately once, there’s no need to figure out why one total doesn’t match another.
I’ve set out to mold these spreadsheets in my own image (you knew I would). It’s been a slow process, to say the least, but progress has been made. I’m nearing completion of one of the more complex projects and I started thinking about how I’ve simplified things. To simplify, I’ve shifted the complexity from the front-end to the back-end, which is to say I haven’t simplified it at all. It may be super easy to enter the correct data one time in one place, but the machinations that allow this are more complex than the non-normalized, scattered, huge spreadsheets they replaced.
Now instead of having a handful of people who can navigate a maze of spreadsheets, we have exactly one guy who can fix a problem in the code. Of course my code is perfectly structured, copiously commented, and bug-free. Yeah, right. There’s no question that I’ve improved day-to-day efficiency, but what happens when something goes wrong and I’m not there to fix it? Like most quandaries in life, this one comes down to the hit-by-bus scenario. If I get run over by a bus tomorrow, have I done this company a service? Am I the only who thinks about this stuff?