How to Create a Pivot Table in Excel

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

How to Create a Pivot Table in Excel

(3) Click on the ‘Up Arrow’ in order to select the dataset that you want to pivot:

Create PivotTable

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

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

New Worksheet

You’ll now see the new spreadsheet, 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 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:

PivotTable Fields

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:

Table with data

(2) Then, drag the ‘Sales’ field to the ∑Values box:

How to Create a Pivot Table in Excel

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:

Pivot Table in Excel

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:

Check box

Finally, drag the ‘Country’ field into the Rows box.:

How to Create a Pivot Table in Excel

Your pivot table will now display the sales by country:

Example of a table

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:

Pivot Table in Excel

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

Value Field Settings

Select Max, and then click on OK:

Max - Value Field Settings

The maximum sale for each employee will appear as follows:

Pivot Table

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:

Value Field Settings

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.