facebook twitter
Webucator's Free Advanced Microsoft Excel Tutorial

Lesson: Using Pivot Tables

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

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

Lesson Goals

  • Learn to use pivot tables to analyze data.
  • Learn to edit pivot tables.
  • Learn to format pivot tables.
  • Learn to insert pivot charts.
  • Learn to use Slicers.

A pivot table is an interactive table 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:Options

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 2010, then select PivotTable):PivotTable
  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:Create PivotTable  Dialog Box
  4. In the PivotTable Field List, which opens up on the right (left in Excel 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:Values
    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:Report
  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 Field Settings:Field Settings
    2. Select the calculation you want to perform and click OK:OK Option
  6. To change the format of the numbers in the pivot table:
    1. Click the field name and select Field Settings:Field Settings
    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
    4. In the Field Settings dialog box, click OK:Field Settings Dialog Box

Creating a PivotTable Timeline

You can now use the PivotTable Timeline feature to filter your data by time.

To create a PivotTable Timeline:

  1. Click the PivotTable.
  2. From the PivotTable Tools Analyze tab, in the Filter group, select Insert Timeline. Insert Timeline
  3. In the Insert Timeline dialog box, check the check box of the date fields you want in the timeline. Daet of Sale
  4. The timeline is displayed. To filter by date, click the arrow next to the time section and make a selection. Months
  5. Drag the scrollbar to see the filtered data. Navigate
  6. To see a specific time period, click and drag the timeline handles. Detail Navigate
  7. Click the Clear Filter button to clear the timeline. Clear Filter

More PivotTable Functionality

More PivotTable functionality includes the following:

  • Searchable PivotTable fields: You can use the PivotTable Fields navigation pane to search for fields within the table. Pivot Options
  • Custom measures: You can use the PivotTable fields navigation pane to create and edit custom measures.
  • PivotChart drill-down menu: You can use the drill-down menu in PivotCharts to more easily work with your data. Active Field

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 2010), in the Filter group (Sort & Filter group in Excel 2010), click the Insert Slicer command (and then select Insert Slicer in Excel 2010):Insert Slicers
  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:Insert Slicers Dialog Box
  4. To use the Slicer, simply select one of the fields in the Slicer to filter your data by that field:Filter Data

Multi-Select Option in Slicers

You can select multiple items in a slicer, using the Multi-Select option.

Multi-Select Slicer Option

PivotTable Enhancements

Excel 2019 provides many enhancements to PivotTables:

  1. Personalized layout: You can set default options for PivotTables that will be saved for future PivotTables.
  2. Relationship detection: Excel now automatically detects relationships in the tables in a workbook.
  3. Fields list updates: You are now able to create, edit, and delete custom measures using the Fields list.
  4. Time grouping: Excel will group time-related fields by detecting them automatically and then grouping them.
  5. Automatic renaming: Smart renaming allows you to make an update to tables or columns and then Excel will automatically update related data.

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. Group Option
  3. The data appears as a new group. 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. PowerPivot
  5. The PowerPivot tab now appears on the Ribbon.

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.

Excel 2019 provides some enhancements to PowerPivot:

  • You can save the diagram view as an image by selecting File > Save View As Picture.
  • The Edit Relationships dialog box now allows you to add or edit a table relationship while looking at a data sample of it. You can also use this dialog box and type the first letter of column name to move the first column starting with the letter selected.

Managing Relationships

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 Option
  2. In the Manage Relationships dialog box, select New. New Option
  3. In the Create Relationship dialog box, enter the tables and columns you want to be related, and click OK. Create Relationship Dialog Box
  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 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 and follow along as your instructor demonstrates additional pivot table functionality, including:

  1. How to Sort, Filter, Move, Remove, and format fields by right-clicking on them:PivotTable Option
  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:Filter Data
  5. Some of things you can do from the PivotTable Tools Analyze tab:PivotTable Analyze Tab
  6. Some of things you can do from the PivotTable Tools Design tab:PivotTable Tools Design Tab

Creating a Standalone PivotChart

In previous versions of Excel, a PivotChart needed to be associated with a PivotTable; however, starting with Excel 2013 and continuing you can create a standalone PivotChart.

To create a standalone PivotChart:

  1. Click in your data.
  2. Select the Insert tab and, in the Charts group, select Recommended Charts. When you find a chart you would like to select, click Ok.Recommend Charts
  3. On the Recommended Charts tab, select a chart with the PivotChart icon and view the preview. Choose a chart
  4. The PivotChart is displayed. Chart

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 Excel2019.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:Table Image
  3. Insert a pivot chart that looks like the chart below:Chart Image
  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:Table Image
  5. Insert Slicers to analyze sales by salesperson and product in addition to industry.

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 2010):Table range
    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:Pivot Choices
    5. Click Sum of Sale Amount and select Value Field Settings:Value Field Settings
    6. Click Number Format:Number Format Option
    7. In the Format Cells dialog box, select Currency, change the number of decimal places to "0", and click OK:Set Decimal Places
    8. In the Value Field Settings dialog box, click OK:Value Field Settings Dialog Box
  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 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 chart that looks like the chart below:Chart
    1. Select any cell within the PivotTable.
    2. On the PivotTable Tools > Analyze tab, in the Tools group, click PivotChart. Choose PivotChart
    3. In the Insert Chart dialog box, click Pie from the list of chart types, and then click OK.PivotChart. Insert Chart
  4. 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 2010) - 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:PivotTable Select
    3. In the PivotTable Field List, drag and drop Industry into the Rows box (Row Labels box in Excel 2010), Calendar Year into the Columns box (Column Labels box in Excel 2010), and Sale Amount into the Values box:Values in Boxes
    4. Click Sum of Sale Amount and select Value Field Settings:Value Field Settings
    5. Click Number Format:Value Field Settings
    6. In the Format Cells dialog box, select Currency, change the number of decimal places to "0", and click OK:Set Decimals
    7. In the Value Field Settings dialog box, click OK:OK Option
  5. 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 2010), in the Filter group (Sort & Filter group in Excel 2010), click the Insert Slicer command (and select Insert Slicer in Excel 2010):Slicers Command
    3. In the Insert Slicers dialog box, check the Product and Salesperson check boxes and click OK:Slicer Options
    4. To use the Slicer, simply select one of the fields in either Slicer to filter your data by that field.