Create A List Of Named Ranges In Excel

Hello Excellers, Do you use named ranges?. Have you inherited a workbook that contains a lot of named ranges?. In this Excel Tip blog post let’s look at how to create a list of named ranges that are in an Excel workbook.

What Is A Named Range?

A named range is simply a name that refers to either a cell, a range of cells, a constant value or a formula. These names can then be used in a formula to replace a constant value or a cell reference.

For example, if you have a constant value in a cell such as Exchange Rate, you can name that cell that contains your exchange rate value as ‘Exchange Rate’ instead of using A2 in your formulas.

Learn More About Named Ranges

If you want to read more about named ranges I have a blog post right here. In summary, my top three reasons for using named ranges are

Your formulas will make more sense
You can easily edit your formulas
Formulas can be created really quickly

So, if you have a lot of named ranges in your Excel workbook, it can get complex keeping track of them. Or, if you have inherited a workbook, then knowing how the Excel solution works is key, especially if named ranges are used. To reduce complexity, create an extract of all of the named ranges. It only takes a short few steps- if you know where to look.

To open the Name Manager dialog box follow the steps below

Formulas | Defined Names | Name Manager

Alternatively, if you just love a shortcut then press Ctrl+F3

Look all you will, the Name Manager dialog box does not give an obvious way to extract a list of named ranges. We need to use the PASTE LIST option button in the PASTE NAME dialog box.

So we need to get to the Paste List button. To display the dialog box follow the steps below

Formulas | Defined Names | Use In Formula | Paste Name

Once again if you are mad for those shortcuts then just hit F3 and the Paste Name Dialog box appears. I suggest that you click the starting cell of where you want the list to be displayed BEFORE opening the Paste Name Dialog Box.