Meet the Excel 12 formula bar, or “don’t hijack my grid!”

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Graham — Wed, 19 Oct 2005 03:29:49 GMT

All very sensible. 64 levels of formula nesting, eh? Those ugly formula contests are going to get a whole lot more interesting...

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Colin Banfield — Wed, 19 Oct 2005 05:13:50 GMT

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

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Biff — Wed, 19 Oct 2005 06:27:55 GMT

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!

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Jean-Marc Decouleur — Wed, 19 Oct 2005 12:37:50 GMT

These are long-awaited and very welcomed features. Thanks.

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Jim Rech — Wed, 19 Oct 2005 17:11:06 GMT

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?

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Harlan Grove — Wed, 19 Oct 2005 20:28:37 GMT

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?

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

DavidL — Wed, 19 Oct 2005 21:16:36 GMT

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.

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Jim Rech — Wed, 19 Oct 2005 21:22:41 GMT

David L-

The trick is to press F2 to switch between Enter and Edit modes.

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Biff — Wed, 19 Oct 2005 21:42:47 GMT

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.

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

DavidL — Wed, 19 Oct 2005 21:54:30 GMT

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?

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

DavidL — Wed, 19 Oct 2005 21:56:01 GMT

Biff: That's why they call it a "cursor". :)

5. ????
6. Profit!

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Simon Murphy — Wed, 19 Oct 2005 22:32:55 GMT

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

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Rob van Gelder — Wed, 19 Oct 2005 22:54:19 GMT

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

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Jim Rech — Wed, 19 Oct 2005 23:09:32 GMT

>>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.

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

DavidL — Wed, 19 Oct 2005 23:29:36 GMT

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.

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Harlan Grove — Thu, 20 Oct 2005 00:01:45 GMT

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.

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

mschaef — Thu, 20 Oct 2005 00:57:51 GMT

"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? :-)

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

headtoadie — Thu, 20 Oct 2005 02:12:13 GMT

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.

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

David Gainer — Thu, 20 Oct 2005 02:42:48 GMT

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.

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Harlan Grove — Thu, 20 Oct 2005 08:15:11 GMT

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?

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Charles Williams — Thu, 20 Oct 2005 10:12:33 GMT

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.

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

ben — Fri, 21 Oct 2005 02:50:29 GMT

Harlan...

<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?>

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?

re: Meet the Excel 12 formula bar, or “don’t hijack my grid!”

Harlan Grove — Fri, 21 Oct 2005 19:36:04 GMT

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.

Microsoft Excel Meet the Excel 12 formula bar or don t hijack my grid | Patio Chairs

Wed, 03 Jun 2009 05:38:37 GMT

PingBack from http://patiochairsite.info/story.php?id=26687

Microsoft Excel Meet the Excel 12 formula bar or don t hijack my grid | adirondack chairs

Sun, 14 Jun 2009 16:25:00 GMT

PingBack from http://adirondackchairshub.info/story.php?id=2138

Microsoft Excel Meet the Excel 12 formula bar or don t hijack my grid | patio cushions

Sun, 14 Jun 2009 19:19:08 GMT

PingBack from http://patiocushionsource.info/story.php?id=233

Microsoft Excel Meet the Excel 12 formula bar or don t hijack my grid | patio set

Thu, 18 Jun 2009 05:00:19 GMT

PingBack from http://patiosetsite.info/story.php?id=81

Microsoft Excel Meet the Excel 12 formula bar or don t hijack my grid | patio umbrella

Thu, 18 Jun 2009 07:07:59 GMT

PingBack from http://patioumbrellasource.info/story.php?id=1783

Microsoft Excel Meet the Excel 12 formula bar or don t hijack my grid | garden decor

Fri, 19 Jun 2009 10:26:25 GMT

PingBack from http://gardendecordesign.info/story.php?id=5182

Microsoft Excel Meet the Excel 12 formula bar or don t hijack my grid | porch swing

Fri, 19 Jun 2009 10:42:54 GMT

PingBack from http://fancyporchswing.info/story.php?id=2283