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.
Slicers have their own tab on the ribbon, from which they can be edited:
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.
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
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:
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:
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:
- i. a database: SQL Server, Access, Analysis Services & Power Pivot, Other Sources
- ii. a file: Text or Excel
- 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:
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.