A Belated Review of Excel 2007

Office 2007 has been out now for well over a year, and I’ve been using it in some capacity since before the beta kicked off over a year before its commercial release. Lately I’ve gotten more and more jobs for Excel 2007 as companies upgrade and users buy new computers with Excel 2007 pre-installed. I’ve decided it’s about time I gave a coherent review, rather than posting bits and pieces in various forums. This review is based on my two and a half years of experience with the new version of Excel, including beta testing and development work.

The RibbonBy far the biggest change in Office 2007 is the complete redesign of the user interface. After a decade or more, the familiar menubar and toolbars are gone, replaced by a large monolithic Ribbon splayed across the top of the application window. The user’s first reaction upon seeing this wonderful new interface is “Wow!” This is followed soon after by panic, as the user desperately tries to figure out where all the old controls have gone. They all are still there, and eventually most are found. Some are pretty obvious, and the Ribbon was designed to make them so, but others stubbornly stay hidden. The “Copy Picture” command in “Classic Excel” (97 through 2003) was somewhat obscure, requiring the user to hold Shift while selecting the Edit menu. This was kept obscure; it took me, a seasoned Excel user, a month to find it, cleverly buried under the Paste dropdown button.

Copy As Picture button, Excel 2007

Found at last!

The Ribbon isn’t all that bad, as far as it goes, and a typical user probably becomes reasonably comfortable with it within a week or two. The Ribbon does take up a lot of space atop the window, more than a typical menubar plus two rows of toolbars. The screen shot below shows that my typical Excel 2003 toolbars plus the Drawing toolbar (an extra toolbar) is still smaller than the Ribbon.

Comparing the thickness of Excel 2007's and 2003's Headdeer UI

Ultra-Enhanced Excel 2003 menu and command bars (left)
and Standard Excel 2007 Ribbon (right)

The Ribbon is rather sparsely populated with controls: some buttons are huge, like the Paste button, and spacing between others is greater than in the old toolbars. I find that the Ribbon provides me about half of the controls I would have visible in my highly customized Excel 2003 environment. Another unfamiliar behavior is that the Ribbon groups its controls in tabs of similar controls. If you are working on data in Excel, for example, you would use the controls on the Data tab. Unfortunately, it often seems like the tab you need has just disappeared from view. I do a lot of charting, of course, and with three contextual Chart Tools tabs, the odds don’t favor the visibility of the tab I’m looking for.

Well, no problem, you may say, I’ll just customize the Ribbon, just like the toolbars in Excel 2003. But you would be mistaken. There is hardly any facility to modify the Ribbon via the user interface. There is a single one-row high strip along the title bar of the application window that you can customize. The QAT, or Quick Access Toolbar, allows you to fit 30 to 40 controls along the top of the screen; in fact you can fit more, but then the QAT scrolls from side to side. You cannot undock the QAT, the best you can do is have Excel display it below the Ribbon instead of above it.

Classic Excel allows you to undock toolbars from the menu area and float them near where you are working. Classic Excel also has at least 15 tearaway palettes, each containing a number of formatting or shape insertion controls, which you can also bring next to your work area. As helpful as these have been, they are no longer available in New Excel. Excel 2007 requires you to drive the mouse greater distances, and make extra mouse clicks, to do the same tasks as before.

Floating UI elements in Excel 2003

Relics from a Different Age:
Classic Excel’s Floating Toolbars and Tearaway menus

The formatting dialogs behave differently in Excel 2007. For fifteen years users have double clicked on objects to access the Format Object dialog. Excel 2007 breaks this behavior. Double clicking an object activates the Ribbon tab that Excel thinks you might want to use, but the dialog does not appear. You must right click on the object and choose the Format Object item from the pop up menu, or select the object and press Ctrl+1 (the numeral one).

Many of the dialogs relating to shapes and charts are now modeless, so that the user can interact with objects in the Excel workspace while the dialog is showing. This has several consequences. First, the formatting is applied to the object as it is selected in the dialog. Second, other objects can be selected and formatted without dismissing the dialog. There are downsides as well. In Classic Excel, an entire visit to a dialog was saved as a single entry in the Undo/Redo/Repeat queue, whereas Excel 2007 saves each individual action. In Classic Excel, you could select a chart’s axis, for example, and change the scale, the tick and label patterns, the line color, the font features, and so forth. Then you could select another axis, press the F4 shortcut key to repeat the last action, and every format applied to the first axis would be applied to the second. This is a great productivity feature of Classic Excel. In Excel 2007, since each action is recorded separately to the queue, the Repeat Last Action command repeats only the absolute last action, not the entire history of the visit to the dialog. Thus the F4 shortcut is essentially broken in Excel 2007.

