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


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!


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.