How to Fix Large Power Query Workbook Issues on OneDrive
Question details
Users experience performance drops and co-authoring difficulties when working with heavy Excel workbooks containing extensive Power Query data, KPI charts, and numerous worksheets stored on OneDrive.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Co-authoring and maintaining a heavily loaded Excel workbook with multiple planner sheets, Power Query data, and KPI integrated views through OneDrive.
- Observed behavior
- The workbook becomes too large and difficult to co-author or navigate efficiently through OneDrive.
Review your workbook structure to identify which sheets contain raw data and which contain charts or summary formulas, as this will help you plan the splitting process.
Split the Workbook into Data and Presentation Files
Separating your source data and Power Query connections from your charts and KPI summaries significantly reduces file size and improves co-authoring performance.
Heavy workbooks with numerous Power Query connections require significant memory, which hinders cloud collaboration.
By dividing the workbook into two distinct files—one for backend data and one for frontend presentation—you can minimize lag and make the files easier to maintain.
Create a new workbook dedicated entirely to your raw data and Power Query connections, such as your planner sheets.
Create a second workbook specifically for your KPI summaries, integrated views, and charts.
Link the charts and summary formulas in the presentation workbook to the specific data ranges in your newly created data workbook.
Utilize External References for KPI Charts and Formulas
Use external cell referencing to allow charts and formulas on one worksheet to pull data from a completely different workbook.
Try WPS Office for Lightweight Spreadsheet Management
If heavy Microsoft Excel workbooks are slowing down your daily workflow, WPS Office provides a highly compatible, fast, and lightweight alternative for handling complex spreadsheet tasks without the bulk.
- 1. Download the software: Visit the official WPS Office website and click the free download button.
- 2. Install WPS Office: Run the installer and follow the simple on-screen instructions to set up the software.
- 3. Open your workbooks: Launch WPS Spreadsheet and open your .xlsx files to experience faster load times and smoother navigation.
Frequently Asked Questions
Can a chart in one workbook use data from another workbook?
Yes. You can link a chart to an external workbook by opening both files simultaneously, right-clicking the chart to choose 'Select Data', and highlighting the appropriate source data range in the external workbook.
Why is my Power Query workbook slow when co-authoring on OneDrive?
Large workbooks with complex Power Query connections, multiple KPI charts, and dozens of sheets require significant processing power and memory. When co-authoring on OneDrive, the constant syncing of these heavy elements causes lag and synchronization delays.
How do I safely test splitting my large workbook?
Create copies of your files and remove any sensitive or confidential data. Build your external links, formulas, and chart data references on this mock data. Once you verify everything works properly, apply the exact same structure to your main files.
