Excel #VALUE! Error When a Linked Workbook Is Closed

Yes—Excel can return #VALUE! when a formula uses certain functions, such as SUMIF or COUNTIF, to evaluate ranges in a linked workbook that is closed. A direct link to a single cell usually can still show its last saved value; whether a closed source causes an error depends on the formula, the link, and whether Excel has a usable saved result.

What the error means

A linked workbook is another Excel file whose cells are referenced by a formula in the workbook you are using. For example, a sales summary might draw figures from a separate file called Sales.xlsx. If that source file is closed, Excel may still display values from the saved link information, but some calculations need to evaluate the source range itself. If Excel cannot do that calculation while the source is unavailable, the result can be #VALUE!.

This is not the same as every external-link problem. #VALUE! generally means a formula has encountered a value or operation it cannot use as written. A broken path or a renamed source sheet may instead lead to #REF!, while an unavailable lookup result may produce #N/A. The error alone is a clue, not proof that the source being closed is the cause.

Excel for Microsoft 365 on Windows is the focus here. The same basic behavior applies in current Excel for Mac, though some link-management controls may look different. Excel for the web has limitations around certain workbook links and should not be assumed to behave exactly like the desktop app.

Why a closed source can trigger #VALUE!

Many formulas that point to another workbook can read a saved cell value without opening the source file. Criteria-based formulas are a notable exception: formulas such as SUMIF and COUNTIF can return #VALUE! when their referenced criteria range is in a closed external workbook. These formulas must assess which source cells meet a condition, rather than merely retrieve one saved result. Depending on the function and workbook setup, Excel may need access to the source range to perform that evaluation.

For example, imagine a summary formula that adds sales for the East region. With the source open, Excel can inspect the region labels, match “East,” and add the corresponding amounts. With the source closed, the same formula may fail even though the link path is correct and the source file still exists. This is a limitation of evaluating that kind of external reference while the source is closed, not necessarily evidence that the underlying sales data is wrong.

Other causes can produce the same error, including incompatible arguments, mismatched range dimensions in a formula, or a formula that tries to perform arithmetic on text. That is why the most reliable test is to open the source workbook and see whether the formula recalculates successfully.

Direct links versus calculations over ranges

A direct external reference asks Excel for a particular cell. With the source file at C:\Reports\Sales.xlsx, the sheet named Data, and a value of 1,250 in B2, the formula below points to that cell. When the source workbook is closed, Excel can often display the value saved with the link, so the result in this example is 1,250.

Assumed layout: the source workbook is C:\Reports\Sales.xlsx, its worksheet is named Data, and cell B2 contains 1,250. The formula returns 1,250 when Excel can resolve the link and has that saved value:

='C:\Reports\[Sales.xlsx]Data'!$B$2

A criteria formula asks for more work. Suppose the source sheet has region names in A2:A5—East, North, East, and West—and amounts of 120, 75, 80, and 60 in B2:B5. The next formula totals the two East amounts. Its result is 200 when the source is open and the formula can evaluate the ranges; with the source closed, it may return #VALUE!.

Assumed layout: in the same source workbook, Data!A2:A5 contains the region names and Data!B2:B5 contains the amounts listed above. The formula returns 200 when evaluated successfully:

=SUMIF('C:\Reports\[Sales.xlsx]Data'!$A$2:$A$5,"East",'C:\Reports\[Sales.xlsx]Data'!$B$2:$B$5)

The important distinction is between retrieving a stored cell result and calculating across an external range. A formula that works while the source is open is not automatically guaranteed to work when it closes.

How to diagnose the cause

Start with a copy of the summary workbook if it is important, then follow a controlled check. Avoid changing several links or formulas at once; a small test makes it easier to identify what fixed the problem.

Open the source workbook from the location referenced by the formula. If Excel asks whether to update links, allow the update only if you trust the source and expect its data to be current.
Return to the summary workbook and check whether the error disappears. If it does, the closed-source behavior is a strong possibility. If it remains, inspect the formula and its inputs for other errors.
Check the formula bar for the actual external path, workbook name, worksheet name, and cell or range references. A moved or renamed file may leave a stale link. A missing reference often causes #REF!, but formula details still need checking.
Confirm that the source file is accessible and that the referenced sheet and cells exist. Network drives, cloud-synced folders, and permissions can affect whether Excel can reach a file.
Check calculation mode under Formulas > Calculation Options. If the workbook is set to Manual, choose Automatic if that is appropriate for your work, then recalculate.
Test a direct cell reference to a known source value. If that works but the criteria formula does not, the problem is more likely the formula’s handling of an external range than a completely broken link.

