Some Excel users link their spreadsheets with reference links. A value in one Excel file will then be linked to another. Users can usually remove those reference links with the Edit Links dialog, which includes a Break Link option.
However, sometimes users can’t break all spreadsheet links with the Edit Links dialog. If you can’t break all reference links in an Excel file, check out some of the resolutions below.
How can I break links when the Edit Links dialog doesn’t break them?
1. Unprotect the Excel worksheet
- Select Excel’s Review tab.
- Click the Unprotect Sheet button.
- If the sheet protection has a password, you’ll need to enter that password in the dialog window that opens.
- Click the OK button.
- Then the Break Link option might not be greyed out. Select the Data tab.
- Click the Edit Links option.
- Select the links listed, and click the Break Link option.
2. Delete named cell ranges
- Click the Formulas tab.
- Press the Name Manager button.
- Select a cell range name there.
- Press the Delete button to erase it.
- Click the OK option to confirm.
- Delete all the referenced cell range names listed within the Name Manager.
- Click the Close button.
3. Clear all data validation rules from the sheet
- Click the Select All button at the top left of a worksheet.
- Click the Data tab.
- Click the Data Validation button.
- Select the Data Validation option.
- A dialog box should then open that asks to clear all.
- Press the OK button on that window.
- Repeat the above steps for other sheet tabs in the spreadsheet.
4. Check out the Break Link Excel add-in
The Break Link Tool is an Excel add-in with which you can quickly remove all reference links from an Excel worksheet. The add-in is not freely available, but you can still try out the add-in with a one-week free trial.
Click the Download button on the Break Link Tool page to install that add-in.
With that add-in installed, you’ll see a Break Links button on the Home tab. Click that button to open the tool’s window. Then you can click a Select all option to select all the tool’s break link settings. Pressing the Start button will remove all the links.
So, that’s how you can break links in your sheet that when you can’t get rid of them with the Edit Links tool. Note that spreadsheet objects and pivot tables might also include reference links.
So, also check if any objects and pivot tables on sheets have any reference links.
Which one of these methods worked out for you? Let us know by leaving a message in the comments section below.
RELATED ARTICLES TO CHECK OUT: