Excel performance when using tables and structured references

Question

I've recently come across a problem where the performance of spreadsheets becomes very poor when using lots of Excel tables (on different tabs) and structured references for my calculations. Recalculating worksheets occurs relatively quickly but some operations suddenly cause Excel to become unresponsive for 10-15 minutes (e.g. when changing the structure of the workbook, or when clicking the 'X' to close the file down).

Any reason why this is happening? Converting all tables to ranges seems to fix the problem...but this means that I lose the Excel Table functionality.

Answer 1

Thanks for your reply Mayra - unfortunately it doesn't quite answer my question. 

The Excel file is not corrupt, my computer has plenty of memory available and the Excel file itself is just over 3 MB.

Could the performance issues related to changing the structure of the workbook (e.g. inserting a row in a table) be linked to the inability to trace dependants in Excel tables, if the source data is on another worksheet? Alternatively, could it be linked to Excel's dependency tree?

Answer 2

Hi,

To help you resolve your concern regarding Excel 2016, we suggest that you follow the steps found on this link. It is an Office repair link which helps us detect what's causing the issue.

Note: Please restart your device after following the steps on that link.

If you need further assistance, just send us a reply.

Answer 3

Hi,

Listed below are some reasons why a program works slowly:

• Providing too much data on the program.
• Applications running on the background.
• Corrupted system files.

To help you with the concern, we recommend running a System File Checker that can help identify and repair issues that you have encountered. Furthermore, here is a link that has information on how to repair an Office application that can help improve the performance of Excel.