Share this page
Excel - Pivot tables
Excel offers you a fascinating tool to create a synthesized view from a vast pool of data called a pivot table. As the name indicates, Excel generates a table that allows you to see the contents of one or several variables at the same time. Furthermore, the table is dynamic. It means that you can add, remove and change the location of elements in the table Excel will automatically give you a new view on your data.
The next exercise consists in creating a pivot table that offers the total of salaries according to gender and the type of work an employee occupies in the company.
You need a database before being able to create an use a pivot table. It's possible to create and manage simple databases from Excel. There are certain terms that you should know before starting.
In an Excel database, every column represents a field. The name of the field should be on the first row. Every following row represents a record. So that Excel is capable of recognizing all the records that compose the database. It's important not to leave any empty rows. All the rows after the name of the fields must have records. The following database has some data on the employees of a company.
the cursor on any cell between A1 and G16; where the database
Excel asks you where the data required for creating the pivot table is located. The database can come from four different sources.
Excel will then asks you for the type of report that you want: a pivot table or a pivot chart? This version of Excel allows not only to generate a pivot table but also a pivot chart.
this exercise, use a Microsoft Office Excel list or database to create a PivotTable.
Excel asks you to confirm the place where the data that you need is located for the pivot table.
sure that the cells selected are between A1 and G16.
Excel will then ask you where you want to save the pivot table. Is it in a new worksheet or an existing worksheet?
For this exercise, select the New worksheet option.
You could press the End button and to begin to create the pivot table. But before we do that, let's see what other options are offered in this window.
Press the Layout button.
This window also allows you to create immediately a pivot table. You can place the fields that you need, located on the right, into four different areas: page, row, column and data.
This presentation of the a pivot table's layout was only to show you the different areas that compose a pivot table. We will not be using this window to create the pivot table. But you could use it when you will be creating you own PivotTables.
the purpose of the demonstration, press the Cancel button.
This window allows you to personalize how the data will be viewed in the pivot table. For example, you can decide to activate or not the sums for each row and column of the table. Furthermore, you can change these options at any time according to your needs.
Press the Cancel button.
Excel created a new worksheet with an empty pivot table "shell". The four areas that were before mentioned in the Layout window are all here: page, row, column and data. It's up to you to place the fields that you need in their proper areas and view the results.
There is also a toolbar for the pivot table that should appear next to it. If you don't see it, here's what you should do to see the toolbar.
the Edit menu, select the Toolbar option.
It's also possible that you don't see the list of the fields that composes the database. To view it, place the cursor anywhere inside the pivot table shell.
From the Pivot table's field list, select the Salary field.
and hold the left mouse button and move the field into the Data area.
The table now indicates that the sum of all the salaries for the company is 394 400 $. The next step consists in distributing this by occupation within the company.
From the Pivot table's field list, select the Title field.
Press and hold the left mouse button and move the field in the Column area.
The new table shows the total of salaries by occupation (title: Manager, Worker...) with always a grand total of 394 400 $. The table shows each of the values of the Title field with the total of salaries for each. The next step consists in distributing the total of salaries by title and by gender.
From the Pivot table's field list, select the Gender field.
The Gender field will automatically be placed in front of theTitle field. Because of the length the table, only a part is shown on your screen. It's also possible to change the order of the fields in an area. The next step consists in giving the priority to the Gender field over the Title field.
the cursor over the Title field in the columns area of the pivot
Here is the same data from the previous table but shown in a different way. The salaries totals for the female managers of the company is always 27 000 $ whereas the men have 126 500 $. However, the data is now grouped by occupation and then followed by gender. The next operation will show an easier way to understand the same data.
the cursor over the Title field in the columns area of
the pivot table.
Although it's the same values than the previous tables, this view is much clearer to understand. A pivot table is dynamic. You may place a field in any of the four areas and the table will automatically regenerate with a newer, and hopefully, better view.
Excel allows you to see the records that compose the results of the table. The next step consists in seeing the records that are the total of the managers (153 500$).
the cursor in the cell containing the grand total of the managers (153 500).
A new worksheet will be created with the records and the data on the managers. You can redo the same thing for every cells of the pivot table.
Return to the worksheet with the PivotTable.
The next operation will allow you to filter the values that you need. It consists in determining the salary total of only the women in the company. The pivot table allows you to mask or to hide the values that you don't need. For this case, it's necessary to hide the men.
Click on the button with a triangle pointing down in the right-hand side of the Gender field. For the example, there are only two possible values: F or M.
The pivot table shows you a list of all the values that are in the records.
the box with the M from the list of possible values.
This new table shows the salaries total for all the women of the company. notice that the value " M " is not shown in the table.
Reactivate the selection M for the Gender field.
But there is another way of filtering the data. It's by placing the field in the page area.
the cursor over the Category field of the area of the columns
of the pivot table.
Because the Category field is in the page area, it's now possible to filter all the data of the table on that field. The next exercise consists in showing only the data from the employees that are in category 3.
Click on the button with a triangle pointing down in the right-hand side of the Category field.
the list of the possible values, select the value 3.
Here is the table of the total of salaries for all the employees that are in category 3. This demonstrates that you may filter the records that compose the pivot table on the fields that compose it; whether it's placed in the row area, the column area or the page area.
Replace the filter for the Category field to All.
The pivot table's toolbar offers other options to change the pivot table's presentation. This next part describes you these options and how they work. You have below a combined picture with all the options from the pivot table.
Format Report option
You created a pivot table with the fields and all the criteria that you need. This option allows you to improve the presentation of your table.
Press the button.
You may change the presentation of the table by selecting one of the predetermined formats. You can change your mind at any time and take another format that better represents the data.
For this exercise, don't change the presentation. Press the Cancel button.
Pivot chart option
There are situations where it's better to represent a mass of data in the form of a chart. As mentioned in some occasions on this site, it's useful to use a chart:
simplify the analysis of a mass of data.
Press the button.
This activates the graphic assistant that generates charts. It will pass through the same steps as making a chart with data from your worksheet. Because there is already a Web page that explains charts with Excel on this site, we'll quickly go to the next option.
Press the Finish button.
This chart represents the salaries totals by occupation and the gender from the employees of the company. You can change the presentation of this chart as you would for any other chart. And because it's a dynamic chart, you can change the presentation of the data according to the fields that were chosen.
Return to the worksheet where the PivotTable is located.
Pivot table assistant
This option allows to change the arrangement of fields in the pivot table. This section will demonstrate that you may change the presentation by adding the fields Name and First name to the rows area. This is necessary to be able to demonstrate how the next option works.
Press the button.
Press the Next button.
Press the Layout button.
the Name field below the Title field in the rows area.
Press the Finish button.
Here is the part of the new table that shows now in the rows area fields Title, Name and First name.
Update the data
This option allows you to update the data of the pivot table after you updated the tables' source database.
the cursor in the worksheet with the database.
The change that was made in the database has not been updated in the pivot table. The next action will update your pivot table.
Return to the worksheet where the Pivot table is located.
The partial sum for the workers as well as the total of salaries should have changed to 79 600 $ and 400 000 $ respectively. The pivot table doesn't update unless you press the update button.
Hide and view details options
As seen before, you may have in an area several fields to better describe the values. The next two options allows you to view or mask the values of the fields that are to the right of the selected field. If you haven't already done it, add the fields Name and First name to the rows area.
the cursor on the Name field.
Although the field First name remains visible, the values are masked.
This option hides the values of the fields that are to the right of the selected field.
Press the button.
The values of the First name field will reappear.
the cursor on the First name field.
This option can also help you add fields to an area if none are presently hidden from view. Excel will show you the list of fields that are not in the area. You can select one or many fields to add and press the OK button. But we won't be using that right now. But you now know another way of adding fields to an area.
Press the Cancel button.
For the purpose of the next exercise, hide the contents of the First name field.
the First name field located in the rows area.
Add a field to the data area
This next exercise will demonstrate how to add several fields in the Data area. First, we will adding the same field in the same area. But we'll change some options so that the fields won't be showing the same thing. The first field will show the number of persons in this category and the second will show the sum of the salaries.
From the list of fields, place the Salary field a second time in the data area.
At first, the content of the two fields will be the same. But not for long.
Change the field's parameters
In the previous table, the salaries total appears twice in the Data area. The next part consists in changing the properties, the characteristics, or the parameters in Excel's parlance, of a field to view some other important information and demonstrate the potential of the pivot table.
Click one of the boxes with the text Sum of SALARY.
Press the right mouse button.
You can change the content of the Name box to better represent the content of the field. You have also many options to represent the data. You can show the sum, the number of records in the category, the average and many more.
the name of the field from Sum of SALARY to Number.
This Number field now shows the number of persons in this category instead of the total of the salary. It's possible to change at any time the synthesis option to one from the following list:
The field parameters window also offers you other options such as demonstrated in the next part.
one of the boxes the Sum of SALARY2.
the name of the field Sum of SALAIRY2 to Salaries.
The Number option allows you to change the presentation of the values of the field. It's the same thing as the Number option under the Format, Cell and Number for a cell of your file. But it only affects a field instead of a cell.
the list of the categories, select the Percentage option.
Another powerful element of the parameters of fields is that you may view the values compared to other fields or the total. In this case, we will show the value of field with regard to the total of salaries.
the types of views, select % of the total.
The table's presentation changes again to show the number of persons, by gender, as well as their percentage of salary with regard to the grand total of the salaries.
Not only can you summarize values by field, you can also group together the values of different fields. For example, you can group together the employees who are in the head office (managers and secretaries) of those that are " on the ground " (sales rep and worker). The next part consists exactly in creating these two groups.
From the rows area, click in the cell where it's written Manager.
The CTRL key allows you to select several values to be able to group them together.
Press the right mouse button.
Select the Group and Show Detail option, and Group.
This context menu shows to you some of the options that you saw before. It's easier at times to use the right mouse button then to constantly return to the pivot table's toolbar. It's however necessary to master these options before being able to use them in this menu. There is however an option that's is not anywhere else; that's to group together the values of a field.
From the context menu, select the Group and Show Detail option followed by Group.
You'll notice that a new field added to the rows area that's called Title2. It only has a single value called Group1. It groups together all the values for managers and secretaries.
It's now time to group together the values worker and salesperson together.
the rows area, click in the cell where it's written salesperson.
There are now two groups: group1 and group2. The next part consists in improving the presentation of these groups just a little by changing the names of the field and the values.
Change the name of a value
You can change the content of a cell in the pivot table like you can do in any ohter cell in the worksheet.
Place the cursor in the cell with the Group1 text.
the cursor in the cell Group2.
All that remains is to change the name of the field Title2 to Location.
the cursor on the field Title2.
The employer at need of a synthesis that does not include fields Title, Name and First name. You could remove the useless fields. But we're simply going to mask them for the moment.
the cursor in the cell with the cell Office.
Here is an interesting table with several data represented in various ways. It shows the number of persons who work at the head office or on the field and the proportion of the salary compared to the grand totals. But there's even more.
The pivot table allows to add calculated fields. This allows you to do operations on the data in the pivot table. Besides the data supplied in the last table, the employer would like to know in how much his contribution to different programs such as insurance and the pension plan costs the company. This contribution equals to 50 % of the salary of the employees. The next part consists in adding a calculated field that calculates this according to the salary of the employees.
the cursor on the pivot table.
the box Name, write Deductions.
The employer now knows what its contribution is by category and the grand total. For your part, you now know how to add a calculated field in a pivot table.
The last table show the data wanted by the employer. However, you may improve the arrangement of fields. It's time to clean up the report before handing it over. The next part consists in placing the data on the contributions just after the number of persons by group and to remove from the row area the fields Title, Name and First name.
the cursor on the table.
To remove fields
Place the cursor on the Name field in the Row area.
To change the order of the fields.
the cursor on the calculated field Sum of Deductions in the Data area.
Press the OK button.
As you are able to understand it after this page, the pivot table offers a multitude of options to represent a mass of data. You can now take advantage of all these options for your own needs. Enjoy!