BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Excel 2013 contains radical changes to charts, pivot tables and the user interface. This review explains all the new features of Excel 2013
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.
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.
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:
|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.