#REF! identifies a reference problem, but the accompanying message matters. A deleted input and an array blocked by existing cells require different actions.
Read the detailed error
Select the cell and inspect the explanation. Look at the formula for a deleted or invalid reference. If an array result cannot expand, identify the reported output area instead of assuming the source data is gone.
Check the recent change history: a removed column, deleted tab, moved block, or pasted value can explain when the formula stopped working. Preserve newer changes before considering any version restoration.
Repair the correct relationship
For a deleted reference, recover or identify the intended input and update the formula deliberately. Pointing it to the nearest available cell may remove the error while producing a wrong result.
For blocked array output, inspect every destination cell and merged area. Move needed content to a safe location or move the formula to a sufficiently clear output area. Do not clear an entire sheet merely to make the spill succeed.
Check the repaired result
Compare a known record and the expected output dimensions. If the formula references another file, verify that file’s identity and authorization as well; an access-dependent import has additional requirements.
A blank-looking cell can contain a formula, so inspect contents before clearing suspected obstructions. Keep the source formula and a recovery point until the result is reconciled. Finally, check downstream calculations that consumed the error: removing #REF! from one cell does not prove that every dependent summary now includes the intended population or that a previously substituted fallback value has been removed.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.