This article and video will show you one way to break external links from only selected cells in an Excel workbook. As we can see, this revenue data is sourced from an external workbook. At this time we can save and close it.
For illustrative purposes, we will sum the 2021 revenue for each business unit. Subsequently we can fill down column H, and then sum all the revenue for 2021. Finally, let’s aggregate forecast revenue for each month across the worksheet.
The shortcomings with Edit Links
As most Excel users know, Excel does allow the breaking of all external links from another workbook. However, Excel does not allow users to break only some of these external links.
In this example, let’s say we want to break only links for January and February 2021 data sources. In addition, we want to reference data from another external workbook. This additional file contains the projected FX rates of Australian Dollars into US Dollars.
Importantly to break external links from selected cells. We will require a macro that can to step over, or disregard standard formula cells. For example we need to preserve a formula cell like the one in J16.
At this time let’s select the data across the January and February 2021 columns.
Remember if we were to break these links using the standard Excel approach. Clearly it would break all links in the workbook.
Writing a macro
Indeed the only way to break select, external links in a workbook is to compose a macro.
Go to Visual Basic for Applications and ensure to insert a Module. Be sure to notate your macro.
We will call the macro, killLinksSelection
Undoubtedly we must disable the following three Excel properties. As this will improve the macro’s performance. Moreover, we want to declare the following object variables. Importantly, we only break external links from our selected cells. Therefore we must state the rng variable, which is based on the Selection property.
Importantly the loop code in the below screenshot will ensure the macro passes across each cell in the selected range. It will identify whether the cell contains a formula. Furthermore, the below IF statement will assess if this formula cell contains an opening square bracket or “[“.
If a formula cell contains square brackets, it means it is referencing an externally sourced workbook. These are clearly the only sort of formula cells we want to break.
The above code “cell.Value = cell.Value” is akin to copying and pasting values in a cell in Excel.
Whilst, the “Next cell” code moves the macro to the next cell in our selected range.
Running the external links selected cells macro
The macro is complete. We can now re-enable those Excel properties, which we disabled at the top of the macro. Let’s review the macro, and ensure it is fully correct before we run it.
We are set to run the macro on our selected cell range.
As an initial trial, we will select these cells in the range H4:J5. Remember we want to preserve the formula in cell H5. The macro is working correctly; our formula in cell H5 is intact.
Now we will select all other cells in columns J to K. The macro is correctly breaking on external links in selected cells. As we can see, when we go to Edit Links, the other non-selected cells still reference these external links as required.
In summary, here are the source workbooks used in the video. It contains the macro, which allows Excel users to break external links from selected cells.
Download Spreadsheet Vault SpreadsheetVault – How to break external links from selected cells in Excel.xlsb Download Spreadsheet Vault SpreadsheetVault – Source External Data.xlsb Download Spreadsheet Vault SpreadsheetVault – Source External FX Data.xlsb