How to Create a Pivot Table in Microsoft Excel - Yahia El-Shall

By Yahia El-Shall

The last post discussed the utility of databases. Here we will go through how to create a pivot table from a data table in order to extract, manipulate, and analyze the data to our needs. 

Microsoft Excel's Pivot Table feature is an indispensable tool that allows us to extract, manipulate, and analyze data to our needs. Especially when dealing with large databases that have thousands of rows, pivot tables facilitate easy access to the data we need. Also, as data changes in the database, our pivot tables will update automatically. Thus, changing the way our data is summarized becomes much simpler.  We will be making a simple pivot table to summarize sales data from our previous simple database. 

We will be creating our pivot table in Microsoft Excel 2007. The steps do not differ significantly in Excel 2010 or 2013. 


By the time you finish reading this you will be able to:
  • Create your data table in the proper format
  • Product a pivot table from that data table
  • Manipulate the pivot table report to summarize your data as it suits your needs
Our data table includes order data for sales reps broken down by items sold, units of items sold, cost, and total. 

OrderDate Region Rep Item Units Unit Cost Total
1/6/14 East Jones Pencil 95            1.99     189.05
1/23/14 Central Kivell Binder 50          19.99     999.50
2/9/14 Central Jardine Pencil 36            4.99     179.64
2/26/14 Central Gill Pen 27          19.99     539.73
3/15/14 West Sorvino Pencil 56            2.99     167.44
4/1/14 East Jones Binder 60            4.99     299.40
4/18/14 Central Andrews Pencil 75            1.99     149.25
5/5/14 Central Jardine Pencil 90            4.99     449.10
5/22/14 West Thompson Pencil 32            1.99       63.68
6/8/14 East Jones Binder 60            8.99     539.40
6/25/14 Central Morgan Pencil 90            4.99     449.10
7/12/14 East Howard Binder 29            1.99       57.71
7/29/14 East Parent Binder 81          19.99  1,619.19
8/15/14 East Jones Pencil 35            4.99     174.65
9/1/14 Central Smith Desk 2        125.00     250.00
9/18/14 East Jones Pen Set 16          15.99     255.84
10/5/14 Central Morgan Binder 28            8.99     251.72
10/22/14 East Jones Pen 64            8.99     575.36
11/8/14 East Parent Pen 15          19.99     299.85
11/25/14 Central Kivell Pen Set 96            4.99     479.04
12/12/14 Central Smith Pencil 67            1.29       86.43
12/29/14 East Parent Pen Set 74          15.99  1,183.26
1/15/15 Central Gill Binder 46            8.99     413.54
2/1/15 Central Smith Binder 87          15.00  1,305.00
2/18/15 East Jones Binder 4            4.99       19.96
3/7/15 West Sorvino Binder 7          19.99     139.93
3/24/15 Central Jardine Pen Set 50            4.99     249.50
4/10/15 Central Andrews Pencil 66            1.99     131.34
4/27/15 East Howard Pen 96            4.99     479.04
5/14/15 Central Gill Pencil 53            1.29       68.37
5/31/15 Central Gill Binder 80            8.99     719.20
6/17/15 Central Kivell Desk 5        125.00     625.00
7/4/15 East Jones Pen Set 62            4.99     309.38
7/21/15 Central Morgan Pen Set 55          12.49     686.95
8/7/15 Central Kivell Pen Set 42          23.95  1,005.90
8/24/15 West Sorvino Desk 3        275.00     825.00
9/10/15 Central Gill Pencil 7            1.29         9.03
9/27/15 West Sorvino Pen 76            1.99     151.24
10/14/15 West Thompson Binder 57          19.99  1,139.43
10/31/15 Central Andrews Pencil 14            1.29       18.06
11/17/15 Central Jardine Binder 11            4.99       54.89
12/4/15 Central Jardine Binder 94          19.99  1,879.06
12/21/15 Central Andrews Binder 28            4.99     139.72

If this data table was comprised of thousands of rows, using formulas would not be an efficient way to summarize extracted data. Pivot tables can provide different combinations of data much more simply.

How to organize your data 

First, your data table must have column headings. Column headings should accurately describe the data listed in the column. When we create our pivot tables, these column headings will assist us in organizing our extracted data.

Next, your data table cannot have empty rows or columns. If formatted as a data table and not just a range, make sure your table does not have empty cells. 

Ensure that your data corresponds with the appropriate column heading and row.

Insert a blank Pivot Table

1. Select an empty cell to insert your pivot table. 

2. Click on the Insert menu and click the PivotTable button.
yahia el shall

3. The following dialog box will appear:
Excel, How to create a PivotTable, Create Pivot Table dialog box

4. Select your data table either by clicking and scrolling over the data table with your mouse, or if you have named your table, input the name of the table. You can choose where to insert the pivot table.

5. Once finished, click OK. A blank pivot table will have been inserted.

Modifying the layout of your Pivot Table

Below is what your blank pivot table should look like. How do we populate it with data? To build a report, choose fields from the pivot table field list.
Excel, How to create a PivotTable, blank pivot table report

The field list should show the column headings from your data table.

yahia el shall

1.Select the fields you want included in your table.

2. Place the fields in the correct location for the layout you want. (report filter, column labels, row labels, or values.

3. In this report, we have selected Rep for the Row labels, Item for the column labels, and total revenue for the values.

4. The pivot table generated from these selections looks like this: 

yahia el shall

Now we see the total revenues / item for each of our sales reps. This is just one example of how we can summarize our data. Again, it depends on how you want to summarize and eventually analyze your data. You can change the layout of your pivot table from tabular under the Design tab. 

In the next post, we'll take a look at how to build on your use of pivot tables with pivot charts.