- Excel brings lots of functions that help us get our work done as fast as possible, but when the function we need doesn't work it can be really annoying.
- Such a function can even be the Excel break links that if it's not working can really be a pain.
- Fortunately, this article shows you exactly how to make it work again in a matter of minutes.
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 encounter an error where the excel function to break links is not working. If you can’t break all reference links in an Excel file, check out some of the resolutions below.
How to fix the Excel function break links is not working?
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.
- Select the Data tab.
- Then 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 when you can’t get rid of them with the Edit Links tool. Note that spreadsheet objects and pivot tables might also include referral 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.