When a formula in Excel returns a #REF! error or a broken reference, it can stop your entire spreadsheet from calculating correctly. These errors often appear after you move, delete, or rename cells, worksheets, or external workbooks that a formula depends on. Copilot in Excel can analyze your formulas and identify the exact source of the broken reference without you manually tracing each cell. This article explains how to use Copilot to audit broken formula references, what prerequisites are required, and which settings affect its ability to scan your workbook.
Key Takeaways: Using Copilot to Find and Fix #REF! Errors in Excel
- Copilot pane > Formula audit > Analyze references: Sends a request to Copilot to scan all formulas and return a list of cells with broken references.
- Ctrl+Shift+F5: Opens the Go To dialog to quickly navigate to a specific cell that Copilot identifies as broken.
- File > Options > Formulas > Enable iterative calculation: Must be turned OFF when auditing broken references, otherwise Copilot may skip circular references.
How Copilot in Excel Detects Broken Formula References
Copilot uses the Microsoft Graph and the Excel calculation engine to read the formula dependency tree in your workbook. It identifies cells that contain #REF!, #VALUE!, and #N/A errors caused by missing or invalid cell references. Copilot does not modify your formulas automatically. It returns a list of affected cell addresses and the formula text that is broken. To use this feature, you need a Microsoft 365 subscription that includes Copilot for Microsoft 365. The workbook must be saved to OneDrive or SharePoint Online. Copilot cannot audit formulas in local-only files or in files opened from network drives.
Prerequisites for the Audit
Before you start, confirm that your Excel version is Version 2402 or later. Check this by going to File > Account > About Excel. The Copilot button appears on the Home tab of the ribbon. If it is grayed out, your workbook may be in compatibility mode. Save the file as .xlsx format. Also ensure that the workbook contains at least one formula with an error. Copilot will not run an audit on an empty or error-free sheet.
Steps to Audit Broken Formula References with Copilot
- Open the workbook and enable Copilot
Open the Excel workbook that contains the broken formula references. Click the Copilot button on the Home tab of the ribbon. The Copilot pane opens on the right side of the screen. - Select the sheet with the error
Click the sheet tab that contains the formula you suspect is broken. Copilot only scans the active sheet unless you specify a range. To audit the whole workbook, first select all sheets by right-clicking any sheet tab and choosing Select All Sheets. - Type the audit prompt in the Copilot chat box
In the Copilot pane, type: “List all cells with broken formula references in this sheet.” Press Enter. Copilot processes the formulas and displays a numbered list of cell addresses and the broken formula text. - Review the results in the Copilot pane
Copilot shows each cell address, the formula it contains, and the type of error. For example: “Cell C5 contains =SUM(A1:A10) but A1:A10 are deleted. Error: #REF!” Scroll through the list to see all broken references. - Navigate to each broken cell
Click any cell address in the Copilot response. Excel jumps to that cell and highlights it. Alternatively press Ctrl+Shift+F5, type the cell address, and click OK. - Fix the reference manually
Correct the formula in the formula bar. For a missing sheet, update the sheet name in the reference. For a deleted cell range, adjust the range to a valid one. Copilot does not auto-correct broken references. You must edit the formula yourself. - Re-run the audit to confirm fixes
After you fix one or more formulas, type “Check for remaining broken references” in the Copilot pane. Copilot scans again and shows only the errors that still exist.
Common Issues When Auditing with Copilot
Copilot Returns No Results Even Though Errors Exist
Copilot cannot audit formulas that reference external workbooks that are closed. Open the external workbook first, then re-run the audit. Also check that the workbook is saved to OneDrive or SharePoint. Local files are not supported.
Copilot Lists the Wrong Cell
If your workbook contains merged cells, Copilot may return the merged cell address instead of the individual cell that holds the formula. Unmerge the cells before running the audit. You can unmerge by selecting the merged cells and clicking Merge & Center on the Home tab.
Copilot Does Not Recognize Indirect References
Formulas that use INDIRECT, OFFSET, or ADDRESS functions are not scanned because their references are built dynamically at runtime. Copilot only audits static cell references. To find broken indirect references, use the Evaluate Formula tool on the Formulas tab instead.
Copilot Audit Is Slow on Large Workbooks
Workbooks with more than 50,000 formulas may cause Copilot to time out. Limit the audit to a specific range by typing: “List broken references in range A1:Z100.” This speeds up the scan.
| Method | Copilot Audit | Manual Trace Dependents |
|---|---|---|
| Time to find one broken reference | 5 to 10 seconds | 2 to 5 minutes |
| Supports external references | Only if external workbook is open | Yes, even if closed |
| Lists all broken cells at once | Yes | No, one cell at a time |
| Works on local files | No | Yes |
| Requires Microsoft 365 Copilot license | Yes | No |
You can now use Copilot in Excel to quickly locate every broken formula reference in your workbook. Start by opening the Copilot pane and typing the audit prompt. After Copilot lists the broken cells, navigate to each one and correct the formula manually. For workbooks with indirect references or closed external links, use the manual Trace Dependents tool on the Formulas tab. This combination gives you a complete audit workflow.