Make it easier to find external links
Not just break them - help us to find where they are. It's time consuming to look in charts, data validation, conditional formatting, data and shapes.
Levi Bailey
shared this idea
Thanks for your feedback! We’re reviewing your suggestion. Remember, the more votes a suggestion gets, the more likely it is that we’ll do it.
24 comments
-
Jerry Norbury commented
There's even more places they can hide - conditional formatting and inside Tables. Neither of these is particularly visible.
The formulas in Tables feature is particularly horrible because it is completely hidden and can differ from the formula visible in the cells...thus still point at an external link even after you've changed ALL the cells in a column to point somewhere else. Nightmare.
-
Miguel Ulloa commented
The issue is that if the external reference is in an exotic place, like a validation list or named reference, there's no way of finding out other than going through cell references and visually inspecting everything to make sure. The "edit links" menu should be updated to display every place where external references can exist.
-
Anonymous
commented
Inquire Add In is not enough. I can see what the linked workbook names are, but I cannot see how they are linked and am therefore in no position to fix them. I have tried editing the link in Inquire but that has only "stuck" about 1/3 of the time in one workbook, and no times in another workbook that I am fixing.
-
PDR
commented
Please Please Please provide a simple answer to this. I have often found problems due to external links I never knew I had. In my work setup I can't put some important files in sharepoint because external links prevent them being opened and I just can't find them.
-
ZC88
commented
Finding and fixing external links (i.e. links to other workbook) is almost a Sherlock Holmes investigation, namely because these may be "hidden" in data validations or conditional formatting, This may happen as a result of a simple copy/paste sequence between workbooks.
Why not adding an "External links" option to the Find & Select menu , which should identify the cells searching in formulas, but also in validations, formatting, defined names etc..
-
Gerdami Des Betes commented
Wow, it is already under review ... since 24 September 2015!
-
Anonymous
commented
Really need this feature. I just okayed copying 25 "links" that matched formulas in a spreadsheet. The difficulty? When I went looking for the links using the text given in the error messages they didn't actually exist. The single cell with a link to a different workbook that caused the problem, I inherited from another user.
-
Mark
commented
Although it's not super fast, this can be done using the Inquire add-in
-
mark
commented
For me this is one of the most annoying limitations because we know that the application knows exactly where that external link is (otherwise it can't break it), but there is no button for us to see where the link is. We can work around it by doing a Find in formulas, but a shortcut from the "Edit links..." screen would make a LOT of sense.
-
balakrishna sappa
commented
yes, finding external references in conditional formatting rules is the most tricky/difficult one.
-
Col Delane
commented
This is definitely required - and shouldn't need a pile of votes to prompt MS to fix it, for it just makes sense that it should be done!
Whilst they're at it, they should also fix one of the major causes of this problem, being the replication in the target workbook of EVERY Defined Name in a source workbook when one or more of the latter's sheets (whether or not there are any Sheet-scoped Names, or formulas which include Names elsewhere in the workbook, on the target sheets) are moved or copied from source workbook to target workbook. This could be avoided if during the Move/Copy Sheet process:
1. Excel identified if there are any Names referencing cells on the target sheets or if there are any formulas on the target sheets that reference Names elsewhere in the workbook, and
2. users were given the option to replicate all Names, only user-selected Names, or no Names.I live in hope but with little expectation.
-
I have been using the utility by Bill Manville who commented below. Thank you Bill.
-
Anonymous
commented
Please do this - I have worksheets where I have tried everything to find links, searching all worksheets, looking in named ranges, looking for hidden shapes, conditional formats etc etc.. and still I have somewhere a link which shouldn't be there. It must be possible to do this automatically, after all, Excel knows enough to flag that there is a link there: so why doesn't it tell us where!
-
Levi Bailey
commented
Ryan that's a good start, but, along with the false positives you mentioned, it misses a lot of extenal links. External links can be in defined names and shapes, commonly including charts, plus a bunch of other places that aren't springing to mind right now (and undoubtedly plenty I haven't discovered yet).
Presumably the Excel team could do away with this problem once and for all.
-
Ryan
commented
I find external links by searching the entire workbook for the square bracket [ character. Unfortunately, this finds references to tables as well.
-
Tim Graham
commented
This seems like such a simple, basic thing and it's mind boggling that Excel doesn't already help you with this. If it can warn you that you have external links, why can't it tell you WHERE THEY ARE? Seriously, please fix. Thank you!
-
Jan Wx, NZ
commented
Ah Bill - I have to thank you once more, as a few days ago I had a colleague with a similar problem.
-
Jan Wx, NZ
commented
Ah Bill - many thanks - conditional formatting and axis titles - that's where they are. Now Excel team can you see how important this is?!!!!
-
Bill Manville
commented
I fully support this suggestion, as it is a common pain point.
My FINDLINK utility - a free download from http://www.manville.org.uk/software is a widely used interim alternative while we wait for a proper solution. It finds links in conditional formatting, data validation, axis titles etc as well as the more common places.
Bill Manville, Excel MVP for 21 years
-
Dory Owen
commented
The biggest problem I have is other users who accidentally create external links when they copy formulas from one workbook to another or do VLOOKUP to ext file. Then they mail their workbook to me not realizing that it contains references to data on their own C: drive. I look at the data and see a fixed total, they look at the same file, formulas update and they see a different total. When opening the file, it warns me that there are links to external docs, so I search around to figure out where this stuff is so I can resolve it. Could be in formulas, in Named references, etc. Need a diagnostic tool similar to the "Show Dependencies" or "Name Manager" that makes all this easier:
https://support.office.com/en-au/article/Find-external-references-links-in-a-workbook-fcbf4576-3aab-4029-ba25-54313a532ff1If user is about to Send as Attachment a warning about external references would be useful. Tells the sender they need to cleanup with PasteValues first.