I love Excel. I just wanted to state this outright. I would like to think that I use it the right way and that I am nearly an expert in most things Excel.
I think Excel can be used in many circumstances and scenarios that require a very fluid, ad hoc analysis. The problem is that everyone has a different definition of what an “ad hoc analysis” is, and, at times, they use Excel for things that are not appropriate.
The many (strange) use of Excel
Using Excel as a word processor
Some years ago, my father used Excel extensively for his business, using it to track product sales as well as create invoices and even write books. He was a prolific writer and ran his own consulting business, and I remember introducing him to the concept of spreadsheets and Excel in particular and showing him how flexible it was. My passion for the software, which originated in the days of VisiCalc, was undoubtedly transmitted to him; it didn’t take long for him to recognize that Excel can do a lot of things.
But when I caught him using it to write one of his poems, I had to stop him. I tried to tell him about word processors, but he interrupted and simply asked, “Why would I use another software if Excel can do this?”
As much as I could discuss the functionality of a word processor over Excel, I was unable to answer him because, for his purposes, Excel was sufficient and did the job. I had no clear and convincing arguments to the contrary until one day, he came to me and asked me how he could reflow the entire document. He wanted a smaller font. It was then that he understood at least one case where a word processor was better for the job.
Unfortunately, this is just one of many stories that have followed that demonstrate that, even to this day, businesses and data professionals often use the wrong tool for the job.
Using Excel as a calendar tool
Using Excel as a database
Excel for gaming
7 Reasons Why Spreadsheets Aren’t Enough For Your Business
Here’s a story that’s more business-focused.
I was helping the finance department of a large multinational company implement some proper financial software when I had some difficulty and push back from the user community as they recognized they’d be moving away from Excel, replacing many of its functions with the database application. Their push backs were not totally unfounded—it was slower, and it added more steps to their processes.
Worse, the benefits of it were not apparent at first. Excel’s ease of use overshadowed any application that our consulting organization could ever build for them. The fact that they could quickly glance at a batch of invoices from the previous day, swiftly edit them without having to search first, or even summarize them in one quick selection eclipsed any type of reporting facility or BI application that we could ever implement for them.
Having learned from my experience with my dad, I took a different approach to demonstrate why Excel isn’t the right tool for some processes.
To start, I asked all five members of their team to do the following things—access their Excel spreadsheet, enter an invoice, and run the macro that reported the day’s totals—and to do them all at the same time as each other. They looked at me quizzically, as if questioning my computer expertise, and asserted that it wasn’t possible to do that. Only one person can make changes at a time and be able to save those changes, they told me. (Of course, this was before the days of Google Sheets and Excel Online—but they pose their own sets of problems.)
Then I asked the team to identify who created or who changed one of the rows of data on the Excel spreadsheet. Again, all I got were blank stares as they apparently doubted my knowledge of Excel—or my knowledge of anything else, for that matter.
“Impossible,” they stated. “We would add a column with the person’s name, but four out of five times, we’d forget to change it or set it. Besides,” they added, “we only do that when we create the invoice anyway.”
At that point, I launched into a more detailed conversation about accounting and financial responsibility, auditing, and fraud management. In other words, I pointed out the necessity to know why someone did something for the sake of knowing, learning, or communicating.
I asked many more questions, pointing out how their new software can do the tasks they need so much more efficiently, reliably, and quickly. Excel has reached the limits of its ability to pretend it’s a database, I told them. It’s not a database, and it is simply not made to do certain things. Thankfully, other software is around that can do those things a lot better.
So when is Excel not applicable to your business?
1. Data Volume and Size
You have most likely heard about Excel’s 65,000-row limit in versions prior to Excel 2002 and the fact that today it will allow more than one million rows. So, that’s an old issue that doesn’t apply to us today, right?
Well, not quite. According to several reports, it was due to Excel’s size limitations that caused the underreporting of almost 16,000 cases of COVID-19 in the last week of September 2020 in England. This is a real-life (and unfortunate) example of how using Excel can lead us to produce incomplete, and therefore poor data and affect public health. Using Excel as the unique data management and database can also hurt your business. But, we’ll explain that in the following points.
Remember that Excel has limits—and those limits are not always visible.
See this for more information about Excel’s current specifications and limits.
2. Formula Reference
The best thing about Excel is undoubtedly its ability to reference cells both dynamically and in static form. If you’ve never tried to insert a row or column in Excel, and your SUM or AVG formula no longer referenced the data correctly due to missing the new column or shifting down or right the entire reference, then you have not used Excel extensively.
But it happens all the time, and it is a major cause of errors. If you don’t notice it early enough in the process, you will lose hours, maybe days, of work. Or worse—never notice it and continue to manage using misleading and erroneous numbers.
3. Data Updates
I think one of the biggest time wasters and error-prone processes with Excel is what happens when you want to re-use the same worksheet, calculations, or presentation with new data.
Do a copy-and-paste or a Refresh Data query, and it’s easy to get disruptive quirks like different data structures, new bad data, static cell references (see the previous point), or named ranges.
If the data needs to be updated manually, then other problems can arise. Someone might overwrite a formula, for example, and a specific value that makes the entire thing work is lost, invalidating data in a myriad of other places. Or an “auto-suggest” might present itself but insist on having a mind of its own, and in the blink of an eye, one value populates the entire column. Disaster.
4. Complexity
Complexity is everywhere in software, but when you open an Excel document with more than five worksheets inside, you know you are in for hours of pure enjoyment trying to figure out how they relate to each other.
The “business logic” of how data flows from one cell and sheet into another is difficult at best and impossible at worst, except to those who built it in the first place. Even then, it’s probably only decipherable if they put some notes somewhere.
This complexity is volatile and prone to error. Changes made over time will certainly break calculations, introduce errors, and on and on.
5. Macros
Advanced Excel users can create macros to do all sorts of things for the users automatically. In other words, an Excel spreadsheet can become a mini-application in just a few minutes simply by selecting some menu options and adding macros.
The problem with macros is that the code is highly accessible and modifiable. The logic of the code is visible and can become susceptible to bad use—intentional or otherwise—wreaking havoc. It can be something as simple as erroneous calculations or as damaging as deleting critical files from your computer.
6. Multiple users
While the majority of us still use local versions of Excel, a big issue arises as more and more people are being drawn into online versions of spreadsheets that allow multiple people to access the same document at the same time, do modifications at the same time, and even see what the others are doing.
Although it’s a great feature, it can get pretty comical to see two users going back and forth by overwriting each other’s cells and values. I am not sure of the value of allowing multiple people to modify the same document at the same time since I think too much time would be lost trying to modify a moving target; I think it depends on the organization of the team and the document itself.
7. Data Tracking and Versioning
Finally, something that is becoming ever more critical in today’s world of auditing, financial responsibility, and other regulatory aspects—data lineage and versioning. Knowing who did what and when to a document, data element, or number is important, if not critical, for most businesses.
Spreadsheets, whether local or online, might have security around them, some stronger than others. But when access to an entire document is given along with the ability to randomly change any portion of it, data tracking can become very difficult.
Data lineage refers to the description of where the data came from and how it is used. Again, it is very difficult to determine data lineage using free, editable documents such as spreadsheets.
Summary
I will continue to use Excel and Google Sheets, but only for specific tasks—the right tasks, I believe—the ones for which spreadsheets are perfect. But not for database activities.
With a world full of databases, SQL, NoSQL, large and small, cloud and on-premises, you, too, should use a database to control and store transactions, repeatable data elements, and data that you need to aggregate and report on.
There are some great online cloud solutions, such as Airtable, that are very simple to use and that have interfaces very similar to spreadsheets but all the advantages of a database. Check them out.
Until then, keep a lookout for bad cell references.
Via clicdata
