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 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: 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: 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
- 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