You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Defined names are a very useful tool for authoring formulas. Defined names allow users to name cell ranges, formulas, and values and refer to those names in their formulas. Used in formulas, defined names make formulas easier to read and more robust. Additionally, when writing formulas, names are less likely to get mis-typed than cell references, and they are easier to remember than cell references (“Tax_Rate” as opposed to “G36”). In this article, I would like to discuss some of the work we’ve done to defined names in Excel 12 – specifically, how we have added new management and creation UI, and how we have added comments to names.
The new Manage Names Dialog
Probably the most common piece of feedback we receive about defined names is that the user interface we provide to manage names is inadequate. When we visit customers on site, we often see workbooks with dozens or hundreds and even thousands of defined names, which makes tasks like deleting multiple names, renaming names, and finding broken ones challenging. Enter the new Manage Names dialog, which is designed specifically for viewing and managing the defined names in a workbook.
The entry point to the Manage Names dialog is in the centre of the new Formulas tab. Here is a shot of the Formulas tab in current builds - you can see a big button titled "Name Manager".
(Click to enlarge)
When you click on the Name Manager button, you will see the Manage Names dialog.
Using this dialog, you can:
View existing Defined Names
Create New Names
Edit existing names
Delete Names quickly
Sort the Name list
Resize the Manage Names dialog
Filter the Name List
One thing we would be interested in hearing is whether hidden names should be surfaced in this dialog. The current design allows users to show hidden names using a control on the filter drop-down, but they are not displayed by default. Our reasoning for this is that customers tell us hidden names cause many problems for users and generate helpdesk issues. At the same time, some solution providers use them as variables with the knowledge that you can't see them in the UI. Our current design would allow savvy users to find the hidden names without writing code. Note, there is a workaround for solution developers, which is to use very hidden names which can be created by using the hidden namespace in XLM (i.e. SET.NAME).
The New Name/Edit Name Dialog
While we were improving name management, we set another goal to simplify the experience of creating a name. To do this, we created a dialog that surfaces the UI needed to define a new name or edit an existing name (the title of the dialog changes between New Name and Edit Name depending on the context of how it was launched). Of note is the Scope drop down which allows the user to easily set the scope of their name to a specific sheet or the entire workbook (no more secret knock needed).
For mouse users, we’ve made it easier to get to the new name UI by adding a right-click menu option that will launch the dialog with the selected range in its refers-to box.
Other entry points exist on the Ribbon and Manage names dialog.
Finally, I’d like to talk about an enhancement to the Defined Name object itself – we have added a name comment property. The comment property allows the user to document what a name refers to, what it should and should not be used for, etc. This field can be edited from both the UI (New/edit name dialogs) and the OM and is surfaced as the name’s tooltip in formula auto complete as well as in the Manage Names dialog.
Some Interesting uses of this field might include
Another thing we would be interested in hearing is how would you use the name comment feature.
That wraps up "Formula Editing Improvements Week"; I hope you found this interesting. Talk to you next week.
I create hidden names and sheets all the time, so I have mixed feelings about letting users see them. That being said, I find that I'm moving away from xlVeryHidden when I create hidden sheets. It's so much easier to tell a customer how to unhide it, if I want to help them debug some problem on the phone, or whatever. So, with reservations, I think I'd vote for letting the users view the hidden names if they really want to dig.
One thing about names I noticed in previous Excel versions is that the larger the name table gets, the longer it takes to add or remove a single name. I haven't checked this in the most recent Excel versions, but I remember (in Excel 2000?) that the slow down was pretty much linear with the size of the name table. Many times I have started to implement some functionality using hidden names, only to revert to a hidden sheet scheme because I realized I simply had too much data to write, and the slow down was too severe. Eventually, I created a rule for myself that I would never implement any code that required the creation or deletion of more than about 1000 names in a workbook. Of couse I haven't rechecked my rule with newer Excel versions.
All this talk about hidden names and hidden sheets makes me think about what we all seem to use these things for. Their primary purpose seems to be to attach persistence information to cells, worksheet, and workbooks in a way that will update as the user makes changes to the workbook. And in many ways the hidden names and hidden sheets work well. But in many ways they are problematic.
For example, they don't really handle copy and paste very well. If I want to attach some hidden info to a cell, I can do so. But if the user copies and pastes that cell to another location, there is only one hidden name. To get around this problem, I've play with things like encoding information into a cell's custom formatting fields and similar ridiculous schemes.
What I'd really like to see it an Excel sanctioned method for storing custom data in a cell (or really, with any object. I've played games to store hidden data in drawing shapes too.) This is sort of like the old "BigData" structure in the XLL interface. But unlike "BigData" there would hopefully be a way to keep references and formulas updated as the user made changes to their model. And the data could get copied when the object was copied, etc.
Just a thought...
Harlan - No, it wont.
Roger - If you are ok writing some VBA, you could put pretty much anything you want in the comment, including the name's refer to.
Nigel - Thanks for the feedback.
Tianwei – Great. We have exactly the solution for you in Excel 12 – see most recent post (“Tables Part 1”) … more details on how this works coming later this week.
Erik – Thanks for your feedback. Custom data isn’t something that have addressed directly in Excel 12, but it is on my list of things to think about for future versions.
Name dialog: Please also show the Refers To for each name as a column in the box, so I don't have to select a name to see what it refers to. Most of the time (as a developer) I'm much more interested in its Refers To formula than its value or comment.
Name comments: It would find them most useful if the Filter dropdown could filter on a textual wildcard match of the comments. I could then use the comment for categorisation of names, or if I'm including change logging, I could easily find the name(s) I changed yesterday. Lastly, in most cases, the comment would be for the developer, not the end-user, so I wouldn't want the comment (for a specific name) to appear as tooltips etc.