Excel 2007 is useless. This was a shocking revelation when I tried to create the charts for last post (Chart Design: Abortion Ratios 1980-2003).
It was my first serious attempt at using Excel 2007. I failed miserably and gave up. I had to do everything in Excel 2003 and then open the file in Excel 2007 for some polishing.
I new there was some senseless options in conditional formatting (actually, it is worse than I expected, as you can see on the left chart). And Stephen Few said everything I needed to know about Excel 2007 new chart engine. And, by that time, Microsoft bought Dundas. Isn’t that enough to raise some red flags?
But I had to see it for myself. I will not talk about the ribbon. I keep it hidden, so it really doesn’t bother me. And having to learn a new interface is something that I don’t mind - let’s call it brain workout.
There are several small but frustrating things, like trying to select multiple charts as objects, but what really pissed me off was that empty macro I got, after recording some chart formatting. I couldn’t believe it and tried several times, before googling for this “error”. It isn’t one (you can compare both versions here and get more bad news, as I did).
I suspect that this is just the tip of the iceberg. And it is not the major changes in features or the new user interface. It is the small things, left behind just to annoy you.
Bottom line: stay with Excel 2003. Charts in Excel 2007 look much better due to the new rendering engine, but do you really want to buy Office 2007 just to polish some charts?
Please share your thoughts in the comments. I’d like to know if (and how) you can do productive work with Excel 2007.
If you're new here, you may want to subscribe to my RSS feed or subscribe via e-mail. Thanks for visiting!
{ 2 trackbacks }
{ 27 comments… read them below or add one }
I was with you a few months ago, when I first started using 2007, but actually some of the features are good. The Ribbon is more efficient once you get used to it and know where stuff is. The ability to select different data series while you have the format dialog boxes open is great.
For the most part though the functionality is just ‘lipstick on the pig’. I use mostly keyboard shortcuts, and the underlying interface is pretty much the same as 2003 for most things. Seems like they just moved toolbars around.
I don’t use Excel 2007 for real work, though I’ve done a fair amount of programming in 2007 for clients who “up”graded from 2003. Most of the programming has been designing ribbon-based interfaces to replace the custom menu/toolbar interfaces they’ve used for generations.
I disagree that “Charts in Excel 2007 look much better due to the new rendering engine”. The rendering engine provides lots of new special effects: transparency, shadows, gradients, funky font formatting. Big deal. It means it’s easier than ever to pile on the chart junk. The defaults are maybe a little better than in 2003, but I’ve never used the defaults. I think the 2007 charts don’t look as crisp, and it’s more of a pain to work with them with the new ribbon and dialogs. Exported 2007 charts in PowerPoint and Word don’t look as good as before, either.
I covered a lot of the changes in Excel 2007’s charts in Changes to Charting in Excel 2007.
Jorge, I’ve got to disagree. I made the transition a year ago and could not think of going back. The macro recorder complaint is valid, and I agree that most of the default conditional formatting options are useless. My biggest complaint about 2007 is the sort dialog.
These are the features that leave 2003 in the dust: 1 million rows, IFERROR, SUMIFS, COUNTIFS, Tables, reduced file size, improved autofilter, increased maximum formula length, and increased capacity of Pivot Tables.
@Chuck: When using Excel 2007 I keep getting that “beta” (or even “alpha”) feeling of something that is not quite finished, and that’s very annoying because I can’t concentrate on the work. And yes, there is much “lipstick on the pig”.
@Jon: given the option of creating an image of exactly the same chart (to add to a post here, for instance), I would rather do it in Excel 2007 (and you agree that “the default Excel charts look much nicer than in earlier versions” - from the link). This is not enough to upgrade, of course.
@Jonah: this is a depressing release from a visualization point of view. There are some “nice to have” new features, but the macro issue is a mortal sin.
Hi, all in all I am disappointed by the changes in Excel 2007. I felt dazzled and disoriented by all the new options which overall made it more difficult to do basic tasks. I admit to having trouble understanding notions like bevel and shadow.
Conditionnal formatting is another matter. The options do make sense in good hands, else it’s just gratuitous fluff. but when used well it can be impressive and useful.
The paper by Stephan Few made me wonder. AFAIK, he is the leading authority on excel-type graphs. I’m quite surprised he emails suggestions to a guy who doesn’t appear to run the show, just like a beta-tester. If I was MS, and got an email from him, I would hire him instead and make sure people listen to him. By comparison, MS regularly picks the mind of presentation gurus like Garr Reynolds to make ppt better. (which is a more daunting task than fixing excel charts…:) )
I absolutely agree with all the criticism about the new chart engine. However, I think the Microsoft UX Team did a very good job introducing the Ribbon as a new UI paradigm. You simply can’t avoid a new UI for feature rich applications like Microsoft Office 2007.
However, the pain point of the 1st version of the Ribbon is the themed Ribbon with all the glow effects, effects of light, stuff you should avoid in Data Visualization (and Effective User Interfaces).
I tried to apply Effective Chart Design Rules and redesigned the Ribbon on
http://blog.xlcubed.com/microsoft-pimp-down-my-ribbon/
I hope this will be addressed this with Office 14 !
Andreas
Jorge - I agree about the “beta” feel to Office 2007.
Chuck - At first the modeless dialogs seem like a good idea, because you can format one series, select another, and format it in turn, without dismissing the dialog. However, the downside of the modeless dialog is one of the most productivity-robbing aspects of Excel 2007. In 2003, I could format a series, dismiss the dialog, select another series, press the F4 key, and repeat all formatting from the previous trip to the dialog. Then select another series and press F4, then another and another. In Excel 2007, the F4 functionality has been eviscerated, and instead of click+F4 to repeat a whole set of formatting options, I have to actually repeat manually each option, one by one. I would gladly give back the modeless dialogs, plus the three (wow!) new worksheet functions, plus half of the new table features (Excel 2003’s Lists were nearly as powerful), in order to reclaim my F4 key.
To clarify, you asked how you can do productive work with 2007. The impact comes from using the new features in combination. For example:
Sumifs on Tables lets me summarize data that may be too large for 2003, or too conditional or multidimensional for Getpivotdata. The Table replaces writing a Dynamic Range. Sumifs replace very slow calculating Array Formulas. IntelliSense lets me write faster and with fewer errors. 2007 enabled previously impossible aspects of my work, and made all other areas more productive.
Jon, have you benefited from saving a chart as a template for reuse?
For large data, I’ve found that Excel 2007 bogs down worse than 2003, especially if you try charting it. In either version, it’s often better to use DB queries to extract relevant records from an external database. I’m not an expert on this, it’s harder than using the worksheet, and most of my data is at most a few thousand records.
The 2003 List replaces dynamic ranges, but like 2007’s Table, it only replaces the most rudimentary dynamic ranges. The 2007 Tables have a counterintuitive relative referencing system, and if you need absolute referencing, you’re stuck with using cell addresses as in 2003.
The formula intellisense is very nice, but 2003 had the rudiments of this. Chart templates are more flexible and reliable than the old user types (I was always corrupting my user galleries).
Nothing in my day-to-day activities is made any easier in 2007 than in 2003, and in fact most tasks are much more difficult or tedious in 2007. Have you never used the F4 (Repeat Last Action) key in Excel? They’ve hobbled it in 2007.
I use MSQuery in almost all of my 2007 workbooks to return a Table. Even if the query sums data, the result is often very large (10k-200k rows). Sumifs on the resulting Table make it easy to write a formula like: sum sales for products with SKUs that begin with EXC*, purchased between 1/1/08 - 6/1/08 but not on 2/29/08, where the order purchase price was >= $100. You can easily parameterize the query itself, or parameterize conditions in the Sumifs.
Though an edge case, my last year has been incredibly productive because of 2007.
I was unaware of F4 in 2003 and am disappointed to learn I won’t have the chance to use it in 2007. Combine that with a macro recorder that doesn’t work on charts and I certainly understand your disappointment.
@Jerome: Like some other ideas the conditional format is great, but very poorly implemented. Just create a chart similar to the one above and you’ll see that the bars of the conditional formatted cells do not match with the bars in a regular chart. Well, in fact, it is hard to tell, because you really don’t know where the bars end…
@ Andreas: I hate the ribbon because of a simple reason: it clutters my screen and distracts me from what I’m doing. My Windows task bar is often in auto-hide mode and I like to work in full-screen mode. “Minimize the ribbon” is one of the coolest things in Excel 2007…
@ Jonah: I’m working on a new project and soon I’ll be exposed to other areas of Excel 2007 I didn’t explore until now. I couldn’t be productive with a very simple task, and I’m afraid of what lays ahead, but I’ll try to be open-minded and do my best to see what is a natural learning curve and what is not.
A nice feature in a dashboard I’m working on is the visual what-if (the user moves a data point in a chart to test scenarios). That is something you can’t do in Excel 2007.
@Jonah: Some users are find Excel 2007 great, depending on the set of functions/features they use on a daily basis. As Stephen Few said, the new chart engine is a lost opportunity. Not being able to record chart formats is plain stupid (I know, there must be some compatibility issues with the current VBA version, but I still think is plain stupid). The new functions may be nice, but I always find a way to sumif the data the way I want.
The macro recorder shortcomings relate to the new shapes implemented in 2007. Since the chart uses these new shapes for its elements, any formatting is not recorded. I do not think there are incompatibilities with VBA, since you can access the new shapes through the VBA object model. Not easily, because the help content has gotten less helpful than ever before. My suspicion is that Microsoft simply did not have time to add this functionality to the macro recorder.
Since I don’t use any of the special new chart junk formatting in 2007, I simply record what I need in 2003 (I still have versions 2000-2003 on one computer, and I can fire up 97 if needed), and paste it into 2007.
Jonah -
“Though an edge case, my last year has been incredibly productive because of 2007.”
So you’re the guy, ha ha.
Interesting to read how “chart professional experts” react to Excel. There is something though I do not understand in your complaints : OK you cannot record the chart in VBA, but there is a very useful possibility in Excel 2007 to define your own templates, which I use a lot to apply to all my charts the same design right when I create them. I find this really useful, and is for me one of the main reasons I decided to use Excel 2007 (the ribbon really being the main reason)… I only have to twick the axes scales, et voilà !
Paul - In 2003 you could define your own custom types. It wasn’t implemented as elegantly or flexibly as in 2007, but it worked reasonably well. For me the chart templates are a plus 1, while the broken macro recorder is a minus 2 on the same scale, the decreased efficiency I have with the ribbon is a minus 2, and the loss of F4 functionality is minus 10.
This is a piece of garbage!
Here is the link where Mirosoft admits they are complete idiots
http://support.microsoft.com/kb/937620
Brady, thanks for the link. I specially liked the Status (”This behavior is by design.”).
This statement makes a lot of sense - NOT:
“In Excel 2007, the new ChartFormat object provides access to OfficeArt formatting for chart elements. However, the properties of the ChartFormat object are read-only. Therefore, they cannot be recorded.”
I have spent hours trying to figure out the whole deal with ChartFormat. ChartFormat itself is read-only, but its properties are writable (or else how would you change them?). However, the online help was not really fleshed out for this object and its members, and it’s also poorly connected to the VBA object browser.
My consolation is that all of the ChartFormat stuff makes up the craptastic effects that should be discouraged anyway. The code that works in 2003 is sufficient to make un-junky charts in 2007.
By changing charts in Excel 2007, Microsoft broke more than it fixed.
In the first release, before Service Pack 1, names defined on the worksheet referring to ranges couldn’t be used in series. Excel couldn’t resolve them properly.
There seem to be lots of off-by-one errors too, which programmers will instantly spot. The most glaring is the horizontal X axis line/plot-area border, which has a kink in it. The anti-aliasing makes it stand out even more. See:
Jorge,
I run the heck out of excel as a substantial analytical tool in my practice. Dating back to the visicalc, lotus and Symphony, I can say with some credibility 2007 is a blunder. I have to agree, there are no substantive improvements in function or productivity, but 2007 accross the board adds a very unproductive user interface (the only way to fix it is to recreate the interface by ratically customizing the menu bar), a definite reduction in power due to the new memory allocation scheme and a discovered fatal incompatibility with Microsoft SQLServer 2005. Excel 2007 is a dog; with charting, the shapes formating is squirley/non-professional, the copy/paste/reference function across spreadsheets is totally screwed up and in general just a bad program, stay with 2003.
Walter -
I’m glad to hear from other knowledgeable users who share my opinion. I will point out that to “fix” the user interface requires not only heavy alteration of the ribbon, but also recreation of dialogs which worked nicely in 2003 and which are massively inefficient in 2007. I’m in the process of constructing a set of these dialogs, but it’s a huge task.
Excel 2007 is awful. The ribbon is difficult and unnecessarily clutters up the interface. Excel 2007 crashes more with large data sets and I think it was designed for people making home budgets and simple lists. Think about MS’s last few products — Vista — somewhere between terrible and okay, Internet Explorer — tired and bloated, Office 07 — ineffective…
I’ve moved to Excel 2007 about two months ago, and consider it a HUGE step down from 2003, specifically from the following two perspectives:
1) With the Ribbon, you have to click at least twice for a lot of functions that you had accessible on customized toolbars in 2003. Adding everything in one line in the Quick Access toolbar is not as good as the old toolbars.
2) The new charts: everybody complains so far about a) the look (which is imo a matter of taste), as well as the recording issue (a BIG disadvantage, which affects productivity and output negatively). I have one other MAJOR gripe: the performance / speed. I have a single-core PC running XP Pro with 1 GB RAM. If you have several charts with many data points on them, and make a change to any of the data values, Excel takes AGES to update the charts, while it becomes completely unresopnsive. The same charts in Excel 2003 would update almost instantaneous.
In terms of productivity and output from someone who uses Excel extensively, I have to give 2007 a BIG thumbs-down.
It seems as if you can’t select a discontiguous range of cells using Ctrl-drag. This means that you can’t plot them in a chart - which is disastrous!
I have compared the behaviour of Excel 2003 and 2007 and find that the Ctrl-drag method which works in 2003 doesn’t work in 2007. Surely there must be a way to plot discontiguous data?
Helen
Helen -
You’ve posted this statement somewhere else recently, and I pointed out that it’s not true. Select an area, hold Ctrl, and select each additional area.
I would like to hear the details of your comparison. Excel 2007’s charting functionality has certainly been reduced from Excel 2003’s, but this is not a specific issue I have had any problem with.
As in previous versions of Excel, your multiple selection must meet some requirements:
1. You must be able to collapse the different areas into a single contiguous area by deleting entire unselected rows and columns passing between selected areas.
2. Junctions between ranges must be continuous (If you select B1:B4 and B5:B8, then selecting C1:C8 as a single area or C1:C3 and C4:C8 as a multiple area violates this requirement).
3. No cells may be selected more than once.
Jon, thanks for clarifying this.