Partner The Knowledge Base
Share this content
AIA

Tutorial: Sales analysis in Excel 2010/13, part 1

by
17th May 2013
Partner The Knowledge Base
Share this content

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:

Set up Excel 2013 import from Access database

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:

Import data from Access to an Excel 2013 Table

The Properties button allows us to choose the automatic refresh options for our data:

Select background refresh in Connection Properties

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.

Access data stored as an Excel 2013 Table

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:

Create a PivotTable from Excel 2013 Table data

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:

Excel 2013 Create PivotTable dialogue

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:

Excel 2013 PivotTable Fields process

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:

 Assign a number format to PivotTable data

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:

Sort PivotTable data by sum of extended price

This makes it much easier to see how well each country is doing, here’s the comparison:

Alternative sort of PivotTable sales data

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:

Graphical conditional formatting on sorted data

Further reading and tutorials

Replies (5)

Please login or register to join the discussion.

avatar
By Excelcrafter
20th May 2013 02:35

PowerPivot vs Data Model

Simon,

This series of tutorials has been an excellent introduction to the more powerful and robust data analysis options in Excel 2010/2013. I liked you rather charitable comment regarding the lack of PowerPivot in Excel 2013 (except in Office 2013 Professional Plus): "Perhaps in order to compensate for this, Excel 2013 does include as standard the data model that is a key element of PowerPivot."

Thanks (1)
Simon Hurst
By Simon Hurst
20th May 2013 17:52

Hi Excelcrafter - many thanks for your comments - much appreciated. I'm glad you are finding the series of interest. I think putting the Inquire add-in into the Professional Plus edition only (which is I think the case) is an even worse decision. Every single spreadsheet user could probably benefit from the auditing and documentation capabilities of Inquire, so it's a real shame Microsoft see it as relevant only to Professional Plus.

Thanks (0)
avatar
By Ron007
10th Aug 2020 20:59

Good series of articles, but "gosh darn it" where is the link to "In the next episode we’ll look at a range of additional PivotTable ..."?
.
I really wish you authors and web site publishers would think this through better and provide forward and backward links to "series" of articles.
.

Thanks (0)
avatar
By Ron007
10th Aug 2020 20:59

Good series of articles, but "gosh darn it" where is the link to "In the next episode we’ll look at a range of additional PivotTable ..."?
.
I really wish you authors and web site publishers would think this through better and provide forward and backward links to "series" of articles.
.

Thanks (0)
Replying to Ron007:
Simon Hurst
By Simon Hurst
12th Aug 2020 08:20

Hi Ron007, glad you liked the series and thanks for your comments. This post is over 7 years old and I guess that the chain of articles has got a little muddled in the mists of time! If you click on the 'Read more by Simon Hurst' link in the author profile box, and delve back to July 2013, I think you will find part 2 which should be here: https://www.accountingweb.co.uk/practice/skills/tutorial-sales-analysis-...

Thanks (0)