Navigation

Previous
Next
Homepage
FAQ
What's new
Sitemap

Topics

Excel
Office button
Home
Insert
Page Layout
Formulas
Data
Review
View
Developer

Excercises
Autosum
Chart - Insert a second axis
Create a half-moon chart
Create a demographics chart
Conditional formatting
Data table
Goal Seek
Pivot table
Scenario Manager
Solver

Tutorials

Word
Excel 2003
Excel
PowerPoint
Access

Others

Demonstration files
Texte en français

Contact

By e-mail
Join our Newsletter

Share this page

Excel - Exercise on PivotTable

Introduction
Before we start
The four areas
Fill-in the table
Move a field
Filter the fields

From a field in the PivotTable
Remove the filter
From the Filter Report area
Using slicers

Remove a field
Add a field
Rename a field and change it's properties

Add a calculated field
Group a fields' values
Refresh the PivotTable
The PivotChart
All the commands

Introduction

A PivotTable is a powerful tool to easily generate summary reports from a massive amounts of data. But it has been underused in previous versions of Excel. That's why Microsoft placed it as the first option under the Insert tab in Excel.

You can compare the results from one field to another. This option is also dynamic. You can add/change/remove fields from one area to another and PivotTable will recalculate itself to show you the new results. This allows you to "dig" deeper into the data and maybe finds somethings you didn't expect.

This page is dedicated to show you how to use this tool with all it's options. The next part of this page is to create a PivotTable.

Before we start

For this exercise, you can use the file excel2007-2010-exercises.xlsx on the demonstrations files web page.

Select the Data worksheet.

You need to have a data list or some form of database that will be the source for your PivotTable. The data must be placed in a certain way.

Excel 2007 : Data de base pour l'exerice sur le tableau croisé dynamique

In a data list, the field names must be on the first row. No column should be left empty. They must all be filled with a description of the column's content (name, first name ...).

The next rows must contain data. You cannot leave a blank row. Every row must contain some data even if a few cells are empty. Make sure that the cells around the Data list are empty. If not, Excel will think that those cells are also part of the Data list.

Place the pointer into any cell of the Data list.

Select the Insert tab.
Press the PivotTable button Excel 2010 - PivotTable - bouton .
Make sure you have the proper selection of cells.

it's best to place the PivotTable into a new worksheet.
Press the OK button.

Excel 2010 - PivotTable - empty areas

Here's your first glimpse of your PivotTable empty. You need to populate it with fields that are in the PivotTable Field List to the right of the screen or create your own calculated fields.

Note : Note:
The list of fields PivotTable will disappear if you click outside the table. Select a cell in the PivotTable to regain access to its options.

The PivotTable is composed of four areas. Each has a specific function.

Excel 2007 : Liste des champs de tableau croisé dynamique

The four areas

Report filter	Allows you to filter the table data on one or more fields.
Column labels	Brings together the common values together and displays the result of each value in a column.
Row labels	Displays the results of the synthesis of each value on a separate row.
Values	Displays the results of functions required (sum, count, average ...) According to the values shown on the rows and columns.

Fill-in the PivotTable

Marck the box beside the field Salary.

The content will automaticaly be placed in the Values area since the content of this field is only values The PivotTable will also choose by default the Sum up the values. You will see how to change the operation a little later in the text.

Marck the box beside field Title.

Excel 2007 : Tableau croisé dynamique avec la description d'un champ

The first description is automatically placed in the Row labels area. Chaque ligne represent l'a of valeurs the field (Administrateur, Ouvrier …) Each row represents the values of the field (Assistant, Manager, Sales, Worker ...)

Excel 2007 : Tableau croisé dynamique avec deux champs

Select the Gender field.

The field is also added in the Row labels area. But it makes the table harder to read. It would be better to move this field to the Column labels area.

Move a field

	Press on the button at the end of the Gender field in the Row labels area. From the list of options, select Move to Column Labels area. OR Place the pointer over the box with the Gender field in the Row labels area. Press and hold the left mouse button. Move the box into the Column Labels area.
	The values in the PivotTable are now easier to read because a field has been moved from one area to another. It's easier to regroup the values in this fashion.

Filter the fields

There are times when you do not want to have a global view but need to filter through to a smaller group of values that hold the information you seek. Le table croisé offre many ways of filter The PivotTable offers several ways to filter through all the data available: from a field in the PivotTable, from a field in the Filter report area and by using Slicers in Excel 2010.

