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
All very sensible. 64 levels of formula nesting, eh? Those ugly formula contests are going to get a whole lot more interesting...
There needs to be an easier way to build worksheet formulas for 64 levels of nesting and 255 arguments to be meaningful. Perhaps a "Formula Composer" dialog box or something that allows you to build up a complex formula in parts and allows you to evaluate the partial formulas. When you're done, and the formula is displayed in the sheet, all of the parentheses would be automatically put in the correct places.
Colin
I'm lovin' both the new formula bar and the name box!
Will the new name box display dynamic named ranges? (which I guess are technically named formulas)
Formula auto complete? Now that sounds interesting!
These are long-awaited and very welcomed features. Thanks.
Nice touches for sure. I'm curious to see if the active cell is potentially pushed off screen when the expanding formula bar pushes the top of the worksheet down. The grid would have to be scrolled upward to prevent it, which could get messy I imagine.
Also, the worksheet window is maximized in the screen shots. If it was not would the tabs disappear offscreen to the bottom?
First with regard to the limits. Other spreadsheets have accomodated more than 7 nesting levels and 30 function arguments for years. It's nice the Excel Development Team is finally brining common early 1990s spreadsheet functionality to Excel.
Also, with regard to nesting levels, Excel's recalc engine has been able to accomodate existing formulas with more than 7 nesting levels for several versions, at least as far back as Excel 8 (97).
For example, in 123 enter 1..20 in A1..A20. Then in B1 enter the formula
@SUM(A1,@SUM(A2,@SUM(A3,@SUM(A4,@SUM(A5,
@SUM(A6,@SUM(A7,@SUM(A8,@SUM(A9,@SUM(A10,
@SUM(A11,@SUM(A12,@SUM(A13,@SUM(A14,@SUM(A15,
@SUM(A16,@SUM(A17,@SUM(A18,@SUM(A19,
A20)))))))))))))))))))
and save the file in .XLS format from 123. Open it in Excel, and the B1 formula appears as
=SUM(A1,SUM(A2,SUM(A3,SUM(A4,SUM(A5,SUM(A6,
SUM(A7,SUM(A8,SUM(A9,SUM(A10,SUM(A11,SUM(A12,
SUM(A13,SUM(A14,SUM(A15,SUM(A16,SUM(A17,
SUM(A18,SUM(A19,A20)))))))))))))))))))
and it calculates correctly as 210. Select A1:A20, copy and paste special multiply on top of itself, and the B1 formula recalculates correctly as 2870.
It would seem it was only Excel's formula parser that choked on more nesting levels.
So y'all are FINALLY eliminating a limitation in the formula parser that's been inconsistent with Excel's recalc functionality for the better part of a decade at least? So bold!
Now a real question, if we can resize the name box, does that mean we can finally effectively get rid of it? That is, can it be resized to zero width? Is that setting automatically stored as an application setting? If not, will there be some new object model property to set its width from a macro in Personal.xls?
As for the formula bar, it's good it can now be restricted to 1 row of text. However, at 1024x768 screen resolution and 10 point typesize, the formula bar will only display 4300 or so characters, which means that when it resizes it'll effectively eliminate document windows from the application window.
Did it not occur to anyone that it might have been better to provide a resizable dialog in which to edit formulas, and provide a cell pointer icon (e.g., like the one at the right side of the Row input cell entry box in the Table dialog) to shrink the formula entry dialog when in Point mode. Or even better, automatically shrink it in Point mode. Even better still, providing another icon that would automatically indent different nested levels to make formulas easier to check, e.g.,
=IF(x=y,
a,
IF(x=z,
IF(ISERROR(VLOOKUP(p,t,n,0)),
"",
VLOOKUP(p,t,n,0)
),
b
)
Or am I all wrong, and the new formula bar automatically collapses to a single line when the user enters Point mode?
I'm sure I'm not using Excel properly, but there is one thing about the formula editor in the current version that annoys me tremendously and that I would like to see fixed: Way too often I am not able to use the arrow keys to move the cursor in the formula without inserting a cell reference instead.
Steps to reproduce:
1. Start entering a formula in A1 ("=A2+").
2. Use left arrow to go back to change the "A2" to "A3".
3. Curse.
Ever tried to adjust the range in a range dialog, e.g. the one for the pivot table source, by using only the keyboard? Excel will happily insert all kinds of cell references, even if they are not valid in that context, which feels totally unnecessary and unintuitive.
If someone knows of a trick to switch this behaviour off already today, I would be very grateful.
David L-
The trick is to press F2 to switch between Enter and Edit modes.
DavidL wrote:
>3. Curse.
You left out step 4: Repeat step 3.
LOL!
Been there done that!
You just have to remember to enter edit mode as Jim Rech mentions.
Jim, Biff: Thanks guys, I owe you one!
Now, to get back to the UI topic, how do you make an important feature like this discoverable? Clippy, anyone?
Biff: That's why they call it a "cursor". :)
5. ????
6. Profit!
David
sounds good - I have a few questions though:
-Will there be a way to turn off the annoying way pasting long text automatically increases the row height and turns on wrapping no matter what you the user previously set it too?
-does the 8k formula limit apply to array formulas too? I think you said not in a previous post?
-will the 255 formula arguments be accessible to xlls?
-also (finally) if calling a worksheet function in C# (say VS2005 for arguments sake) will I have to pass in all 255 arguments? (thats a lot of missings!), or will there be a full set of helper wrappers?(in the PIAs?)
(sorry for all the questions - thanks for the info!)
Cheers
Simon
Harlan,
AudXL.xla on my website formats/indents a complex formula for easier reading...
Hopefully similar functionality will be introduced to future versions of Excel.
Regards,
Rob
>>Clippy, anyone?
Ha! No I don't think so, David, thanks. Status bar? I know, a lot of people don't look there, but it would be easy to implement and better than nothing.
Re range selecting in general,I don't know if an improved Ref Edit made the cut but there's room on that dialog to tell users the mode they are in and to press F2 to get to the other.
Jim,
Yes, Clippy is dead and buried, but it was quite helpful when it detected that the user had "got stuck" with a feature, like trying to type in the margin of Word by clicking there repeatedly or (in my case) inserting cell references in a formula with the arrow keys, yielding syntax errors.
An idea for the Office team: Reintroduce those "Clippy-tips", but use a non-intrusive info-bar like IE6 SP2 does.
Comments: (loading) Collapse