Creating Pivot Tables

Contact Us or call 1-877-932-8228
Creating Pivot Tables

Creating Pivot Tables

Preparing Your Data

Before inserting a pivot table, make sure the data you want to analyze is in an organized table. Things to consider include:

  1. Header row. Make sure your data includes a header row because the top row of your table will serve as the field names in your pivot table.
  2. No empty rows or columns. If your data includes empty rows or columns, Excel will not treat all your data as one table.
  3. No subtotals. Remove all subtotals before creating your pivot table.

Inserting a Pivot Table

To insert a pivot table in Microsoft Excel:

  1. Select any cell in the set of data you want to analyze.
  2. On the INSERT tab, in the Tables group, click the PivotTable command (and in Excel 2007 and 2010, then select PivotTable):PivotTable Command
  3. In the Create PivotTable dialog box, verify that Excel has selected the correct range, select where you want the pivot table to show up (you will almost always want to select New Worksheet), and click OK:Click OK
  4. In the PivotTable Field List, which opens up on the right (left in Excel 2007/2010) of the worksheet in which your pivot table will be located, drag and drop fields from your data into the Report Filter (Page fields go here), Column Labels (Column fields go here), Row Labels (Row fields go here), and Values (Data fields go here) boxes:PivotTable Field List
    1. In the image below, fields have been dragged into the boxes as the first step in creating the report shown at the beginning of this lesson:Image
  5. Excel assumes you want to sum the values of the Data field (the field in the Values box). To perform a different calculation such as Count or Average:
    1. Click the field name and select Value Field Settings:Value Field Settings
    2. Select the calculation you want to perform and click OK:Select Calculation
  6. To change the format of the numbers in the pivot table:
    1. Click the field name and select Value Field Settings:Field Name
    2. Click Number Format:Number Format
    3. In the Format Cells dialog box, select the number format category, the number of decimal places to show and whether or not to display a comma (if applicable), and then click OK:Format Cells Dialog Box
    4. In the Value Field Settings dialog box, click OK:Value Field Settings