Formula building improvements Part 4: Defined Names
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.
(Click to enlarge)
Using this dialog, you can:
View existing Defined Names
- See the name’s reference (“Refers to” control), the name’s scope (“Scope” Column), and the name’s value (“Value” Column – note, this displays error values as well)
- Confirm whether it is referenced in the grid or not (the “In Use” column)
Create New Names
- Easily define the scope of a name in the New Name dialog (dialog discussed below)
- Easily set the name reference
Edit existing names
- You can now rename a name without having to redefine it from scratch
- You can quickly modify scope from the Edit Name dialog (dialog discussed below)
Delete Names quickly
- Select and delete multiple names at once
Sort the Name list
- You can sort the name list by clicking on the column headers
Resize the Manage Names dialog
- Make the refers-to box as wide as you need, so the number of names you can see is limited only by your monitor size
Filter the Name List
- A powerful filter drop down allows you to filter large name lists based on a number of common criteria including scope, in use, and if the name returns an error
- You can set multiple filters by simply selecting whatever filters they want … for example, you can filter to see all defined names that are not in use an that return errors with two mouse clicks
(Click to enlarge)
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
- Detailed description of a Names purpose
- Name auditing (track changes, data refresh dates, etc)
- General notes
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.
