قالب وردپرس درنا توس
Home / Tips and Tricks / Find links to other workbooks in Microsoft Excel

Find links to other workbooks in Microsoft Excel



Microsoft Excel logo

One of the best features of Microsoft Excel is the ability to link to other workbooks. So if there comes a time when you need to find those workbook links you’ve added, you need to know where to start.

A general search to find the links to workbooks is easy if you just look through the text in the cells. But if you̵

7;ve linked workbooks in formulas, defined names, charts, or objects, it’s not an obvious search. Using a mix of built-in tools and your own eyes, here’s how to find links to your other workbooks in Microsoft Excel.

Find workbook links in formulas

Other than a simple cross-reference link in some cell text, formulas are common places to include workbook links. After all, getting data from another sheet that calculates against what’s in your current sheet is a powerful way to use Excel.

Start by opening the search function. You can do this with Ctrl+f or Find & Select > Find in the Ribbon on the Home tab.

Click Search & Select and choose Search

When the Find and Replace box opens, you only need to enter three pieces of information. Click “Options” and enter the following:

  1. Find what: Enter “.xl”
  2. Within: Choose “Workbook”
  3. Look in: Choose “Formulas”

Click on “Search All” to get your results.

Find and replace settings, click Find all

You should see your linked workbooks appear under Book. You can click that column header to sort alphabetically if you have more than one workbook linked.

Find and replace results

Tip: You can use the Find dialog box to also find workbook links in values, notes, and comments. Just choose one of those options instead of Formulas from the “Look in” drop-down list.

Find workbook links in defined names

Another common location to have external references in Excel is cells with defined names. As you know, it is useful to label a cell or range with a meaningful name, especially if it contains a reference link.

While a search and select dialog box for your search, like formulas, is not currently an option, you can retrieve all defined names in your workbook. Then look for those workbook links.

Go to the Formulas tab and click on ‘Name Manager’.

Go to Formulas and click on Name Manager

When the Name Manager window appears, you can search for workbooks in the Refers to column. Since these have the XLS or XLSX extension, you should be able to recognize them easily. If necessary, you can also select one to see the full name of the workbook in the Refers to box at the bottom of the window.

Name Manager workbook results

Find workbook links in charts

If you use Microsoft Excel to put your data into a handy chart and you get more data from another workbook, it’s pretty easy to find those links.

Select your chart and go to the Format tab that appears once you’ve done this. On the far left side of the ribbon, click the “Chart Elements” drop-down in the Current Selection section.

Go to Format and click the Chart Elements dropdown

Choose the data series from the list where you want to find a link to a workbook.

Choose a data series

Then move your eyes to the formula bar. If you’ve linked the workbook, you’ll see it here, indicated by an Excel extension. And you can check any data series in your chart the same way.

Workbook linked in chart

If you think you linked a workbook in a chart title instead of a data series, just click the chart title. Next, look at the formula bar for a Microsoft Excel workbook.

Find workbook links in objects

Just like inserting a PDF into an Excel sheet using an object, you can do the same for your workbooks. Unfortunately, objects are the most annoying items when it comes to finding links to other workbooks. But with this tip, you can speed up the process.

Open the Go To Special dialog box. You can do this with Ctrl+g or Find & Select > Go To Special in the ribbon on the Home tab.

Click Search & Select and choose Go To Special

Choose “Objects” in the box and click “OK”. This will select all objects in your workbook.

Choose Objects and click OK

For the first object, look at the formula bar (like the one above) for charts. Then press the Tab key to move to the next object and do the same.

Workbook linked in object

You can keep pressing Tab and look at the formula bar for each object in your workbook. When you come back to the first object you rated, you’ve gone through them all.

Remember these helpful tips the next time you need to find a link to a workbook in Microsoft Excel.




Source link