How to analyse sales using pivot tables

Sales Analysis using Pivot Tables

At the start of a new year, it is useful to examine last year's sales figures. This gives us a baseline against which to measure this year's performance.

Pivot Tables are Excel's in-built tool for quickly compiling standard data into meaningful data analysis. Spreadsheet Data 1 shows some sample data for the first three months of 2009.

Spreadsheet Data 1

First, highlight the table of data that we want to analyse, as in Spreadsheet Data 2.

Spreadsheet Data 2

From the DATA menu we now select "PivotTable and PivotChart Report.." which brings up the "PivotTable and PivotChart Wizard - Step 1 of 3", as in Spreadsheet Data 3.

Spreadsheet Data 3

We click "Next" and this brings up "Step 2 of 3". As we have already highlighted the data we want to use, we can click "Next".

Spreadsheet Data 4

This brings up "Step 3 of 3", as in Spreadsheet Data 5. We are asked where we want the PivotTable to be placed. Here, I selected "New Worksheet", then click on Finish.

Spreadsheet Data 5

This brings up the PivotTable in a new worksheet, as in Spreadsheet Data 6. The areas with a blue border are the active areas of the PivotTable. We see the PivotTable Field List which lists the columns of data we highlighted at the beginning. The third element is the PivotTable menu.

Spreadsheet Data 6

In this example I'm going to show you how to summarise the your sales by month, and by customer. To add data to the PivotTable we click and drag from the Field List into the relevant section. For this example, I first click and drag the Date element into the "Drop Row Fields Here" section, as in Spreadsheet Data 7.

Spreadsheet Data 7

This puts in the order date for each sales order in the original table of data, but we want to group these by month. To do this, right-click on "Date", then select "Group and Show Detail", then "Group ", as in Spreadsheet Data 8.

Spreadsheet Data 8

This brings up the Grouping dialog box, as in Spreadsheet Data 9. Select "Months" and click OK.

Spreadsheet Data 9

This groups all the order dates by month, as in Spreadsheet Data 10.

Spreadsheet Data 10

We now add in the other data elements. Click and drag "Customer" to the "Drop Column Fields Here" section, as in Spreadsheet Data 11.

Spreadsheet Data 11

Finally, we click-and-drag the "Total" field to the "Drop Data Items Here" section to get Spreadsheet Data 12.

Spreadsheet Data 12