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.
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.
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.
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.
Thank you in advance for your help!
Best Regards,
Vipin
If you format the source data as a table and then create a range name that points to just the body of that table, you can link to the range name from your other workbook. This ensures you do not need to have your formulas point to any empty rows.
please try these:
Switch to Manual Calculation Mode: If you’re making multiple changes at once, switch to manual calculation mode (Formulas > Calculation Options > Manual). After making all changes, you can then calculate manually by pressing F9.
Disable Background Error Checking: Go to File > Options > Formulas, and uncheck Enable
Ensure only the necessary rows are included in the link. Remove any empty rows or columns from the range, as they add to processing time.
Use dynamic named ranges instead of full column or large ranges. Dynamic ranges adjust automatically based on the data size, which can reduce the recalculation burden.
Avoid volatile functions such as NOW(), TODAY(), RAND(), OFFSET(), and INDIRECT(), as they force recalculation every time any cell changes. If these are necessary, try to use them in a single cell and reference that cell rather than repeating them across rows or columns
Use Power Query for Desktop Excel only Excel online not support it:
Load Once, Refresh When Needed: Use Power Query to import data from Workbook1 to Workbook2. Set Power Query to only refresh on demand rather than auto-refreshing frequently.
Disable Auto-Refresh: Instead of setting Power Query to refresh every minute, set it to refresh only when needed, which reduces processing
If feasible, store your data in a database and use ODBC or SQL connections from Excel. This approach can handle large datasets more efficiently than linking workbooks, and Excel can pull only the data it needs
Use the Excel Data Model instead of linking to other workbooks directly. By importing your data into the data model and creating relationships, you can work with large datasets more effectively.
if you are comfortable with VBA, then create macro that refresh data only when you need e.g. every hour or when button pressed etc.
Sample VBA code that refreshes all data connections in Workbook2 when needed:
Sub RefreshDataConnections()
Application.Calculation = xlCalculationManual
ActiveWorkbook.RefreshAll
Application.Calculation = xlCalculationAutomatic
MsgBox "Data Refreshed"
End Sub
