4 Fixes for “Reference Isn’t Valid” Error in Microsoft Excel for Windows

Quick Tips

• Excel typically shows this error if a formula refers to a cell or data source that is no longer valid or available, so ensure you haven’t hidden any worksheets.
• Excel’s Design Mode can trigger this error, so it’s best to turn it off.
• Saving your Excel file to a different location with a different name can also help resolve this particular error.

Try Basic Fixes

• Close and reopen Excel: Microsoft Excel sometimes gets stuck with the “Reference isn’t valid” error due to temporary issues with one of its app processes. Several users on a Microsoft Community post reported getting rid of this error by closing Excel completely and reopening it. You can also try it.
• Rename the Excel file: Using square brackets in the name of your Excel file can also cause errors like “Reference isn’t valid” on Windows. To avoid this, rename your file to remove any square brackets.

Fix 1: Unhide Worksheets

Another common reason Excel may throw this error is if your formula refers to a cell or data source that is no longer valid or available. This can happen if you have hidden some worksheets in your Excel file. Here’s how to unhide them.

Step 1: Open your Excel workbook, right-click on any worksheet tab at the bottom, and select Unhide.

Step 2: Select the sheet you want to unhide and click OK.

Fix 2: Exit Design Mode

Editing cells in Excel’s Design mode can also trigger the “Reference isn’t valid” error. If so, all the ribbon items under the Developer tab will also appear grayed out. Here’s how to fix that and resolve the error.

Step 1: In your Excel workbook, select OK when the “Reference isn’t valid” dialog box appears.

Step 2: Click the X icon on the toolbar at the top.

Step 3: Click the Design Mode button to deactivate it.

Fix 3: Enter the Correct Cell Range Name in the Pivot Table

Do you see the “Data source reference is not valid” error in Excel while creating a pivot table? That could happen if you enter an invalid or unavailable cell range name in the pivot table field. To avoid this, create a new named range and use it to create a pivot table.

Step 1: Open your Excel workbook and select the cells you want to include in the named range.

Step 2: Navigate to the Formulas tab and click on Name Manager.

Step 3: Click on New.

Step 4: Type a name for the cell range in the Name field and click OK.

Step 5: Switch to the Insert tab and click on PivotTable. Enter a name for the cell range in the Table/Range field and click OK to create a pivot table.

Fix 4: Save the Excel File to Your Local Drive

Such Excel errors can also occur if you edit a workbook that isn’t stored on your local drive. Try saving your Excel file on your local drive to see if that solves the problem.

Open your Excel file > press the F12 key on your keyboard > navigate to your preferred location in the Save As dialog box > click OK.