Newsletter January 2010
Spreadsheet Tips - Sales Analysis using Pivot Tables. Pivot Tables offer a fast and powerful method for compiling simple data into meaningful graphs. You can follow the example below using your own data. Alternatively, the sample data included in this example is available as a free download .

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. Fig 1 shows some sample data for the first three months of 2009.

Fig 1



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

Fig 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 Fig 3.

Fig 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".

Fig 4



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

Fig 5



This brings up the PivotTable in a new worksheet, as in Fig 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.

Fig 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 Fig 7.

Fig 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 Fig 8.

Fig 8



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

Fig 9



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

Fig 10



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

Fig 11



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

Fig 12



Hopefully this short example will have demonstrated the usefulness of PivotTables. You can try lots of different combinations of parameters until you get what you want. To remove a parameter, click-and-drag it back to the Field List. The sample data and PivotTable are available as a free download. Remember to always virus scan any file you download from the internet.

..back to top