What is new in Microsoft Excel 2010? [Office 2010 Week]

Posted on May 17th, 2010 in Featured , Learn Excel - 100 comments

Excel 2010 - What is new?This week we are celebrating Office 2010 launch at chandoo.org

Office 2010 [download beta version | purchase], the latest and greatest version of Microsoft Office Productivity applications is going to be available worldwide in the next few weeks. I have been using Office 2010 beta since November last year and recently upgraded my installation to the RTM version (Ready to Manufacture, a version that is final and used for burning CDs that MS sells).

I was pleasantly surprised when I ran Microsoft Excel 2010 for first time. It felt smooth, fast, responsive and looked great on my comp.

This week, we are going to celebrate launch of Office 2010 by learning,

  1. What is new in Excel 2010
  2. Introduction to Excel 2010 Spark-lines
  3. New Conditional Formatting Features in Excel 2010
  4. Making your own ribbon in Excel 2010
  5. Using the Backstage View in Excel 2010
Leave a comment to win a copy of Office 2007 – Home & Student Edition
(with free upgrade to Office 2010 in June)

What is new in Excel 2010?

There are a ton of new and cool features in Excel 2010. My favorite new features are,

Sparklines

Excel 2010 - Sparklines
These are small charts that can be shown inside a cell and are linked to data in other cells.You can insert a line chart, win-loss chart or column chart type of spark line in excel 2010. They add rich information analysis capability to mundane tables or dashboards. We learn more about using them in tomorrows article.
[meanwhile: Learn how you can make sparklines in earlier versions of Excel]

Slicers

Slicers to filter pivot tables with ease
Slicers are like visual filters. They are an easy way to slice and dice a pivot table (what is a pivot table – tutorial). A sample slicer at work is shown above.

Improved Tables & Filters

Tables show filters even when you scroll down
When working with tables in Excel 2010, you can see the table filtering & sorting options even when you scroll down (the column headings – A,B,C… change to table headings)
[Related: Introduction to Excel Tables]

Also, in Excel 2010, data filters have a nifty search option to quickly search and filter values you want. (I still prefer the excel 2003 style one click filtering).

New Screenshot Feature:

Excel 2010 - Screenshot Picker tool
Now, using Excel (or any other Office 2010 app) you can grab a screenshot of any open window. This could be very useful for those of us in teaching industry as you can quickly embed screenshots in to your teaching material (like slides or documents).

Paste Previews:

Preview before pasting
There are a ton of cool paste features buried in the Paste Special Options in earlier versions of Excel. MS has bought all these to fore-front with Paste Previews feature in Office 2010.

Improved Conditional Formatting:


Excel 2010 added a lot of simple but effect improvements to conditional formatting. One of my favorites is the ability to have solid fill in a cell based on the value in it. This provides an easy way to create in-cell bar charts.

Customize Pivot Tables Quickly

Excel 2010 - Pivot Table Options
Now you can easily change pivot table summary type and calculation types from Pivot Table “Options” ribbon in a click (learn how to do this in Excel 2007 and earlier).

Also you can do what-if analysis on Pivots (I am yet to try this feature).

Customize Add-ins from Developer Ribbon

Excel 2010 - Add-in Menu in developer ribbon
In Excel 2007, if you want to customize or add a new add-in, you have to circumnavigate cape of good hope. But Excel 2010 makes it a pleasant experience again. There are two buttons, right on developer ribbon tab using which you can quickly add, change any add-ins.

(also, it seems like developer ribbon is turned on by default, which is pretty cool.)

Customize Ribbons and Define your own Ribbons

You can customize ribbon in Excel 2010
One the most beautiful and powerful features about Office products is that you can customize them as you want. You could easily add menus, change labels, and define toolbars the way you like to work. It made us feel a little powerful and awesome. Then, for some reason, MS removed most of these customizations in Office 2007 leaving us frustrated and powerless. Thankfully, they restored some of that in Office 2010. In this version of office, you can easily add new ribbons or customize existing ribbons (by adding new groups of tools).

One File Menu to Rule them all

