My First Look at Excel 2010
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
Last week Microsoft released the 2010 technical preview version of Office 2010 to an excess of noise in the media and blogosphere. You’re all probably bored spitless of all the hype. Everyone was spewing the same press release fodder, and nobody had anything of substance to report.
Two weeks ago, I wrote that my main laptop died, and I had to scramble about to get a new one all configured. At about the time the Office 2010 preview was released, my dead laptop came back to life. To punish it for scaring me like that, I installed the preview on the revived machine. The installation went very smoothly, better than you’d expect for a beta version, and better than many installation experiences.
I’ve run Excel 2010 a few times now, and Word 2010 once by accident when I double clicked on a document in Windows Explorer. I didn’t notice at first because I was mesmerized by the dynamic splash screen, but Excel 2010 starts up very quickly. In general Excel 2010 seems to lag a bit less than 2007 while doing routine tasks.
The Excel 2010 Ribbon
My first impression of the 2010 ribbon was that it was cleaner and maybe a little smaller than in 2007. When I took a few screenshots and put them together, it was clear that the new ribbon was no smaller than before,still a good row taller than an overloaded toolbar area in 2003. But the 2010 ribbon definitely was cleaner.
The 2003 interface is pretty cluttered, but that clutter is a price I’m willing to pay for an efficient interface that displays all of my most-used commands at once, without hiding 90% of them. (I don’t think I’ll ever forgive Microsoft for this design debacle, but apparently we’re stuck with it.) The 2007 ribbon looks nearly as cluttered as the classic toolbar interface, not because it has lots of controls, but because everything is in boxes, and there is no shortage of graded coloring.
The 2010 ribbon has the same density of controls as in 2007, but it looks cleaner because of its lighter background and the elimination of much of the chart-junk-like features. Instead of boxes around all of the groups of controls, as in 2007, the groups are separated by a single light line, and the shadowing is much less pronounced.
A Designer Cleans up the 2007 Ribbon
A year ago Andreas Lipphardt of XLCubed wrote Microsoft, Pimp Down My Ribbon, a request to clean up the 2007 ribbon. He even showed how. He started with the ribbon out of the box:
Andreas cleaned up the ribbon by removing all the gradients and glows, and without all of the gratuitous effects, it certainly looked much cleaner.
Microsoft stopped here with the 2010 ribbon, but Andreas took it one step further, removing the large office button and the oversized ribbon buttons (e.g., Paste), and closed up the excess space. This was becoing too much like the classic interface.
Why Can’t Users Clean Up Their Own Ribbons?
Now they can, to some extent. Microsoft has added the ability to make changes to the interface through the interface. Even though RibbonX, the ribbon-specific subset of XML, is relatively easy to use to customize the interface, RibbonX is still way beyond a typical user’s capabilities. This ribbon customization capability gives back to the user some small measure of control, maybe 10% of what we once had.
Charting on the Ribbon
I’ll show a couple shots to further illustrate the cleaner ribbon appearance in 2010. Here are the Charts groups from the Insert tabs of Excel 2007 and 2010.
Although the 2010 group looks much cleaner, the buttons are all the same as before, and in fact, all the options under the buttons are the same. The gallery of chart types in 2010 is just like that in 2007. The Chart Tools contextual tabs are also cleaner but with essentially no real changes. The one addition is a Draft Mode button on the Design tab. Its purpose is to allow charts to be drawn with only rudimentary formatting, to speed up screen refreshing in Excel 2007.
A Couple New Features – Look But Don’t Touch
However, right next to the Charts group on the Excel 2010 Insert tab are two new groups: Sparklines and Filter, which houses the Slicer. These much heralded features are probably the most talked about new tricks in Excel 2010. The advance press for Sparklines on the MSDN Excel blog is very promising.
At first I thought that neither feature is enabled for testing in this early preview version. I didn’t notice that I was using Compatibility Mode, which disables advanced features introduced in 2007 and later. I apologize for my newbie error, and I anticipate testing out these features in the very near future.
Chart Dialogs
I made a few charts, just to see what if anything has improved in the awkward Excel 2007 dialogs. One thing has changed: after being temporarily retired for one version, the ability has returned to double click on a chart element to open its format dialog! Unfortunately the dialogs that open up are unchanged from those of 2007. Too many tabs, too many options hidden in dropdowns when there is ample space to use listboxes or option buttons, too many clicks required to do otherwise quick tasks. And the F4 (Repeat Last Action) command is still AWOL. It remains to be seen whether any changes will be forthcoming in upcoming releases of the preview.
I’ve been working on a charting interface add-in for Excel 2007, and I suspect that it will be useful for users of Excel 2010 as well.
Macro Recorder
I recorded a few macros, nothing too complex, to see whether the huge gaps in Excel 2007’s macro recorder coverage have been addressed. In 2007 if you recorded a macro while doing anything with shapes, the macro came out blank. When working with charts, some actions were recorded, but many were missed, particularly those related to formatting of the shapes that make up the chart’s elements. Because the recorder didn’t record, we were without a tool that was so helpful to decode the intricacies of the object model.
Without examining the details, I was able to see that all actions were represented by commands in the recorded macros. Shapes and chart element formatting were again represented in the code. This is a great (re)addition to a developer’s toolbox.
Summary
Excel 2010 looks a bit cleaner and seems to run a bit more smoothly than Excel 2007. In the small part of Excel that I have examined, very little has changed. I will be looking at Sparklines and other neat new stuff in the near future.
Possibly Related Posts:
- How To: Assign a Macro to a Toolbar or Menu
- My Customized Excel Toolbars
- Dashboard Competition
- What happened to my favorite Excel 2003 Chart feature?
- Make Your Recorded Macro Independent of Which Sheet is Active
- PTS Charts in Excel 2010
- A Belated Review of Excel 2007
- How To: Assign a Macro to a Button or Shape
- Blue Ribbon Interface
- How To: Record Your Own Macro
Posted: Thursday, July 23rd, 2009 under Excel 2010.
Comments: 30
Comments
Comment from Rick Williams
Time: Thursday, July 23, 2009, 2:23 am
Thanks for the review. It’s good to know there is at least /something/ being improved in the new version. I think the ribbon customisation is a big win, as is the double clicking on chart elements. But yes, it is still so frustrating to reformat charts without the F4 redo! I notice that format painter also is disabled for chart elements, which would be at least a start of a work around. There isn’t even an easy way to (for example) remove all markers from a line plot without visiting ‘change chart type’ and lose other formatting you may have applied. And on top of that – the modeless “Format Data Series’ dialog box, which /might/ have been useful for something like this has the decidedly UNhelpful habit of reverting to the first ‘tab’ (Series Options) everytime the selection is changed!! Oh my god, I cannot express how infuriating this is! to do this for *FOUR* data series: click,Ctrl+1, click, click, click, click, click, click, click, click, click, click, click, click. Want to change the marker fill, shape and size (from 7 to 2) instead? 41 clicks plus typing the size of the marker. or use the scroll buttons and that takes it to a nice total of 61 clicks.
Absolutely ridiculous!
Comment from sam
Time: Thursday, July 23, 2009, 3:33 am
Jon,
Here is list of improvements / Bug Fixes is Excel 2010
1) The Limit of 8192 areas is removed (increased to ???? – Havent tested is yet but its atleast 524288). Now it makes more sense have more rows
2) Auto Filter and Pivot Filter comes with Search box (not powered by Bing :-))
3) If there are more than 10 K unique items in a filter drop down a message is displayed saying “Not all items displayed”
4) Pivot Tables has the following extra options
a) Show Value as % of Parent column – Extremly helpful when you have more than 2 row fields
b) Fill Lables -The Pivot then starts looking like a Data table
c) What If Analysis (For Server based pivots)
d) Manage Sets (dont know what this is yet – Excel crashes when I click on this)
5) Slicers – work for me – Its just a floating page field for a pivot…. nothing to get very excited about
6) Spark lines work for me – They are a good start but they have a long way to go as there are very limited options(compared to the options available in Sparklines.xla form http://sparklines-excel.blogspot.com/
There are No bullet charts, box plots area charts etc.
There is no possibility of showing Bench mark lines (Avg/Percentile etc) for Bar charts
7) Conditional Formating – Solid Fills for Data Bars – Accuracy finally takes precedence over fluff – It can now be used as a spark line
It also has some neat options like setting the Max value, Show only the Databar and hide the data etc
8) Very Limited Ribbon Customization
Can Create your own tabs
But cannot
a) specify Size of button directly (need to say don’t display Lables to get small buttons !!)
b) specify Screen tips, Super Tips etc
c) Very limited image Gallery for custom buttons
d) Can remove and rearrange groups in existing Tabs – But cannot add/modify existing groups……but some how its not consistent… Next time you open XL its back to normal
e) Customization files can be exported/imported but edited only via Note Pad
UI Editor does not recognise them
f) Green Blob appear in place of menus/buttons of a custom tab which has many buttons if you resize the Excel window or open the file on a “small screen”
9) Pastspecial options available on right click
10) Macro Recorder Caution : If you install Excel 2010 on a machine which has older versions of Excel then the macro recorder in the older version gets completely messed up…. and starts recording junk even for simple actions
I have sent you a file showing images of a customized ribbon highlighting
underutilized real estate
Sam
Comment from jeff weir
Time: Thursday, July 23, 2009, 3:45 am
There’s the odd thing about excel 2007 (and presumably 2010) that I like more than in excel 2003. But I think you could sum it up by stating that for every step forward in one area, there’s 2 steps back in another area. So on average, it sucks.
Comment from Jan Karel Pieterse
Time: Thursday, July 23, 2009, 5:14 am
Hi Jon,
Sparklines and slicers are available in this build, but you need to have a cell selected to use them.
Comment from Jon Peltier
Time: Thursday, July 23, 2009, 8:05 am
I tried using Sparklines with cells selected, first with the range I wanted to plot selected, then with the cell selected where I wanted the range selected, and the buttons never became enabled.
I made no pivot tables, so I didn’t try the Slicer. Given that Sparklines seemed disabled, I assumed (yes, I know the ass-u-me thing) that the slicer also was always disabled.
I’ll have to try to jump start the sparkline feature, because I wanted to play with something new.
Comment from Dennis Wallentin
Time: Thursday, July 23, 2009, 8:15 am
Jon,
Perhaps we can agree that the new version of the Ribbon UI is in the right direction.
For me Excel becomes a better companion when doing BI-analyses with large sets of data. The improvcements in the P/T are good but I wished there was a way to export data from the P/T tool to XML. The Slice tool is very nice and although the Sparkline tool is version 1.0 it now exists in Excel.
The major good news for me is that it now exist a x64 version of Excel. OK, not everyone will be using it but for those who make computation on large amount of data should be pleased. Of course, the major limit is the x32/x64 situation.
Sam,
The major disappointment (for me) with the Ribbon is the lack of a similar tool in Excel as we find in VSTO and is provided by third-parties in the .NET environment. OK, a new version of the Custom UI editor will be available but it’s still an external tool.
I can understand that MSFT is looking ahead when the new .NET IDE and then provide a similar tool but it still a must to improve the present tool.
Kind regards,
Dennis
Comment from Jon Peltier
Time: Thursday, July 23, 2009, 11:08 am
A few people have commented that they can use the Sparkline tool. I restarted Excel 2010, checked the Insert menu, and the Sparklines controls were disabled. I filled a range with data, selected the range, and the controls were still disabled. No joy.
I decided to uninstall 2010, and also uninstall Office 2003 which is also on that machine. Then I’ll reboot, apply all the latest Windows updates (it’s XP Pro SP3 if that matters), then reinstall 2010 only. Then try it again.
If anyone has any suggestions, let me know.
Comment from sam
Time: Thursday, July 23, 2009, 11:54 am
Jon,
Sparklines and Slicers are disabled if the file is opend in “Compatibility mode”
Sam
Comment from Colin Banfield
Time: Thursday, July 23, 2009, 12:23 pm
Jon, can’t imagine why your sparklines aren’t enabled. I have three versions of Excel installed – Installation order was Excel 2007 first, Excel 2010 technical preview second and Excel 2003 last.
Sam, there are a few more PT custom calculations you didn’t mention. The full list would be:
% of Parent Row Total
% of Parent Column Total
% of Parent Total
% Running Total In…
Rank Smallest to Largest
Rank Largest to Smallest
The new What-If and Set features work only with OLAP sources. What-If allows manually changing values in the OLAP based pivot tables and having totals recalculated with the new values. However, the feature works only with simple server aggregations (i.e. sum). The Set feature allows you to predefine the dimension members and measures you want to work with. (e.g. long lead time products, last twelve months etc). Instead of dragging and filtering individual dimensions and measures every time you wish to work witha subset of the data, you simply drag the set (which you give a name to) to the row or column area. Named Sets and What-If were introduced in Analysis services 2005. Server based named sets show up in the pivot-table field list. The sets and What-If scenarios created in Excel are saved in the workbook – they aren’t written back to the server.
Comment from Mike Alexander
Time: Thursday, July 23, 2009, 12:46 pm
Jon: There are still a few things that are annoyingly difficult to find.
Example, try to find Print Preview in less than 10 seconds.
Comment from Jon Peltier
Time: Thursday, July 23, 2009, 12:52 pm
Colin -
Sam indicated that compatibility mode disables advanced (2007 and 2010 only) features, and a couple screen shots I took last night indicate I was in incompatibility mode. Right out of the box I wouldn’t have expected that.
Mike -
Is this a test?
Comment from JP
Time: Thursday, July 23, 2009, 1:06 pm
Would it be safe to say that Excel 2007 is like Excel 2000 (lots of new features) and Excel 2010 is like Excel 2002 (just a few minor upgrades)?
Comment from Mike Alexander
Time: Thursday, July 23, 2009, 1:38 pm
Jon: Not a test. It’ took me 20 minutes to find it. I must admit, now that I know where it is, it’s easy to find, but it’s definitely not where you would think it should be.
Comment from Colin Banfield
Time: Thursday, July 23, 2009, 2:28 pm
Jon, this post might be a good place for users to list the new features they’ve discovered in Excel 2010. Sam listed a few things I hadn’t yet discovered (1 and 3 on his list). Those are the kind of features you have to be looking for (or discover by accident). So to add to Sam’s List (excluding the new PivotTable options already mentioned):
1) Completely re-organized Office button menus
2) Charts
2(a) Draft Mode – removes all special effects (glow, shadow, reflection etc.), anti-aliasing (most noticeable with lines, where the edges are jagged) & gradient fills.
2(b) Removal of 32K data point plot limit (haven’t determined new limit but I plotted over 200K points)
2(c) Chart updates faster than Excel 2007 SP2 AND Excel 2003. To test this, I used your test on updating 10K random numbers in an XY chart. Excel 2010 won, followed by Excel 2003 and then Excel 2007 SP2. Interesting observations in this test were that draft mode didn’t make much difference in the results (Excel 2010 won whether the chart was in draft mode or not) and using multiple processors didn’t make much difference to the results (because I was running a system with a quad processor, I had to enable a single processor only to test against Excel 2003).
2(d) Double-click on series displays format dialog box (as Jon discovered).
2(e) PivotChart field list filters make a comeback on the chart, but are much less intrusive than the Excel 2003 implementation.
3) New Conditional Formatting Icon Sets (one directional and two rating). The sets are now categorized. Furthermore, you can mix and match icons from different sets to create a custom set (with up to five icons).
4) Tables
4 (a) The filter drop-downs now appear in the worksheet column header when the table is scrolled (yay!)
4 (b) New structured referencing shorthand – For calculated columns, the table name is no longer included in the reference, and “[#This Row],” is replaced with “@”
5) Insert screenshot
6) New SmartArt graphics – amazingly though, still no fishbone graphic!
7) New tools for inserted picture
7 (a) Background removal tool
7(b) Artistic Effects
7(c) Enhanced Brightness, Contrast and Recolor tools
8) Several Trust Center Enhancements and the security warning bar that is now in red to attract the user’s attention.
9) New & revised worksheet functions
9(a) A bunch of revised statistical functions (with new names)
9(b) Two new Math functions – AGGREGATE and ISO.CEILING. AGGREGATE is like SUBTOTAL on steroids.
9(c) Two new Date functions – WORKDAY.INTL and NETWORKDAYS.INTL. These work like the existing functions, except that you can specify the weekend days.
9(d) MOD function fixed!!!
10) Macro recording works perfectly for shapes, but there are still problems with charts. The very first chart macro I recorded (with a few formatting options) failed on playback.
11) Equation Editor
12) Copy as Picture option added to Ribbon copy button
13) Undecipherable Paste options icons replace text options
14) Gradient Fill controls modified
15) Greatly expanded Format Shape dialog box
Anything else?
Comment from Jon Peltier
Time: Thursday, July 23, 2009, 2:40 pm
Jimmy -
I’d been thinking that 2007 was like 2002, a forgotten version that was hardly implemented. 2002 because it offered so few changes, 2007 because many users were unhappy with the changes.
Colin -
Thanks for the list. I have yet to see such a list from Microsoft.
Comment from Colin Banfield
Time: Thursday, July 23, 2009, 2:58 pm
Jon, I think that Dave Gainer did a much better job in listing new features during the
Excel 2007 beta. It seems that the Excel team believes that Sparklines are the best thing to talk about right now. I’m already jaded by the three posts on this one topic, and more posts are promised!
Comment from Jon Peltier
Time: Thursday, July 23, 2009, 3:07 pm
It’s official: I’m an idiot. After reinstalling Office 2010, I restarted Excel. This time, compatibility mode was not activated, and all of the buttons which were disabled before have been enabled. I made a few sparklines, and I will admit that they’re pretty cool. Of course, I was looking for drawing baselines or target lines or other features, which aren’t available. But at least I know how to get to these features.
Comment from Mike Alexander
Time: Thursday, July 23, 2009, 3:10 pm
Colin: They seemed to have corrected the Camera Tool bug that made your linked picture disappear if the source was on the same sheet, but off-screen.
Comment from Colin Banfield
Time: Thursday, July 23, 2009, 6:36 pm
Mike, for some reason I thought that that bug was fixed in Excel 2007 SP2. But it’s still there. Thanks for pointing this out.
Comment from Jon Peltier
Time: Thursday, July 23, 2009, 9:50 pm
I’ve amended the text above so people don’t come away thinking that the new features haven’t yet been enabled. That was my mistake: the features are ready for use, and I will be looking at them soon. Especially those sparklines. A lot of people have made comments like “It’s about time, Microsoft, what took so long, and why don’t they have all these features?” The new sparklines are a first version, and they don’t in fact do everything, but this is a fine start.
Comment from DaleW
Time: Thursday, July 23, 2009, 10:47 pm
I’m not sure whether to be happy or sad that Sparkllines seem to be the hottest new feature in Excel 2010.
Did Microsoft implement their Sparklines v1 in the drawing layer (like Fabrice’s open source add-in), or by a custom font as the commercial Sparklines add-ins (BonaVista and Bissantz) primarily use?
BTW, anybody know if anyone has implemented Sparklines for Open Office Calc? (I’m not talking about faking it with something like TinyCharts, but a modestly functional implementation.)
Thanks!
Comment from DaleW
Time: Thursday, July 23, 2009, 11:36 pm
Oops, I just noticed EuroOffice Sparklines for OpenOffice. This 2009 experiment may be nice for pure and very simple Tufte Sparklines (with dialog options to enable/disable each of the four colored indicator dots on a line), but it appears to have very limited functionality so far, even compared with Excel 2010 screenshots.
So I guess that if we want to create Sparklines using a spreadsheet, Excel 20xx is the only practical spreadsheet so far?
Comment from Jon Peltier
Time: Thursday, July 23, 2009, 11:58 pm
Dale -
I’ll be writing about Excel’s Sparklines in an upcoming post. They are actually well done, for a first version; as well done as the second version of in-cell Data Bars, which I am also writing about. I’m sure if you need more advanced sparklines, the third-party add-ins will be able to handle your needs.
Excel’s sparklines are neither drawing layer additions nor tricks with special fonts. They are actually drawn on the back surface of the cells, behind any text which may exist in the cells. This is the same as the data bars. I believe that both are actually bitmaps constructed on the fly and inserted behind the cells.
Comment from Sandi
Time: Friday, July 24, 2009, 11:50 am
As always, your posts don’t disappoint. Thanks Jon !
Comment from sam
Time: Saturday, July 25, 2009, 10:45 am
Also removed in 2010
Cells with more than 255 Characters are not truncated when you copy by “Move or Copy”
Comment from Metrick
Time: Tuesday, November 10, 2009, 8:28 am
Feature: Direct manipulation of data points on charts
97-2003: You can drag data points on a chart and change their source values on the worksheet.
2007: Dragging data points to change the source values on the worksheet is no longer supported.
Has dragging data points to change the source values make it make back to 2010 version.
Comment from Jon Peltier
Time: Tuesday, November 10, 2009, 9:38 am
Metrick -
As far as I know, the Goal Seek capability of charts (i.e., the dragging of points) has not been reinstated, and will not be reinstated.
Microsoft has admitted their mistake, though, and have released an Excel Add-In for Manipulating Points on Charts (MPOC). Wonder of wonders, it’s even written in VBA, not the latest flavor of dot-net.
This add-in lets you select a point, then use sliders on a userform to adjust values. I’ve been noodling around with a similar add-in that actually lets you click and drag the point, like in the good old days. It’s been hard, though, because I am trying to invoke Goal Seek for those cases where the plotted value is a formula depending on other values.
Comment from Dennis Wallentin
Time: Tuesday, November 10, 2009, 12:53 pm
>>not the latest flavor of dot-net
Simple because MSFT knows that VBA-developers don’t know how to handle this kind of high-tech add-ins .
All the . regards,
Dennis
Comment from Jon Peltier
Time: Tuesday, November 10, 2009, 1:06 pm
Either that, or dot-net would be absolute overkill for such a simple project.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.