How to Cure the 10 Worst Excel Problems that Companies Have
Excel’s ten biggest problems are solved with a clear strategy that standardizes design, automates data, and ensures accuracy.
SUMMARY: According to ChatGPT, companies suffer from ten persistent problems with Excel. An all-Excel strategy—with help from ChatGPT or Copilot—solves all of them by standardizing workbook design, flowing data smoothly from sources to outputs, and automating repetitive tasks without using a programming language. The result? Ultra-productive Excel users who deliver agile, insightful, and manager-delighting business reports.
I asked Chat GPT to list the top-ten problems that companies have with Excel, in their order of importance. And for each problem, I asked it to describe the problem, its cause, and how to solve it. You’ll find its answers below.
You’ll also find how the Excel Data Plumbing® (EDP) Strategy solves each of those problems.
Problem 1. Ad Hoc Design Strategy: A Lack of Structure and Standardization
Every analyst builds Excel workbooks their own way, with inconsistent layouts, naming conventions, and solution logic. This makes workbooks hard to understand, reuse, and audit.
Cause
No formal standards or templates. Each user works in isolation, improvising solutions as they go.
ChatGPT Solution
Create company-wide standards for workbook structure, naming conventions, and worksheet layout. Distribute templates and require their use for recurring reports.
The EDP Strategy
I call this problem the Frankenbook Strategy. Replace it with a worldwide standard for workbook structure, naming conventions, Power Query downloads, and worksheet layout—called the Excel Data Plumbing (EDP) Strategy.
Problem 2. Manual Data Handling (Drudgework)
Analysts spend hours copying and pasting data, refreshing pivot tables, updating formulas, and assembling charts—over and over again.
Cause
Lack of automation and reliance on manual workflows for updating reports and analyses.
ChatGPT Solution
Use Power Query to automate data loading, cleaning, and transformation.
The EDP Strategy
Use Excel’s Power Query feature to flow cleaned and transformed data from any number of sources through their flowbooks to their reflows (flowbook-automated reports, analyses, models, forecasts, etc.)
With this approach, Plumbers can update each flowbook with one command.
Problem 3. No Single Source of the Truth:
Different teams use different numbers for the same metrics because they’re pulling data from separate systems, files, or versions.
Cause
Excel data is decentralized and unmanaged. Multiple files are saved, shared, and modified without reconciliation.
ChatGPT Solution
Centralize critical data sources using shared databases or structured Excel data tables. Use Power Query to import from a controlled source instead of relying on email or exports.
The EDP Strategy
ChatGPT largely missed this solution, because the web hasn’t had an answer to this problem until now. Here’s the actual solution:
There’s no need to set up new databases because each source of data is already maintained by someone: your IT department or by the IT department of your external sources. All we need to do is to import their data using Excel’s Power Query feature, along with a company-wide Metrics Catalog.
That is, each source file already represents its single source of the truth.
Problem 4. Hidden Errors and Broken Links
Models contain hardcoded numbers, outdated formulas, or links to other files that no longer work—errors that are often hard to detect but can lead to bad decisions.
Cause
Ad hoc development without built-in checks. Workbooks evolve over time without systematic maintenance.
ChatGPT Solution
Use structured modeling practices. Implement checks for out-of-bounds values, missing data, or broken links. Avoid excessive inter-file links; use queries or references to shared data instead.
The EDP Strategy
Use the EDP Strategy, as explained in Here’s How to Make Excel Ultra-Productive—With AI’s Help. The strategy includes automatic error-checking tables in your flowbooks—tables that reconcile calculated values in your reflows with values returned from source files.
Never link to other workbooks; use Power Query instead, if necessary. Link formulas only to named ranges or Excel Tables in other worksheets of the same workbook.
Problem 5. Version Control Chaos
Dozens of versions of the same file float around—often named things like “Q4_Report_FINAL_v7_REAL.xlsx.” No one is sure which is correct.
Cause
Files are shared via email or saved locally. There’s no system for tracking revisions or controlling access.
ChatGPT Solution
Store all important Excel files in a cloud location (like SharePoint or OneDrive) with version history. Enforce version naming protocols (e.g., Sales_2025Q2_20250601_JD.xlsx).
The EDP Strategy
ChatGPT’s solution is wrong here. Never include a date or time when naming a flowbook. That’s because flowbooks are designed to update each period with one command.
Instead, name each new version of a file with a sequence number, and name a copy of the current version with no sequence number. With this approach, everyone knows that the version without a sequence number is always the current version.
And its data? That can be updated with one command. If you want to capture the results at a specific point in time, save the reflow as a PDF file and distribute it—not the workbook.
Problem 6. Poor Scalability
Excel slows to a crawl or crashes when handling large data sets, complex formulas, or heavy formatting.
Cause
Using Excel for data volumes or tasks it wasn’t meant to handle, especially without pre-processing or aggregation.
ChatGPT Solution
Pre-aggregate large datasets using Power Query, databases, or Python. Use summary tables and pivot tables instead of loading all raw records.
The EDP Strategy
In Excel, use Power Query, SQL, APIs, or Python to aggregate data to tables for your flowbooks to use.
PivotTables will work, as well, but they’re typically not as powerful as Power Query.
Problem 7. Underuse of Automation Tools
Teams manually do things that Excel can already automate, like cleaning data, repeating calculations, and structuring tables.
Cause
Users don’t know what Excel tools exist or how to apply them to real business workflows.
ChatGPT Solution
Offer hands-on training focused on business use cases. Encourage adoption of Power Query, dynamic arrays, and formula-based automation. Build a culture of “automate first.”
The EDP Strategy
ChatGPT got it wrong here, as well. Most Excel reports and analyses are broken. And one should never automate a broken process! This is particularly true when each Frankenbook requires custom coding.
Instead, companies should offer hands-on training for business use cases—particularly for processing data in Excel. Dynamic arrays are seldom needed. Flowbook automation definitely is needed!
With flowbook automation in place, Excel Data Plumbers can use either VBA or Python to update and distribute a batch of any number of flowbooks—with one command.
Problem 8. Limited Collaboration and Audit Trails
Excel isn’t optimized for multi-user collaboration. Users overwrite each other’s work, and changes are made with no traceability.
Cause
Legacy desktop file sharing and no process for tracking who did what, when, or why.
ChatGPT Solution
Use cloud-based Excel (via SharePoint, OneDrive, or Teams) to enable co-authoring. Create change logs or commentary tabs for sensitive models. Use cell comments and version history for review.
The EDP Strategy
I’ve never seen happy workbook collaborators.
With the EDP Strategy, audit trails are obvious—because the source files and calculations are obvious. And where auditors don’t understand the calculations from either an Excel or professional perspective, ChatGPT can explain them quickly.
Instead, for collaboration, I recommend two methods. First, use DATA collaboration—typically with Power Query. That allows users to find different insights from the same data sources.
Second, use INSIGHT collaboration. That is, use a product like the Simplebooklet SaaS to collaborate about what the data MEANS.
The SaaS offers a flipbook that can be viewed by anyone with the proper credentials. The SaaS supports text, audio, and video commentary about each page of a flipbook—by anyone with access to the flipbook. That feature supports asynchronous meetings—with a permanent record for each conversation.
Problem 9. Insufficient Documentation and Knowledge Transfer
When a key Excel user leaves, their workbooks become black boxes that no one else understands. (I call them orphan workbooks.) Rebuilding or interpreting them is slow and risky.
Cause
Workbook creators don’t document logic, assumptions, or data sources. Workbooks are built with implicit knowledge—the unspoken, undocumented understanding of their creators.
ChatGPT Solution
Require an “About” or “Documentation” tab in critical files. Train analysts to annotate formulas, note assumptions, and include a changelog or contact info.
The EDP Strategy
When Problem #1 (a lack of structure and standardization) is solved, documentation and knowledge transfer become much less of a problem. That’s because there’s much less that needs to be documented.
However, what often does need to be documented are reasons why a certain analytical method was used, links to sources of relevant professional knowledge, ideas for future changes in the flowbook, errors in source data to be careful of, useful AI prompts, and so on. That information should be added to a Notes tab in the flowbook—for two reasons.
First, it will help the next Excel Data Plumber who inherits the workbook. And second, it will help your future self when you must explain or modify a flowbook that you created several years earlier—and remember nothing about.
Problem 10. Security and Compliance Gaps
Confidential financial or HR data is often stored in unsecured workbooks on desktops or shared drives, with little to no access control or encryption.
Cause: Excel files are portable and easy to copy, email, or share without oversight. Few safeguards are in place.
ChatGPT Solution: Use Microsoft 365 security features (like sensitivity labels and restricted sharing). Store files in secure cloud environments. Audit access and usage periodically.
The EDP Strategy: ChatGPT’s solution is fine, as far as it goes. But I don’t favor distributing workbooks around the company, no matter how they’re protected.
Instead, try this: Upload your Excel reports to Simplebooklet, which offers a SaaS-based flipbook product that supports password-protected access with audio, video, and text commentary. Not only does this solution allow Excel Data Plumbers to explain the insights they’ve discovered—as explained in How to Use Excel and AI to Tell Powerful Stories About Your Data—it allows all recipients to add their own questions and comments to the flipbook.
Toward a More Reliable, Scalable, and Insightful Use of Excel
The most common Excel problems aren’t just technical—they’re structural, procedural, and organizational. They reflect a widespread absence of strategy in how workbooks are designed, maintained, and used.
The EDP Strategy offers a coherent solution. It replaces the Frankenbook Strategy with modular design. It automates data flows to reduce errors and manual effort. It supports clarity, auditability, and scale. It creates space for higher-value work—analysis, insight, and better decisions.
And it saves companies brutal costs, as explained in One Plumber vs Ten Pasters: The $100K Monthly Advantage.
These are not small gains. They represent a shift in how Excel functions within a business—from isolated task tool to integrated infrastructure.
Solving Excel’s top problems won’t happen overnight. But with a deliberate strategy, clear standards, and the right use of existing tools, it’s possible—and increasingly necessary.
Fix all 10 Excel problems with the Data Plumbing Strategy.
Learn about the EDP Institute here.
