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
No comments:
Post a Comment