EXCEL file takes 15 minutes to save

[huzeifa_z]

Hi.

I am using Excel on Office 365.

I have a file that's approximately 22-25 MB in size with 5-6 worksheets. The main worksheet has approx. 100 columns and 30,000 rows. The file has thousands of internal links and formulae and thousands of links to one external Excel sheet. All external links use the XLOOKUP function. There are no tables or pivot tables in the workbook.

I am facing the following issues:

1. Inserting rows or columns to the sheet takes a long time.

2. Changing | updating the source file (using the "CHANGE SOURCE" function) causes the sheet to hang.

3. Saving the file takes almost 15 minutes.

I have changed Auto-Recover to 60 minutes only because the file takes 15 minutes every time it starts saving.

Can anyone help with this?

Thank you.

Huzeifa.

[#2]

@huzeifa_z 

1. Put raw data in Excel tables.

2. Check whether calculations could be done with Power Query.

3. Output in pivot tables not with formulas.

4. Connect to external data with Power Query.

5. Check whether the workbook contains a lot of objects and delete them.

[#3]

@Detlef Lewin 

Thanks for the quick reply.

I am not familiar with how to use Power Query and Pivot Tables, but will look up and try.

[#4]

Subsequent to this, I tried breaking the links. The file saves instantly. It's obvious that it's the links to the external source file that are slowing down the process.

Can anyone recommend a solution?

Thank you.

Huzeifa