Exceljet

Quick, clean, and to the point
From the video: How to manage named ranges

How to manage named ranges

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

After you've created a named range, you may want to modify it's name, or change the cells it references.

You can easily do this 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 all formulas that use that name will be updated automatically to use the the new name.

Notice that if you only need to update the reference for 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.

There's also a way in Excel to get a list of all defined names in a workbook directly in a worksheet.

First select an empty cell on the worksheet.

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

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

Author 
Dave Bruns