Excel in Business: The Common Pitfalls and Modern Alternatives

In the realm of business operations, Microsoft Excel has long been the go-to tool for data storage, processing, and reporting. Its widespread availability, user-friendly interface, and versatility make it a natural choice for businesses of all sizes. However, as organisations grow and their data needs become more complex, Excel often transforms from a helpful ally into a hidden bottleneck.

This article explores how businesses typically use Excel, the challenges and risks associated with it, and modern solutions that can better support sustainable growth and efficiency.

Let me start by giving some real-world examples where Datahub Consulting supported an insurance company and a global retail organisation with data transformation projects. They both heavily relied on excel for strategic decision making which created its own problems.

Let me explain more…

---

Case Study 1 – (Insurance)

A UK insurance company wanting to be smarter with their data. They had one member of staff that collates data for a management pack. This pack contained data for performance data comparing the current year to the previous year, to summarize the management pack typically would include:

• Number of new customers
• Customer satisfaction and retention
• Total number of cases opened and closed.
• Case Types
• Average time to resolve a case.
• Percentage of cases resolved within the SLA.
• Case Outcomes

Most will think that this would be straight forward, but in reality, for an organisation without a structured database with time intelligence this was a very manual task. They collated this information in excel which took one person 8 days to complete. For this reason, they had strategic management meetings every quarter. They would prefer to have these strategic meeting monthly but time constraints to prepare the data made this not possible. They reached out to Datahub as they realised that there needed to be smarter with their data analytics. They were a small insurance broker with employing 23 people with a small budget to work with.

Our aim was to install Power BI and to use the Power Query engine look after the data work.The semantic model would be created using a STAR schema for time intelligence reporting.

---

Case Study 2 – (Retail)

A global retail company relied heavily on excel reports from their third party data warehouse. With an initiative to move to an Azure data warehouse this gave the ideal time to migrate away from excel and provide their team with a global reporting solution.

The challenge was that the business had worked on excel for so long all their critical reporting from finance, operations, and marketing all relied on excel. After collating a list of excel based reports, globally there was a total of 205 business reports in use, of which 153 were considered business critical. An additional challenge with excel being in place for so long was staff were apprehensive of change, as excel was in their comfort zone.

To understand more about this case study here’s a link to this project on our website’s Case Study page: Power-BI-Migration-Case-Study.pdf

---

These are examples where excel is not the right tool and created problems for the business. Excel as its place with business but unfortunately, it’s the go to tool for a lot of businesses. Even if it’s not the right tool to use. Why does this happen?

• Users may be familiar with excel, so their first though is to use it as a go to application.
• It’s installed on all users’ machines as part of Office 365.
• Quick to get started. Users can start analysing data without the need for a BI developer.

Culture

Let’s address the elephant in the room, culture. There’s industries (I’ll not name them!) that have a culture of wanting to work with tabular data (tables of data). This is fine, but to achieve this they instantly go to excel.

We’ve even experienced creating some nice Power BI reports and one of the first questions is “how do I export this data into Excel?”. With any data transformation project, we look at working smarter, being efficient, and to address the “this is how we’ve always done it”, or “I’m happy using excel” culture within business.

Here’s an example of a table in excel. The table contains demo healthcare appointment data. During this article we will use this dataset for demo purposes.

What is Your Go To Application For Data Analysis?

If your answer is, it depends on the individual scenario, you’ll be correct. Excel as an option can work effectively in some scenarios. If your answer is I can always do it quickly and easily in excel, then you may face challenges.
There are pitfalls and reasons why excel is not to be used in all instances. Here are some pitfalls that we have come across on a number of projects using excel to within a business:

Increased Manual Processing

Having to perform a task manually can be time consuming. There are technologies today that can handle automation, transformations, and processing more efficiently taking away the need for manual processing and freeing up time of staff.

Reluctant To Migrate Away From Excel

Businesses start to use excel as data is smaller and easier to handle. In this instance excel is fine to use. But as data volume and data complexity increases this then becomes an issue, but the business finds it difficult to move away as there can be a reliance on excel.

Manual Errors

Where ever there is manual intervention this can lead to manual errors. Here are some examples:

• Manual Data Entry
  Errors like typos, incorrect formats, or missing data are common when users manually input data into Excel.
• Copy-Paste Errors
  Users copying and pasting data may inadvertently overwrite existing data or misalign rows and columns, causing inconsistencies.
• Lack of Validation
  Excel does not enforce data validation. So, if a user doesn’t have much time in their day then any validation of changes may be forgotten
• Version Control Issues
  Working on multiple versions of the same file, especially in collaborative settings, can lead to conflicting updates or lost of changes.
• Human Dependency
  Processes like manual sorting, filtering, or reformatting introduce a high likelihood of accidental errors, especially with large datasets.

Data Loss

