Resolved: Where and How to use Name Management function in Excel 2011 for Mac

Resolved: Where and How to use Name Management function in Excel 2011 for Mac

So this is one of those things I keep referring back to. One of the key things I do when I work in Excel is control the values that can be entered in certain fields. I am clearly not good at remembering possible options and every now and then I have typos. So imagine if you will a Table where I track different things and one of those columns clearly becomes a list of things. So when I am trying to provide a list of possible values for a field, I want it to bind to that column listing the valid options. It makes programatic referencing much more easy. So if you are familiar with the method of “Data Validation” you know you can bind to a list… but a table / column? No. So clearly we are in dire need of binding to a list, but how can we convert the values of a column to a list? Using the Name Management function to define a Name (“list”) which is defined as the values of a column in a table.

Most people know exactly where to find this option on Excel for PC (say Excel 2013). Simply go to Formulas -> Name Management. But where is Name Management exactly on Excel 2011 for Mac? I looked and looked but no luck. This forced me to restudy the situation and find a way to add it to a menu. Fortunately that’s exactly what you need to do, but I am still dumbfounded by it being missing on Excel’s ribbon. But anyway, below are the steps you need to follow in oder to add this to a Menu:

1. Right-click on the very top toolbar (the regular toolbar, not the ribbon) and then select from the drop down the option: Customise Toolbars and Menus…
2. In the Commands tab, select All Commands
3. Find the Define… command (If you start typing after selecting any item on the list it will take you to what you’ve typed)
4. Click and drag that command from the list onto the toolbar or menu you wish you use.

If this is a one time deal or you are more of a shortcut guy, you can open the Name Management function via keyboard shortcut as well: Command-F3. Note that I am using F3, so on a Mac keyboard that is usually 3 keys: Command (The Apple key) + the function key (fn) + F3. If you don’t use the function key it would do the keyboard shortcut drawn on your keyboard. If on System Preferences you already indicated your Mac that you want to treat F1 – F12 as Function keys not fancy control keys then there is no need to hold the function key before hand.