Pivot Tables
&
Pivot Charts
Pivot tables... 2
Creating pivot table using the wizard ... 2
The pivot table toolbar ... 5
Analysing data in a pivot table ... 5
Pivot Charts... 6
Creating a pivot chart using the wizard ... 6
Formatting your chart ... 10
Exercises... 11
Pivot tables exercise ... 11
Pivot charts exercise ... 14
Pivot tables and pivot charts enable you to analyse large quantities of data. The data can be in a single range, several ranges on a single worksheet or several ranges spread over different worksheets. This is an introduction to pivot tables and pivot charts.
Pivot tables
Pivot tables are interactive tables that enable you to quickly summarise large amounts of data. A pivot table will allow you to look at the data in different ways, with
virtually no effort at all. You can also choose what should be shown, leaving out columns that are not of interest for a particular analysis.
Creating pivot table using the wizard
Open the Excel workbook containing the data you wish to analyse and click somewhere on your data (not in a blank cell).
Select ‘Data’ from the menu bar and choose PivotTable and PivotChart Report. The wizard dialog box is then displayed.
Step 1 allows you to choose the source for the pivot table. If you have already opened the workbook containing your data, you would select ‘Microsoft Excel list or
database’.
Step 1 also allows you to choose between creating a pivot table or a pivot chart.
Choose PivotTable.
Click next.
Step 2 of the wizard enables you to choose the range of data to be included in your PivotTable. You will find that it has been filled in automatically, however, you can change the range by highlighting a new selection on your worksheet.
Click next.
The final step of the PivotTable wizard is displayed.
This final step allows you to choose where you want to put the pivot table.
Before you click finish, select layout so that you can choose the layout of your pivot table. The following dialog box appears.
This dialog box allows you to choose how to display the data. Whatever layout you choose, you can easily change it later.
You set up the layout of your pivot table by dragging the appropriate button over to the appropriate box.
The example below shows that Salesperson will be the page reference, month no has been position for rows etc.
(If the button in the DATA box was displaying something other than Sum of, you would have to double-click the button and choose the function you required in the Pivot Table Field dialog box.)
Finally, once you have completed your layout, click OK, or press Enter. This will return you to step 3 of the wizard. Make sure that new worksheet is chosen and click finish.
The pivot table is created, and displayed in a new worksheet. The PivotTable toolbar is also displayed. If you wanted to use the PivotTable and PivotChart Wizard to make some changes, you would click the PivotTable Wizard button in this toolbar and then click Layout.
The pivot table toolbar
Analysing data in a pivot table
A new worksheet with your pivot table will be displayed like the example shown below. To analyse your data, simply click the arrow button and choose your selection from the drop down list.
The example below is displaying all figures for all salespersons. If you wanted to view the sales figures for ‘Smith’ then you would click the arrow in cell B1 and choose Smith from the drop down selection. You can choose to view only specific products or months in this way. You might want to find out which salesperson has sold the most TVs or whether certain months are busier than others?
Format report
This will create a chart from your pivot table
This button returns you to the wizard
This button will display a subset of data on a new sheet or show additional information in the pivot table
This button will hide information
This will refresh the pivot table from any amendments you make to the original spreadsheet
This will enable you to change the calculation and number settings in your table
This simply hides the fields from your toolbar
You can change the layout of your pivot table on the worksheet by dragging the fields to different places. For the example above, if you were to drag the salesperson field to where the month numbers are, your pivot table would then be displayed like the one below
The bottom row of the PivotTable toolbar contains buttons for the data in your worksheet. If you had clicked Finish instead of clicking Layout in third step of the Wizard, Excel would have created a blank pivot table. You could then have dragged the buttons onto the worksheet to layout the pivot table. You can also use these buttons to change the layout of the data in the pivot table — just drag them onto the worksheet instead of dragging around the buttons that are already there.
Pivot Charts
Creating a pivot chart using the wizard
Open the Excel workbook containing the data you wish to analyse.
Select ‘Data’ from the menu bar and choose PivotTable and PivotChart Report. The wizard dialog box is then displayed.
Pivot charts work in a similar way to pivot tables, but the data is displayed as a chart.
Excel needs a pivot table from which to produce a pivot chart. If you want a pivot chart and a pivot table, it’s easiest to create the pivot chart first — Excel will create a pivot table for you at the same time.
As you will already have the Excel workbook open with the data you wish to analyse, select Microsoft Excel list or database.
In the What kind of report do you want to create? group of options, click PivotChart (with PivotTable). Click Next.
Notice Step 2 of the Wizard is displayed. The correct range has already been filled in for you. As with the pivot table, you can highlight a new selection on your worksheet if you wish. Click Next.
Notice Excel tells you that you will save memory if you base your chart on your existing pivot table report.
This time you will base the chart on a new pivot table report. Click No.
Step 3 of the Wizard is displayed. You could click layout to choose the layout of your pivot chart, but we will do this directly in the chart this time so click Finish.
Notice a blank pivot chart is created. The Chart toolbar and the Pivot Table toolbar are displayed.
Notice that Excel has created sheet 2, which contains a blank pivot table for your pivot chart.
You could create a pivot table here by dragging the buttons from the PivotTable toolbar. As you have already learnt, this is an alternative to creating them in the Layout dialog box. This time, you are going to work with the pivot chart so click the Chart1 sheet tab.
You need to drag the buttons from the PivotTable tool bar to the position required on the chart.
Excel creates a pivot chart of the data, example below.
You can change the pivot chart in the same way that you changed the pivot table and as you make each change, watch for the effect on the chart.
You can either click the arrows and select from the drop down lists or drag the buttons to different positions. You can also drag the buttons back to the PivotTable toolbar to remove them from the chart.
Before you close the workbook, take a look at Sheet2 and you will notice the pivot table is no longer blank — it contains the used data to produced your chart. The positions of the buttons in the table correspond to the current layout of the chart.
Formatting your chart
To format the chart by changing the colouring or font colours, double-click on the section of the chart you wish to alter and the relevant dialogue box will appear allowing you to complete your task.
If you want to change the type of chart, add titles or labels or move your legend, right click in the ‘Chart Area’. A dialogue box will appear and you will have the options for chart type or chart options. If you have chosen a 3D chart type, you will also have the option of formatting the 3D view.
Exercises
Pivot tables exercise
1. Enter the following information onto a blank spreadsheet and save in my
documents. This information shows how many times each patient has been seen by the doctor each month.
Patient name Doctor seen Month Number of visits
Smith James January 2
Smith James February 1
Smith James March 2
Jones North January 3
Jones North February 4
Jones North March 2
Barton Wright January 1
Barton Wright February 2
Barton Wright March 4
Adams Wright January 2
Adams Wright February 2
Adams Wright March 1
Smith Williams January 1
Smith Williams February 3
Smith Williams March 2
Barton North January 2
Barton North February 1
Barton North March 1
Adams James January 3
Adams James February 4
Adams James March 2
Jones Williams January 1
Jones Williams February 3
Jones Williams March 2
Smith Wright January 3
Smith Wright February 2
Smith Wright March 1
Smith North January 1
Smith North February 2
Smith North March 1
Adams Williams January 2
Adams Williams February 2
Adams Williams March 3
Barton James January 3
2. Create a pivot table using all this data to display the patient name as the page reference, the doctor seen as the row reference. The month should be shown as the column reference with the sum of the number of visits as the data.
Your table should look like the one below.
3. Display the information for Smith to show how many times he saw Dr James in January and February only. Your answer should be January 2, February 1, Grand Total 3.
4. Now display the information for all patients seen by Dr Wright in March. You should have a Grand Total of 6 visits.
5. Display all the information for Barton. Your table should resemble the one below.
6. Change the layout of your pivot table so that the Doctor seen is the page reference and the patient name is the row reference. Your table should now look like the one below.
7. Now change the display to show all the information for Dr North. Your table should now look like the one below.
8. How many times did Adams see Dr Williams in the 3 month period? Your answer should be 7.
9. Change the function of the number of visits to show the average visits for all doctors seen and all patients. Your table should look like the one below.
10. What is the average grand total for all visits? The answer will be 2.03.
11. Close the spreadsheet without saving changes.
Pivot charts exercise
1. Using the same data as for the pivot table, create a pivot chart with the doctor seen as the page field, the month as the series field, no of visits as data items and patient name as the category field.
Your chart should display like the one below
2. Change the display of the chart to view just March. Who was seen most in this month?
The answer should be Adams – see below.
3. Change ‘sum of’ to ‘average’ number of visits, alter the type of chart to a stacked column chart and swap the patient name and month fields. Which month averaged the most visits?
The answer should be February – see below.
4. Change the average function back to the sum function. Then move the doctor seen to the category field and view only March. Which doctor saw the most patients in March?
The answer should be James and Williams with 7 visits – see picture below.
5. Put the doctor seen back as the page reference. Which month had the least number of visits?
(The answer should be March (with 24 visits).
6. Change the chart colours to white for Smith, red for Jones, blue for Barton and green for Adams.
7. Move the chart legend to below the chart.
8. Add the title ‘Patient Visits’ to your chart.
9. Change your chart type to 3D column.
10. Alter the 3D view of the chart so the height is 50% of base. Your chart should resemble the one below.
