Excel #VALUE! error after copying workbook — real fix

Quick answer

Use Find & Replace (Ctrl+H) to replace the old file path (e.g. C:\Users\old\[workbook.xlsx]) with nothing. That strips the broken external reference from every formula at once.

Why this happens

When you copy a workbook — whether by Save As, dragging the file, or using Windows Explorer — Excel doesn't rewrite formulas that reference the original file. What you're actually seeing is a formula that still says =SUM([OriginalFile.xlsx]Sheet1!A1:A10). The original file might not be open, or it's moved, or it's a different version. Excel can't evaluate that reference, so it throws #VALUE!.

The real trigger? You probably copied a sheet or a workbook that contained external references (also called cross-workbook formulas). These are different from plain cell references like =A1+B1. An external reference includes the source workbook name in square brackets, plus the sheet name and an exclamation mark. When the source workbook isn't available, every formula using that external reference fails.

How to fix it — the one that works

1. Open the copied workbook that's showing #VALUE! errors.
2. Press Ctrl+H to open the Find & Replace dialog.
3. Click Options >> to expand the advanced options.
4. In the Find what field, type the path to the original workbook — something like C:\Users\YourName\Documents\[OriginalWorkbook.xlsx]. Don't forget the square brackets.
5. Leave the Replace with field blank.
6. Set Look in to Formulas (not Values or Comments).
7. Click Replace All.

After that, Excel removes every external reference path from all formulas. The formulas become local references, like =SUM(Sheet1!A1:A10), and the #VALUE! errors disappear because Excel can now evaluate them against the sheets in the current workbook.

If that didn't fix it completely

Sometimes the external reference is hiding inside a named range or a data validation rule. Here's what to check:

• Named ranges: Go to Formulas > Name Manager. Look for any name whose Refers to column includes a file path. Select it, click Edit, and remove the file path portion. Keep only the local sheet and cell reference.
• Data validation: Select all cells (Ctrl+A), go to Data > Data Validation > Settings, and check the Source box. If it shows an external reference, clear it and set the correct local range.

If you still see #VALUE! after those, check for array formulas — they look like {=SUM(...)} with curly braces. Select the cell, press F2, then Ctrl+Shift+Enter to re-enter the array formula. Sometimes that forces Excel to re-evaluate the reference.

Alternative fix: break the link manually

If Find & Replace feels too broad, you can do it link by link:

1. Go to Data > Edit Links.
2. Select the broken link (it'll show the original file path).
3. Click Break Link. Excel will convert those external references to their current values — meaning it replaces the formula with the last calculated result. This is destructive (you lose the formula), but it kills the error instantly.

I don't use this unless there are only a few formulas. For a whole workbook with hundreds of formulas, Find & Replace is way faster and preserves the formulas.

Prevention tip

Before copying a workbook, break all external links explicitly. Go to Data > Edit Links, select each link, and click Break Link. Then save the original workbook. Now when you copy it, the copy won't contain any external references. Alternatively, use Save As with the file type set to Excel Workbook (*.xlsx) — that strips most external references automatically during the save.

Another trick: if you regularly copy workbooks that reference a central data file, store that central file in a shared network folder and use relative paths in your formulas. For example, =SUM('..\Data\[Central.xlsx]Sheet1'!A:A) works even after copying, as long as the folder structure stays the same. Excel's external reference paths are absolute by default — that's the root cause of this whole mess.