Review of Microsoft Excel 2010

Posted on Monday, December 21st, 2009 by Print This Post Print This Post

Categories - Analytics, Featured, SEM

I recently got to play around with the beta version of Excel 2010.  There are some very cool additions and changes, so why not spread the joy and tell fellow Excel enthusiasts about them?

A) Pivot Tables – enhancements

1. Slicers – a new way to filter pivot tables

It used to be that if you wanted to filter the pivot table, you had to click on the little drop-down, and start checking/un-checking boxes, or doing a text filter with “Does not contain” and similar exclusion criteria.

In 2010 pivot tables have a new, much sexier way, to get filtered.  It’s called slicers.  When you insert a slicer (from PivotTable Options) it brings up a separate box containing all the entries in the field you want to slice (Search Engine/Distribution, in this example).  To include a Search Engine in the pivot table, click it in the slicer.  To exclude it, unclick it in the slicer.

Picture1

Picture2

Slicers have their own tab on the ribbon, from which they can be edited:

Picture3

2. Repeat down labels – we can now used VLOOKUP and HLOOKUP with pivot tables

If we wanted to do a VLookup on SearchEngine&Campaing in this 2007pivot, we wouldn’t be able to, because  the Search Engine label does not repeat on every row.

Picture4

In 2010, we can adjust the pivot table to repeat the Search Engine label on every row, so VLookups are now a piece of cake:

- highlight the pivot column on which you want the label repeated

- right-click > Field Settings

- Layout & Print > Repeat item labels

Picture5

B) Sparklines

Miniature charts that fit inside a cell.  Chart types supported: bar, line, win-loss.  Works with data that trends,  for example daily data over a month.  You can then show the general trendline in a cell, surrounded by the first days’ and last days’ data:

Picture6

C) Charts

The user can now double-click on a chart element to edit it.  No longer necessary to right-click, then try to guess under  which menu item you’d be able to find the feature you’re trying to edit.

D) Ribbon – customizable

The Ribbon is now customizable.  As an example, the user can now add a tab, with the commands they use most often:

Picture7

The user can also turn off/on the commands on the ribbon.  All this functionality resides under the File tab.

E) Developer tab

Excel add-ins and COM add-ins (those are the custom ones we build) can now be managed from the Developer                 tab.

The way to add the Developer tab has changed a little bit.  It is now done via File > Options > Customize Ribbon >                 check the “Developer” box.

F) PowerPivot Add-in

This is a Microsoft add-in, that’s basically a pivot table on steroids (http://www.powerpivot.com/).

The data source for a regular pivot table is an Excel file.  PowerPivots, on the other hand, can work with all sorts of data sources:

  1. i.      a database: SQL Server, Access, Analysis Services & Power Pivot, Other Sources
  2. ii.      a file: Text or Excel
  3. iii.      a data feed: Reporting Services or Other Feeds

This gives the user the flexibility to work with very large data sets, over the 1,048,576 rows that Excel can hold.

PowerPivot opens its own window, from which the user can select the data source, and preview the data imported:

Picture8

Picture9

Other advantages of PowerPivots:

  • a bigger variety of functions that can be used for calculated fields
  • users can set up filters, and save those filters by creating sets.
  • users can create their own BI systems

Excel 2010 is an exciting upgrade from 2007 in terms of cool new features, like the Slicers, the Sparklines and the PowerPivot add-in, and also a welcome improvement in ease of use with options like the repeat-down labels on pivot tables, the double-click to edit of the charts, and the customizable ribbon.

Tags | , , ,

7 Responses to “Review of Microsoft Excel 2010”

  1. Ted Ives says:

    The sparklines are a cool feature, can’t wait to use it!

    • mark williamson says:

      The copy, paste special values button, doesn’t display what you’ve pasted on the first cell when pasting a string of values across a row of cells. Also, the clear contents feature will remove any formatted cell (making them white) while your viewing the page. I think microsoft puts in errors into their new products to “correct” on the next version.

  2. Matt Kain says:

    I’m a big fan of Sparklines also, got turned onto them by Edward Tufte. Plenty of applications in an SEM/SEO reporting environment – more “impressions” of data, less actual data, fewer worksheets. The perfect client report to strive for is one which can print out on a single sheet of paper.

    Nice review, Silvia.

  3. Barbara says:

    Great detail! May need a tutorial.

  4. Sparklines are my Nirvana!

    We’ve built out our Keyword Position Reports using standard Excel charts, but it quickly gets unwieldy with 100′s of keywords. In my research I found a number of cool companies / solutions / addins but none of them had economically sound scalability for the company, or were feature complete.

    There *are* solutions out there, but 2010 looks like it has the bases covered…

    Excel product manager posts some additional tricks here: http://blogs.msdn.com/excel/archive/tags/Sparklines/default.aspx

    Looking forward to more compact reports!

    Only negative? Mac users, like myself, will have to wait a bit for Sparkline feature set :-(

  5. Ann says:

    What superb piece of text! No idea how you wrote this report..it’d take me days. Well worth it though, I’d suspect. Have you considered selling advertising space on your blog?

Trackbacks/Pingbacks

  1. Review of Microsoft Excel 2010 Get Pivot

Leave a Reply

Follow Us on Twitter

Authors