What's new


Office button
Page Layout

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


Excel 2003


Demonstration files
Texte en français


By e-mail
Join our Newsletter

Share this page

AddThis Social Bookmark Button

Excel - Exercise on PivotTable

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


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.

Excel 2010 - Create PivotTable window

*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.


Displays the results of functions required (sum, count, average ...)
According to the values shown on the rows and columns.

Fill-in the PivotTable

Excel 2007 : Choisir le champ Salaire de la liste pour le tableau croisé dynamique

*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

Excel 2010 - PivotTable - move 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.
*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.

Excel 2010 - PivotTable - Move field result

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

Excel 2010 - PivoTable - Title bouton

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

Excel 2010 - Filter field

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 values​for 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.

Excel 2010 - PivotTable - Clear filter

*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.

Excel 2010 - PivotTable - Filter on field Category

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

Excel 2010 - PivotTable - Insert Slicer *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 Excel 2010 - Pivottable - Remove Slicer's filter 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.

Excel 2010 - PivotTable - Uncheck the Gender field *From the PivotTable Field List, uncheck the box next field you want to remove from the PivotTable.
Excel 2010 - PivotTable - Remove Field *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.
*Double-click in the cell named Sum of Salary.

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.

Excel 2010 - PivotTable - Calculated field bouton

*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.

Excel 2010 - PivotTable - Group cells

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

Excel 2010 - PivotTable - Group Selection bouton

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

Excel 2010 - PivotTable - Group values

A group has been made with the values Assistant and manager. The Office 2007 : Boite moinsbutton 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 Office 2007 : Bouton Plus.

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 Office 2007 : Boite moinsbox).
*Press and hold the CTRL key.
*Select the cell with the text Sales (the one without the Office 2007 : Boite moinsbox).
*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.

Excel 2007 : Changer le name d'un regroupement à partir de la barre formule

*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.

Excel 2010 - PivotTable - Changed initial data

*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.

Excel 2010 - PivotTable - Refresh bouton

*From the PivotTable's Options tab, press the Refresh button.
*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.

Excel 2010 - PivotTable - PivotChart bouton

*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.
*Press the Office 2007 : Boite moinsbeside 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


Give a name to the PivotTable. Imporant if you have more than one in your workbook.


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.


Remove a group and view the results of each value.

Group fields

Group the values of many fields together.


Sort in ascending order.


Sort in descending order.


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.


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.


Remove all the fields from the PivotTable


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.


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.


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.


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