How to fix a reference not valid in excel.

If Excel shows "reference not valid" and then freezes with the window grayed out, use this sequence:

1. Force close Excel first Since Excel is unresponsive, close it with Task Manager.
   • Press Ctrl + Shift + Esc
   • Select Microsoft Excel
   • Choose End task
2. Reopen Excel and test the workbook again If the problem happens only with that workbook, the issue is likely inside the file rather than Excel itself.
3. Check for invalid references in the workbook A #REF! error means a formula refers to a cell that is no longer valid. This commonly happens when referenced cells were deleted or pasted over.
   • Look for cells showing #REF!
   • Review formulas that point to deleted or moved cells
   • If the workbook uses INDIRECT to another workbook, open the referenced workbook first, because INDIRECT to a closed workbook can return #REF!
   • If the workbook uses OLE links, start the linked program
   • If it uses DDE, verify the topic reference and check Trust Center Settings for external content
4. If the freeze started after adding drawing objects, inspect file contents Excel performance and crashing can be caused by large numbers of shapes or complex shape objects, as well as hidden objects, excessive styles, or invalid defined names. Since the issue started after minor drawing changes, shapes in the workbook are a likely area to check.
5. Update Excel There is a documented issue where Excel may prompt for file access and then hang or show a warning that it cannot open the file correctly. That issue was fixed in version 16.34 and later. Updating Excel is recommended.
6. If the file opens but stays locked or read-only If the workbook is being co-authored or opened from a shared location, unsupported features can cause lock behavior. The file owner can try:
   • Turning off the Shared Workbook feature
   • Removing password encryption or restricted access from File > Info > Protect Workbook
   • Turning off Refresh data when opening file on tables and PivotTables
   • Disabling Excel add-ins from File > Options > Add-Ins > Excel Add-ins > Go
7. If the error appears while creating or using a PivotTable If the workbook filename contains square brackets such as [ ], Excel can show "Data source reference is not valid". Rename the workbook to remove invalid characters.

If Excel freezes again immediately when opening that file, the safest next check is whether the workbook contains broken formulas, external links, or shape objects added during the drawing changes.

References:

• How to correct a #REF! error
• Excel not responding, hangs, freezes or stops working
• Excel prompts you to grant access to files and then hangs or shows a warning that it cannot open the .xlsx file because the format or extension is not valid
• Excel file is locked for editing
• Excel PivotTable error "Data source reference is not valid"