Excel: The Sneaky Trap that's Costing You Time and Money

Don’t get caught in the trap of Sunk Cost

Every year you add another “feature” to your Excel “app”.

Every year, your investment in the Excel “app” grows.

Every year it becomes harder to unwind.

Ever have an Excel formula that looks like this? Yuck! Product of adding-on over the years 🙁

Because you've already committed to the cost, either time or money, you are more likely to follow through on whatever it is you've invested in, regardless of the outcome. This is something known as the trap of sunk cost.  

Every time you choose to invest in anything in your business, you’re making a commitment. When this happens (and as time goes on), it’s only natural you become less and less likely to abandon that commitment. In fact, it often takes a catastrophic event to cause us to pick our heads up and look around for something better.  

You shouldn’t wait for disaster before you pick your head up.

Every time you’re asked to make the Excel file do something it doesn’t already do - is an opportunity.

Every time you open the Excel file and find out that someone else made changes to calculations - is an opportunity.

Every time you copy and paste data to or from your Excel file - is an opportunity.  

All of these are opportunities to escape the trap by making a better investment with your time or money.

Locked in your cell. Are you going to throw away the key?

All of the things your Excel workbook is doing for you could be blinding you to real constraints you’ve created for yourself.

Over the years, I’ve worked with many people who have developed a method that “works for them”. They have a specific set of steps that they repeat weekly, or maybe daily, that produces reliable outcomes and results. Data gets exported from a service, pasted into a worksheet, calculated columns produce magic, and pivot tables help aggregate to make sense of it all.  

Even I fell victim to something like this for a few years, until our team grew and forced me to rethink the solution. The precision and efficiency I thought I had created was being crushed by all of the new demands on my time. But I found myself stuck. The calculations and pivot tables had become inescapable walls that I had built around myself.  

I had to escape. I had to find the key to unlock the cell.

One option was to delegate and train someone else how to use the spreadsheet. I couldn’t do it. By this time, even I was no longer proud of the complexities I had created in Excel. I could not hand it off to someone else and feel good about it.  

So, we broke out, ditched that Excel file, and actually changed how we did that reporting altogether. In this process I realized that in many ways we had chosen to do things in our business based on what was possible in the spreadsheet. Rather than taking a step back to ask why we were doing what we were doing, we conformed a part of our business to a spreadsheet. It’s hard to believe in hindsight, but it really happened that way.

This scenario is all too common in many businesses. People will often say, “if it ain’t broke, don’t fix it”.  

But what if you don’t know that it’s broken?  

Do you have a team? Here’s your sign.

Modern office tools have come a long way. Today you can collaborate in real-time in an Excel file effectively. You can secure and share Excel files with others on your team and even with those outside your team. Embed data and charts from Excel in presentations. Connect it to complex data sources and line-of-business data to generate reports. It’s a very useful tool, and it definitely has a place in the modern workplace.

However, there is no escaping the reality that as your team grows, roles and responsibility must change. Storing, managing, and working from core operational data that lives in Excel is not a good idea for an organization with more than a couple people. As an organization grows, roles develop, and responsibility is assigned, working from something like Excel not only doesn’t provide clear separation for these roles, but it also adds risk to your business. One unfortunate mistake can wipe out months or years of work. It can result in unwanted downtime or work stoppage.  

Assuming you do have a team or you’re more than just a one-person operation, you have a real opportunity to make your business better by moving away from your Excel “app” to a real solution.  

What do those opportunities for “better” look like?

Let’s talk about some of the ways things get better when you move away from your Excel “app” and toward a software solution of some kind.

Less Limitations

You’ll be casting off the limitations associated with large sets of data. Simply put, Excel is not designed for large-scale data sets and becomes slow and unusable once your data grows too large.

Better Access

You have an opportunity to move into a more secure and sophisticated solution for managing access to your business-critical data.

Although you may be using the Microsoft cloud for storage and collaboration, Excel files stored in OneDrive or SharePoint still don’t stop someone from mistakenly deleting data or even the Excel file itself.

Better Features

By moving from Excel into a more specialized, line of business (LOB), packaged, or custom application, you’ll be able to take advantage of features that enable things like:

• Role based access
• Data integration
• Process automation
• Better workflow and user experience
• Audit trails
• Expanded delivery platforms (web, desktop, mobile)

So, what can you do about it?

Unfortunately, that’s not as straightforward as we would like. There are lots of options to consider. Do I find something off the shelf? Do I invest in a custom app? Can I use no code / low code? Who should I hire to do this? We have some additional resources to help you think more deeply about these questions. We’ll post links to those at the end of this article.

For the sake of thinking further, let’s assume you’ve chosen to build something using M365 tools in the cloud. Here are how some of the features and usage patterns you’re using in Excel might map.

Do you have structured data in one or more worksheets? A better solution might consist of lists, libraries, or relational data stores. In M365 or Azure, think SharePoint Lists, SharePoint Libraries, Dataverse, SQL Server, etc.

Do you spend time regularly copy-pasting data into one or more worksheets? This is an opportunity to integrate with the source of this data. Look for options for direct real time integration through service endpoints. Scheduled data import with an extract transfer & load (ETL) process or similar. In M365, look to tools like Power Automate, Data Gateways, standard or custom connectors.

Is your Excel workbook full of complex calculated fields and macros? These kinds of things map directly to commonly used software and data storage patterns like Calculated Columns, Business Rules, and Conditional Triggers. In M365 & Azure, think Dataverse, SQL Server, Power Automate.

Different strokes for different folks? Not everyone on the team does everything, nor do they need to see everything. People should only see and manage what matters for them. All the rest is a distraction. Move your data into a real app and define roles that can be assigned to your users. In M365, think Model Driven App, Security Roles, Organization / Team / User Scopes, & Field level permissions.  

It’s probably a given that you’ve got at least one pivot table / chart in your Excel workbook. If so, look to modern analytics platforms for charts, dashboards, KPIs, prediction models, and even AI. In M365, think Model Driven Charts, Power BI, etc.

Wishing you could be effective with your business data from a mobile device? Getting out of Excel and into a web enabled responsive app is a great first step to support mobile. In M365, think Power Apps.

Get ahead of it

This concludes my rant on not using Excel as the tool to run your business. Over my years as a consultant, I’ve come across hundreds of these scenarios and even experienced the issue firsthand as a result of my own doing. There’s no harder way to learn a lesson than that.

Admittedly, none of these scenarios ever start with a stated plan to “run the business with Excel”.  

It just sort of happens.  

And at some point, the business grows up and the natural progression is to make a shift from Excel to an ERP, CRM, or other line of business software. My best advice is to simply get ahead of it. Plan for the shift early. Be ready for it and know when it’s time. Then do it.

Helpful resources

3 Things to Consider When Deciding Between Low-Code and Custom Development

When to Build a Low-Code Business Application

Low-Code in the Workplace