What's new in Excel 2013
Part four of a seven-part series of blogs

Excel 2013 contains radical changes to charts, pivot tables and the user interface. This review explains all the new features of Excel 2013

  1. Excel 2013 - the Wise Owl Review!
  2. First Impressions - the Excel 2013 Look-and-Feel
  3. Excel 2013 changes: working with files
  4. Excel 2013 changes: pivot tables and data analysis (this blog)
  5. Excel 2013 - Changes to Charts
  6. What's new in Excel 2013 - general changes
  7. Excel 2013 - summary and recommendations

Posted by Andy Brown on 30 January 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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

Quick Analysis menu

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

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

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

Recommended charts

The recommended charts for the dinosaur height data.

 

Choose TOTALS to create automatic totals or formulae:

Automatic totals

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

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

Automatic pivot tables

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

Recommended pivot tables icon

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:

Choosing pivot tables

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:

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

Multiple tables pivot table

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:

Insert timeline icon

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:

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

Inserting table slicer

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:

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

Feature Details
Power View If you're using Office Professional Plus, you can use the Power View add-in within Excel.  You can see more details on what's new in Power View for Excel 2013 here.
PowerPivot Many of the features previously separately available only through the wonderful PowerPivot application are now integrated into Excel.  To find out what PowerPivot is, attend our two-day PowerPivot course!

If you use Analysis Services, there are also a number of new features, such as the ability to create OLAP measure calculations within Excel and drill-up and drill-down functionality.

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

This blog has 0 threads Add post