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
I has a misgiving about the New Name dialog, specifically the Scope drop down.
It appears that the Scope drop down has in it, at least, "Workbook" and the name of every worksheet in the active workbook. If that's the case I think it's overkill. I believe the scope choices should be Workbook and Worksheet (or Current Sheet). How likely is it that a user will be on Sheet1 with the intent of creating a name local to Sheet2? Negligible I'd say. And in the off-chance he did want to create a name local to Sheet2 referring to a range on Sheet1 I think he should have to start on Sheet2, in exchange for way simplifying this dialog.
A client recently sent me a workbook of 761 worksheets. Do we really want the names of 761 sheets clogging up this dialog?
David, if I'm missing something please don't spare my feelings.
Jim:
I agree about the scope dropdown, but like to suggest a bit more intelligence:
I would suggest something along the lines of what Charles Williams and my Name Manager does:
1. Current name refers to a single sheet
- just use offer that sheet to localise to
2. Current name refers to multiple sheets
- just show those, so the user can pick which to localise to
3. Current name refers to no sheet
- show all sheets to localise to.
I think you are right in stating that in this last case just showing the active worksheet should do.
Would it be possible to enable the name comment to display the "Refers To" property?
Is the user going to be able to customise what can be displayed?
I find it incredibly frustrating when named ranges are used for almost everything. Whilst it may make understanding formulae easier (IF sensible names are used following a consistent convention). However, for auditing formulae I find it more of hindrance because you cannot the identify ranges used.
Excellent.
At last something really useful.
The historical name handling has always been a problem: being able to easily control/set the scope of names is particularly useful.
This looks like real progress.
Did I miss something: is the name drop-down box going to be wider than in the current version (or even better, customisable?)
M
Hidden names should stay hidden from the users, in my opinion. That's the only place where we (developers) can store data that the user cannot see without VBA - other than xlVeryHidden sheets, but those have a lot more overhead.
Alternatively, how about extending the concept of xlVeryHidden to names? Have "just hidden" names that users can show/hide manually, and very hidden names that can only be shown / hidden with VBA.
Great stuff - can't wait to get my hands on Excel 12. I moved from Lotus 1-2-3 for DOS (!) to Excel 14 years ago and the sum of what you're showing here will be almost as big an improvement.
Could I share one dream? A chart (e.g. bar chart). I right-click one data set. I can choose to change this data series (and none other) to a line. In other words, form of presentation (at least between compatible forms of presentation, e.g. bar/line) is a property of a data series just like colour. What do you think?
Viewable, Hidden and Very Hidden is a good architecture just as Sheets. I vote yes for allowing user show hidden names and have developer level go to VBE to do the Very.
David, would 12 allow user to define a name for column headings instead of ABC...? Allow a toggle option for user to turn on names or true Excel headings (BTW, SAS has the ability when viewing data).
Also, extremely like the idea of Jean Martineau about dynamic names. That should come in as a default when you select entire column for name.
Can you make a simple Excel 12 document available to us - real Excel generated file.
Samples are great but it would give us a better fill for the format.
Brian did that for Word some months ago and it helped a lot. Thanks.
"I find it incredibly frustrating when named ranges are used for almost everything. Whilst it may make understanding formulae easier (IF sensible names are used following a consistent convention). However, for auditing formulae I find it more of hindrance because you cannot the identify ranges used. "
I couldn't agree more.
<>
I use the trace precedent and trace dependent arrows for this purpose but it sounds like what you want is a name/cell reference display toggle like the A1/R1C1 display toggle or the cell result/formula display toggle.
Colin
Thanks everyone for the comments and discussion and feedback on hidden names.
Rob - The in use-column indicates whether a name is being referenced in a cell, or by another name in the active workbook. It doesn’t look at the following to determine if a name is in use or not:
- names that are referenced as strings via INDIRECT (not parsing this string)
- names that are referenced in VBA (not possible to parse the code manually and accurately pull out defined names).
- names that are referenced by other workbooks (external references)
Hazz – The current limit is ~1,000. Also, the New/Edit Name dialog is resizable, thanks for helping me point that out. 3D ranges for SUMPRODUCT has not changed from Excel 2003.
Jean, Gary, Tianwei – what is the scenario you have in mind when you say “dynamic ranges”? I will be talking about some work we have done around tables starting tomorrow that might be helpful to you.
Harlan – Yes, you diehard keyboard users will still be able to create worksheet-scoped names the way you do today. We are very aware of keyboard users on the Excel team. We have not changed the limit for defined names in Excel 12.
Jim – The current design does not allow users to create hidden names. The goal of that filter option is to help users troubleshoot their workbooks (hidden names copied over from other workbooks can cause problems and this would allow saavy users to find those names and remove them if necessary). I understand, however, the concern of developers who use hidden names in their apps not wanting customers to have control over them. Thanks for the feedback on the scope drop down. One goal of this dialog was to allow users to do workbook-level operations, but yoru point is well taken.
Jan Karel – Thanks for the suggestions.
Roger – You can manually paste the “refers to” contents into the name comment, but there is no way to link them. You can type pretty much whatever you want into the comment box – is that what you meant by customise?
Mpemba – Yes, the name drop-down will be wider by default and resizable. See my post on the formula bar.
Tianwei - Stay tuned for upcoming posts on tables. Then let me hear if we have addressed your scenario around names for column headings.
Alexandre - Just to make sure I understand, do you mean you would like a sample of the file format?
Should have thought to ask this before - will the new Manage Names dialog show worksheet scope names in VERY hidden worksheets?
David,
What I meant by customise was a group of radio buttons within the Name Manager so that each user could decide what they want the Name Comment to tell them.
Is there no way of programming the Name Comment to reflect the current "refers to" property? A manual paste solution is fine on the rare occassion that a worksheet is not going to have any rows/columns inserted/deleted.
Dave,
Thanks again for giving us our daily bread in the way of this blog.
I disagree with Jim Rech about the scope suggestions both in creating new ones and showing existing ones in the new UI. When a name is created I often want to create the same name, say on 12 sheets for the months of the year. We should be able to create a name once but make it local to one or any number of specific sheets at the same time by some sort of checkbox list. However I agree that we don't necessarily want a list of a zillion names so the Filter should be able to show:
'Names Scoped to Activesheet'
'Names Scoped to All Worksheets'
'Names Scoped to Workbook'
For dynamic named ranges (especially those using formulas to construct addresses and refer to them via the INDIRECT function) it would be very useful to have displayed the RefersToRange property somewhere in the UI.
Also, the UI must have a way of excluding some names from the general users view. I agree that we should be able to create and hide a name via the UI, but I also concur that a xlVeryHidden VB switch should be included for access via the VBE, I believe this to be very important.
Thank you,
Nigel
"Jean, Gary, Tianwei – what is the scenario you have in mind when you say “dynamic ranges”? I will be talking about some work we have done around tables starting tomorrow that might be helpful to you. "
One of the scenario is charting. When user add data the chart will automatically pick up the data. John Walkenbach has done a wonderful tip here: "j-walk.com/.../tip053.htm"
Would like to see that as standard.