How To Create a Pivot Table in Excel

Pivot Tables are one of Excel’s most powerful and time-saving features. Instead of manually entering and formatting figures, you can simply create a pivot table which will allow you to manipulate the data to your heart’s content.  Pivot Tables are an extremely efficient way to create reports and this article will describe the basics behind creating one and answer the question of how to create a pivot table in excel.

First Step – Gathering Data

The first step in creating a Pivot Table is to gather the information you want to analyze and think of a goal the report should accomplish.  Analysts use Pivot Tables for just about everything: calculating sales figures for individual sales reps or regions, determining the number of specific products that were sold in each month of different years, state and country populations by year (which will be used in this example), are just a few.  Once you have the data in a spreadsheet, the fun begins.  Below is the data I used (click the image to enlarge).

How to create a pivot table in excel – Creating the Table

Once the data is in Excel, select any cell within its boundaries and click the Insert tab, then the PivotTable drop down arrow, and finally PivotTable.

You will then be prompted to select the range of data you would like to include in the table, and will have to choose where you want the PivotTable to be placed (click image to enlarge).

How to create a pivot table in excel – Selecting Fields

This step is when you can actually start analyzing the data: choose the fields (the headings of your columns) that you want to add to your report.  In this example my options are State, Region, and 2016 Population.  Because my goal of creating this report is to determine 2016 population by region, I would drag Region as my Row Label, and 2016 Population as the value. See below:

After selecting the fields, you should have a useable which will look like this:

Depending on your intended use of the Pivot Table, you change switch around the fields to create different outcomes.  See below for variations (click image to enlarge).

Variation 1

Variation 2

 Number Formatting

After creating the table, the data within should be formatted.  To do this, right-click within the PivotTable on the value you want to change and select Value Field Settings then click Number Format.  From here you can change the way your numbers are formatted.

How to create a pivot table in excel – Summary

Hopefully this article, how to create a pivot table in excel, gave you some basic insight on how to create and manipulate a Pivot Table – more articles will follow covering advanced techniques.  Please leave a comment with any questions or suggestions.

Check out this section of our site for more excel tips and tricks!