Easy Way to Manage Names in Excel

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.