The ribbon is actually straightforward to customize programmatically. It makes use of RibbonX, a flavor of XML designed specifically for the Ribbon. There are a number of third party utilities that provide mechanisms for modification of the Ribbon through the user interface: one of these is Patrick Schmid’s RibbonCustomizer. There are a number of good references that will help you program the Ribbon. One of the best web pages is Change the ribbon in Excel 2007 by Ron de Bruin. In addition, I’ve found the following book to be very comprehensive and very helpful.

RibbonX – Customizing the Office 2007 Ribbon
by Robert Martin, Ken Puls, and Teresa Hennig.

Despite the ease of programming of the Ribbon, the undocking and tearaway features of Classic Excel cannot be replicated in Excel 2007. Perhaps you could fake it with a modeless userform; in fact, I have been toying with some custom dialogs to mimic my favorite features of Classic Excel.

In summary, Excel 2007 has introduced a new user interface paradigm, designed for improved discoverability of features for new users. Seasoned users will soon notice a distinct loss of productivity, beyond the temporary problem of not knowing where to find familiar commands. The new Ribbon interface takes up more space than even a highly enhanced Classic Excel menu and toolbar environment, but offers fewer controls in this space. The controls are housed in a system of tabs so that only a subset of controls are visible at a given time, and the “right” tab is often not visible. The user has very little ability to modify the Ribbon through the interface, and even productivity-boosting practices like selectively undocking toolbars and tearing off formatting palettes have been removed from Excel 2007. Finally the new modeless formatting dialogs seem at first to be helpful, but they come with the loss of Classic Excel’s Repeat Last Action functionality.

I use Excel 2007 a fair amount these days, and I don’t really mind it too much. Most of my work is directed by clients, and involves upgrading of Classic Excel programs and interfaces to work nicely within Excel 2007. Because of the productivity penalty I’ve described, I continue to use Excel 2003 for my own direct use personally and in my business. When clients ask about upgrading, I tell them what I’ve written about here, and I support them whatever they decide.

 

Peltier Tech Charts for Excel