Filter a field located in the PivotTable

From the PivotTable Field List, press the triangle at the end of the Title field.
OR
From the PivotTable, press the triangle at the end of the Row Labels button.

You will see the list of options to sort and filter the field you have chosen. Among the filter, you'll see a list of valuesfor the field. You can choose to filter by removing or selecting these values.

You can also filter a field through it's labels or it's values. (Equals, Does not equal, Greater Than...)

From the list of field values, uncheck the boxes beside Worker and Sales.
Press the OK button.

This will leave you with only the employees who work in the office and not those who are on the road.

Excel 2010 - PivotTable - Filter field result

You will notice that the number of employees is less than before since there are only wizards and managers. Also take note that there is a filter icon next to Row Labels.

Remove a filter from a field located in the PivotTable

In addition to filtering an active field of the table, you can also filter directly from the PivotTable. The next procedure will remove a filter from a field.

From the PivotTable, press on the button at the end of Row Labels.

From the list of options, you can sort or filter the values in many ways.

From the list of options, select Clear the filter from Title.

This will remove the filter and show all the values of the field.

Apply a filter to a field in the Report filter area

The Category field regroups all the employees salaries into fours groups (from 1 to 4). The next part will filter the PivotTable even if this field is not located in the table or in any areas.

From the PivotTable Field List, move the cursor over the field Category.
Press the triangle and the end of the field Category.

Excel 2010 - PivotTable - Filter on field Category

From the list of values for this field, uncheck the boxes beside the values 1 and 2.
Press the OK button.

A filter has been placed on the field. But it's not active. The grand total in the PivotTable is the same as before. To have an impact, the field must be placed in one of the PivotTables areas.

Excel 2010 - PivotTable - Add a field to the Report filter area

From the PivotTable Field List, place the pointer over the Category field.
Press the right mouse button.
From the list of options, select Add to Report Filter.

Excel 2010 - PivotTable - Report filter activated

With the Category field now in the Report Filter area, you will notice that the results have changed. Note also that there is a filter icon next to the Category field in the Report Filter area above the PivotTable.

Return to the Category field and remove the filter.

Filter using slicers

Excel 2010 offers a new way to easily filter through all the data. It's called a "slicer". It "slices" throgh your data by using the fields' values placed into boxes. You decide if you want to include a value by simply pressing a box that includes the value you want.

Add slicers

From the PivotTable's Options tab, select Insert Slicer.

Excel 2010 - PivotTable - Slicer field list

Select the fields yu want to filter.

For this exercice, select the fields Gender and Title.
Press the OK button.

The slicer boxes will be on top of each other.

To move the slicer boxes:

Place the cusor over the boxes title bar (either Gender or Title).
Press and hold the left mouse button.
Move the box to the location you want it to be.

You can also change the bose's size by pressing and holding the doted boxes on the sides and corners of each box.

Excel 2010 - PivotTable - Slicer options

Place the cursor over a slicer box.
Press the right mouse button.
From the contextual menu, select Slicer settings.

Excel 2010 - PivotTable - Slicer settings

Here are the options you can chosse from.

Press the OK button.

Filter by using the Slicers

Excel 2010 - PivotTable - Filter with slicers

To acheive the same results

Press the value M from the Gender slicer box.
Press the value Manager from the Title slicer box.
Press and hold the CTRL key.
Select the value Worker from the Title slicer box.

You can use CTRL key to select values individualy. You can also use the Shift key to select many values that are one after the other.

Select the first value of the group.
Press and hold the Shift key.
Select the last value of the group.

Each values between the first and last value will be selected. After, you can use the CTRL key to add or remove values from your selection.

To remove the filter, press on the at the top right of the slicer box.

To remove the slicer

Place the cursor over the slicer box you want to remove.
Press the right mouse button.
From the contectual menu, select Remove followed by the name of the field (gender, title ...)

Remove a field

it's time to remove this a from the PivotTable and continue with the exercice. There are two ways to remove a field from the PivotTable. Select from on of these two ways to remove the Gender field from the PivotTable.

	From the PivotTable Field List, uncheck the box next field you want to remove from the PivotTable.
OR
	From the list of areas, select the field to remove. Press the triangle at the end of the field. From the list of options, select Remove field.

Excel 2007 : Tableau croisé dynamique avec la description d'un champ

