If you work with named ranges in Excel, you know they make formulas easier to read, but finding every named cell in a large workbook can be a hassle. This list covers ten practical methods to display, locate, and manage named cells—from the built-in Name Box to advanced Go To tricks. Whether you’re a beginner or a power user, you’ll find a technique that fits your workflow.
1. Use the Name Box Drop-Down
The Name Box sits to the left of the formula bar. Click the arrow next to it to see a drop-down list of all defined names in the current workbook. Selecting any name instantly jumps to that named cell or range, making it the quickest way to navigate to a known name.
2. Open the Name Manager
Press Ctrl+F3 (or go to Formulas → Name Manager) to open the Name Manager dialog. It lists every name, its current value, the cell reference it points to, and the scope (workbook or specific sheet). From here you can edit, delete, or filter names by scope—essential for auditing large workbooks.
3. Use the Go To Special Feature
Press F5 (or Ctrl+G) to open the Go To dialog, then click “Special…” and select “Names.” This highlights all cells that are part of any named range on the current sheet. It’s a visual way to see exactly which cells are named, especially when names overlap or are non-contiguous.
4. Apply the Name Box to Select a Named Cell
If you know the name, type it directly into the Name Box and press Enter. Excel will select that range, even if it’s on another sheet. This works for single cells and multi-cell ranges, and it’s faster than hunting through menus when you remember the name.
5. Use the “Use in Formula” Drop-Down
When writing a formula, go to the Formulas tab and click “Use in Formula.” A drop-down appears listing all names; selecting one inserts it into your formula. While not for navigation, it shows all names in one place and helps you avoid typos.
6. Create a List of Names with the “Paste Names” Feature
In the Paste dialog (Ctrl+V after copying), click “Paste Names” (or press F3) to open a list of all names. Select one and click OK to paste it as text into a cell. This is handy for creating a reference sheet that documents all your named ranges.
7. Use the Name Manager’s Filter Options
The Name Manager has a filter button that lets you show only names with errors, names scoped to a specific sheet, or names defined on the current sheet. This narrows down a long list, making it easier to spot a particular named cell without scrolling.
8. Navigate with the “Go To” Dialog
Press F5 to open the Go To dialog. It displays a list of all named ranges in the workbook (along with any previous cell references you’ve used). Double-click a name to jump to it. This is especially useful when you have many names and want to see them all at once.
9. Use the “Name Box” to Jump to a Named Cell on Another Sheet
Even if the named cell is on a different worksheet, typing its name in the Name Box and pressing Enter will take you there immediately. This works across sheets and workbooks, saving you from switching sheets manually.
10. Use VBA to List All Named Cells
If you need a complete inventory, a simple VBA macro can loop through all names and print their references to a new sheet. For example, a short subroutine can iterate over ThisWorkbook.Names and output the name and RefersTo range. This is a powerful option for advanced users who want a permanent record.
How to Choose
The best method depends on your task. For quick navigation, the Name Box drop-down or F5 Go To dialog are fastest. For managing or deleting names, the Name Manager is essential. If you need to audit which cells are named, Go To Special or a VBA list will give you a visual or printable overview. Consider whether you need to see the actual cell addresses (Name Manager) or just jump to the cells (Name Box). For a one-time check, Go To Special is sufficient; for ongoing documentation, generate a list with VBA.
Frequently Asked Questions
Q: How do I see all named ranges in Excel?
A: Press Ctrl+F3 to open the Name Manager, which lists every defined name, its value, and its cell reference.
Q: Can I jump to a named cell from another sheet?
A: Yes, type the name in the Name Box and press Enter, or use F5 and select the name from the list.
Q: Why don’t some named cells appear in the Name Box drop-down?
A: The Name Box shows only names scoped to the current sheet, not the entire workbook. Use the Name Manager (Ctrl+F3) to see all names.
Q: How do I highlight all named cells on a sheet?
A: Press F5 → Special → Names. This selects all cells that are part of any named range on that sheet.
Q: Can I print a list of all named ranges?
A: Yes, use the Paste Names feature (F3) to paste the names into cells, then print that sheet. Or run a VBA macro to generate a list.
Q: Is there a way to see named cells without opening the Name Manager?
A: Yes, use the Go To dialog (F5) which lists all names, or the Name Box drop-down for sheet-specific names.
Final Thoughts
Knowing how to display and navigate named cells can save you time and reduce errors, especially in complex workbooks. Start with the Name Box and Name Manager for everyday tasks, and explore Go To Special or VBA when you need a broader view. Each method has its place; the key is to pick the one that matches your immediate need.