Pivot table is a powerful tool that can help you summarize and organize your data in an efficient manner. In this guide, I’ll review a simple example with the steps needed to create a pivot table in Excel.
Example of a Pivot Table in Excel
To start, let’s say that you have a small company with 5 employees, who sell a product across different countries.
You then decided to break your product sales into four quarters:
|Name of Employee||Sales||Quarter||Country|
You may now want to derive some meaningful information from the data above. Examples include:
1. Total sales per employee, throughout the entire year (4 quarters)
2. Total sales by country
3. The employee who made the highest sale
You can then use a Pivot table to derive the above info in an easy manner.
Steps to Create a Pivot Table in Excel
Let’s now look at the steps to create a Pivot table in Excel:
(1) First, select the table above and then copy it into a new spreadsheet in Excel
(2) Next, click on the Insert tab, and then press on the PivotTable icon:
(3) Click on the ‘Up Arrow’ in order to select the dataset that you want to pivot:
(4) Select all the cells that contain your table (including the cells that contain the column names), and then press on the ‘Down Arrow’:
(5) Choose a ‘New Worksheet’ in order to display your pivot table on a new worksheet. Then, press OK:
You’ll now see the new spreadsheet, where you can select your pivot table fields:
In the following section, we’ll review 3 examples to pivot our dataset.
Example 1: Total Sales per Employee
Let’s see how you can generate a pivot table that shows the total sales per employee:
(1) First, drag the ‘Name of Employee’ field to the Rows box on the right-hand-side of your screen:
You’ll notice that the names of all the employees would appear in the pivot table, created on the top left-hand-side of the screen:
(2) Then, drag the ‘Sales’ field to the ∑Values box:
You’ll notice that the total sales associated with each employee are now displayed in the Pivot table.
Also note that the Grand Total sum, across all employees, will appear at the bottom of your pivot table:
Example 2: Total Sales by Country
Let’s now look at the total sales by country.
But before you proceed, you’ll need to uncheck the ‘Name of Employee’ field from the pivot table:
Finally, drag the ‘Country’ field into the Rows box.:
Your pivot table will now display the sales by country:
Example 3: Max Sales
Finally, let’s get the employee who made the highest sale.
Firstly, you’ll need to check the ‘Name of Employee’ field, and then uncheck the ‘Country’ field:
Next, press on the ‘Sum of Sales’ field under the ∑Values box. Then, from the drop-down menu, select the Value Field Settings…
Select Max, and then click on OK:
The maximum sale for each employee will appear as follows:
In our example, we can see that Jon is the employee who made the highest sale.
You may consider promoting Jon for his hard work!
Note that you can perform a variety of arithmetic and statistical operations using your Pivot table (not just the sum or maximum).
For example, you can select different operations (such as the Average or Count) under the Value Field Settings that you saw earlier:
Try to experiment using different operations, and check the results under your Pivot table.
You may also want to check the following source for additional tutorials on MS Excel.