facebook google plus twitter
Webucator's Free Advanced Microsoft Excel 2013 Tutorial

Lesson: Using Pivot Tables

Welcome to our free Advanced Microsoft Excel 2013 tutorial. This tutorial is based on Webucator's Advanced Microsoft Excel 2013 Training course.

In this lesson, you will learn to work with pivot tables.

Lesson Goals

  • Use pivot tables to analyze data.
  • Edit pivot tables.
  • Format pivot tables.
  • Insert pivot charts.
  • Use Slicers.

What Are Pivot Tables?

Pivot tables are interactive tables that can be used to analyze, organize, and summarize large amounts of data. Pivot tables make it easy to rearrange data, quickly displaying it in different ways. Pivot tables usually have four types of fields:

  1. Page field (Report Filter). Use the Page field to filter data by a specific field.
  2. Column field (Column Labels). Use the Column field to display a field from the source data as column headings.
  3. Row field (Row Labels). Use the Row field to display a field from the source data as row headings.
  4. Data field (Values). The Data field is the field from the source data that you want to summarize or analyze.

In the following image, the pivot table shows the average price of homes in different towns based on the number of bedrooms. At the top is a Page field which can be used to show only houses with a specific number of bathrooms:Page Field

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

Inserting Slicers

Slicers make it really easy to filter data in a pivot table. Adding a Slicer is similar to adding a second Page field (Report Filter), but it's even easier to use.

To add a Slicer to a pivot table:

  1. Select any cell in the pivot table.
  2. On the PIVOTTABLE TOOLS ANALYZE tab (PivotTable Tools Options tab in Excel 2007/2010), in the Filter group (Sort & Filter group in Excel 2007/2010), click the Insert Slicer command (and then select Insert Slicer in Excel 2007/2010):Insert Slicer
  3. In the Insert Slicers dialog box, check the field or fields (Yes, you can create multiple slicers at once!) to use to "slice" your data and click OK:Check Fields
  4. To use the Slicer, simply select one of the fields in the Slicer to filter your data by that field:Select a Field

Working with Pivot Tables

Grouping Data

You can group data in a Pivot Table to show a subset of your data

To group data:

  1. In the Pivot Table, select the data you want to group.
  2. Right-click and select Group. Select Group
  3. The data appears as a new group. Data Appears as New Group

Using PowerPivot

PowerPivot is an add-on that you can use in Excel 2013 to perform more powerful data analysis.

To add PowerPivot:

  1. Select the FILE menu tab, and then select Options.
  2. In the Excel Options dialog box, on the right, select Add-Ins. Add-Ins
  3. From the Manage drop-down list, select COM Add-ins and then click Go. COM Add-ins
  4. Check the Microsoft Office PowerPivot for Excel 2013 check box and click OK. Check Box
  5. The POWERPIVOT tab now appears on the Ribbon. POWERPIVOT Tab

Once PowerPivot is installed, you can launch the PowerPivot window by selecting Manage from the POWERPIVOT tab. Here you can import and manage external data.

Managing Relationships

A new feature of Excel 2013 is the Relationships option on the DATA tab, in the Data Tools group.

To manage relationships in Pivot Tables, you must have at least two tables in your worksheet. Then, to create a relationship:

  1. From the DATA tab, select Relationships. Relationships
  2. In the Manage Relationships dialog box, select New. Manage Relationships
  3. In the Create Relationship dialog box, enter the tables and columns you want to be related, and click OK. Create Relationship
  4. The information now appears in the Manage Relationships dialog box. Manage Relationships Dialog Box

Inserting Pivot Charts

Pivot charts are simply charts that use the data in your pivot table. Pivot charts make it easy to visualize your data.

To insert a pivot chart:

  1. Select any cell in the pivot table.
  2. On the PIVOTTABLE TOOLS ANALYZE tab (PivotTable Tools Options tab in Excel 2007/2010), in the Tools group, click the PivotChart command:PivotChart Command
  3. In the Insert Chart dialog box, select the type of chart to insert and click OK:Insert Chart Dialog Box

More Pivot Table Functionality

