Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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.

241 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Levi Bailey shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    24 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Jerry Norbury commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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..

      • Anonymous commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        Although it's not super fast, this can be done using the Inquire add-in

      • mark commented  ·   ·  Flag as inappropriate

        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.

      • Col Delane commented  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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-54313a532ff1

        If 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.

      ← Previous 1

      Feedback and Knowledge Base