Tutorial: Sales analysis in Excel 2010/13, part 2by
This is the fourth part of our series looking at new tools for working with accounts data in Excel 2010 and 2013 and the second of two articles looking at the analysis of transaction data. As in the previous article, we will use the Invoices data from Microsoft’s sample database: Northwind. [Our thanks to Microsoft for permission to use it in this tutorial. If you are not able to locate a copy of the 434kb zipped you can to download a copy here then extract Northwind.mdb].
We will start almost where we ended last time, with the PivotTable displaying our sales invoice totals by country, sorted in descending order by amount.
As we have already seen, Excel will ‘guess’ which areas to place fields into when we select them. Value fields go to the VALUES area and other fields to the ROWS area, or you can drag fields directly to particular areas either from the field list or from one area to another. If we select another text field it will join Country in the ROWS area and create subtotals:
At the moment, our Salesperson totals are subtotalled within Country. We could change this by dragging Salesperson above Country in the ROWS area of the field list.
In order to demonstrate one of the new features introduced in Excel 2010, we will change the Report Layout to ‘Tabular’ using the option on the PivotTables Tools, Analyze ribbon. This will display our fields in separate columns:
As you can see, the default view just shows the Country heading at the top of each column. If we wanted to use this PivotTable as an intermediate stage in analysing our data, then we might want all rows relating to Germany to include the word Germany in column A. We could then use Excel functions such as SUMIF() and SUMIFS() to calculate totals or extract individual figures. The Report Layout option in Excel 2010 and 2013 includes an option to ‘Repeat All Item Labels’. We can also right-click a cell in the Country area and turn off the Subtotal for Country to give us a simple table listing sales by Country by Salesperson:
We’ll combine a demonstration of the COLUMNS area with an introduction to grouping by dragging the OrderDate field to the COLUMNS area. This will display a column for each individual OrderDate. We may want to group our dates into Year and/or Month columns for example. To do this, we just right-click on any date and choose Group. We can then choose one or more periods to group by.
If we choose Months and Years, our OrderDate field will be grouped by months and a new Years field will be created:
Note how the Repeat Item Labels option applies to the column fields as well.
The Month and Year groups are straightforward, but if you select Days then you will be able to choose a number of days. Choosing 7 would allow you to create weekly groups and you might also want to set the starting date to a Monday:
It might seem a strange thing to do, but you could drag the ExtendedPrice values field to the COLUMNS or ROWS area. This will total the ExtendedPrice field for each value in the ExtendedPrice field:
As you can see, one item was sold for 13.50 whereas 2 were sold for 14.00 giving a total of 28.00.
This might make more sense if we group our ExtendedPrice field in the ROWS area to show the total sales values for different ranges of amounts. Here we have grouped in 1,000s and set the starting value to 0:
As well as grouping based on the contents of fields, it is also possible to create your own groups where the information to group the fields is not available within the data. In this example we have added the Salesperson field to the ROWS area and selected several Salespeople and the right-clicked on them and chosen Group. We can rename the groups created and also the new Group field that we have created.
Grouping and the PowerPivot data model
In previous parts of this series we have mentioned that Excel 2010 and 2013 include an alternative PivotTable feature: the PowerPivot add-in. In the case of Excel 2013, the ‘data model’ that was previously only available via PowerPivot is part of standard Excel. If you use PowerPivot or the Excel 2013 data model, then you cannot group items in the way that we have just detailed. Instead, you would need to add columns to existing tables or even create new tables, in order to create the groups required.
DrillDown – Excel 2013 limitation
Whether we are using a ‘normal’ PivotTable or one based on the Excel data model, we can look at the detail underlying our summary totals. We just need to double-click the summary in which we are interested and Excel will insert a new sheet listing the individual records that make up our total. However, there is a difference in the result. The DrillDown sheet for a normal PivotTable will be include a ‘static’ snapshot of all of the individual records. For a data model or PowerPivot DrillDown sheet, only the first 1000 records will be included by default. There is a strange difference between PowerPivot in Excel 2010 and Excel 2013. In Excel 2010 the DrillDown table right-click menu include as Refresh option and you can also access the connection properties and change the 1,000 – this does not currently seem possible in Excel 2013 where the equivalent options are greyed out.
Filters and Slicers
The one area we haven’t investigated so far is the FILTERS area. Fields dragged to this area can be used as criteria for the PivotTable, allowing individual or multiple fields to be selected. One of the most important PivotTable enhancements in Excel 2010 was the introduction of Slicers to act as more interactive, more visual filters. More than this, Slicers can be attached to multiple PivotTables enabling them to be used to as part of an interactive dashboard based on tables and charts:
Excel 2013 extended the capabilities of Slicers introducing a new Timeline Slicer that can be based on any date field in the underlying data:
In the next part of the series we will look at some of the features available to help us turn our data from a list of invoices into the interactive dashboards illustrated above, including the incorporation of KPIs.
Further reading and tutorials
- Working with accounts data in Excel 2010 and 2013
- Sales analysis in Excel 2010/2013, part 1
- Excel 2013: Where the productivity gains are
- Excel 2013: Up close and personal
- Excel 2010: Simon Hurst’s greatest tips
- Excel FAQs: Working with accounts data
- Improve your reporting skills with self-teach tutorials
- Improving sales reports at Northwinds Traders (first of a 2007 tutorial series by David Carter)
- AccountingWEB pivot table tutorial archive