How to Find a Missing Named Range Used by an Excel PivotTable
Question details
The user needs to find a named range acting as the source data for an existing PivotTable, which is currently hidden or missing from the Name Manager.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Auditing or modifying an existing PivotTable's source data when the associated named range is not visible in the Name Manager.
- Observed behavior
- The named range used by the PivotTable does not appear in the Name Manager, preventing the user from viewing or editing the bounds of the source data.
Ensure your workbook structure is not protected, as workbook protection will gray out the option to unhide worksheets where missing named ranges might be stored.
Unhide Worksheets to Reveal Missing Named Ranges
Use this solution to reveal sheet-scoped named ranges that disappear from the Name Manager when their parent worksheet is hidden.
In Excel, named ranges can be scoped either to the entire workbook or to a specific worksheet. If a named range is scoped to a specific worksheet and that sheet is hidden, the named range will no longer appear in the Name Manager. Unhiding the sheet restores its visibility.
Navigate to the bottom of your Excel window where the worksheet tabs are located, and right-click on any currently visible tab.
Select 'Unhide...' from the context menu to display a list of all hidden worksheets in the workbook.
Select the hidden worksheet that contains your data (for example, 'Extract' or 'Data_Source') and click OK.
Go to the Formulas tab on the ribbon and click 'Name Manager'. The missing named range should now be visible and available for editing.
Easily Manage PivotTables and Named Ranges with WPS Office
WPS Spreadsheet provides an intuitive and lag-free environment to manage complex named ranges, unhide sheets, and update PivotTable data sources effortlessly.
- 1. Unhide your worksheets: Open your spreadsheet in WPS Office, right-click the sheet tabs at the bottom, and select 'Unhide' to reveal hidden data.
- 2. Open the Name Manager: Navigate to the Formulas tab and click 'Name Manager' to easily locate and manage your named ranges.
- 3. Update the PivotTable source: Click your PivotTable, go to the PivotTable Analyze tab, and select 'Change Data Source' to apply your verified named range.
Frequently Asked Questions
Why is my named range completely missing from the Name Manager?
Named ranges are often scoped to a specific worksheet rather than the entire workbook. If the worksheet it is scoped to is hidden by the user or a macro, Excel will temporarily hide the named range from the Name Manager.
How do I check exactly which named range my existing PivotTable is using?
Click anywhere inside your PivotTable to reveal the PivotTable Tools. Go to the PivotTable Analyze tab on the ribbon and click 'Change Data Source'. The dialog box will display the exact named range or the cell references supplying data to the table.
Why is the 'Unhide' option grayed out when I right-click a worksheet tab?
This happens if the workbook structure is protected. You need to navigate to the Review tab and click 'Protect Workbook' (entering a password if required) to unprotect it before you can unhide worksheets.
