You can reference another spreadsheet in a formula in Excel, but there’s a problem. If you decide to share your spreadsheet, the link to the other spreadsheet won’t work. You’ll need to know how to break links in Excel to fix this problem. If you’re unsure how, follow the steps below.
What Are Links in Excel?
Excel is designed to allow you to copy data from one spreadsheet to another. It’s also designed to allow you to call in data from other spreadsheets, allowing you to mix and match your data. A formula you use might refer to data in another spreadsheet file, with Excel able to pull up the information when needed. These are links in Excel. The name isn’t anything more complicated than that. Links simply create a join between your current worksheet and data that’s held elsewhere. The problem is—what if you send your XLSX file to someone else? The links to those other files won’t work. Your data will be incomplete. Thankfully, there’s a few methods you can try to break the links in Excel to allow you to move your files. We’ll explain in detail below.
How to Break Links in Excel by Copying and Pasting
You’ll typically see links in Excel when a formula refers to cells elsewhere—in another sheet or file. You can break these links by removing the formulas and replacing them with the values they return. You can do this by copying the data containing the formulas (and the Excel links) and pasting over them. It isn’t as simple as a few keyboard commands, however. We’ll explain what to do below. To break links in Excel by copying and pasting: If you followed these steps correctly, the cells should appear to be exactly the same. If you select one of them, however, you should see that the formula is gone—only the answer it returned remains. This means the links are broken and you can save your file to share elsewhere.
How to Break Links in Excel Using the Data Tab
Copying and pasting over formulas in Excel is the quickest way to remove links, but it’s a brute method. It pastes over all of the formulas in use—even those that don’t contain links. If you only want to replace formulas that do contain a link, you can use the Edit Links tool. This will replace the formula with the value it returns, but without replacing any formulas that don’t use links. To break links in Excel using Edit Links: With the links removed, make sure to press File > Save As to save your document.
Working on Excel Spreadsheets
If you’ve followed the steps above correctly, you should be able to break links in Excel. You can now send your file to others without worrying about missing or incomplete data. New to Excel? You might want to give these Excel tips a try. Breaking links isn’t the only useful way to use the copy and paste feature, either. You can also try some copy and paste tricks for Excel to help improve your workflow. Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
