Easy Way to Manage Names in Excel

Add to Favorites

How to manage Names in Excel so they are easy to view and change as needed.

This is a little trick that I use to keep all of my Names in Excel in one place where I can easily access them.

Names are pieces of data that can be used in formulas and functions.  Names store basically anything you want, a cell reference, a formula or function, text, or numbers.  Using them can greatly improve the readability and usability of your spreadsheet.

To learn more about what is a Name is and how to use it, check out our tutorial on how to create and use a Defined Name.

The Key to Managing Names in Excel

The key is to keep all Names and their values on a separate worksheet within the workbook.

Note that the values above are on a separate worksheet tab called Values.

When you add a Name to the Name Manager, give it the same name as the names in Column A and make the Names reference the cells in Column B. 

In the Name Manager it looks like this:

You can see that we used cell references in the Name Manager instead of hardcoding the values into the Names.  These cell references point to the values on the Values worksheet.

This allows us to be able to easily change the values for the Names by changing what is in the Values worksheet.

The benefit of doing this instead of just using the Name Manager and hard-coding values is that this method is often easier for users to understand and to manage.

Hide the Value Tab

Once you have the values input into the Values worksheet and have created the Names, simply hide the Values worksheet so that it cannot be seen by default.  This will make it so that a user cannot accidentally change these values since they would have to first unhide the worksheet to even see it.

To hide a worksheet simply right-click it and select Hide.

To view it again, right-click another worksheet tab, click Unhide and select the desired sheet and hit OK.

Notes

You do not have to organize your Names this way but, in many cases, I have found that this method is very helpful for keeping track of Names, especially when you have a lot of them in a spreadsheet or when you may need to change them relatively often.

Make sure to download the spreadsheet that accompanies this tutorial so you can see this technique being used.


Downloadable Files: Excel File

Similar Content on TeachExcel
Enter a Constant Value in a Defined Name in Excel - Text, Numbers, Formulas, Etc.
Tutorial: In Excel you can store values in Defined Names.  Often people use a Defined Name to refer...
Count the Number of Cells that Start or End with Specific Text in Excel
Tutorial: How to count cells that match text at the start or the end of a string in Excel. If you w...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a...
How to Add Boxes, Buttons, Arrows, Speech-Bubbles, Hearts, and More to a Spreadsheet in Excel
Tutorial: In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as...
Using Formulas with Tables in Excel
Tutorial: Easy way to reference data tables and make formulas within a data table. This allows you ...
Naming Cells in Excel to Make Using Formulas/Functions Easier
Tutorial: In this tutorial I am going to introduce the idea of Named Cells. A Named Cell is a cell w...