The PivotTable is simpler to read. But it may still not have all the information you need.

Add a field

You're not limited to placing a single field in the Values area, or any other area from the PivotTable. In fact, the more field you have, the more information you can access or filter. The next exercise consist of adding a field in the Values area to know how many people are associated with each value from the Title field. Since this field is already in the PivotTable, you can't just add it a second time in the same way you did it the first time. You must drag it into the proper area. In this case, into the Values area.

From the PivotTable Field List, select one more time the Salary field.
Press and hold the left mouse button.
Move the Salary field one more time into the Values areas.

Excel 2010 - PivotTable - with two Salary field inthe values area

The salary field appears twice in the Values area.

Rename the fields and change it's properties

At first, it looks silly to have the same information twice in the PivotTable. But you need one of these fields to determine the wages and the other to find the number of people who are in each group. The next part consist of changing the name and it's properties

There are two ways to select a field.

Excel 2010 - PivotTable - Field properties

In the PivotTable, click on the cell named Sum of Salary.
Press on right mouse button.
From the list of options, select Value Field Settings.
OR
Double-click in the cell named Sum of Salary.
OR

Excel 2010 - PivotTable - Field settings

In the PivotTable, click on the cell named Sum of Salary.
From the PivotTables' Options tab, select Field Settings.

Excel 2010 - PivotTable - Field settings window

You can change the fields' name and the summarize options. Not only can you sum up a fields' value but also count the values, find the average, the biggest (max) or smallest (min) and other operations.

From the list of operations, select Count.
Change the name of the filed to Number Employees.
Press the OK button.

Always select the fields' operation before changing the name. Or the PivotTable will automaticaly change the name of the field to "Operation of Field Name". Also, the name you give cannot be one that is already in the PivotTable Field List.

Select the field Sum of salary2.
Changer the name to Wages.
Press the OK button.

Excel 2010 - PivotTable - Renamed fields

The pivot table now shows the number of employees and their wages. The names of the fields was also changed to better represent the content.

Add a calculated field

You can add calculated fields in the PivotTable to better analyze or interpret your data. The next exercise will add a calculated field that equals 50% of the employees' wages to represent the benefits (life and medical insurance, retirement plan and others) they receive.

From the PivotTable's Options tab, press the Fields, Items, & Sets button.
Select the Calculated Field button.

Excel 2010 - PivotTable - Insert calculated field

In the Name box, enter the text Benefits.
Select the Formula box.

Like every other formula in Excel, the calculated field must start with a « = » sign.

From the list of fields, select the field Salary.
Press the Insert a field button.
Return to the Formula box.
Add * 0.5 to the formula ( or * 50%).
Press the Add button.
Press the OK button.

Excel 2010 - Pivottable - List of fields

You will notice that the new field Benefits is in the PivotTable Field List and also in the Values area. It was automaticaly placed there because it's content is only values.

Excel 2010 - PivotTable - Result with the Benefits field

Regroup the fields' values

The next step is to create two groups: InOffice and InField. The first group will include the values Assistant and Manager. The second group will include the values Worker and Sales.

In the PivotTable, select the cell with the text Assistant.
Press and hold the CTRL key.
Select the cell with the text Manager.

From the PivotTable's Option tab select the Group Selection option.
OR
Press the right mouse button.
From the list of options, select Group.

A group has been made with the values Assistant and manager. The button is used to mask the individual values and leave only the grand total to be seen. You can always unhide the indiYou can always the réafficher by appauyant for the button .

The next step is to group the values Worker and Sales together.

In the PivotTable, select the cell with the text Worker (the one without the box).
Press and hold the CTRL key.
Select the cell with the text Sales (the one without the box).
From the PivotTable's Option tab, select the Group the selection.

Excel 2010 - PivotTable - Grouped values

Change a group's name

Place the pointer in the cell named Group1 .
Press F2 and change the title to InOffice.
OR

Place the pointer in the cell named Group1.
Place the cursor in the formula bar and change the title to InOffice.

Changez the name Group2 to InField.

Excel 2010 - PivotTable - renamed groups

Refresh the PivotTable

Unlike the formulas that you uses every day, the PivotTable doesn't update itself automaticaly when you change the underlaying data. You must force the PivotTable to "refresh" itself.

