How to manage range names

Range names can be very useful in Microsoft Excel. You can use them for quick navigation to any location by pressing F5 and selecting a range name, or you can use them in formulas, making the formulas easier to understand. If you have just a few range names, they are easy to manage — but when you have a lot, having a way to manage them becomes important, and that is where the Name Manager is very helpful.

Memory usage. The Name Manager is a VBA add-in, so it fully opens all the time with little impact on memory. You can have many VBA Excel add-ins open at one time; opening just several COM add-ins at one time can cause problems. All of our add-ins are VBA add-ins.

Download and installation. The download is a zip file. Unzip it to a folder in your My Documents — we put ours in My Documents\Excel add-ins\Name Manager. You can install manually by opening the Excel add-ins dialog, clicking the Browse button, navigating to the installation folder and selecting Name Manager.xla. Or, if you allow macros to run, you can use the install workbook “Setup Name Manager.xls” instead.

When installed, you will find a new menu under the Add-ins ribbon. Clicking it expands to show the following options:

When the menu item “Name Manager…” is clicked, a dialog like the following appears:

Mousing over the buttons provides tooltip help. For help, there is a Word document in the zip file titled “Name Manager Manual 41.doc”, which is fairly extensive. Using the filter “Local to any sheet” shows range names that are sheet-specific rather than workbook global — such sheet-specific names can be created accidentally by copying sheets from one workbook to another.