Copy / paste, filtering, and the use of formulas can lead to data being omitted and only a subset used. With copy and paste it’s a manual task and can lead to not all data being included. Moving on to the use of formulas, We’ve seen formulas with a range that was fine initially. Let’s say for example to report total sales amount the formula sums column K, the range is set to rows 2 to 10,000.
=SUM(K2:K10000)
but 6 months later with more data, let’s say 12,000 rows, then the formula range doesn’t cover all the rows in the dataset. We have seen how this example has affected excel based reports that use formulas like Count, Sum, and Average leading to inaccurate reporting.

Also, it’s been widely reported that the UK government during COVID accidentally omitted data in an excel by saving the file in the wrong format. This led to the loss of data.

Rounding Issues

If excel is not set up correctly with data types, then rounding of numbers can cause variances. For example, if the excel does not take decimal placing into consideration in the specific cells, then we have seen where this can lead to over $1,000 variance.

Inadequate Security For The Data

With excel files a workbook or worksheet can be password protected but there are limitations on the security that can be used. With modern data platforms including reporting tools like Power BI there is increased security for access by the user, but also on specific elements of the data.

• Security within APPS
• Workspace Access
• Report Access
• Row Level Security (Applies security on the rows in a table based on the user)
• Column Level Security (Specific columns have security applied. This can protect sensitive data)
• Object level security (Where the security is on the object i.e. table)

We now expect application security to comply with IT security policies and are connected to network security groups like Entra ID (AD Groups). This then allows the security including password to be centrally managed through the IT systems including password policies etc.

Problems With Data Compliance and Data Governance

Businesses are wanting to be compliant with data protection legislation and also ensure there is data governance in place. This can be difficult to manage and implement with business-critical data in excel documents. Here are some examples why:

• Lack of centralised control
• Limited security and access control
• Data integrity risks
• Difficulty in implementing policies

Not Following “One Version of The Truth” Best Practice

If a user wants to share some data with another user, then they could send them a copy of the excel. This then could be shared again and before you know it there is no “one version of the truth”, but multiple versions. Also, some of the users could change the data to suit their specific needs, for example operations want the data by fiscal year, yet the marketing team want it viewed by calendar year. Then the finance team want to see the figures without tax applied, but another team want it with the tax applied.

This is an example where one excel with one version of data, without adequate controls can lead to multiple versions all showing different numbers. Which numbers are correct? We don’t know.

Excel Files Stored Locally on Machines

Its easy for a user to have business critical data in an excel spreadsheet and saved locally to their machine. There are a number of pitfalls here:

• It may not be included in any backup strategy.
• If the machine should fail, then the data could be permanently lost.
• Other users would not be able to access the data.
• Security risk if the laptop was stolen.

How is Excel used in Business

You will be surprised how many businesses from small start-ups to large global brand names rely on excel. I understand why for small businesses as the data could be smaller and less complex and they also need a cost effective solution. I get that. If excel works for your business that’s fair, excel has it’s place. But for large enterprise businesses that have large volumes of data, there are better solutions.

Businesses commonly use Excel in three main capacities, lets have a look at these.

Data Storage

Many businesses use Excel as a lightweight database, capturing everything from customer lists and sales records to project plans and financial transactions. Its simplicity allows users to get started quickly without technical expertise.

Data Processing

Excel’s built-in formulas and functions make it easy to clean, analyse, and manipulate data. Pivot tables, conditional formatting, and macros further extend its processing capabilities.

Reporting

Excel has basic chart tools and ability to handle custom layouts, Excel is a popular choice for generating reports and dashboards, often shared across teams or presented to stakeholders.

Why Relying on Excel Is Problematic

While Excel’s versatility is undeniable, its overuse comes with significant drawbacks, particularly when it’s stretched beyond its intended purpose. Let’s have a look at some of the problems:

Lack of Scalability

Excel struggles to handle large datasets effectively. As data grows, files become unwieldy, slow to process, and prone to crashes. Large spreadsheets with millions of rows or complex formulas can significantly degrade performance.

Increased Risk of Errors

Human error is a major risk in Excel-based workflows. Simple mistakes, such as a misplaced decimal, an incorrect formula, or accidental deletion of data, can have severe consequences. In fact, several high-profile financial disasters have been linked to Excel errors.

Limited Collaboration

Excel’s collaboration features are rudimentary compared to modern tools. Shared workbooks often lead to versioning issues, overwritten changes, and difficulties in tracking who edited what. Even with cloud integration, real-time collaboration is not as seamless as with dedicated platforms.

Poor Data Governance

Using Excel for critical data lacks robust security, audit trails, and access controls. Sensitive data stored in spreadsheets can be easily duplicated, shared, or misplaced, exposing organizations to compliance risks and potential data breaches.

Time-Consuming Processes

Manual data entry, cleaning, and consolidation are inherently time-consuming in Excel. Automating repetitive tasks is possible through macros or VBA, but these solutions often require specialized knowledge and can break easily with changes in data structure.

