Others

How to Display Cell References Behind Named Ranges in Excel

John Wilson Oct 21, 2026 869 views

Question details

The user needs a way to view and list all the underlying cell references for the single-cell named ranges present in their Excel workbook.

How to Display Cell References Behind Named Ranges in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing a complex workbook with multiple single-cell named ranges where underlying references are hidden.
Observed behavior: The cell references are hidden behind defined names, making it difficult to track or audit formulas, requiring a method to extract and display them.

Before you start

Before generating the list of named ranges, ensure you select an empty area in your worksheet, as the pasted list will overwrite any existing data in those cells.

Solution 1Recommended

Use the Paste Names Feature to List References

Excel provides a built-in command to instantly paste a complete list of all defined names and their corresponding cell references into your worksheet.

This method is the quickest way to audit all named ranges in your workbook without having to open the Name Manager repeatedly.

Select a blank cell

Click on an empty cell in your active worksheet where you want the list of named ranges to begin.

Navigate to Formulas

Go to the 'Formulas' tab located on the Excel ribbon.

Access Use in Formula

Click on 'Use in Formula' in the Defined Names group.

Paste the list

Select 'Paste Names...' from the drop-down menu, then click the 'Paste List' button in the dialog box. The names and references will populate directly in the sheet.

Use the Paste Names Feature to List References

Space Requirement: The pasted list requires two columns: one for the name and one for the cell reference. Make sure there is enough blank space to accommodate the entire list.

Solution 2

View References via the Name Manager

If you only need to view the references without printing them onto the worksheet, the Name Manager is a perfect alternative.

Seamless Spreadsheet Management

Easily Manage Named Ranges in WPS Spreadsheet

WPS Office Spreadsheet provides intuitive tools for managing defined names and tracking cell references, making formula auditing a breeze.

1. Open your workbook in WPS: Launch WPS Office and open your spreadsheet file.
2. Go to the Formulas tab: Click on the 'Formulas' tab at the top of the interface.
3. Open Name Manager: Click 'Name Manager' to view, edit, or manage all defined names and their corresponding cell references.

Quickly paste and audit named range referencesFully compatible with Microsoft Excel (.xlsx) formatsIntuitive UI for effortless Name Manager navigationLightweight application that runs smoothly on any device

Frequently Asked Questions

Will the pasted list of named ranges update automatically if I change a reference?

No, the pasted list is static. If you modify or add new named ranges later, you will need to clear the old list and use the Paste List command again to get the updated references.

Why is the Paste Names option grayed out?

The 'Paste Names' option will be grayed out if there are no named ranges defined in your workbook. Ensure that you have created at least one named range before attempting to paste the list.

Can I edit the cell references directly from the pasted list?

No, editing the pasted text on the worksheet will not change the actual named range references. To modify a reference, you must use the Name Manager under the Formulas tab.

Office Quick Solutions