Forum Discussion
Excel Performance Issue with Linked Workbooks - Data Update Delay
Hi Microsoft Community,
We are experiencing a performance issue with linked workbooks in MS Excel. Here’s our problem statement:
We have two workbooks (Workbook1 and Workbook2) that contain one or more worksheets. Workbook1 (Sheet-1) is linked to Workbook2 (Sheet-1) using standard Excel formulas to connect specific cell data. However, when we update cell data in Workbook1 (Sheet-1), which is linked to Workbook2 (Sheet-1), it takes approximately 10 minutes for the data to reflect in Workbook2.
The records we are working with are in the thousands, which may contribute to the delay. We are looking for any possible optimizations or solutions to reduce the time it takes for the data to update.
Any guidance or suggestions on how to improve this performance issue would be greatly appreciated.
Thank you in advance for your help!
Best Regards,
Vipin Kumar
1 Reply
What we tried so far:
1. Direct Link of Workbook
Method: Directly link the required range in the formula to the dependent workbook.
Problem: Significant delay in updating data.
Causes:
Empty Rows: Including rows without data slows down the process.
Recalculation: Every change triggers a recalculation of all formulas, causing delays.
Formula Copying: Copying formulas down to the last row for automatic updates is inefficient.
Complex Formulas: Using complex formulas for data fetching is time-consuming.2. Import Data Using Workbook Link
Method: Import data from the dependent workbook and use the local sheet range.
Problem: Significant delay in updating data.
Causes:
Empty Rows: Including rows without data slows down the process.
Recalculation: Every change triggers a recalculation of all formulas, causing delays.
Formula Copying: Copying formulas down to the last row for automatic updates is inefficient.3. Import Data Using Power Query
Method: Import data from the dependent workbook using Power Query.
Problem: Delay in updating data due to limitations in the current version of Excel Online, which does not support Power Query; an updated Office 365 version is required.
Causes:
Frequent Refresh: Setting auto-refresh to every minute causes frequent, time-consuming refreshes.
Formula Copying: Copying formulas down to the last row for automatic updates is inefficient.In addition to these methods, a consultant also attempted to link the workbooks using a hyperlink. Although this method was not fully implemented, I believe it may encounter similar delay issues due to the volume of data and linked dependencies.
