Yesterday a coworker of mine came up to me with an Excel problem he was having. His worksheet was running very slow. When he was navigating it would take 10 to 15 seconds to change tabs. I thought this was odd and went to his desk to investigate.
My first observation was that the size of the workbook was just under 75 meg in size. Not extra large but also not too small. While continuing my investigation I noticed that several tabs had different colors. I inquired about the tabs and he advised that he brought these in from a different workbook.
I took a look at the data in these tabs and many of the cells had vlookup formulas linked to the original workbook.
Well the original workbook is stored on a rather slow server so I asked him if the data in the vlookup fields would need to change when that other workbook was updated and he advised that they did not.
So I decided to break the links.
When you break a link to a source workbook of an external reference, all the external formulas in the source workbook are converted to their current values. I.E. the formula is replaced with just the result of the formula. The calculation is removed. Since there were hundreds of vlookups in his workbook, the performance of the worksheet was impacted.
Now breaking links is serious because once the link is broken it cannot be undone unless you recreate the formula. So if you do decide to break the links, you may want to first make a (just in case) copy of your workbook.
Breaking links in a workbook is as easy as 1,2,3….
1. On the Data tab select the Connections group and click on Edit links. If the Edit Links command is unavailable (displayed below) then your file does not contain linked information.
2. In the Source list, click the link that you want to break.
To select multiple linked objects, hold down CTRL, and then click each linked object.
To select all links, press CTRL+A.
3. Click Break Link.
After breaking the links and saving his workbook, the performance issues were resolved. Now external reference links are not the only cause of sluggish worksheets, but in this instance, breaking the links resolved the issue.
BEGINNER, Break Link, vlookup