Excel Performance Issue with Linked Workbooks - Data Update Delay

Question

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:
     1. Empty Rows: Including rows without data slows down the process.
     2. Recalculation: Every change triggers a recalculation of all formulas, causing delays.
     3. Formula Copying: Copying formulas down to the last row for automatic updates is inefficient.
     4. Complex Formulas: Using complex formulas for data fetching is time-consuming.
   • Method: Import data from the dependent workbook and use the local sheet range.
   • Problem: Significant delay in updating data.
   • Causes:
     1. Empty Rows: Including rows without data slows down the process.
     2. Recalculation: Every change triggers a recalculation of all formulas, causing delays.
     3. Formula Copying: Copying formulas down to the last row for automatic updates is inefficient.
2. Import Data Using Workbook Link
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:
  1. Frequent Refresh: Setting auto-refresh to every minute causes frequent, time-consuming refreshes.
  2. Formula Copying: Copying formulas down to the last row for automatic updates is inefficient.
  3. 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

Answer 1

Dear Isabella,

Thank you for your response. As you suggested, I have submitted feedback to Microsoft. Here is the link for reference: https://feedbackportal.microsoft.com/feedback/idea/59556c97-169d-ef11-95f5-6045bdbc7c39

I’m looking forward to further suggestions or updates from the community to help resolve this issue.

Best Regards,

Vipin

Answer 2

re:

We are looking for any possible optimizations or solutions to reduce the time it takes for the data to update.

How about database and extract data with sql.

Answer 3

Dear Vipin Panwar,

Thank you for your reply and feedback.

I have also asked my partner to help you vote and submit feedback.

We're also looking forward to Excel's performance getting better in the future to make it easier for everyone to use.

Thanks again for your understanding and I wish you all the best.

Best regards

Isabella-MSFT | Microsoft Community Support Specialist

Answer 4

Dear Vipin Panwar,

Welcome to the Microsoft Community.

I understand that you are experiencing delayed data updates when you link workbooks in Excel to process large amounts of data, and I understand how you feel because I have experienced the same problem as you.

The problem of lagging, delay also happens when we are working with large amount of data (containing thousands of data) in Excel. It does seem to be the performance of Excel that is causing the problem, and I understand that this may be an inconvenience to you as it happens to us as well.

I hope you can understand that since we are not Excel developers or designers in the community, we cannot be directly involved in the development of Excel to deal with performance issues. However, we will report your issue and provide you with feedback. And continue to collect feedback from other users.

In order to make the development team aware of this issue as soon as possible, we encourage our users to also provide feedback through our feedback channel. Please click on the link: Ideas · Community (microsoft.com)(English platform) to post it, where a dedicated developer will follow and comment on your feedback. After sending your feedback, you can share the feedback link in this forum, so that other users in the forum with similar problems and I can also contribute by voting for you.

Will try with other software you can use to process the data. We will keep this thread open and hopefully users in the community will have better suggestions.

Sorry for the inconvenience and thank you very much for your understanding.

Best regards

Isabella-MSFT | Microsoft Community Support Specialist