Pivot tables are one of the most powerful and useful tools in Excel. Watch the presentation that follows this reading. It will demonstrate additional pivot table functionality, including:

  1. How to Sort, Filter, Move, Remove, and format fields by right-clicking on them:Format Fields
  2. How to change or update the data source.
  3. How to Refresh the pivot table.
  4. How to filter the data in the pivot table using the filter drop-down arrows:Drop-Down Arrows
  5. Some of things you can do from the PivotTable Tools Options tab:PivotTable Tools Options
  6. Some of things you can do from the PivotTable Tools Design tab:PivotTable Tools Design

Working with Pivot Tables

Duration: 15 to 25 minutes.

In this exercise, you will practice using pivot tables, pivot charts, and Slicers.

  1. Open PivotTables.xlsx from your Excel2013.3/Exercises folder.
  2. Insert a pivot table in a new worksheet showing total sales by company. Format the numbers as Currency and show zero decimal places. The pivot table should look like the following image:PivotTable
  3. Insert a pivot chart that looks like the chart below:Chart
  4. Insert a pivot table in a new worksheet showing total sales in each calendar year by industry. Format the numbers as Currency and show zero decimal places. The pivot table should look like the following image:PicotTable
  5. Insert Slicers to analyze sales by salesperson and product in addition to industry.Insert Slicers

Solution:

  1. To insert a pivot table showing total sales by company and to format the numbers as Currency and show zero decimal places:
    1. Select any cell in the table (cells A1:H62).
    2. On the INSERT tab, in the Tables group, click the PivotTable command (and then select PivotTable in Excel 2007/2010):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 (select New Worksheet), and then click OK:Create PivotTable Dialog Box
    4. In the PivotTable Field List, drag and drop Company into the Row Labels box and Sale Amount into the Values box:PivotTable Field List
    5. Click Sum of Sale Amount and select Value Field Settings:Sum of Sale Amount
    6. Click Number Format:Number Format
    7. In the Format Cells dialog box, select Currency, change the number of decimal places to "0", and click OK:Format Cells Dialog Box
    8. In the Value Field Settings dialog box, click OK:Value Field Settings
  2. To insert the pivot chart showing total sales by company:
    1. Select any cell in the pivot table.
    2. On the PIVOTTABLE TOOLS ANALYZE tab (PivotTable Tools Options tab in Excel 2007/2010), in the Tools group, click the PivotChart command:PivotChart Command
    3. In the Insert Chart dialog box, select Pie charts, select the first pie chart, and then click OK:Pie Chart
  3. To insert a pivot table showing total sales in each calendar year by industry and to format the numbers as Currency and show zero decimal places:
    1. Select the sheet named "Data" and then select any cell in the table (cells A1:H62).
    2. On the INSERT tab, in the Tables group, click the PivotTable command (and then select PivotTable in Excel 2007/2010):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 (select New Worksheet), and click OK:Create PivotTable Dialog Box
    4. In the PivotTable Field List, drag and drop Industry into the ROW box (Row Labels box in Excel 2007/2010), Calendar Year into the COLUMN box (Column Labels box in Excel 2007/2010), and Sale Amount into the VALUES box:VALUES Box
    5. Click Sum of Sale Amount and select Value Field Settings:Value Field Settings
    6. Click Number Format:Number Format
    7. In the Format Cells dialog box, select Currency, change the number of decimal places to "0", and click OK:Format Cells Dialog Box
    8. In the Value Field Settings dialog box, click OK:Value Field Settings Dialog Box
  4. To insert Slicers to analyze sales by salesperson and product in addition to industry:
    1. Select any cell in the pivot table.
    2. On the PIVOTTABLE TOOLS ANALYZE tab (PivotTable Tools Options tab in Excel 2007/2010), in the Filter group (Sort & Filter group in Excel 2007/2010), click the Insert Slicer command (and select Insert Slicer in Excel 2007/2010):Insert Slicer Command
    3. In the Insert Slicers dialog box, check the Product and Salesperson check boxes and click OK:Insert Slicers Dialog Box
    4. To use the Slicer, simply select one of the fields in either Slicer to filter your data by that field.