Excel 2010 - File Menu and Backstage View
One of the biggest WTFs in Excel 2007 is Office Button. It wasn’t immediately clear for most of us, how we should save or work with existing files as everything was hidden behind the office button. Office 2010 rectified that problem beautifully by restoring “File” menu. But the engineers at MS didn’t stop there. They also added a host of other powerful features to the file menu and branded it as “backstage view”. Kudos! [Learn more about File Menu and Backstage view on this Friday]

Many more new features:

Not just these, there are many more subtle UI enhancements, features and improvements in Excel 2010 (and all other Office products). For eg. macro recorder now works with charts too, you can double click on chart elements to format them, you can collapse ribbon with a click, there is a new UI for solver, lots of statistical formulas have improved accuracy, there is exciting PowerPivot Add-in (my review of powerpivot) to let you do poweful BI and Analysis work right from Excel and many more. [read about all changes in Excel 2010 at TechNet]

You could win a Copy of Office 2010 – Home & Student Edition

Through out this week, I will be posting about Excel 2010′s new features and how you can use them to be even more awesome. I have 2 3 free licenses of Office 2007 Home  & Student Edition (free upgrade to Office 2010) to giveaway.To qualify, all you need to do is drop a comment on any of the 5 posts this week.

The contest is sponsored by Microsoft and winners will be chosen randomly.

Addendum: I got 3 licenses to giveaway. 2 of them for Indians and one for a lucky international reader.

So, what are you waiting for? Go ahead and tell me what your favorite feature in Excel 2010? Leave a comment to win an Office license.

Things to do:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