Inconsistent Reporting

Modern Alternatives to Excel

To overcome these challenges, businesses should consider alternative tools and platforms tailored for data storage, processing, and reporting.

Cloud Databases

By using a Cloud-based databases like Microsoft Azure SQL, Microsoft Fabric, Amazon RDS, or Google BigQuery offers scalability, reliability, and security. Use a structured database to centralise data and enforce validation rules, ensuring data accuracy and consistency.

Data Lake Storage

Data lake storage can be used as an alternative to storing business data in excel. Firstly data lakes can utilise structured data, non-structured data, and semi-structured data. This means that whether its a structured table like a Delta table, or maybe you have a CSV file that users update on a regular basis, this is possible.
Another advantage over excels is that data Lakes also provide a high level of data security and governance.

Data Processing with ETL Tools

With any Business Intelligence application you need to control the movement of data and understand any failures or slow running process. This is where ETL or ELT is vital.

Tools like Microsoft Fabric’s Dataflows, Data Pipelines, PySpark Notebooks, SQL Stored procedures. Alternatively for smaller datasets Power BI’s Power Query engine can automate the Extract, Transform, Load (ETL) process, eliminating manual data cleaning and transformation tasks. With this there is not a one solution for all but there are tools that can be used for all scenarios.

Reporting with Business Intelligence (BI) Platforms

Reporting and dashboard tools such as Microsoft Power BI provide dynamic, visually compelling dashboards that can be updated in real-time.

Power BI can create semantic model for reporting but be aware that there are data size limits and limits on the number of refreshes per day . For example if you have a Power BI Pro licence then there is a data limit of 10gb with 8 refreshes per day. To help with this you may want to consider a Premium Per User (PPU) licence instead of the Pro licence.

Microsoft Power BI:
https://www.microsoft.com/en-us/power-platform/products/power-bi

Collaboration with Modern Platforms

Tools like MS Power BI and SaaS platforms like MS Fabric support advanced security, scalability, performance enhancements, sharing and collaboration. The use of domains, workspace’s, and Org Apps provide latest technology, ease of use, and accessibility for all global users.

For a demo of Microsoft Fabric please speak to Datahub Consulting where we have transformed customers data using MS Fabric across the world.

For more information on Datahub Services with MS Fabric:
https://www.datahubconsulting.co.uk/microsoft-fabric/

We also have informative articles relating to MS Fabric
Why should small / medium sized businesses use MS Fabric:
https://www.datahubconsulting.co.uk/articles/why-should-small-and-medium-sized-businesses-use-microsoft-fabric/

How MS Fabric transformed an airline:
https://www.datahubconsulting.co.uk/articles/how-can-microsoft-fabric-help-in-aviation/

Enhanced Data Governance with Centralized Systems

Why It’s Better: Tools like Microsoft Purview ensure compliance and security by providing robust data governance capabilities.

Best Practice: Implement role-based access controls and monitor data usage to protect sensitive information.

The above solution would be a cost effective alternative to using excels for reporting. This would be a solution for start-ups or small businesses that want data security, automated ETL, and to create, share, and collaborate reports. With the use of semantic models business specific calculations can be created here for use in the reports.

Transitioning from Excel to Modern Tools

Moving away from Excel might seem daunting, but a structured approach can make the transition smoother:

• Assess Current Workflows
  Identify processes heavily reliant on Excel and evaluate their pain points.
• Create a Migration Plan
  This can be different from one business to another. You may decide to go department by department. or alternatively address the most critical report first. There’s no right or wrong here. But having the plan as doing it systematically is key.
• Start Small
  If you have any concerns begin with a pilot project, such as automating one reporting process or migrating a specific dataset to a database. We talked of a migration plan above and this plan could be to talk one department as a pilot, lets say marketing. In parallel to the current reports migrate the reports and check the numbers are accurate. Only release them to the business when you are happy with the numbers.
  Once release to the business let them work with the reports for a week and get feedback. Did anyone have access issues to the reports, did the reports run faster than the excel versions, are the business users happy with the reports etc.
• Train Your Team
  Equip your staff with the skills to use new tools effectively. Workshops, certifications, and hands-on training can help with ease adoption.
• Integrate Systems
  Ensure your new tools integrate seamlessly with existing software to avoid creating silos.
• Iterate and Improve
  Continuously refine your workflows based on feedback and performance metrics.

Conclusion

While Excel has its place in business, its limitations make it unsuitable for scalability, efficient, and secure data management in the long run. By adopting modern alternatives like cloud databases, ETL tools, and BI platforms, businesses can unlock greater productivity, reduce risks, and position themselves for sustained growth.

The key is to recognise Excel’s role as a starting point rather than a permanent solution. With the right tools and practices, organisations can elevate their data strategies and make more informed decisions.

Working with Datahub Consulting
Lets work together.