Excel 2013 changes: pivot tables and data analysis

This (long) page lists the main improvements to pivot tables and data analysis in Excel 2013.  Let's start with the quick analysis feature.

Quick Analysis

Supposing you have a list of dinosaurs, and you want to analyse their heights (a common business requirement!):

Right-click on a range of data to analyse it.

You can right-click as above, to bring up the following options:

It's hard not to be excited by features like this! As you let your mouse button rest over each option the relevant icons or colours appear on the data.

If you choose CHARTS, you get to choose from a list of recommended chart types:

The recommended charts for the dinosaur height data.

Choose TOTALS to create automatic totals or formulae:

You can scroll across to the right to choose even more options.

Choose TABLES to have Excel suggest sensible pivot tables to create:

Some features of Excel 2013 are hard to distinguish from magic - this is one of them.  Each pivot table icon will show a different way of analysing the data.

To power-users this feature may not mean much, but for casual users it is seriously impressive!

Recommended pivot tables

Not sure which pivot table you should create?  Excel has some ideas for you (think of it as like the Genius play-list in Apple):

This new icon allows you to get help ...

You can scroll up or down to find the closest pivot table to the one you want:

Here we've gone for total height by body type.

Basing pivot tables on multiple tables

You can now base a pivot table on multiple tables (although if you're doing much of this, you may want to consider learning PowerPivot instead).  Here's one way to do this, to give you the idea.  First, connect to multiple tables:

Here I've ticked the Enable selection of multiple tables box - to do just that!

You can now create a pivot table based on these two tables:

You never used to be able to do this! Excel will automatically use any underlying relationships between the data.

This feature could change some people's working lives!

Pivot table timelines

If you have dates in your pivot table, you can filter by them (similar to the slicers shown below, but using dates instead of text values).  First click in your pivot table and choose to insert a timeline:

The option to add a timeline to a pivot table.

Want to now show data just for September and October 2012?  Click and drag with the mouse on the timeline:

Click and drag on the timeline to extend the time period for the pivot table's data.

Very impressive!

Table slicers

You could create slicers to filter pivot table data easily in Excel 2010 - now you can do the same thing for tables of data:

Here we're using a slicer to show the Jurassic era dinosaurs.

New data sources

You can import data from a larger set of data sources:

A list of the other data sources now available for use in Excel 2013.

Power View and PowerPivot enhancements

Excel includes the following new or enhanced features.  This blog is in danger of going on too long, so I'll treat these as separate applications and just provide links to the new features:

Having looked at changes to data in Excel 2013, let us turn our attention to charts.