Pivot Tables

Pivot Tables

Pivot Tables arrange and summarise data from a table that has labelled columns. Usually, two fields from the data mark the column and row headings. The table represents information in different ways.

For instance, if you have a table of staff details with headings, a pivot table compares pay rates for staff working in different departments. Pivot Tables summarise information, e.g., finding the number of people working in a particular department with specific salaries. The values can be added together, counted, and averaged.

The work files associated with this can be downloaded at www.digidiscover.com/downloads. Click on the Management Spreadsheets book cover to download the files.

1. Open the workbook Staff List

2. Click inside the table

3. On the Insert tab, locate the Tables group, select Pivot Table

4. For Choose where to place the Pivot Table, click on New Worksheet

5. In the Create Pivot Table dialog box, click on OK

6. Name the worksheet as PivotTable

7. On the Pivot Table Fields pane, click and drag First Name to Rows, Department to Columns and Gender toValues

8. The Pivot Table will give a summary of employee genders by department

9. Switch First Name to Columns and Department to Rows

10. The Pivot Table shows employee names along the top and departments along the right

11. Click & drag First Name to Values and move Gender to Columns

12. The PivotTable now displays a summary of Employees in each Department based on Gender

13. Save the workbook and leave it open

Modify the Data Source

The information for the Pivot Table updates to reflect any changes made to the original data. For instance, if there are updates to salaries for employees, the data can be changed in the original table and updated in the Pivot Table

1. Open the workbook Staff List

2. Return to the Staff List worksheet and enter the following details:

James, Smith, M, 15/04/1975, Waterfront, Administration

3. Return to the PivotTable worksheet

4. On the Pivot Table Analyse tab, select Refresh

5. The Pivot Table updates

6. Return to the Staff worksheet and delete the added entry from the table

7. On the Pivot Table Analyse tab in the Data group, select Refresh

8. The Pivot Table updates to include the additional entry

9. Save the workbook and leave it open

Filter & Sort a Pivot Table

A filter in a Pivot Table can show separate categories. The table displays only required information, e.g. the average salary for employees in the marketing department. You can sort details such as surnames in ascending or descending alphabetical order.

1. Open the workbook Staff List

2. Click on the arrow beside the Row Labels heading

3. Click on the Select All checkbox to deselect the different departments

4. Select the Sales checkbox and click OK

5. It filters the table displaying employees in the Sales department

6. Click on the Row Labels arrow again and click on Select All and click OK

7. This setting shows all the employees working in each department

8. Click on the arrow beside the Row Labels heading

9. Sort the Row Labels by clicking on Sort A to Z

10. The list is sorted in alphabetical order by Department

11. Click on the Row Labels arrow and hover over Label Filters

12. Select Equals

13. Enter F for the Label Filter value

14. Click OK

15. It sorts the table by Department in Ascending Order for Female employees

16. Save the workbook and close it