Your Excel formulas show #REF! errors after you close another workbook. This happens because the formula’s external reference loses its link. The reference points to a cell in a file that is no longer open. This article explains why this error occurs and how to fix it. You will learn how to convert those volatile links into static values to preserve your data.
Key Takeaways: Fixing #REF! Errors from Closed Workbooks
- Paste Special > Values: Replaces a formula with its current result, permanently breaking the link to the external source.
- Data > Edit Links > Break Link: Severs all connections to a specific source workbook at once, converting formulas to values.
- Find and Select > Formulas: Quickly highlights all cells containing formulas, including those with external references, for batch conversion.
Why External Links Turn Into #REF! Errors
An external reference, or link, is a formula that pulls data from a cell in another Excel workbook. A formula like ='[Budget.xlsx]Sheet1′!$A$1 directly references cell A1 from the Budget file. Excel needs to access that source file to calculate the formula’s result. When the source workbook is open, Excel can read the data directly from its memory.
When you close the source workbook, Excel can no longer retrieve the live data. To prevent showing incorrect information, Excel changes the reference to a #REF! error. This is a safety feature. It warns you that the data is not currently available from its original source. The formula itself is still intact, but the path to the data is broken while the file is closed.
Re-opening the source workbook will typically restore the values and remove the #REF! errors. However, you often need to share or archive a report without its supporting files. In these cases, you must convert the formulas to their calculated values. This action removes the dependency on the external file permanently.
Steps to Convert External References to Static Values
Converting formulas to values replaces the link with the number or text it displayed. The value becomes a regular piece of data in the cell. Use this method when you no longer need a live connection to the source data.
Method 1: Convert Individual Cells or Ranges with Paste Special
- Select the cells with #REF! errors
Click on the cell or drag your mouse to select a range of cells containing the broken formulas. - Copy the selected cells
Press Ctrl+C on your keyboard or right-click the selection and choose Copy. - Open the Paste Special dialog
Keep the cells selected. Go to the Home tab on the ribbon. Click the small arrow under the Paste button and select Paste Special. - Paste as values
In the Paste Special dialog box, select the Values option. Click OK. The #REF! errors will be replaced with the last calculated values from the formulas.
Method 2: Break All Links to a Specific Source Workbook
This method is efficient when your file has many formulas linking to one external source you want to remove.
- Open the Edit Links dialog
Go to the Data tab on the ribbon. In the Queries & Connections group, click Edit Links. - Select the source workbook
The Edit Links dialog lists all external workbooks your file references. Click on the name of the closed workbook causing the #REF! errors. - Break the link
Click the Break Link button. A warning message will appear stating this action converts all formulas to values. Click Break Links to confirm. All formulas referencing that workbook will be replaced with their current values.
Method 3: Find and Convert All Formulas in the Sheet
- Select all formulas
Click the Find & Select button on the Home tab. Choose Go To Special from the menu. In the dialog, select Formulas and click OK. This highlights every cell containing a formula. - Copy and paste as values
With all formulas selected, press Ctrl+C to copy. Then, without changing the selection, open the Paste Special dialog (Home > Paste > Paste Special). Choose Values and click OK. This converts every formula in the sheet to a value, including internal ones.
Common Mistakes and Limitations When Converting Links
Data Becomes Static and Won’t Update
After you convert a formula to a value, the connection is gone. If the source data changes later, your pasted values will not update. This is the intended result. Only use this technique on data you are finalizing. For reports needing periodic updates, keep the source files together and open them before refreshing.
Breaking Links Affects All Formulas From That Source
The Break Link command in the Edit Links dialog is absolute. It converts every formula linked to that specific external workbook. You cannot selectively break some links and keep others to the same file. If you need to keep some links active, use the Paste Special method on specific cell ranges instead.
#REF! Errors Persist After Using Paste Special
If a cell shows #REF! and you use Paste Special > Values, you will paste a #REF! error as a static value. To avoid this, you must perform the paste operation while the source workbook is open and the formula is displaying the correct value. Open the source file, let the formulas calculate, then convert them to values.
Paste Special vs. Break Link: Key Differences
| Item | Paste Special > Values | Edit Links > Break Link |
|---|---|---|
| Scope of Action | Works on the currently selected cells only | Affects every formula linked to the chosen source workbook in the entire file |
| Control | High precision; you choose exactly which cells to convert | Broad control; converts all links from one source, cannot exclude specific formulas |
| Best Use Case | Converting a specific table or range of external data | Permanently removing all dependency on a single, entire source file |
| Result on #REF! Errors | Pastes the visible error as a static value if source is closed | Converts the formulas to their last known values before the link was broken |
| Location of Command | Home tab, Paste Special dialog | Data tab, Edit Links dialog |
You can now stop #REF! errors by converting external links to values. Use Paste Special for selective updates or Break Link to remove an entire file dependency. For advanced control, use the Find and Select tool to target all formulas at once. Remember to open source workbooks before converting to ensure you capture the correct data.