Pivot table is a powerful tool that can help you summarize and organize your data in an efficient manner. In this tutorial, I’ll use a simple example to show you how to create a pivot table in MS Excel 2016.
Example of a Pivot Table in MS Excel 2016
To start, let’s say that you have a small company with 5 employees. Your 5 employees 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 definitely use a Pivot table to get that info in an easy manner. In the next section, we’ll see how to apply different Pivot tables based on the above scenarios.
Note that we used a small sample of data, but the same principles apply to much larger data-sets. In those cases, using a pivot table will save you a lot of time and effort!
Create a Pivot Table in MS Excel 2016
Let’s now look at the steps to create a Pivot table in MS Excel 2016:
(1) First, select the table above and then copy it into a new spreadsheet in Excel
(2) Now, press the Insert tab, and then click on the icon PivotTable:
(3) You’ll now need to click on the ‘Up Arrow’ (so that you can select the data-set 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 that new worksheet. Then, press OK:
A new spreadsheet will now open, where you can select your pivot table fields:
In the following section, we’ll review 3 examples to pivot our data-set.
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 field ‘Name of Employee’ to the Rows box on the right-hand-side of your screen.
You’ll notice that the names of all the employees would now appear in the pivot table, created on the top left-hand-side of the screen:
(2) Then, drag the field ‘Sales’ 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 look at the total sales by country.
But before you proceed, you’ll need to remove the ‘Name of Employee’ field from the pivot table. To do so, simply uncheck the ‘Name of Employee’ field:
Finally, drag the field ‘Country’ 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 uncheck the ‘Country’ field, and then check the ‘Name of Employee’ field:
Now, press on the ‘Sum of Sales’ field under the ∑Values box. Then, from the drop-down menu, select Value Field Settings…
Select Max, and then press OK:
The Max 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 to promote Jon for his hard work!
We have seen how to create a Pivot table in MS Excel 2016. Similar principles will apply if you’re using an older version of Excel.
Note that you can perform a variety of arithmetic and statistical operations using your Pivot table (not just the sum or maximum).
You can select diffident operations (such as average or count) under the Value Field Settings that we 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.