Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.


How to find cells with external links in Excel

It is exceptionally easy to link Excel spreadsheets together. However. sometimes you want to find these external links in Excel. Not only the link, but also which cells in Excel are using these links. This could be so that you can delete them (without causing REF errors), remove the link or just better understand what impact the external links are having on your spreadsheet. Below a description of how to find cells with external links in Excel.

Watch, like, subscribe

At the moment within Excel you need to use the next few steps which are a little bit manual in that you can only do them one at a time. There are some tools and Excel AddIns that do it for you. As this is an important step in any Excel spreadsheet review, it is part of the free Reviewing an Excel spreadsheet course.

As a simple example see below the report that shows where the links are and you can click on the hyperlinks and be taken directly there. Also useful note that the 2 links identified here are very similar but one is v5 and the other v6. That is strange and should be investigated.

The first thing to do is find which other spreadsheets are being linked to. This is critical as we need to know the exact name of the external link. This is easy to do in Excel. As shown below, with the file open, click on DATA and then External Links. You will see a dialogue box which tells you the name of every other workbook linked to.

How to find cells with external links in excel

There is much that can be done with this dialogue box, but for now we are trying to find the cells with external links. So looking at the Edit Links box, find the link  that you are searching for. Remember or jot down that name. It is best to take the full name including the .xls or whatever at the end, or enough of the name so that it will be unique.

Now that you know the precise spelling, close the Edit Links box and open the FIND box.

How to find cells with external links in excel

In the FIND box, input the filename you remember from above. The more precise you can be the better the results.

The reason this works is that by default, the FIND looks into the formula of a cell and not the result (you can change this if you want, but for this purpose it works correctly).

Note below that you can choose to use FIND NEXT or FIND ALL.

FIND NEXT is great if you want to go to each cell individually. FIND ALL will list them all (this is the option we chose below) and you can then work through them.

How to find cells with external links in excel

Note that sometimes when you do the search it will tell you that it can’t find anything, even though it is shown in the External Links. In this case you are going to have to look (manually) into:

  • Graphs/ charts
  • Named Ranges (very common place)
  • Data Validation
  • Conditional Formatting.

There are some nice tools out there to help with this process. Have a look at finding External Links with Excel Analyzer and finding errors in Pivots, Charts, Conditional Formats and Data Validation.

Excel asking for a password on a spreadsheet that does not have a password

How to stop Excel from asking to update links

Remove phantom links from Excel spreadsheets

Double click Excel cell to follow formula not working

Excel FIND not working

Best Online Excel course for you