Pivot Table in Microsoft Excel is an adaptable, interactive tool for grouping and analysing data. It is especially useful in the preparation of a report that has different dimensions as it is quite easy to re-order columns and rows to obtain the desired insight or view of the underlying data.
In this tutorial, we will use Pivot Table report to analyse sales performance for region and salesperson over a time period by comparing actual results to budget. This report will be created using Microsoft Excel 2007.
Preparing the Pivot Table Data Source
Pivot Table requires a source data that is properly organized into consecutive columns and rows, as in our data extract image below.
The data source can be a range in an excel file (as in our case) or it can be an external source such as a Microsoft Access or MySQL database; or even a text file as long as it is properly ordered.
Creating the Pivot Table Report
We could have both data source and pivot table report on the same worksheet but we will separate them to avoid clutter. Therefore, for our exercise, we will put our source data in a separate worksheet named 'data' while the pivot table report will be on a different worksheet named 'pivot'. Our data source has columns for 'Region', 'SalesPerson', 'Company', 'Date', 'Actual', 'Budget', 'Year' and 'MonthNo'. We will be referencing these columns (fields) as we move through this exercise.
With our source data in place, we can proceed to create our pivot table report in steps, as follows:
- Place your cursor anywhere within the data source.
- On the Insert menu, choose Pivot Table.
- On the pop up box, you will find that the range is already populated. We want the pivot table report to be on another worksheet. Therefore, on the bottom half of the dialog box, we want to select 'Existing Worksheet' and then specify our 'pivot' worksheet as the report destination, as per the sample image below.
Pressing OK will dismiss the dialog box and move you on to the 'pivot' worksheet.
- On the 'pivot' worksheet, you should see the Pivot Table Field List and placeholders anchored to the right of the worksheet with the pivot table report area to the left. The top half of the Pivot Table Field list shows the fields (columns) from the source data while the bottom half mimics the appearance of the pivot table report. Note: clicking on an empty cell of the 'pivot' worksheet will cause the Pivot Table Field List to disappear. To bring it back, you will have to click anywhere within the pivot table report area.
- The preliminary design of our pivot table report will have 'Region' and 'SalesPerson' as rows and 'Year' as columns with 'Actual' and 'Budget' as values. We will use drag and drop to place these 'variables' where we want them, using the placeholders at the bottom half of the Pivot Table Field List.
- On the Pivot Table Field List, click once on 'Region' which background should now be highlighted. Without releasing the mouse, drag and drop it in the Row Labels area. As you do this, you will find that the pivot table report begins to take shape.
- Apply the same drag and drop procedure to 'SalesPerson' but place it just below 'Region' in the Row Labels area.
- Next, drag and drop 'Date' to the Report Filter area.
- Following on, drag and drop 'Year' to the Column Labels area.
- Finally, drag and drop Actual and also Budget to the Values area.
- Depending on your source data formatting for values, Excel will apply either 'Count or 'Sum' to the values. In our case, we want Excel to sum Actual and Budget values. We accomplish this as follows:
- In the Values area, click on 'Actual' and select the downward pointing arrow to its right to open the context menu. We then choose Value Field Settings.
- On the following dialog box, for Custom Name, clear what is in there and type in 'Actuals' (without the apostrophe). Repeat for Budget, naming the Custom Name field 'Budgets'.
- Note: you cannot use the label of any of your data source columns in the Custom Name field because Excel will complain. That is the reason 's' is added to 'Actual' and 'Budget' in order to make them different words.
- Next, we make sure that the data is being summarized using 'Sum' and then we set our number format.
The above steps have provided us with a basic setup for our pivot table report, as per the image below. We now need to customize it further for our purposes
Enhancing the Pivot Table Report
We could easily have added a column to the pivot table source data to calculate the difference between 'Actual' and 'Budget' and the corresponding performance ratios. However, if the source data is an external data source such as a database, we may not always have the possibility to inject extra columns. Thankfully, we can add extra calculation fields on the pivot table report itself.
When your cursor is in the pivot table report area, the PivotTable Tools context menu should become visible at the very top of the main menu.
We will calculate the difference between Actual and Budget using the Formulas option on the PivotTable Tools menu. While the cursor is on 'Actuals', we click on the Formulas option and select Insert Calculated Field. On the dialog box, we assign a name of 'AvB' representing Actual vs Budget and enter the formula using the field names to give Actual-Budget. We then click on Add to make that formula available.
As we are here, we will also add another name, Achvmt, for Achievement. When we clicked Add previously, AvB wasn't cleared from the Name area of the Insert Calculated Field dialog even though that newly field was added to the pivot table. We can go ahead and overtype on the name to create the Achvmt field and then apply a formula similar to =IF(ISERROR(AvB/Budget),0,1+(AvB/Budget)) to calculate the percentage achievement. After clicking OK, both AvB and Achvmt will be added to the Values area of the PivotTable Field List and will also appear on the pivot table report. For the Achievement field, Excel will assume that we are summing up when what we want is a percentage. Therefore, we will format that field as a percentage in order to get the expected result.
Pivot Table Classic View
Currently, our pivot table report shows the totals for 'Region' followed by a breakdown for 'SalesPerson' under the same column. Also, we cannot drag fields around without having to use the Pivot Table Field List. An alternative display mode is the Classic Pivot Table report style which shows each Row Label under its own column, and also enables dragging of fields. The quickest way to access this is to right click anywhere within the pivot table report and choose PivotTable Options from the context menu. On the PivotTable Options dialog is a 'Display' tab under which is the option for Classic PivotTable layout. We simply place a tick against this box to activate it.
We can 'drill down' on any value to obtain the source data details that make up that value. We do this by right clicking on the value of interest and selecting Show Detail from the context menu. We can also simply doubleclick on the value instead. The Pivot Table report will create a new worksheet to display those details. There would be instance when we would not wish to allow drill down. We can disable this functionality by removing the tick on the Show Detail option of the Data tab on the PivotTable Option dialog.
The same set of data can also be used to report on either Actuals or Budget by removing whichever field we do not wish to show. In our example, we can filter the 'Year' for a single period, remove 'Budget' and bring down 'Date' which has been formatted as mmm-yyy across as Column Labels to see actual results by month for the filtered year.
For presentation and printing, options are available on the PivotTable Options dialog under the Printing tab. For the Row Label items, we can specify a blank line separator to be included between each 'Region' for example, by enabling it under the Printing & Layout tab of the Field Settings menu.
Pivot Table is a versatile tool for any serious analytical work on Microsft Excel. Hopefully, this tutorial has helped to at least open up some of the posibilities of a pivot table report. The completed exercise file, including the source data, may be downloaded below. A link for the video version is also provided