Learn how to find, modify and break links to an Excel workbook.
Linking is great, until you no longer need it. Then it can be a bit of a pain.
A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data is within your workbook and another is actually breaking the link.
This workbook contains links
The message ‘This workbook contains links’ is shown when you open a workbook that has links to a closed (as in, nobody currently has it open to work on it) external source file.
You can put up with seeing this message for so long…then it starts to become annoying. You also start asking yourself, “should I be clicking Update or Don’t Update”.
You will eventually want to locate where the links are to establish what the source data is and if you still need the links in your workbook.
Finding linked data within your workbook
Links aren’t obvious and it can sometimes be frustrating trying to locate them within the worksheet. To quickly locate linked cells try the options listed below.
Option 1 – Use the Show Formulas button
From the Formulas tab, in the Formula Auditing group, click on Show Formulas. All cells containing formulas will now display the formula instead of the value, allowing you to easily identify those cells that are linked to other worksheets.
Option 2 – Try searching for the filename
To quickly locate links to external files, press CTRL + F to display the Find dialog box. In the Find what box type .xls or just .xl if you want to ensure you include other types of Excel files in your search. Make sure the Look in option is set to Formulas. If you can’t see this option click Options. Click Find All and all formulas containing links will be listed.
Option 3 – Check additional elements
Check that there are no links to external files in Conditional Formatting or hidden worksheets.
- To check in Conditional Formatting, from the Home tab click Conditional Formatting and then click Manage Rules. From the Show Formatting Rules for drop-down box select This Worksheet. Check all of the rules and delete any that are connected to external files.
- To unhide hidden worksheets, right-click any sheet tab, select Unhide and then check for external links.
Modifying and breaking links to a source workbook
1. Open the dependent workbook.
2. From the Data tab, in the Connections group, click on Edit Links.
3. In the Edit Links dialog box click Open Source to open the linked source workbook, Change Source to select the name of a new source workbook or Break Link to break the link to the source workbook.
4. Click Close. If you have broken the link all cells that previously held link formulas will now be shown as values.
Note: when you change the source file, cell references formats in the new source file will match those of the old source file; otherwise Excel will display a #REF error. Make the necessary adjustments to the new source file before attempting once again to use it, e.g. if the referenced cell is being used in a calculation ensure it does not contain text.
Tip: if you open a dependent worksheet and your formulas have been replaces with #VALUE errors try closing the dependent workbook, opening the source workbook and then opening the dependent workbook again.
Breaking links using Paste Values
Links between worksheets can be removed using Paste Values.
1. Open the dependent workbook and then select the range containing the linked data. BTW, you can select the entire worksheet if you want (CTRL + A).
2. Copy the data.
3. Leave the linked range selected and then do one of the following.
- From the Home tab, in the Clipboard group click the Paste drop-down arrow. Select Paste Values from the list.
- Right-click and select Paste Values.
This replaces the external reference with the values of the cells, therefore breaking the link to the source data. For more info check out this video on how to use Paste Values.
If you enjoyed this you may also enjoy...
How to freeze a row in Excel
[Watch on YouTube] / [Subscribe to our YouTube Channel]Let’s look at how to freeze a row in Excel. It can be a real pain when you don’t have freeze panes on, and when you start to scroll down the screen you lose the headings at the top of your columns.
Use Conditional Formatting to identify cells that are of interest
Learn how to use Conditional Formatting to identify cells that are of interest. For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using
Link cells in the same or different Excel worksheets
Learn how to link Link cells in the same or different Excel worksheets. Linking saves a huge amount of time (and a huge amount of mistakes) in that it allows you to create connections from one cell to another. For example, if I’m creating a personal cash flow worksheet and at the end of the month I
Use the SUMIF function to total only the cells that match your requirement
Use the SUMIF function to total only the cells that match your requirement. For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a
Insert an Excel function into your workbook
Learn how to insert an Excel function into your workbook. Excel comes equipped with a large number of pre-defined formulas. These are known as functions. There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few.
Insert subtotal rows into sorted data
Learn how to insert subtotal rows into sorted data without having to spend time doing it manually. Recently I ran a training session for an Accounts Manager and her staff. They spent a lot of time pulling data out of their in-house computer system, sorting it by customer and then inserting a new row at every change
