Pivot Table Tutorial Part 2 - Create an Excel Pivot Table


This page shows you how to create an Excel Pivot Table. The instructions on this page apply to Excel 2010 or Excel 2007. If you have an earlier version of Excel, go to the page on how to create a pivot table in Excel 2003.

We will start with a basic pivot table, using an example spreadsheet of company sales figures obtained during the first quarter of 2013. This spreadsheet, which was introduced in Part 1 of this tutorial, is repeated below:

  A B C D E
1 Date Invoice Ref Amount Sales Rep. Region
2 01/01/2013 2013-0001 $819 Barnes North
3 01/01/2013 2013-0002 $456 Brown South
4 01/01/2013 2013-0003 $538 Jones South
5 01/01/2013 2013-0004 $1,009 Barnes North
6 02/01/2013 2013-0005 $486 Jones South
7 02/01/2013 2013-0006 $948 Smith North
8 02/01/2013 2013-0007 $740 Barnes North
9 03/01/2013 2013-0008 $543 Smith North
10 03/01/2013 2013-0009 $820 Brown South
11 .
.
.
.
.
.
.
.
.
.
.
.
.
.
.


We will first create a very simple pivot table, which shows the total sales for each of the four sales reps in the above spreadsheet.

1.

Select any cell within the data range or select the entire data range to be used in your Pivot Table.

Note: If you select a single cell in the data range, Excel will automatically identify, and select the whole data range for your Pivot Table. In order for Excel to successfully do this, the following must be satisfied:

-   Each column in the data range has a unique header

and

-   The data does not contain any blank rows

2.

Click on the Pivot Table button, which is generally (in Excel versions 2010 or 2007) located on the left, within the 'Insert' tab.

Excel 2010 Pivot Table Select Button
3.

You will be presented with the window entitled 'Create PivotTable' (shown on the right).

Make sure that the selected range is the range that you want to use for your Pivot Table.

There is also an option asking where you want the Pivot Table to be placed. If you are not sure, select the option 'New worksheet'

Click OK

Excel 2010 Create Pivot Table Window
4.

You will now be presented with an empty Pivot Table, and a 'Pivot Table Field List', which contains several data fields. Note that these are the headers from your initial data spreadsheet.

Within this 'Pivot Table Field List':

  • Drag the 'Sales Rep.' field into the area of the 'Pivot Table Field List' marked 'Row Labels'.
  • Drag the 'Amount' field into the area of the 'Pivot Table Field List' marked 'Σ Values'.
  • Check: Make sure that the value in the 'Σ Values' section of the pivot table reads "Sum of Amount" and not "Count of Amount"
Excel 2010 Pivot Table Field List

In this example, the values in the 'Amount' column are all numeric and so the 'Σ Values' section will default to calculating the "Sum of Amount".

However, if you have non-numeric or blank values in the 'Amount' column of your original data sheet, your pivot table may default to displaying the "Count of Amount" instead of the "Sum of Amount". If this happens, you can change this to sum the values as follows:

  • Within the 'Σ Values' box, click on the text "Count of Amount" and select the option Value Field Settings....
  • Within the 'Summarise Values By' tab, select the option Sum.
  • Click OK.
5.

Your Pivot Table will be populated with the total sales for each sales rep, as shown on the right.

If you want the sales to be displayed as a currency, this is done by formatting the cells containing these values. To do this:

  • Select the column containing the cells to be formatted.
  • Right click on this column with the mouse and select the option Format Cells ...
  • A window will pop up. Ensure the Number tab of this window is selected
  • From the list of data types, select Currency, and then, from the options that appear on the right, select the currency type and the number of decimal places that you want to display.
  • Click OK

Your final Pivot Table will be as shown on the right.

Pivot Table Before Formatting:
Excel 2010 Pivot Table


Pivot Table After Currency Formatting:
Excel 2010 Pivot Table
Go To Excel Pivot Table Tutorial Part 3 - Group a Pivot Table in Excel

Return to the Excel Pivot Table Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net