If the formula still fails with the source open, review its argument order, range sizes, criteria, and data types. For example, one range containing four rows paired with a sum range of a different size can lead to unexpected results. Check for numbers stored as text, error values in the source data, and formulas that refer to the wrong sheet.

Ways to fix or work around it

The right fix depends on whether you need a live calculation, a periodically refreshed report, or a fixed snapshot. Choose a method that matches how current the result must be and how often the source changes.

Open the source while calculating. If the report is used only occasionally, opening the source workbook, updating the links, and recalculating may be the simplest solution. Save the summary after checking the result. A saved result may remain visible later, but do not assume it will refresh while the source stays closed.
Bring the needed data into the summary workbook. Copying or importing the relevant rows into a local data sheet lets criteria formulas operate on ranges in the same file. This is useful when the report must calculate reliably without opening the source, but the copied data needs a defined refresh process.
Use Power Query for recurring imports. In Excel for Microsoft 365 on Windows, use Data > Get Data to connect to a supported file source, load the required table, and refresh the query when needed. The source workbook does not generally need to be open for a file-based import, but the file must be reachable, and query settings, credentials, or organizational policies can affect refresh. Excel for Mac has Power Query capabilities, but available connectors and controls can differ by version.
Use a local helper table. If only a small set of totals is needed, import or copy the source values into a local table and calculate from those cells. This avoids recalculating criteria against a closed external range, while making the data’s update date visible to report users.
Reconsider a fixed snapshot. If the numbers are for a one-time, final report and should not change, paste values into a clearly labeled copy after verifying them. This removes the live dependency, but it also means later source changes will not flow through.

Simply changing SUMIF to another formula is not a universal fix. Alternatives can have their own limitations, and some may still need to evaluate external data. Test the chosen design with the source closed, reopened, moved, and refreshed as relevant to your workflow.

Keeping links reliable

External links are sensitive to file organization. Moving a workbook, changing a folder name, renaming a source file, or changing a worksheet name can make an existing reference stale. Shared reports are especially vulnerable if one person’s formula points to a local folder that other users cannot access.

For a more dependable setup, keep source files in a stable shared location, document which workbook supplies each report, and agree on who updates the data. Before distributing a workbook, close and reopen it on a machine or account that has the intended access. Check both the displayed results and the link status; a number that remains on screen can be a saved result rather than evidence of a successful current refresh.

Excel for Microsoft 365 on Windows provides a Data > Workbook Links interface in supported builds for reviewing and managing links. The exact options can depend on the workbook and update state. Do not use a command to break links casually: breaking a link can replace formulas with their current values and remove the connection needed for future updates. Keep a backup before making structural link changes.

Common misconceptions

“If the value is visible, the source has refreshed.” Not necessarily. Excel may display a value saved from an earlier update. Open the source or refresh the connection, then verify the result and its update time if freshness matters.

“Every formula linked to a closed workbook returns an error.” No. Direct references and many ordinary external references can display saved values. The risk depends on the formula’s calculation requirements, not just the presence of an external link.

“#VALUE! proves the source workbook is missing.” No. A missing or invalid reference more often points toward #REF!, while #VALUE! can have several causes. Open the source as a test, but also inspect the formula and its arguments.

“Turning on automatic calculation repairs the link.” Automatic calculation can prompt Excel to recalculate formulas, but it cannot make an unsupported closed-workbook calculation work or restore a missing file. Calculation mode is worth checking, not a guaranteed cure.

Frequently Asked Questions

Why does SUMIF return #VALUE! when the source workbook is closed?
Excel may need to evaluate the external criteria range to decide which values to add. For certain external-range criteria calculations, a closed source can prevent that evaluation. Open the source to test the cause, or use a local imported table or a refreshable data workflow.

Will opening the linked workbook fix the error?
It often fixes this specific closed-source limitation if the path is valid and the formula is otherwise correct. If #VALUE! remains after opening and updating the source, check the formula arguments, range dimensions, and source data for a separate issue.

Why does a linked value still show after I close the source?
Excel can retain a previously saved or cached result for an external reference. The displayed number does not by itself confirm that Excel has read the latest source data. Reopen or refresh the link when you need a current value.

Can I stop the error without keeping the source workbook open?
Often, yes, by importing the needed data into the destination workbook and calculating on local ranges, or by using Power Query to refresh data from a reachable file. Which option is best depends on how frequently the source changes and whether the report needs live or scheduled updates.

Is #VALUE! the same as #REF! for a workbook link?
No. #VALUE! signals that Excel cannot use a value or perform a calculation as written. #REF! indicates an invalid reference. Either can require investigation, but the remedies differ: inspect calculation and data arguments for the former, and verify the target workbook, sheet, and cell reference for the latter.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.