100 Responses to “What is new in Microsoft Excel 2010? [Office 2010 Week]”

  1. Hi,

    For me, the best new feature has got to be sparklines. I love the idea of putting a bar graph into a cell using =rept, and now with sparklines, this idea can be expanded upon and greatly imroved. Roll on for the June launch!

    Phil

  2. MrCichy says:

    When I see those Excel 2010 features I can only imagine it is so close to being perfect calc sheet… Creating my own tab is the only feature I was missing in almost perfect 2007, and now we have it. And those gadgets like sparklines or slicers only make this Excel better.

  3. Subhash says:

    Hi Chandoo,
    Can you also include the online collaboration aspects of Excel 2010 in your preview?

    Regards,
    Subhash

  4. Rakesh says:

    Hi,
    I think Sparklines are the best feature that has been added to Excel 2010.

    Thanks!

  5. Saurabh says:

    Purna,
    To me the best feature is definitely the screen shot. You say it is good for people in teaching industry and I feel it is equally useful in IT industry where again you have to educate your customer.

  6. Kenjin says:

    Hi Chandoo,

    I’ve discovered your blog recently when I start to be interested in data visualization and dashboard design (I’m a young graduate). I’ve learnt a lot about Excel thanks to you.

    All these new features are interesting, the less being the screenshot maybe. I am especially interested in paste previews, customization of pivot tables and improved conditional formatting. The intentions behind sparklines are good, but the features seem poor compared to VBA programs.

    What is interesting for data analysis, I think, is Power Pivot. It makes exploratory analysis much more easier, especially with the integration of data from different sources. For sure it won’t be as good as Tableau Software, but for business people who already have a fair knowledge of Excel, they don’t have much to learn.

    Regards!

  7. Victor says:

    Thanks for the always useful post Chandoo!
    I hope I win

  8. Martin says:

    Thanks to additional features, MS Office will still remains the king of office software suites!

  9. Squiggler says:

    Without doubt the biggest aid to my productivity is being able to create my own tabs! While many of us use a subset of each tab on a regular basis, its sometimes infuriating to have to switch several times to find each feature, now I just create a tab with the Items I use the most and cut down on the time! The other nice Item is the availability of Paste Special options from the right click menu, so now I can preview the options by hovering before I paste and Undo as I have countless times!

  10. Raj says:

    Looks like Excel 2010 is good for basic users also. Hope it will help me to build some good dashboard. However, how is its reliability compared to a standard statistical package?

  11. Alex Kerin says:

    Great as ever Chandoo, more commentary on the new sparkline features in Excel vs. options you already have with free software – pros vs cons:

    http://peltiertech.com/WordPress/sparklines-for-excel-vs-excel-2010-sparklines-guest-post/

    A guest blog post by me on JP’s site.

  12. Pedro Roenick says:

    Nice quick review.
    Greetings from Brazil.

  13. Nisar says:

    The favourite features in Excel 2010 is as follows
    1) there are serveral new smart art designs
    2) the Slicer is indeed cool
    3) The customization of ribbon

  14. Reyov says:

    Hi Chandoo,
    Without a doubt, I think the best new feature will be the sparklines. It’s going to make my life easier doing some dashboards.
    Thanks!

  15. TheQ47 says:

    I like the (re-)introduction of Customising and Defining your own Ribbons, and the File menu returning is also great.

    Love the blog, Chandoo, Keep it up, or as we say in Ireland, “Bail o Dhia ar an obair” (directly translates to: “May God bless the work”), A general greeting given when you see someone working hard at something, to praise him and his efforts.

  16. dan l says:

    Chandoo:

    I’m curious. How does the built in sparklines feature compare to SFE? I expect that it will be less robust but far easier to email out to folks. Why I just had a “forgot to deactive the formulas” incident today.

    A little embarrassing.

  17. Alex Kerin says:

    Dan, look at the link in my comment a few posts up

  18. Tom says:

    Chandoo,

    You’re getting me excited for 2010. I don’t know when I’ll get a copy at work because I have a relatively new computer and it came with 2007 installed. I can’t seem them paying for the upgrade just yet (which is why I’d love to win a copy) :) I really like the idea of the customized ribbons the most, and I also really like the paste preview.

    One problem that I have, in using the latest version of Excel, is the distribution of my work to folks with older versions. Hopefully, it won’t be long before the majority of our staff have at least 2007.

    Thanks for all you do!

    Tom (U.S.A.)

  19. Steve says:

    Bring on the sparklines!

  20. Michael says:

    My favorite is Slicers followed by Sparklines.

  21. Lisa says:

    I love the data bars in conditional formatting and the slicers.

  22. Robert K says:

    While PowerPivot & Slicers enhance analysis capabilities, and Sparklines are great (even though I learned in-cell charting from you on CHANDOO.ORG), the most useful thing for me has to be the new % of Parent… options in PivotTable summaries. Previously, there was not a simple way to present these calculations without significantly altering the layout of my PivotTables.

    I’ve been a reader since Jan 2009…thanks for the nice Excel 2010 preview and all the great info!

  23. Karen says:

    If I am lucky enough to win a copy of 2010, I will be happy to post about my favorite features. :)

  24. CMT says:

    Glad to see ribbon customization beyond just the QAT. Now we won’t be so hamstrung by Excel engineers’ vision of which buttons we’ll want when, and can possibly go back to being productive.

  25. Geoff says:

    It’s sad, but Paste Special is by far the most commonly used attribute on Excel for me. Allowing a preview will be exactly what I need. Also sad is that we have just upgraded to 2007 and I on that schedule, we should be on 2010 by 2013 :)

  26. jeff weir says:

    I’m going to double my chances of winning.

  27. jeff weir says:

    There, just doubled my chances of winning

  28. Ivan says:

    It looks like Excel 2010 is making great leaps forward. Too bad we are just getting around to testing Excel 2007 at work.

  29. chrisham says:

    The PowerPivot add-in and the Sparklines ought to be by far the best features in Excel 2010.

  30. jeff weir says:

    Ivan…you don’t test Excel 2007. Rather, Excel 2007 tests you.

  31. Oscar Noriega says:

    Hello,

    The restoration of the “File” menu is nice feature and the Sparkline feature is a nice addtion also.

    Thanks

  32. John Kyle says:

    I definitely agree with the other folks here. Sparklines are definitely a great new feature in Excel 2010. They will add a powerful visual element for Excel dashboards that is not present even in many of the more powerful Web-based business intelligence tools. They will give us the ability to see a lot of information (for example, subtle trends and patterns in the data) that was previously too hard to build by hand.

  33. laguerriere says:

    Hi Chandoo,
    well, I’m rather a “silent” fan of your page, but this time I need to say: awesome post!!! Now there is again a thing I ‘ll dream of to have!! Although I was kind of sceptic about Office 2007, i am now used to it and sometimes it helped me much more than excel 2003. By the way, do you know any good blog for Powerpoint 2007 Tips? It’s so annoying that some features disappeared , it was much easier to double-click on an object.
    Thank you so much!
    All the best to you and your family,
    La Guerrière

  34. Jay Venator says:

    Best new feature: All! in 2007 version I was missing every feature you covered. I need to upgrade, lets see if I win first.

  35. Tim says:

    I’ve been using the 2010 beta for several months. I’m having trouble understanding how slicers are any different than simply selecting the checkboxes in each pivot table’s field – don’t they both get you to the same place? Is there some value to slicers that I am missing?

  36. jay says:

    Favorite feature is Sparklines. It helps me to analyse data faster and better. It also provides me a quick view of information without resorting to opening another chart on top of the sheet.
    Thanks pls count me in the draw.

  37. Patrick says:

    The new PivotTable features are much-needed. I was using PivotTables today and could have really used that feature.

  38. Rob says:

    There seems to be a general reluctance for people to upgrade to 2007/10 because of the ribbon. Where I work, the IT department doesn’t like to install it because users complain. Once I’d tried it and re-learned a few things, I prefered it. Hopefully these new features will win over even more converts.
    Rob

  39. Mark S says:

    Great tips Chandoo! I am going to download the Beta on my home system to start to understand the product. I like what you have described so far. It kinda makes you think if your upgrading to bypass 2007 and go straight to the top product.

    Thanks for you timely updates.

    Mark S.

  40. Mark S says:

    Great article on 2010! going to download the beta and get into it.

    Thanks for the timely update.

    Mark S.

  41. Frederick says:

    My favourite new feature should be slicers. We must remember that the best Excel output is for people who don’t know know Excel to be able to look at what it is and have it explain something.

    This feature will help alot in data visualisation.

  42. dan l says:

    Jesus Alex. I’m an idiot. I didn’t even notice your comment with the linky until you posted it now. Thank you for the detailed commentary.

    It looks like SFE is still the way to go for a ‘hard’ project. The day to day stuff can be done in excel 2010.

  43. Roji says:

    My current work deals with a lot of data and it’s analysis. I use VBA and charts/graphs on a regular basis. To me, the Sparklines, Slicers, improved Filters, improved Conditional Formatting & Pivots functionality is a very welcome move.
    What I would like to see in Excel is the fuel-gauge/speedometer type of charts. I wonder why Microsoft hasn’t considered providing this as an option yet. I have seen the various hacks as to how this can be achieved, but it would have been a lot better if it was provided as a built-in option.

  44. Marrosi says:

    The customisation of the ribbon makes life much easier (….makes Excel-life muche more Excellent …) and certainly improves productivity.

  45. joe says:

    i like spark line in ofice 2010

  46. Cyril Z. says:

    Hello Chandoo,

    As usual your article is very insteresting and detailed.
    I’m very found of the following 3 points :
    - Customisation of the ribbon : I’ve struggled in XL2007 to understand why I eventually lose the ribbon when saving… HOpe this will ease my life (what? too much expectations ? :)
    - File Menu reappears ! Was really not found of the button instead
    - Table headers in columns, but I wonder how this will work with multiples tables… :-/

    Have fun in your new carrier :)

    Cyril

  47. Abdul Kader says:

    Hi Chandoo,
    I was transfering from my house from East Jerusalem to Ramallah,Palestine for the last week and the internet was disconnected, you are the first that I am in contact after resuming internet.

    As usual you are superior from all aspects simple English, good presentation, in my opinion they have done a lot of improvements don’t forget the new functions SUMIFS …ETC

  48. Scaffdog845 says:

    Hi Chandoo,

    Many of the users I help to support are afraid that our company is going to want to change to 2010. They had a heck of a time and still struggle with the changes from 2003-2007. Personally I think 2010 will simplify some of the off the wall things i was working to get 2003+2007 to do.

    Keep the posts coming.

  49. John says:

    A lot of old proficient users didn’t move to 2007 because of the inability to customize the toolbars (now Ribbons).

    As Chandoo says above, you can customize the Ribbon. You can create your own tabs or groups inside those tabs.

    So for those old-menu-fans, the menu is definitely gone, the floating toolbars are gone too. Now the customization is back (Microsoft listens users).

    The Ribbon remains.

    I am sure reticent people (those who says: my time is too valuable to spend multiple-clicking when one click used to do, I want to replace the new Ribbon with the Classic menu interface) will move to 2010 once they confirm the program brings this so-expected capability.

  50. GCD says:

    Sparklines, definitely. Gonna be a giant pain waiting for the world to catch up with the new formats, though.

  51. Amit Arora says:

    For me all the new data analysis and data visualization tools do a world of good. Sparklines and slicers would stop us from looking out for some free add-on to do that work.

  52. Macao says:

    Hi, I think that the sparkline feature is the best new thing in Excel 2010.

  53. Alexandre says:

    Hey,

    Great information!!. Would love to win this to use here in Brazil.

    Let’s wait and see.

  54. Abhishek Jain says:

    the option i liked the most is quick customization of Pivot Tables….would help in reducing a lot of time wastage in data analysis for me…m excited to try out MS office 2010 now!!

  55. Savannah says:

    Chandoo, your website is marvelous and your tool-set has truly made me an Excel rockstar in my office so many, many thanks.

    I’ve been so fortunate to be working with an MS representative this week testing XL2010 and have already informed my boss that I will be a very crabby camper if we do not purchase and implement.

    Thanks again!

  56. darh78 says:

    Great and very interesting resume about what’s coming for us!
    Best Regards

  57. Savannah says:

    Hi Chandoo, love the site – I get teased for all the great things I say about you but I don’t care. You are a hero!

    Also looking and evaluating XL2010 for company purchase – digging so many aspects.

    Thanks so much

  58. Steve says:

    I didn’t realize the amount of new tweaks in 2010. Very nice. Fun to play with Sparklines in particular. The screen cap tool is quite useful, too.

  59. Gaurav says:

    I love how in Excel 2010 they have replaced the old conditional sum wizard with the function wizard that includes SUMIF and SUMIFS functions.

  60. Amit Goyal says:

    The top two features for me are:
    1. Sparklines
    2. Gradient Fill in Conditional formatting.

    When combined with Slicers, they can prove to be a formidable tool for dashboards and such.

  61. Sachin says:

    Hi will love to have free copy of MS Office 2010 in order to move faster with your posts……thanks

  62. mark says:

    This is my registration for your giveaway.

  63. There is always room for improvement. As usual I was expecting new features. Slicers and custom user ribbons will help me a lot. Great work Microsoft excel team!

  64. Steve Berlin says:

    I like the slicers and the sparklines. I have the sparkline addon already but it will be nice so others will be able to use my worksheets if they have office 2010

  65. Karthik G says:

    Chandoo, I liked the powerpivot add in that goes with 2010, its just great.

  66. Vinu says:

    I like
    customizing toolbar
    and Conditional formatting features in 2010 version.
    Waiting to get hang on Excel 2010!!

  67. nitesh says:

    sparklines and conditional formatting features are the best ones
    i never tried this sparklines feature in the beta version of 2010
    gr8 post

  68. Mark in Canada says:

    I downloaded the Office 2010 Beta trial last year when you first mentioned it and have been surprised at how much Microsoft has optimized the applications – the user interface design is very well done and the responsiveness is snappy. I am glad to say that I haven’t had any of the crashes in Excel 2010 :) that I have had with PowerPoint 2010… :(

    And I am (finally) starting to get used to the ribbon… some things still take more clicks, which is disappointing.

    Here’s hoping that I’m the international winner of the Office 2010 license. :)

  69. Miguel C says:

    I like sparklines! I’m still on Office 2003. Please pick me to win Office 2010!

  70. Abhishek says:

    Good new features and apparently the best part is they (Microsoft guys) are reading the mindset of the Excel users very quickly by providing such niche features in every release to reduce the burden of coding and applying complex logic.

    I’m not using Office 2010 but I wish sometime in future when it will be implemented by my company, I’ll be able to enjoy all such great features.

    Thanks for this post it’s really nice to read about all such cool features.

  71. Mahesh says:

    Dear Chandoo,
    I have downloaded office 2010 and felt it was smooth and good.
    Spark lines ands Pivots and good graphing tools (even in 2007) are definite value additions.
    I am yet to try slicers.
    Thanks for the post.I will try all the new features.
    Regards
    Mahesh

  72. John Pomfret says:

    Hi Chandoo,
    It’s difficult to pick a favourite but the conditional formatting is certainly up there in the list.
    I recently resigned from my job as an excel developer after 9 1/2 years at the company.
    The plan was to take some time out and sort out my priorities.
    May 31st was my final day of employment (a Bank holiday here in the UK). At 2pm on June 2nd I got a phone call asking if I wanted to do some work for them as tehy were unhappy with the contractor. I did go for a meeting and am waiting to hear. I think I deserve the prize for being a masochist!
    John

  73. Oliver Montero says:

    The favorite features in Excel 2010:
    1) The Slicer is indeed cool
    2) The customization of ribbon (At last MS)
    3) Powerpivot

    For me Excel 2010 is to Excel 2007 what Windows 7 was to Windows Vista. What it should have been for the first time.

  74. Santosh Maturi says:

    how to lock the image in excel sheet one cell

    Regards
    Santosh Maturi

  75. Jeff says:

    If I upgrade from Excel 2007 to 2010 will I lose my macros?

    Thanks.

  76. Geeky Mummy says:

    Sparklines – a tool given to us to wipe out the all too common glazed over eyeball syndrome – often contracted when looking at tables upon tables of data.

  77. Chris says:

    Chandoo,

    Fantastic site your sharing of knowledge is great. Still stuck using 2003… salivating again at the thought of taking an even larger leap when my company does choose to upgrade. I just hope they finally take the leap!!

    Cheers,
    Chris

  78. Hui... says:

    @Jeff, No
    As an asisde, the macro facilities in 2010 are much better than in 2007.

  79. Tommi says:

    I am also waiting for the 2010 version release. Hope it will be also available via MSDN AA.
    Btw, Chandoo, great blog :-)

  80. Lynn says:

    I have upgraded to Excel 2010 due to PowerPivot, Slicer, and Sparklines. I am the only one to have this version at work!

  81. Subie says:

    Can we finally put Office 2007 behind us???

  82. Abe Asanji says:

    Hi Chandoo,

    I had not yet installed Office 2010 Beta. Is it still possible for me to downgrade it back to Office 2007 if Office 2010 expires?

    • Chandoo says:

      @Abe.. As I mentioned about, do not upgrade your office 2007 to 2010. Instead, choose the option (install only) that will keep both versions working. This way, when beta expires you can uninstall 2010 and nothing breaks.

  83. Akash says:

    Chandoo, I have 2003, 2007 and 2010 Beta installed and working fine on the same machine. Would MS be upgrading the Beta to RTM version on its own?

  84. Kalee says:

    I just recently purchased Microsoft Home and Student 2010. I am having a horrible time with Excel. I can’t seem to make more than a few data entries into cells without Excel freezing up and crashing. I’ve never seen Excel behave so erratic before. Any suggestions?? Thanks!

  85. Akash says:

    Chandoo, Who are 3 lucky ones to win the licences of office 2010? They must be congratulated on this forum.

  86. Godwin says:

    Hello Chandoo,
    Nice article on the new features…

  87. Robapottamus says:

    Gidday Chandoo

    cheers for the write up. lookin forward to 2010, especially the sparklers and slicers. These will be some nifty additions to my reports – maybe enough to impress the boss for a payrise???

  88. Harshad says:

    My Tab…awesome feature in Excel 2010….Customization at its best…
    Previous version lacked that feature…for Data analysis people this will be most useful feature…
    Nice Job Chandoo… I am you fan dude….

  89. jayank2000 says:

    MS Office 2010 is a great upgrade from Office 2007. I personally still have Office 2003. Cannot wait to grab a copy of Office 2010.

  90. renaud says:

    Hi

    Have you had the chance to try out the what if analysis on pivot feature?

    Thanks

    Renaud

  91. wow, still use microsoft office 2007 in my home. but after seeing this description, i should put my 2007 to 2010..hihi

  92. Sarah Black says:

    Hi! I am a college student, and one of my classes is a computer app class. I have office 2007, but my instructor uses 2010 for her teachings. I am not very familiar with excel, but I do have some assignments coming up using excel. I suppose it is going to be a little more difficult for me to get the hang of it if she is teaching us from office 2010 and I have 2007. I am sure I will survice long enough till I can upgrade to office 2010. However, I am liking what I am seeing in the office 2010.

  93. april says:

    what are the 25 functions of excel

Leave a Reply