Excel Performance Issue with Linked Workbooks - Data Update Delay

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.