Data tables are a great feature in Excel but it can be the cause of Excel slowing down during operations and resulting in the closing of a file taking lots of time.
YouTube Data Tables slow down Excel
Data table slowing Excel- during workings- switch formula mode
If you find that data tables are slowing down your work (every time you click enter, you have to wait and in the bottom right of the screen it goes though the data tables), then you should look at switching your calculation mode.
You do this by going to the FORMULA tab, clicking on Calculation Options and choosing the Automatic Except for Data Tables as shown below
This means that the rest of your spreadsheet will work normally (you make a change and all the formula update immediately), but the data tables will only re calculate once you push the F9 key or when you save the file.
While saving
If your spreadsheet contains data tables, when you save it, it will automatically update all the data tables even if your are in the ‘Automatic Except Data Table’ mode. This can be a time consuming process. If you just want to save the file and you are not worried about the data tables for now, click ESC while it is saving. It will warn you that the tables have not been updated and do you want to re calculate the tables. Click NO.
What this means is that the Data Table won’t be up to date but you can easily recalculate when you open up. The more important thing is that your spreadsheet is saved and it hasn’t taken too long.
Learn more about Data Tables in our What If online Course