How to Create a Pivot Table in MS Excel 2016

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 EmployeeSalesQuarterCountry
Jon10001US
Mark3001Japan
Tina4001Brazil
Maria5001UK
Bill8001US
Jon10002Brazil
Mark5002Japan
Tina7002Brazil
Maria502US
Bill602US
Jon10003US
Mark9003Japan
Tina7503Brazil
Maria2003UK
Bill3003Brazil
Jon10004Japan
Mark9004Japan
Tina2504Brazil
Maria7504UK
Bill504US

 

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:

 

Paste a table into MS Excel 2016

 

(3) You’ll now need to click on the ‘Up Arrow’ (so that you can select the data-set that you want to pivot):

 

How to create a pivot table in MS Excel 2016

 

(4) Select all the cells that contain your table (including the cells that contain the column names), and then press on the ‘Down Arrow’:

 

select the cells of your pivot table

 

(5) Choose a ‘New Worksheet’ in order to display your pivot table on that new worksheet. Then, press OK:

 

populate your pivot table on a new sheet

 

A new spreadsheet will now open, where you can select your pivot table fields:

 

pivot tables fields for MS Excel 2016

 

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:

 

Add new fields to a pivot table in MS Excel 2016

 

(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:

 

Add value field into a Pivot table in MS Excel 2016

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:

 

uncheck fields in pivot table

 

Finally, drag the field ‘Country’ into the Rows box. Your pivot table will now display the sales by country:

 

Pivot Table in MS Excel 2016

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:

 

check fields in pivot tables in MS Excel 2016

 

Now, press on the ‘Sum of Sales’ field under the ∑Values box. Then, from the drop-down menu, select Value Field Settings…

 

Value field settings in Pivot tables

 

Select Max, and then press OK:

 

Max in Pivot Table in MS Excel 2016

 

The Max sale for each employee will appear as follows:

 

Max function in the Pivot Table

 

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!

Conclusion

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:

Sum values - Pivot Table in MS Excel 2016

 

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.