Return to the Data worksheet.
Go to the F12 cell.
Change the salary of Alice Thomson from 22 500 $ to 27 000 $.
Return to the worksheet where the PivotTable is located.

You will notice that the wages remains the same is $ 394 400. You need to refresh the PivotTable so that it takes into consideration the changes you made to the original data.

From the PivotTable's Options tab, press the Refresh button.
OR
Press on the Alt and F5 keys.

Excel 2010 - PivotTable - Results after refresh

The wages have passed from 394 400 $ to 398 900 $.

The PivotChart

Excel also gives you the possibility to view the PivotTable in the form of a PivotChart.

From the PivotTable's Options tab, press the PivotChart button.

Excel 2010 - PivotTable - Insert chart window

From the list of chart types available, select 3-D Column.
Press the OK button.

Excel 2010 - Pivotchart - Initial view

Here is the initial view with all the groups and values you selected.

Excel 2010 - PivotChart - Collapse

Select the cell named InOffice.
Press the right mouse button.
From the contextual menu, select the options Expand/Collapse and Collapse.
OR
Press the beside InOffice.

Repeat the same opération to with InField.

Excel 2007 : Graphique croisé dynamique avec regroupements de valeurs

The chart offers less details. But it regroups all the values into a single bar.

The PivotTable offers more options to represent whatever you want. You must first select one of the four tabs.

The PivotChart's options.

Excel 2010 - PivotChart - Analyze tab

The first three tabs are the same as when you create a chart. Under the Analyze tab, all these options have already been described except for the fields buttons.

Excel 2010 - PivotChart - Field boutons

These field buttons allow you to change options to the PivotChart. But they can be in the way when you want to include in in a report or to print it.

All the PivotTable's commands

You have see the most important commands used to make a PivotTable work for you; but not all of them. The next section will give you a description of every command under the PivotTable's two tabs: Options and Design.

Excel 2010 - PivotTable - Options Tab

PivotTable	Give a name to the PivotTable. Imporant if you have more than one in your workbook.
Options	Determine the PivotTable's option on how it will present the results to the screen.
Active Field	Show the field where the cursor is presently located.
Field Settings	Change a fields properties on the way it presents the results depending the the operations (sum, count ...) and presentation (currency, thousands ...)
Expand entire field	Mostly used to show the hidden values from a group of fields. It's also another way to add a field to an area.
Collapse entire field	Mostly used to hide the values of a field to better show the result from a group of fields.
Group Selection	You can group many values together to view the results of the group instead of each value alone.
Ungroup	Remove a group and view the results of each value.
Group fields	Group the values of many fields together.
AZ	Sort in ascending order.
ZA	Sort in descending order.
Sort	Sort manualy or in a specific order.
Insert Slicer	A new option in Excel 2010 to help you filter the data by using boxes with all a fields' values. Select the ones you want to see in the PivotTable.
Refresh	Update the PivotTable after you have mato changes to the original data table.
Change Data source	Select another data list as the source ofr this PivotTable.
Clear	Remove all the fields from the PivotTable
Select	Select an item from the PivotTable or the entire PivotTable itself.
Move PivotTable	Move the PivotTable to another worksheet or a new worksheet.
Summarize values by	What type of operation (sum, count ...) do you want to apply to the field.
Show values as	Can show the current values or a a pourcentage of a grand total or a row or column total.
Fields, Items & Sets	Create calculated fields or items.
Pivotchart	Create a PivotChart from the Data currently in the PivotTable.
OLAP Tools	Online analyticac processing (OLAP) More advanced tools for data analysis.
What-If analysis	Only available with OLAP data.
Show/Hide	View or hide the Field List, the expand/Retract buttons or Field Headers.

Excel 2010 - PivotTable - Design tab

From the Design tab, you can decide to show the total and subtotals, the rows and columns' titles and change the PivotTable's presentation.

SubTotals	Show/Hide the subtotals after each fields
Grand Totals	Show/Hide the grand totals at the end of the PivotTable
Report Layout	Select from three report layout for the PivotTable.
Blank Rows	Add a blank row after each field to make the PivotTable more readable.
Row headers	Place in bold the row headers.
Column headers	Place in bold the row headers.
Banded rows	View the data with banded rows. May make it easier the compare the results.
Banded Columns	Views the data with banded columns.
PivotTable styles	After selecting the style options, select a style from this gallery.

You like what you read? Share it with your friends.

This site is hosted by 1&1.com