I admit it, the title is a little bit racy. I use Excel regularly, but I think it's important to understand the complexity of the tool and its limitations before using it. And then, when I use it, am I sure that Excel is the best solution?
A very powerful tool
I don't think I'm shocking anyone by saying that Excel is a very (very) powerful tool. Pivot tables, charts, functions, VBA macros... Many companies use it as a business application for various purposes. But then why is it "bad"?
In my opinion, there are several reasons for this. Since I'm not going to write a novel, today I'm going to present the three reasons that I think are the most important, detail them, and propose a solution around Microsoft 365 for each problem:
- Security
- Maintainability
- Shadow IT
Security in Excel
The first issue concerns security.
To illustrate my reasoning, nothing better than a concrete example (and based on real facts). A few years ago, I was developing web applications and I started on a project for a community. This one was calculating the contributions of its members (we're talking about hundreds of millions of euros) with an Excel file containing a very complex macro... the whole thing deposited on a network share. Two months of development to transcribe and report the calculation methods in a web application! A file is by definition volatile.
This means, for this file, that a malicious person (or not) having access to it can delete it. This case can eventually be managed with a good backup strategy, provided that the deletion is detected in time.
Another possible scenario, which is more difficult to detect, is that the same person may change the contents of the file. If the file is stored on a hard drive or network share, it is difficult to trace the history and revert to the correct version.
Third case, probably the worst, the person can make a copy of the file and send it by email (another overused application ;). Here, not only do we have 2 versions of the same file, but we also lose confidentiality.
Can we simply secure an Excel file with a password?
So yes, you can protect an Excel file with a password. But is a simple password, and a shared one at that, really enough to secure a file? Without eliminating Excel, good collaborative document management practices can correct most of the problems (for example, using a tool offering versioning and sharing features like SharePoint, with strong authentication).
To go further, it is often possible to replace an Excel file by another solution. In reality, it all depends on the purpose of the file and the use that is made of it. Microsoft's Power Platform (Power BI, Power Apps, Power Automate) can be used to meet a number of needs. For example, if you need to capture and aggregate data, Power Apps can be a good match.
Maintainability of Excel files
The second issue is maintainability.
This situation has probably happened to many of us: we get an Excel file with a macro, and, when we open it, we get an incomprehensible error message. Someone, we don't know who, we don't know when, has developed a macro on the file to do we don't know what. With this, we are well advanced to solve the problem...
And then we still have the same problem of versions. We have as many potential versions of the macro as we have copies of the file.
Another example that illustrates the problem of maintainability: the company's accountant uses an Excel file to create his pivot tables. He retires, another accountant arrives and has to manipulate the data and formulas from the Excel file of his predecessor. Not necessarily easy to find one's way around.
There are several ways to solve these problems.
First, regarding files with macros; many development projects in recent years have involved turning an Excel file with a macro into an application. For pivot tables and dashboards, Power BI is the tool for this. For the record, it was originally an add-in to Excel.
Shadow IT
The last issue, and probably the most common, is the generation of Shadow IT (creation of information systems not driven by the organization).
This point is extremely complicated to identify for an IT department, given that a simple Office license is enough to create full-fledged business applications
I would like to share with you another example that I find interesting to illustrate the problem.
A company has different sites throughout France and mobile technical services agents perform interventions on the different sites. An application is available to them to enter the intervention reports (category, time spent, etc.). However, some agents are unable to use the application correctly (notably due to a lack of network connectivity) and therefore enter their reports in an Excel file.
At the end of the month, the director of technical services consults the dashboards from the application. The data he has is therefore completely wrong because it is not re-entered into the application.
Without going as far as such critical problems, there are many cases where the IT department provides an application and as this application does not meet, or does not fully meet, the needs of the users, it is then replaced or completed by an Excel file.
The key to solving this problem is to involve the business in the integration of new applications. They are the most likely to understand and detail their needs!
In my example, the problem with the adoption of the official tool was that it cannot be used offline. A Power Apps application on mobile could probably address this need.
In conclusion, Excel is a very powerful tool but it is a victim of its own success.
Microsoft 365 offers tools, especially around the Power Platform, to avoid the drifts and pitfalls that could be generated by using Excel. I hope that from now on you will make sure to ask yourself the right questions before using Excel.
