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.