Comments

  1. Jon,

    Seems like a fair review, though I think I like Excel 2007 a bit more than you. Still, I do miss some things about Classic Excel. One thing that I’m surprised you didn’t mention is the new charting engine. It makes pretty charts, but it is slow and I think it still has a number of bugs.

    Anyway, a tip to help regain most of the space that the Ribbon takes up: Double-click on any of the tabs (e.g., Home, but it can be any of them) and the Ribbon will disappear. However, the tabs and the formula bar are still visible (so this is somewhere between the normal and full-screen views). Clicking a tab will make the Ribbon temporarily reappear so that you can use its commands. I use this a lot when making presentations with Excel.

    One other thing: A high-resolution (preferably large) monitor really helps a lot. The Ribbon doesn’t seem so overwhelming, and you can see all of the commands and their full text.

  2. Hi Tim –

    I have two more posts in the works, one on general changes to the charting engine in Excel 2007, and the other on the charting dialogs developed for Excel 2007. Stay tuned.

    To be fair, I’m not too disturbed by the space used by the Ribbon. I have a dual monitor system which serves me well. I brought it up because the initial noise out of Microsoft during the beta was that the Ribbon was Even Smaller than the Classic Office menu bar and two tool bars. Later in the beta, of course, the ribbon expanded by more than a tool bar in height.

    I don’t dislike Excel 2007 all that much. Most of my work in it is on the programming side, so I don’t have to be very productive in the Excel side. When I am doing real work in the Excel side, I do feel handcuffed, and I feel like my mouse is going to wear out. One of my fun activities in the Excel side, though, is documenting and reporting bogs. I filed two this week based on posts in online forums. First, if you have an XY chart and all the X values are negative, and you add a trendline to the data, the trendline always extends up to the Y axis (i.e., to X=0). Second, there is no way in the Excel 2007 UI to change the width of up/down bars in a chart, for example, a candlestick chart. Fortunately VBA still works, so a workaround isn’t too traumatic.

  3. Yes, I too find some serious annoyances in 2007 (all of yours plus a Help that’s almost useless), but having been using if for about 6 months now, I wouldn’t swap it for an earlier version. For me, the highlights are the significant increases in capacity (rows, columns, pivot table unique values, etc.), modeless dialogues, Find All and a recovery that actually works sometimes.

    BTW, there’s an easy way to find where commands have been moved to…

    http://www.microsoft.com/downloads/details.aspx?FamilyId=89718ABD-2758-47B3-9F90-93788112B985&displaylang=en

    … this is so good that I don’t understand why Microsoft didn’t include it (and the Word etc. versions) with the product.

    Regards.
    Brian.

  4. I’m probably faster than ever with Excel 2007. Simple reason for that, like any good linux user I believe that the mouse is a terrible UI interface. So I want to use keyboard shortcuts for everything.

    Based on the decreased efficiency that the Ribbon imposes (as Jon noted) I was inspired to finally learn all of the keyboard shortcuts. I hardly click the Ribbon at all anymore. It has a great feature though. Let’s say you want to turn on autofilter. I know that it’s Alt+H,S,F (hit sequentially, not all at once — like emacs). But if you just press Alt and let go you will see the letter for all of the tabs. Now press h and let go, you’ll see the key for everything on the Home tab. Step through one at a time and you’ll quickly learn how to do everything you want without even using the mouse — faster than even Classic Excel.

  5. Jon,

    I’m one of those people who has to be productive in Excel and thankfully we have not transitioned over to Excel 2007 yet (it gives me more time to get acclimated).

    I’ll be interested to hear your comments in regards to Excel charting. I have my own notions about charts, but will be interested to hear your thoughts (preview: I want my Source data dialog back because adding series now is a pain).

    As far as the Ribbon is concerned, I don’t really care for it. I, like you end up scrolling with my mouse much more often in Excel 2007 then I do in 2003 where even if after 2 months of steady use I still on occassion have trouble finding something like (why is Insert Sheet not on the Insert ribbon but under the Insert Column dropdown?, in fact the entire Insert ribbon is useless to me other then the charts even though it takes prime real estate). Also as frequent user of multiple Add-Ins I find that their bastard existence on the Add-Ins ribbon to be irritating as I frequently have to move back and forth between that and other ribbons.

    Finally, my biggest beef with the ribbon is who decided it was necessary to program the UI? As I mentioned I have to be productive in Excel, this version of Excel has limited the productivity of the high-end user (specifically financial services which I work in). Its inexcusable that Microsoft rolled out a product where the end-user has to code in order to manipulate the location of icons. While this may work great for those that design high-end Excel products and make it easier on some developers it makes my life a nightmare from a productivity standpoint. As a user, I would be recommending that Microsoft return that functionality in the next Service Pack NOT the next release. Along with the Select Data dialog box for Graphs this is my biggest beef with Excel 2007 and will ultimately limit its rollout within certain industries who count on Excel for productivity. Asking someone that literally makes millions of dollars for your firm to retrain to use a new version of Excel is not an option.

  6. Do any of you have a problem being able to use the middle roller on your mouse to quickly scroll down or up through the rows in Excel 2007? I can’t seem to get that to work in Excel 2007–it does work in Word 2007 though so not sure what’s up with Excel. Let me know if you have a solution or know what I’m doing wrong. Thanks.

  7. Jean – I haven’t noticed any problem with the scroll wheel of the mouse.

  8. Andrew Miller says:

    Here’s a question that’s been bothering me with Excel 2007. In Excel 2003 when plotting a time series with a date/time format on the x-axis, you could set axis limits using the same format that was being used for display – e.g. 6/23/2003 13:30 for the upper or lower limit. Now the “format axis” command seems only to accept date/time limits in the decimal days since 1900 format, which is the native format used for dates and times but which is certainly not intuitive. Is there some way to restore this capability?

    Additionally my experience has been that if I am plotting a large data set, the charting engine is so slow that sometimes the program just hangs, stops responding, and has to be killed using Task Manager. This also happens if I have several large spreadsheets open and the Autosave option kicks in and tries to save one of them. It has been frustrating enough that I am thinking about uninstalling 2007 and reinstalling 2003. But I haven’t been able to find any comments about this anywhere. I am running a Lenovo Z61p machine with 2 gb of RAM so this kind of thing should not be happening.

    I just installed Service Pack 1 and am hoping that at least the latter set of problems may be resolved by this. But I would appreciate any relevant insights.

  9. Andrew –

    The only place you can enter axis limits in date or time format in Excel 2007 is for a line chart’s date-scale X axis. They didn’t implement it for other value axes. My suspicion is that neither the designer spec’ing the dialog nor the programmer coding the dialog ever made a chart that used dates and times, and therefore they didn’t know the capability existed, nor that it was even desirable.

    The SP1 update helps with much of the glacial performance of the Excel 2007 charting engine. I don’t think you’ll ever do better than half as fast as Excel 2003.

  10. Andrew Miller says:

    Jon –

    Thanks for the quick response. I’ll settle for half as fast as 2003 as long as it doesn’t sit for minutes at a time every time I try to edit a chart. Odd that the date-time capability existed before and was then deleted – somebody must have thought it was useful originally…

  11. The classic charting UI was actually rather nice, once you got used to it. You could enter values as dates or times, you had everything visible at once, a lot of controls available on a given screen instead of scattered about (e.g., in 2003 you can format your line and markers on one screen, but 2007 needs SIX screens). Also in classic Excel, the F4 key was remarkably useful when formatting a number of charts, but in 2007 it’s practically broken. Unfortunately the designers and builders of the tool were evidently not familiar with the actual usage of the tool.

    The specific capability we’re discussing was not deleted, by the way, it was never included in the rebuilt chart engine

  12. Jean – I also have a problem with scrolling in Excel 2007. It simply does not scroll. When I hold the control key down, I can get a zoom. When I set the option to zoom on mouse scroll – I get nothing, but then if I hold the control key down I get scroll. None of this happens in any other program (microsoft or not) on this machine. I do have Excel 2003 installed along side 2007, this helps me on complex spreadsheets which 2007 is too slow on. Productivity has gone down in 2007, but 2003 Alt key combinations still work – YAY! Alt+D,F,F for autofilter.

  13. Found the solution to my post. From JIM234 “Actually I fixed the problem, in the wheel settings you switch to Office 97 Scrolling Emulation. Not really logic, but…” found here: http://www.fixya.com/support/t411331-excel_2007

  14. My old blazing fast Excel 97 files with large charts on my old slow NT machine have become way to slow with my new fast Vista machine with Excel 2007(SP1). Microsoft has one of my files and acknowledges the same problem but so far has not offered a fix.

  15. Phil –

    This matches the experience of many users. Some activities, particularly charting, can be much slower in Excel 2007, even after SP1 is applied. I don’t think there’s much you can do about it.

  16. I’m in the process of communicating with Microsoft (trouble #1070441661). It’s been an amazing journey. I’ve talked with 3 different people. I’ve given them the one file that is the best example of this problem. They see the same problem as I have described. And yet they still ask me questions that do not pertain to the problem. They offer me solutions with out stating that one important statement–We have solved the problem, here is the fix. I even narrowed down the problem to the kind of chart. (most of my charts work well enough except for one type and of course it’s the one that I need the most.) The chart type is one that has both positive an negative numbers in a range between .28 and -.28 and several thousand data points across 3 different lines. I tried recreating the chart. It works well but printing became a nightmare–of which they offered a printing problem solution–I don’t have a printing problem guys. The original chart took long to appear and long to print but it worked slow. The recreated chart comes up fast enough but I give up after 20 minutes of waiting for it to print. I did learn that the Mac version, Excel 2008, is the same program so it, too, has the same charting problems and, of course, the Mac users are complaining now, too.

  17. I believe I have solved the Excel 2007 problem. Well, at least the lined charts. I don’t use any other kind of charts. The problem was not so much with large Excel 2007 charts but rather the kind of lines being used. I use many dashed and doted lines in my charts. I changed all of my dashed and doted lines to solid lines and all my problems disappeared. The charts appear quickly as they should and printing is back to the way it should be.

    Hope that helps others.

  18. Tim –

    I don’t know about the Mac 2008 version. I do know that in 2007, the F4 key still repeats the last action, but not on all actions, and whereas in 2003 it would repeat a whole sequence of changes, for example, everything you did during a trip to a dialog box, in 2007 it only repeats the absolute last thing you did, so it saves one keystroke instead of dozens.

  19. Hi,
    I am used to using a PC and an earlier version of Excel. I am now on a Mac and using 2008 Excel. I cannot find one of my favourite functions – F4 to repeat the last action. I have been looking around on the web but cannot find this function.

    Can you offer some assistance? Is the function still there??

    Many thanks,
    Tim.

  20. I like the new version. Except, there is no way to select noncontiguous cells. The use of ctrl+click does not work as described in the Help menu. When multiple cells in a column are selected, and I choose the Copy command, all cells between the first and last are placed on the clipboard (like what the Shift key is intended to do).

  21. Kevin –

    If you copy the discontiguous cells, and paste them within the same Excel instance, only the selected cells are pasted. If you paste them elsewhere, the entire range is pasted. This behavior is unchanged from earlier versions of Excel.

  22. If you can’t select a discontiguous range of cells, this means that you can’t plot them in a chart – which seems to me to be disastrous! Have I really understood this correctly?

    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, as Kevin Lade says. Is there really no way round this?

    Helen

  23. Helen –

    You will find A LOT of confusion about Excel 2007 compared to earlier versions. In many cases, people try something, it doesn’t work, and WITHOUT TRYING IT IN EXCEL 2003, they state that some feature is no longer available. I have tried to test my assertions in both versions before making any bold claims; I have surprised myself with my own selective memory.

    You can in fact select discontiguous ranges. I think Kevin has misunderstood how this works differently within the same Excel instance and across different applications. If you ctrl+click areas of cells that follow a rudimentary selection pattern (see below), then Excel will treat this discontiguous range as if it occupies the smaller condensed range. However, if your discontiguous selection does not follow the rules I’ve spelled out, Excel will balk at most non-formatting operations regarding the selection. In addition, as soon as you try to use this discontiguous range outside of the Excel instance in which it was copied, the target application will treat the range as if it includes the unselected regions contained by the discontiguous areas.

    Rules for “well-formed” discontiguous ranges:
    You must be able to remove unselected whole rows and columns from between selected areas, so that the selection condenses into a single highlighted rectangle;
    no cells may be selected multiple times;
    all boundaries between areas must extend continuously across the selection.

    What is the Ctrl-Drag method you are speaking of? Do you mean Ctrl+Select (which works), or something different? You can Ctrl+Select to select usable discontiguous ranges. You can also use Ctrl+Drag to copy contiguous ranges to another location. Neither of these behaviors have changed.

  24. You are quite right to keep Excel 2003 around. I have been spending a great deal of time looking for features that were second nature in 2003 and earlier. I also find the lack of flexibility in the ribbon, and the amount of screen real estate it takes ignorant of Microsoft.

    For example, I have spent hours trying to make four simple adjustments to a stacked chart. 1. Add a title that was not there before. 2. Remove the word date from the first place on the X-axis (This version of Excel thinks that the word Date is a “category” and not a label). 3. Overlay a differently formatted time series using the same X-axis. and 4. Adding some text boxes TO THE CHART explaining key points.

    In 2003, this would take 10 minutes.

  25. Jon – a belated comment on your belated review regarding your comment …”Well, no problem, you may say, I’ll just customize the Ribbon, just like the toolbars in Excel 2003. But you would be mistaken. There is hardly any facility to modify the Ribbon via the user interface. There is a single one-row high strip along the title bar of the application window that you can customize….

    My one-liner (which is rare for me) that summarises MS stupidity: if the QAT is all the chance the average user has got of customising the interface, then why the hell isn’t there an EXCEL OPTIONS button on the effing home tab?

  26. I’ve been using spreadsheets for the better part of every day since Visicalc which I was happy to see replaced with Lotus 123. I fondly remember changing those first 123 diskettes (three of them I believe) back and forth to use the different features of the program. I was sorry to see Lotus 123 edged out by Excel which was an inferior spreadsheet at the time, but not being one to be stuck in the past I went along and made the best of it. Eventually, Excel caught up to where Lotus had been years before and became a really good spread sheet (although Excel never could match the old Lotus /fc command). All I have to say about Excel 2007 is “it really sucks” What cumbesome piece of you-know-what this is–but is sure does look good.

  27. I used to be able to produce a chart with an X axis and two Y axis as in a climatogram. The x axis was months – time. One of the y axis was a bar graph, one a line graph. Can that be done with 2007.

  28. Paul –

    Almost anything you can do in 2003 can also be done in 2007. It might take more swearing, but it usually can get done.

    Make your chart with both series as lines. Right click one, choose Chart Type (2003) or Change Chart Type (2007), and change it to a bar. (Or start with both as bars and convert one to a line.) Right click one of the series, choose the Format item on the pop up menu, and change the axis to Secondary.

  29. I am being dragged to the use of Excel 2007. I have created a number of timesaving macros attaching them to custom toolbars and menus in Excel 2003. My productivity is going down and my frustrations are going up all because someone thought they knew more about my needs than I do. I’ll have to come up with more keyboard shortcuts without interfering with the built in ones that I already use extensively. Is Microsoft listening to their users?

  30. Good question.

    In 2007 you can add buttons to the QAT, which is a weak imitation of a toolbar. I have not done so, but then, I don’t use 2007 much yet for my own work.

    Most of my tools in 2003 are called from buttons on a custom toolbar which is generated when my personal macro workbook is opened, so these buttons end up on 2007’s Add-Ins tab. Not quite as good as a custom ribbon tab, but it’s okay. If you want to follow this approach, Id suggest using the MenuMakr approach from John Walkenbach (http://spreadsheetpage.com/index.php/tip/creating_custom_menus/). My custom toolbars are a highly modified variation on John’s approach.

    Microsoft has heard the whining and complaining, so the Excel 2010 ribbon is actually customizable by the user through the Excel interface. The Office 2007 User Experience team would not have allowed this, but the architecture astronauts who held the view that everyone’s interface should always look the same as when Office was first installed have moved to other projects.

  31. Micosoft, you really did it this time. THIS IS THE WORST YOU DID YET! This new menu system… Where the heck did you get this idea from. I have been using Excel for years and this has slowed production down tremendously. At this point I wish there were another spreadsheet program out there that can replace Excel. Why wasn’t the old menu system an option? It could have been used and the new items added. If you did that, it would have been much better and the production could have continued. I give the new Excel 2007 an “F” for production.

  32. I don’t mind the ribbon interface. I got used to it, and it made sense. But, I didn’t like it when they removed useful buttons like Print Preview..I had to manually add it to the quick bar. Also didn’t like files saved in Excel 2007 format sproadically corrupting, getting “Recovered”, and by “recovered” I mean all my formulas were wiped out and replaced by the hard values. I’ve since kept all my files in 2003 format, which means 2007’s file compression is worthless to me since I don’t use the file format. Likewise, I can’t alter my custom toolbar made in Excel 2000, b/c they totally changed how custom toolbars and buttons work. It’s ridiculous that power-users have to now fiddle around with pasting XML all over the place to get things to work. The macro recorder doesn’t record all steps anymore. All of these things that use to just work are now broken or dysfunctional. I don’t consider this progress. I basically paid money for a superficial upgrde to the interface, and a downgrade to everything else.

Trackbacks

  1. A Belated Review of Excel 2007 - Learn Excel says:

    […] Original post by Jon Peltier […]

  2. […] Wer schon mal Tabellen die in Excel erstellt und formatiert hat, ist oft enttäuscht über das Ergebnis wenn die Tabelle in Word hinüber kopiert wird. Um Tabellen so zu kopieren wie sie in Excel erstellt wurden hat Microsoft die Funktion “kopieren als Bild” bereitgestellt. Die Funktion ist aber leider sehr versteckt und nicht nur ich habe lange danach gesucht sie zu finden. Beim Weblog Peltier Technical Services habe ich dazu eine Anleitung mit Bildern gefunden A Belated Review of Excel 2007 […]

  3. […] Perhaps this assessment will help converts to Excel 2007 who are having difficulty with the changed charting environment, and hopefully any criticisms and suggestions will be taken to heart by developers of future releases of Excel. There is also a companion post reviewing general changes between Classic Excel and Excel 2007. […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0