This can happen if you have hidden some worksheets in your Excel file.
Heres how to unhide them.
Step 1:Open yourExcel workbook, right-punch any worksheet tab at the bottom, and selectUnhide.
Step 2:Select thesheetyou want to unhide and clickOK.
If so, all the ribbon items under the Developer tab will also appear grayed out.
Heres how to fix that and patch up the error.
Step 1:In your Excel workbook, selectOKwhen the Reference isnt valid dialog box appears.
Step 2:Click theXicon on the toolbar at the top.
Step 3:Click theDesign Modebutton to deactivate it.
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 yourExcel workbookand select thecellsyou want to include in the named range.
Step 2:Navigate to theFormulastab and click onName Manager.
Step 3:Click onNew.
Step 4:Typea name for the cell range in the Name field and clickOK.
Step 5:Switch to theInserttab and click onPivotTable.
Enter a name for the cell range in theTable/Rangefield and clickOKto create a pivot table.
Trysaving your Excel fileon your local drive to see if that solves the problem.
Was this helpful?
The content remains unbiased and authentic and will never affect our editorial integrity.
