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
I’d like to shift gears a bit and talk about the work we’ve done to improve the experience around building and editing formulas. For most customers, this is a core activity in their daily use of the product. In planning for this version of Excel, we took a hard look at the features in this area, and we have made what we think are some significant improvements. Over the next week, I am going to cover the work we have done in this area.
To start, let's take a look at some changes to the formula bar. In Excel 12, we’ve redesigned the formula bar to be both flexible and less intrusive. When we were researching the area of formula editing, the most common customer feedback we received about the formula bar was that it didn’t respect their data on the grid. In order to display text in the active cell, the formula bar would spill into the sheet, often resulting in obscured column headers and sheet content. This could be frustrating when users were working with a spreadsheet containing cells with more than one line of text. We’ve addressed this issue in Excel 12 by giving formula bar its own space that never overlaps with the grid. The behaviour is similar to resizing a docked task pane. Let’s take a look at an example.
Below is an image of the Excel 2003 formula bar when a cell containing a lot of text is selected. Column headers and data are obscured by the formula bar.
(Click to enlarge)
Now let’s look at that same document in Excel 12. Rather than spilling the content, we’ve added a scroll bar and kept the formula bar to a single line (which is the default state).
To display more cell content, users just need to adjust the height of the formula bar. This can be done in two ways - by dragging the resize bar at the bottom, or by clicking the auto expand/collapse button at the far right. As users resize the formula bar, it pushes down the grid instead of overlapping it, so that spreadsheet content is never obscured. For the keyboard user, we’ve added a short cut that allows them to quickly toggle between the collapsed (1 line) and expanded state.
Another piece of feedback we heard from users about the formula bar was that the name box was not big enough to display long range names. Accordingly, we’ve added the ability to resize the name box horizontally. This gives customers the ability to accommodate their long range names by dragging the name divider (circular dimple) left or right.
Here is an example of a long range name that doesn't fit in the name box:
Here is what things look like after the name box has been resized:
Finally, I’ve already talked about limits, but I thought I’d mention a few here in the context of the formula bar. The changes we’ve made will accommodate these increased limits and the larger formulas that result. The maximum length of formulas (in characters)Old Limit: 1k charactersNew Limit: 8k characters
The number of levels of nesting that Excel allows in formulasOld Limit: 7New Limit: 64
Maximum number of arguments to a functionOld Limit: 30New Limit: 255
That’s all for today. Next up, formula auto complete (yes, it’s as great as it sounds)
Comments: (25) Collapse
Rob van Gelder...
...
|AudXL.xla on my website formats/indents a
|complex formula for easier reading...
OK.
I took a different path. Modified the syntax definition from another language in my text editor, and copy/paste back & forth between it an Excel.
|Hopefully similar functionality will be
|introduced to future versions of Excel.
Indeed.
I wonder how skewed the sampling of user feedback/requests with respect to the 'formula bar experience' was if many (most?) of the people who work with long/complex formulas on a daily basis long ago gave up on hoping Microsoft would make any serious improvements.
"I wonder how skewed the sampling of user feedback/requests with respect to the 'formula bar experience' was if many (most?) of the people who work with long/complex formulas on a daily basis long ago gave up on hoping Microsoft would make any serious improvements. "
Excel really needs two things: a way to manage editing complex formulas is one, and a way to ___simplify___ complex formulas is another.
VBA UDF's aren't great because they aren't Excel's 'native tounge'. They require another learning curve and the performance hit of calling into another language whenever they are evaluated.
Ideally (IMO), there'd be a way to add paramaters to range names. Are there any plans for such a thing in 12? :-)
While on the topic of Formula Bar, would it be possible to have it stop assuming everything entered without a preceding sign (+, - , =) is text? I mean it's a spreadsheet for goodness sakes!! By default it should assume everything entered is a number or a formula unless explicitly preceded by a quote or single quote to indicate text.
Greetings. Again, thanks for the comments, feedback, humour, and discussion. One thing to note is that what I am presenting is our current builds. We will be releasing a couple of betas, and as we get user feedback from that as well as other sources (including this blog, usability studies, etc.), we may tweak some designs.
Let me try to answer some of the specific questions:
Biff - The name box displays the same contents as in Excel 2003, which does not include named formulas. We do have some new name management UI (coming in a post this week) though.
Jim – We have added logic to not push the active cell off the screen when users are moving the formula bar, which I personally think works quite well. Good point about the different window states – I will take a look.
Harlan – No, we have not added a setting to get rid of the name box. Also, we will never expand or collapse the formula bar without explicit user action, and we give the user control over how much space they want to devote to cell text. Part of the reason we chose to not automatically collapse the formula bar is that we don’t know the user’s intention – they may be selecting another cell with a just as much text to edit.
Simon – No change on the pasting text behaviour. The 8k limit applies to array formulas. The 255 formula arguments will be accessible to XLLs (I will put down XLLs as a topic for later). Good question on C# - we are working on that now.
mschaef – No news to announce there.
headtoadie - No changes in this area either.
Why not show screen images of the new formula bar with a *FORMULA* in it? The screen images all show text constants in the formula bar.
Is there any good reason the insert function button needs to be so wide? It looks like it could hold the cute little dot on the left side and the f_sub_x symbol on the right side at 1/3 its current width. That'd leave more room to display formula text. Wouldn't that be a good thing?
This is all excellent stuff.
I agree with Harlan that the dimple and insert function button seem large.
Also seems inconsistent (maybe this should be a question for Jensen): if you are using a resize bar to expand the formula bar vertically, why not also use a resize bar to move the divider between the name box and the formula bar?
And if you use the vertical resize bar to expand the formula bar, why not expand the name box vertically at the same time?
Thats what I used in the Name Manager addin and people seem happy with it.
Harlan...
I'd imagine the space next to the insert function button is there to hold the committ cancel buttons that appear when editing a formula same as current versions. Did you forget about those?
ben...
|I'd imagine the space next to the insert
|function button is there to hold the committ
|cancel buttons that appear when editing a
|formula same as current versions. Did you
|forget about those?
You're probably right. I wouldn't so much say I forgot about them, more that I've never paid any attention to them since I use the keyboard exclusively to enter and edit formulas.
Still, it looks like wasted space. Perhaps only the little dot on the left side is wasted space, or maybe it's some new feature as yet unmentioned. Even so, the new enter and cancel buttons would seem to be a bit larger (wider) than their current counterparts with no good reason to be wider since wider buttons mean a narrower formula bar.
Could someone help me find an excel formula that gives me the accuracy percentage of processing? I have a population of 278 with 2 errors. How can I obtain the accuracy of processing? Thanks!