Named Ranges are a set of selected cells in Microsoft Office Excel which can be identified by a unique name. These serve a good purpose when you want to refer them across sheets or even when you create protection for parts of Excel files for different users. However, when there are too many of them, it becomes difficult. In this post, we are looking at the Name Manager that can help you manage Named Ranges better.
Why Do You Need a Name Manager?
Now, let’s assume you have a huge excel file with around ten sheets, and each sheet has around seven named ranges. Trying to find a named range in a big spreadsheet can be a nightmare. As you type in the Name box, Excel will suggest the names that match.
Last few weeks, I had been working in a similar scenario and was surprised how I missed the feature called Name Manager, which is present in Excel by default.
This not only helps you to find all the named ranges in the excel file but also to add new named ranges, edit and delete existing named ranges, and save a list of named ranges. You can even set the scope of each of them.
For example, if you have a named range ‘Data’, you want to use it throughout your workbook. But at the same time, you have multiple sheets and a huge amount of data in one sheet. There is a possibility to use the Name Manager to rename the range.
Manage Named Ranges Better with Name Manager in Excel
To open the Name Manager dialog box, follow the steps as below:
- Select any cell in the workbook.
- Choose Formulas > Defined Names > Name Manager.
The Name Manager dialog box displays the following information about each name in a list box: Name, Value, Refers to Scope, and Comment. Select the column headings to sort the list in ascending or descending order. On top of this, you get the power to filter them and find how many of them have errors while creating them.
If there is a change needed, click on the edit button. It will let you change fields such as Name, Range, and few other things.
So go ahead and try Name manager and make your life more productive. Named Ranges are Beneficial. and can be used in many scenarios, such as creating dashboards, reports, and lists.
