Excel - saving Workbook very slow

Question

I have a number of workbooks that are particularly slow when I save them (10 - 30 mins). I know the reason that they are slow is because they have lots of INDEX, MATCH formulas which are linking to another, closed workbook.

The workbook takes 19 seconds to open and about 10 seconds to recalculate when a date is changed (before improvements, recalculation time was about 50 seconds). I just can't seem to speed up the saving time of the workbook.

I have tried to find a way to replace the INDEX, MATCH formula's with VBA code that imports the data when a change is detected, but apparently it isn't possible to import data from a closed workbook, so that doesn't work for me.

I did have the formula's set up as =INDEX( , MATCH( , , )) However I have changed this to have a MATCH column and then lots of INDEX formulas, this has cut the recalculation time down to a fifth of what it originally was and the opening time in half, but if anything the saving time is longer.

Does anyone have any suggestions?

If the workbook which is the source of the links is also open at the time of opening/saving that should make a big difference. Or are you saying that this is impractical? Personally I always ensure that any linked workbooks are open simultaneously when doing work in a file with such external links. — XOR LX
Having the data store workbook open doesn't seem to make a difference unfortunately, I think the amount of resource the data store is using whilst it is open is offsetting any benefit gained from having it open. — megatron77
You say INDEX/MATCH formulas? Not array versions thereof, I trust? And how many such formulas are we talking about? Any volatile functions (e.g. OFFSET, INDIRECT, TODAY, NOW) in your workbook? — XOR LX
Nope no INDIRECT or OFFSET functions, just straight up INDEX and MATCH to do a lookup based on a date. The real reason why I have loads of INDEX and MATCH formulas is because I need to SUMIFS the data that is pulled through by the INDEX and MATCH, that sounds a bit bonkers I know but the INDEX and MATCH works when the data store is closed and SUMIFS doesn't, the data store has to be open for the SUMIFS alone to work, so I pull the data through using INDEX and MATCH and then SUMIFS. Maybe it's that process which is wrong and there's a better way? — megatron77

TheGuyThatDoesn'tKnowMuch TheGuyThatDoesn'tKnowMuch · Accepted Answer · 2016-03-05T14:57:53

Check this link for how to run a macro when a cell's value changes:

https://support.microsoft.com/en-us/kb/213612

From there you can have the macro open the new workbook, add your INDEX MATCH formulas to the cells in your original workbook, copy and paste as values, then close the other workbook. That way your file is all values instead of formulas, which will increase speed significantly.

To increase performance of the macro while it runs, add this to the beginning of the code:

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Then add this to the end:

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Excel - saving Workbook very slow

1 Answers