How to manage named ranges

In this video we'll look at the tools that Excel provides for managing named ranges.

After you've created a named range, you may want to modify its name, or change the cells it references. You can easily do this by using a feature called the "Name Manager."

You can find the Name Manager on the Formulas tab of the ribbon. Just click to open. You can also open the Name Manager using the keyboard shortcut Control + F3.

Once the Name Manager is open, you have several options. You can create a new name with the New button.

You can also edit an existing named range with the Edit button. Note that if you change the name of a named range, all formulas that use that name will be updated automatically to use the new name.

Notice that if you only need to update the reference to a named range, you don't need to click Edit. You can just select the named range in the list, and modify the reference in the "Refers to" box below.

Finally, you can delete named ranges using the Delete button. If you delete a named range that's being used in a formula, you'll see a #NAME error on the worksheet.

The Name Manager works well as a way to review all of the named ranges in a workbook. It also has a handy Filter button that allows you to see just the names you're interested in.

If you'd like to get a list of all defined names directly on a worksheet, there's a way to do that, too.

First, select an empty cell on the worksheet.

Next, click the "Use in Formula" menu, and select Paste Names. When the Paste Names dialog box appears, click the button called "Paste List." Excel will generate a full list of all names and references.

This is a static list, so it won't change as you add or update named ranges. When you use this feature, make sure that you select a cell with empty cells below and to the right, so that you don't accidentally overwrite important data.