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.
Tips
How-to
News
Videos
Stories
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.
Name Comments
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.
Comments: (33) Collapse
Name Comments - awesome!
I'd use them to hint at which linked workbooks rely on them.
Could you please describe how the "In Use" works? Will they work with INDIRECT("my_range")?
Rob
This sounds great.
How much text can the named range comment accommodate and how much will show up in the tooltip? If there is enough then change tracking sounds like a great idea.
Also is the New/Edit name dialog sizable, so we can see the text of long comments or long formulas?
Will dynamic named ranges (named formulas) support 3D ranges (which probably boils down to will the offset formula support 3D range selection)?
Speaking of which, the ability to used 3D ranges for the sumproduct function would be huge too!
Finally Thanks for the great Blog and info, I'm looking forward to 12. The new UI & Features look great and would sell the product (for me) by themselves, but the increased limits would also sell 12 (for me) by themselves. I agree with www.dicks-blog.com/.../too-many-cells that the only times I hit the current row limits is when using Excel as a temporary information processing tool. The new limits will make Excel a better tool for exactly this kind of work. The new UI features such as the Name manager and resizing the name box will also greatly help in managing large data sets and complex analysis.
Hazz
(Business Analyst)
Defined Names improvements, like all your others topics, are going to be very useful for advance and non advance users. You and your team are doing a really good job.
I don't know if it could be possible to have an option so the range of a defined name could be dynamic. This option would be easier than
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
and it should also be available for Insert>Name>Create. For this last case, it should be also an option for the name comments to come directly from the comments of the header cells.
Jean
i think dymanic ranges would be a good addition, too.
Being a solution developer that makes heavy use of hidden Name Ranges, we would prefer to not surface the hidden ones by default. Showing these to the user would allow them to 'experiment' with them which could cause problems on our end.
Nice feature!
As a follow-up to my last comment...
We would actually prefer that the user be unable to very hidden names. We haven't had the need to give too much research into very hidden names but as far as I can tell they need to be set/get with the use of a ExecuteExcel4Macro call (kind of a clunky way to use named ranges).
By not allowing the user to view hidden named ranges through the UI, we could continue to simply set their visible property to false and not be too worried with the user seeing them.
Looks good.
For those (possibly few) of us who are diehard keyboard users and avoid using mice as much as possible, will it still be possible to create worksheet-scope names by preceding the name with the worksheet name, e.g., entering the name to define as ViewSheet!MyName?
As for hidden names, any chance they could be viewable but not editable? Seeing them may be innocuous. Letting users change either their names, definitions or scope could be a major headache. If the UI can't treat them as read-only, then better not to show them.
And you didn't mention it in your earlier post about the new expanded limits. How long can the definition of a defined name be? Currently they're limited to 255 characters. Will they now have the same limits as cell formulas?
I find it odd that you didn't take the chance to remove that restrictive "no space char allowed here" restriction in names. I mean, those are tags and only technical limitations have made names so cumbersome to use.
anon...
|I find it odd that you didn't take the chance
|to remove that restrictive "no space char
|allowed here" restriction in names. I mean,
|those are tags and only technical limitations
|have made names so cumbersome to use.
Unfortunately space characters are either used as intersection operators between range references or as whitespace between other tokens. For example, if you had defined range names (w/o single quotes) 'a', 'a b' and 'b' would =SUM(a b) return the sum of range 'a b' or the intersection of ranges 'a' and 'b'.
While this may have been an unfortunate choice, once done it's done and can't, practically, be undone.
Next Excel version breaks a lot of things, the UI, the look and feel. This is a great opportunity to fix this space char thing. After all, saving as xml fixes the problem with bit-based binary formats.
That they don't do it now is because they don't understand the user entering a name only to be prompted with a message box with some non sense sentence in it. From a user point of view, anything that makes it easier to use is valuable. From a developer perspective, anything that makes the plumbing look consistent and less broken is valuable.
There will be more time in the future for adding shiny color gradients. But since Excel 12 file format is disruptive, why not take the chance and fix it all?
David-
Some of those defined names and sheet names in the screen shots look _very_ familiar to me. I don't remember sending you guys my "Expat" workbook but sure looks like I did.
Jim
Hidden Names...
It's not clear to me whether hidden names can be created (and switched to and from visible) via the new UI. To let users see them (and delete them) but not create them is leaving out some pretty important functionality I think.
Hidden Names II-
I think the new UI should have the ability to deal with hidden names just as if they were visible. And I think that ability should be turned off by default. However, since hidden names are something only developers need to be concerned about for the most part, the "switch" should be in a less prominent place than it is now. Maybe even as a VBE setting.
I think it's somewhat similar the Windows Explorer option to show hidden files. It's there for the more techie user but it's not "in your face".
Please do not allow the users to easily view very hidden names. It is important that developers have at least one hidden location to temporarily store things likes keys, user IDs and passwords.
For anon,
I agree it would have been good if Microsoft had FIRST fixed outstanding problems THEN added functionality, but that's not to be, it seems.
Increasing the limits is good, but it means severely reduced backward compatibility. Since there's been very little added in Excel since XL8 (97), this upgrade will come as a major jolt. Agreed that this SHOULD have opened the way to wholesale fixes (restrictions on range names, fixing the kludgy external workbook referencing syntax, 3D references that seem to have no representation in the object model, various built-in functions that should work A LOT better than they do), but it seems there's already a feature lock.
But you need to realize that the last several versions have provided very little more than eyewash and closer integration with the rest of Office. We should be thankful this time around Microsoft's actually adding some useful spreadsheet functionality to go with the eyewash.