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:
- What is a PivotTable and What is it Used for?
- Step 1: Creating an Empty PivotTable
- Step 2: Adding Fields to the PivotTable
- Step 3: Modifying the 4 Areas of the PivotTable
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:
Now while the information above is useful, but you will probably be interested in knowing the following as well:
- Which Item is selling the most
- Which cleric has the best performance
- How much money has been received by the sales of each item
- 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:
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:
A dialog will open requesting the source data and PivotTable location:
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:
After selecting the PivotTable source and insertion point a blank PivotTable will be inserted at the selected location:
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:
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:
From the PivotTable Tools menu choose the Options Ribbon, then click on the 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:
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.
Step 3: Modifying the 4 Areas of the PivotTable
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:
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:
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:
There are 2 changes we need to make to the 3rd column:
- Change its name from “Sum of Units2” to something meaningful
- 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:
A dialog will open. In this dialog you can change the name of the field and the type of calculation the field does:
I will change the name to “Average Units Sold on Each Purchase” and the calculation type to “Average”:
After making these changes the final result will be:
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:
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
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 field: The 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 :
After making this change the PivotTable will update accordingly
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:
Click on the number format button:
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:
The final result can be seen below:
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