Excel Pivot Tables (Example + Download)

In this article I will explain the PivotTable feature of excel along with 2 examples. You can download the file related to this article here.

Jump To:

Contents

What is a PivotTable and What is it Used for?

One question you might be asking is what is a PivotTable, and what is it used for. I’ll explain this with two examples:

Example 1: Lets say you own a store. Every time a purchase is made you probably record some data. For example:

  • The name of the item purchased (i.e example Eggs)
  • The unit price of the purchased item (i.e example 5$ per box of eggs)
  • The amount of that item which was purchased (i.e  2 boxes of eggs)
  • The cleric which was on duty while the sale was made (i.e John)

You will eventually end up with a spreadsheet that looks something like this:

Excel, Grocery List
Now while the information above is useful, but you will probably be interested in knowing the following as well:

  1. Which Item is selling the most
  2. Which cleric has the best performance
  3. How much money has been received by the sales of each item
  4. How much money has each cleric received from the sale of each item

This information can easily be obtained using a PivotTable.

Example 2: Consider another case where you have several employees in your office. Everyday their check in time and check out time is recorded:

Check In and Check Out Time for Employees, Excel Pivot Table

At the end of the month you probably want to know how many hours each employee has been working. This can be obtained using a PivotTable.


Step 1: Creating an Empty PivotTable:

The first step in creating a PivotTable report is creating an empty PivotTable. To create a PivotTable click on PivotTable on the Insert Ribbon:

Insert PivotTable Excel
A dialog will open requesting the source data and PivotTable location:

Creat PivotTable Dialog Excel
In this example I will use the grocery shop table as the PivotTable source. Note when selecting the source you must also select the column headers, in this case row 1:

PivotTable Source Data Excel

After selecting the PivotTable source and insertion point a blank PivotTable will be inserted at the selected location:

Blank PivotTable Excel


Step 2: Adding Fields to the PivotTable:

In the previous step an empty PivotTable was created. To actually generate a report you would need add fields to the PivotTable. In this example there are 5 fields to choose from :

  • Item
  • Unit Price
  • Units
  • Total Price
  • Cleric

After creating the PivotTable a PivotTable Field List will appear on the right side of the screen:

PivotTable Field List Excel

As you can see the PivotTable Fields List has detected the 5 fields in our table (Item, Unit Price, Units, Total Price, Cleric).

Note: In case the PivotTable Field List does not appear, click anywhere on the empty PivotTable:

Click On PivotTable, Excel

From the PivotTable Tools menu choose the Options Ribbon, then click on the Field List:

Excel, PivotTable, PivotTable Tools, Option, Field List

Example: I will explain how to add fields to the PivotTable Field List using the grocery shop example. Lets say we want to figure out how many units of each item has been sold:

  • The total number of eggs sold
  • The total number of apples sold
  • The total number of celery sold

We would need to add  the Item and Units fields to the field list. This can be done by clicking the checkbox next to those items:

Adding Items to PivotTable Field List Excel

After checking Item and Units from the field list, they were added to the Row Labels and Values area. I will explain those in step 3. By selecting these 2 items from the field list we have created a PivotTable. Below you can see the PivotTable we have created. It has 2 columns:

Column 1, Row Labels: In this column the name of the Items are listed (i.e apples, celery, eggs, ….). This is because the Item field was  placed in the Row Labels area.

Column 2, Sum of Units: This column shows the total number of each item which was sold:

  • Apples : 13 = 5 units  (row 4) + 2 units ( row 14) + 6 units(row 18)
  • Eggs: 6 = 2 units (row 2) + 4 units (row 19)

The reason the second column is calculating the sum of the items sold, is because the Units field was added to the Value area.
Pivot Table Excel


Step 3: Modifying the 4 Areas of the PivotTable

Excel, PivotTable, PivotTable, Column Labels, Report Filter, Row Labels and Values
The PivotTable has four different areas.

  • Report Filter
  • Column Labels
  • Row Labels
  • Values

I will explain them using 2 examples:

Example 1: Lets say we want to create the following report:

Example 1 PivotTable 2 values and Row

As you can see this report has 3 columns. The first column has the item names. The second column is the total number of units sold for each item. The third column has the average number of units sold for each item on each purchase. In the previous section I have explained how to create a PivotTable with the first 2 column. In this section I will explain how to add the 3rd column.

The first thing to do is to drag and drop the Units field in the Value area:

Add Field to Value Area, Pivot Table, Excel

PivotTable Excel, Value Area

After dragging the Units field to the Value area a new column will appear in the PivotTable, Sum of Units2. This new column is identical to the second column:

Excel, PivotTable, 3 Columns

There are 2 changes we need to make to the 3rd column:

  1. Change its name from “Sum of Units2” to something meaningful
  2. Change its calculation formula. Currently the 3rd column is calculating the exact same thing as column 2. Its calculating the total number of each item sold. We want the 3rd column to calculate the average number of items sold on each purchase.

In order to make these changes, click on the triangle next to Sum of Units2: Then click on Value Field Settings:

Excel, PivotTable, Value Field Setting

A dialog will open. In this dialog you can change the name of the field and the type of calculation the field does:

Excel, PivotTable, Value Field Settings
I will change the name to “Average Units Sold on Each Purchase” and the calculation type to “Average”:

Excel, PivotTable, Value Field Settings, Example

After making these changes the final result will be:

Example 1 PivotTable 2 values and Row

Example 2: In this example we want to create a report which displays how much each cleric has sold of each item (cash value). The final report will look like this:

Excel, PivotTable, Example 2

After creating an empty pivot table the first step is to add fields to the list. In order to create the report above we will need to add the following fields:

  • Items
  • Clerics
  • Total Price

Excel, Pivot Table, Fields Lists
After selecting those 3 fields from the field list, by default Excel will place the fields in the following places:

  • Item field: By default will be placed in the Row Labels area.
  • Cleric field: By default will be placed in the Row Labels area.
  • Total Prices field: By default will be placed in the Value area.

Obviously the default selected is incorrect. What we need is:

  • Item field: We want each row to be associated with one Item (i.e first row for apples, second row for celery, …). Therefore the current location of the Item field is correct.
  • Cleric field: Each column should be associated with one Cleric (i.e first column David, second column Ellon, ….). The current location for the Cleric field is incorrect. The Cleric field should be moved to the Column Labels area.
  • Total Prices fieldThe Total Price is the value to be calculated, therefore its position in the Value area is correct.

Moving the Cleric field to the Columns Label area could easily be achieved by dragging and dropping :

Excel, Pivot Table, Drag Drop Cleric Field to the Columns Label

Excel, Pivot Table Example 2 Field List
After making this change the PivotTable will update accordingly

Example 2 PivotTable, After Dragging Cleric To Column Area

In the report above the intersection of the name of each cleric and the name of each item is the amount (dollar value) of each item that each cleric has sold.

In order to change the number format so that the dollar sign ($) appears next to the numbers click the triangle next to the Sum of Total Price field. Then click on Value Field Settings:

Excel, PivotTable, Example2, Value Field Settings

Click on the number format button:

Excel, PivotTable, Value Field Settings Dialog Example 2

After clicking the Number Format button a dialog will open. Select the desired number format. In this case it would be the currency number format:

Excel, Pivot Table, Number Format Dialog, Value Field Settings
The final result can be seen below:

Excel, PivotTable, Example 2

You can download the file related to this article here.

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *