Breaking Links To External References

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

Document Location on Quick Access Toolbar

Here is a quick tip.

You can add the document Location command to your Quick Access Toolbar. This allows you to easily copy/paste the file location and name.

What’s the Quick Access Toolbar (QAT) you ask???

The Quick Access Toolbar (or QAT for short) is a customizable toolbar that contains a set of commands that are independent of the tab on the Ribbon that is currently displayed. The QAT allows you to add buttons that represent commands (shortcuts). Now the QAT can be located above the Ribbon or Below. In this example I have mine below the ribbon.


To modify the QAT, start by right mouse clicking on the QAT, and select Customize Quick Access Toolbar… 








In the Choose commands from dropdown, select All Commands

From the command list, select Document Location.  Click the Add button, and click OK.























Your document location will now display in the QAT