In the first part of this Working with accounting data tutorial series we looked at ways to apply some of the new features in Excel 2010 and 2013 to management reporting situations
We showed how the inclusion of the PowerPivot ‘Data model’ within Excel 2013 itself could replace the use of Excel Lookup formulae to give easier, more automatic and potentially more reliable results. The second tutorial looked at how to work with trial balance data data to create flexible monthly reports. Here in part 3, we are going to move away from a trail balance and start looking at sales invoice transactions. 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.]
Getting at the data
Our data is held in an Access database, so Excel can easily create a dynamic link to the data allowing us to refresh the link to the data manually or automatically, thereby updating all our reports based on the linked data. If you are going to use Excel for data analysis, particularly if you want to work with large volumes of data and complex data structures, then you might find the PowerPivot add-in very useful.
As we have mentioned in the previous articles, PowerPivot is a free add-in that is available generally in Excel 2010 but only available with the Professional Plus version of Excel 2013. Perhaps in order to compensate for this, Excel 2013 does include as standard the data model that is a key element of PowerPivot.
Most of this article will not assume that you have access to PowerPivot but in part 2, we will cover a few of the additional features that PowerPivot does provide.
Given that we are not restricting ourselves to using PowerPivot, we will just use the Excel Data ribbon tab to link to our data. The Get External Data group of commands includes an option to connect to an Access database directly and include one or more queries or tables:
The first accounting data tutorial covered the use of the From Other Sources option to retrieve specific fields from a range of data sources, including Access, using Microsoft Query.
If we choose a single table or query, then we will have a choice as to whether to add it to the Excel Data Model. If we choose multiple items, then they will automatically be added to the data model to allow the relationships between them to be created. We won’t add our single Invoice table to the data model at this stage and, so we can see what our data looks like, we will start off by creating a table, rather than going straight to a PivotTable:
The Properties button allows us to choose the automatic refresh options for our data:
We can set or change these later if we forget or change our minds.
Here is a section of our table. You can see it has over 2,000 rows and lots of columns. When we analyse the data using a PivotTable we can choose just those fields that we are interested in.
It’s worth pointing out that, in all versions of Excel from 2007 onwards, external data is displayed in an Excel Table and we can carry out some analysis using the filtering and sorting capabilities built into the Table headings. However, we are going to concentrate on the use of PivotTables for analysing the crucial sales information hidden in our invoice data.
It’s easy to create a PivotTable from a Table. We can just select any cell in the table and go to the Insert ribbon tab, Tables group and select PivotTable. In Excel 2013 there is an alternative. We can select our whole table, maybe using the Ctrl+Shift+8 keyboard shortcut. Excel 2013 will display the Quick Analysis option. As well as some other options for analysing our data within the Table, such as the use of Conditional Formatting, Quick Analysis includes some PivotTable options:
For now, we’ll just use the ‘standard’ method of creating our PivotTable that will work in Excel 2007 and 2010 as well as 2013.
As you can see, Excel automatically bases the PivotTable on the Table, rather than on a fixed range of cells, making it easier to update the PivotTable for changes in the number of rows and columns in the Table. It’s usually easier to include the PivotTable on its own sheet as we have done here:
From Excel 2007 onwards, we don’t have to undertake the often fiddly process of dragging fields to particular parts of the PivotTable. Instead, we can just select the fields we are interested in and Excel will make a reasonable guess as to whereabouts in our PivotTable to put them:
Here we have just ticked the boxes next to our Country and ExtendedPrice field. Because Country contains text, Excel knows not to put it in the values area so treats it as a Row label. ExtendedPrice is a value so it goes automatically to the Values area. It’s easy to override Excel’s decisions if you want to, you can drag the fields between the four different PivotTable areas shown underneath the list of fields or drag the fields directly from the field list to the area where you want it to be displayed. In this case we are happy with the position of Country and ExtendedPrice so, after about half-a-dozen clicks we have an analysis of our sales invoices by Country.
Not bad so far, but a couple of small changes can make a dramatic improvement to how useful our information is. First of all, we’ll set a much clearer number format. We just right-click any of our value cells and choose Number Format. This will set the number format for all of the values in this area of our PivotTable. We can use any of the built-in number formats or an existing, or new, custom format:
The next relatively simple change to improve our PivotTable is to sort it into a more useful order. If we wanted to compare how well sales were going in different countries then having the report sorted in alphabetical order doesn’t help a great deal. We can right-click on one of our countries and choose Sort, More Sort Options then set the sort order of our Countries to Descending by Sum of ExtendedPrice:
This makes it much easier to see how well each country is doing, here’s the comparison:
In the next episode we’ll look at a range of additional PivotTable features including grouping, drill down, Slicers and charts but as the finale to this article we’ll look at another feature introduced in Excel 2007. The ability to apply conditional formatting to a PivotTable.
We start off by selecting any of our value cells and then using Home ribbon tab, Styles, Conditional Formatting to apply a Solid Fill, Red Data Bar. To start with we just get a single red bar the full width of the selected cell. However, we also get a Formatting Options button that we can click and choose to apply the Conditional Formatting to the area of our PivotTable, excluding the grand total. We can then change the detail of the conditional format by going back to Conditional Formatting, Manage Rules, Edit Rule and then changing the colour of our data bar and setting the format to Show Bar Only – removing the irritating numbers from our PivotTable:
Further reading and tutorials
- Tutorial 1: Working with accounts data in Excel 2010 and 2013
- Tutorial 2: Create a flexible P&L in Excel 2010/2013
- 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