How to Extract a List of Named Ranges in Excel

Named ranges are an essential feature in Microsoft® Excel®.  They are easy to use in formulas and can be used anywhere in a workbook. The trick comes in when you have to remember all the defined names you have created. 

An easy way to remember all the named ranges would be to create a list on a separate worksheet that you can easily reference while creating your report. 

Download the workbook to practice. 

To obtain a list of your named ranges follow these easy steps:

  • Select a blank cell in your workbook
  • Under the Formulas menu item, select Use in Formula
  • Select Paste Names… at the bottom of the list

 

 

A window pops open with a list of all your named ranges.  Select Paste List. 

A list of all named ranges together with their respective formulas is copied to the worksheet.

 

Why should I use Named Ranges?

Named ranges are a handy feature of Excel. It can make your formulas easier to understand (and debug), help to simplify the creation of complicated spreadsheets, as well as simplify your macros.

A named range is a range (either a single cell, or a range of cells) to which you assign a name. You can use that name in place of normal cell references in formulas, in macros, and for defining the source for graphs or data validation.

Using a range name, like TaxAmount, in place of a standard cell reference, like Sheet2!$C$11, can make a spreadsheet easier to understand and debug/audit.

For example, Version 1 (without named ranges) uses normal A1-style cell references in its formulas (shown in the formula bar below).

 

Version 2 using named ranges, making its formulas much easier to understand. Named ranges also make it easier to enter formulas, since Excel will display a list of names, including function names, that you can pick from. Start to type a name in a formula, then double-click the name in the pick list to add it to your formula.

 

Anonymous