As the end of the year approaches, it may be the time to start looking at how your business has been performing over the previous twelve months. If you don’t have an integrated system, you will probably be doing this with spreadsheets. A powerful tool for analysing large volumes of data is a Pivot Table. A Pivot Table is an automated output tool which allows users to extract compiled information from spreadsheets without the need for programming.
Purchase the Sales Analysis with Pivot Tables Spreadsheet Example
Click the button below to purchase the Sales Analysis with Pivot tables Spreadsheet Example.
After completing the purchase on PayPal you receive the download link by email.
With a Pivot Table you can quite quickly compile large amounts of data into some meaningful statistics. This is very useful in business if you want to quickly prepare some reporting for a client or supplier. If you ran a warehouse and you wanted to show a supplier how often his deliveries were late, you could quickly compile delivery times against the supplier using a Pivot Table.
Follow along with the Sales Analysis with Pivot Tables Spreadsheet Example
Let’s take the example of a shoe shop, called Walking Data. In the Working Shoes department, there are two sales assistants, Barbara and Ken. The shoes are sold in sizes 4, 5 and 6, and in colours red, blue, yellow or green. We have the year to date sales figures in a spreadsheet, laid out as a list or table, with column names and rows of information (fig. 1, below).
Spreadsheet Data 1.
We may want to investigate the relative performances of our two sales assistants, Ken and Barbara. Also, it is definitely of interest to view which size of shoe sells most, and which colour of shoe, and to have the total figure in each case. In this way, we can see what the customers really want.
In order to view that kind of compiled data, we can use Pivot Tables. In Excel, Pivot Tables are generated by using the Pivot Table wizard, which is found in the Data menu. (see fig.2)
Spreadsheet Data 2.
So, for the first example, let’s find out how many pairs of each colour of shoe was sold. The method to construct the Pivot Table is the same each time. First step, is to highlight the data, see Spreadsheet Data 3. It is vital to give your columns a title each. These are used afterwards to group and filter your results.
Spreadsheet Data 3.
Next we start the Pivot Table Wizard, which is found in the Data menu. See Spreadsheet Data 4.
Spreadsheet Data 4.
This brings up the 3 step Wizard dialog box. In the first step we select the data source for the Pivot Table. You can connect Pivot Tables to databases, multiple worksheets or multiple workbooks. In this example, we will take the simplest option, which is the default. So, in “Where is the data that you want to analyze?”, select “Microsoft Excel list or database.” In “What kind of report do you want to create?”, select “Pivot Table”. See Spreadsheet Data 5. Click Next.
Spreadsheet Data 5.
In step 2, we define the cell range that we want Excel to use to construct the Pivot Table, i.e. the data. As we highlighted the cells prior to starting the wizard, these are already selected for us, and all we need do is click Next. See Spreadsheet Data 6.
Spreadsheet Data 6.
In step 3, we decide whether the Pivot Table is to be displayed in the same worksheet, or in it’s own individual worksheet. For this exercise, I have chosen Existing worksheet. You then select the cell on the worksheet which will be the top left corner of the Pivot Table. You can either enter the cell value, or, as I have done here, use point and click. Click first on the small square to the right of the box, as shown by the black arrow in Spreadsheet Data 7 below.
Spreadsheet Data 7.
This will collapse the dialog box. Now click on the cell on the worksheet which you want to be the top left hand corner of the Pivot Table. The cell reference (address) will appear in the field of the collapsed dialog box. Now click on the little grey square at the far right hand edge of the field, as highighted by the black arrow in Spreadsheet Data 8.
Spreadsheet Data 8.
This takes us back to the Wizard dialog box, and the cell reference is in the field, as in Spreadsheet Data 9. Click Finish to generate the Pivot Table.
Spreadsheet Data 9.
You now have the Pivot Table, Pivot Table Field List and Pivot Table Toolbar. See Spreadsheet Data 10. The Pivot Table Field List is only visible when one of the cells in the Pivot Table is selected. The Pivot Table Toolbar contains the functionalities available for the Pivot Table, including generating a Pivot Chart. We won’t be going into this at this point, and will instead concentrate on generating a simple Pivot Table.
Spreadsheet Data 10.
Now we have to decide how we want to display the data. The Pivot Table Field List tells us what options we have. We can look at sales of shoes by date, by size, by colour or by sales assistant. With Pivot Tables, we can drag and drop the sets of data from the Field List to the Pivot Table.
So, first of all, click and drag the first data field “Date of sale” to the “Drop Data Items Here” area. This will give a simple count of the number of records in the list, i.e. it will count the number of sales and give the total number. Spreadsheet Data 11. shows how the Pivot Table reduces in size to simply show the number of sales in the list. The top left box of the Pivot Table reads “Count of Date of sale”.
Spreadsheet Data 11.
So, there were 19 sales in total. How many of these were red shoes? In our example, it is quite simple to manually count up how many sales there were in total, and how many of these were red. In a real shoe shop, however, there is likely to be thousands of sales. So, with a Pivot Table, we can generate those figures in seconds. To find out how many of each colour of shoe was sold, click and drag “shoe colour” from the Field List to the cell currently reading “Total”. Spreadsheet Data 12. shows how the Pivot Table has altered to show the total number of shoes sold, and the breakdown by colour. We have made “shoe colour” into a ROW FIELD.
Spreadsheet Data 12.
As you may remember, we have two sales assistants, Ken and Barbara, who are engaged in a fierce battle for salesperson of the year. If you click and drag the “sales assistant” field from the Field List into the cell above “Total” and to the right of “Count of Date of sales”, you get a Pivot Table like Spreadsheet Data 13 below. We have made “sales assistant” into a COLUMN FIELD. In Spreadsheet Data 13. we see that Ken has sold three pairs of blue shoes, whilst Barbara has sold two. In total, Ken sold ten pairs of shoes and Barbara sold nine. We can see not only the breakdown of the number of pairs of shoes that Ken and Barbara sold in total, but also, how many of each colour they sold as well.
Spreadsheet Data 13.
It can be quite easy to make a Pivot Table overly complicated, so it’s important to know how to remove from a Pivot Table as well. To simply see the sales totals by sales assistant without the breakdown by shoe colour, right-click on “shoe colour” in the Pivot Table, and select hide from the menu. This then just shows the sales totals for each sales assistant and the overall total, Spreadsheet Data 14.
Spreadsheet Data 14.
This is a basic introduction to Pivot Tables. There is a large number of possible operators and functions incorporated into Pivot Tables, so it is important to get a feel for the basics first to ensure that you consistently get accurate results. Always check that the initial data is accurate and complete. We will return to Pivot Tables in a future newsletter.