How-to Show All Named Ranges/Formulas in a List

In our previous posts we discussed how to build your dynamic Excel dashboard template charts by using an Offset formula and named ranges/formulas.  Then in our last post we showed you how-to quickly paste your named ranges/formulas into a chart series dialog box without having to remember the exact name by using the F3 function key.  But what if you have lots and lots of named ranges/formulas in your spreadsheet and you don’t know what is defined for each of the named ranges/formulas?

Is there an easy way to show what you have defined for each name without keeping my own list?

Yes there is!

Paste List of Names in Excel

In order to have Excel create this list for you, you must first select a cell where you want the list.  I suggest that you choose a blank cell in a new worksheet as Excel will overwrite any and all entries in the cells below the chosen cell with the formula list.





Excel provides this functionality quickly and easily if you know where to look.  In Excel 2007, it can be found in the Formulas Ribbon > Defined Names Group > Use in Formula (list) > Paste Names (selection).

FormulasMenuUseInFormulaSelected

UseInFormulasMenuSelectingPasteNames

Alternately you can also press the F3 function key and the Paste Names dialog box will open up.F3Key

This will then bring up the Paste Names dialog box.



PasteNameDialogBoxPasteListSelected

You should then choose the Paste List button in the bottom left of the Excel dialog box.

Note: This button will not be active if you are editing a cell in the worksheet.  In order to create the list, you need to select a cell, not be in edit mode for the worksheet cell.

The Result

After you press the Paste List button from the Paste Names dialog box, Excel will display your Defined Names and Named Ranges in a list with the first column displaying the name and the second column displaying the formula or range.  It will look something like this:





PasteListResults

I now have a way to see all the named ranges and formulas in a worksheet.  This will be handy as you build your dashboard templates or if you are opening a file for the first time.

Steve=True





2 COMMENTS

  1. Hello Steve,
    I wonder why there are no likes/comments for these excellent articles. I really enjoyed learning from your website and would truly appreciate your work.

    Best Regards,
    Naveen

LEAVE A REPLY

Please enter your comment!
Please enter your name here