PivotTable

What is a PivotTable?

A PivotTable is a summary created from a worksheet (source data) in a report form that summarizes and analyzes large amounts of data. The report displays data in an interactive format which allows you to view data in a way that makes large amounts of data understandable without making irreversible changes to the original worksheet.

PivotTable Environment

PivotTable Field List- top right section displays the fields, check a field or drag and drop it to the lower portion of the list.
PivotTable Field List areas- lower right section identifies how and where the data shows in the PivotTable. A field can be displayed either a column or row. Data can be counted, totaled, averaged, and so on by dragging into the Values area.
PivotTable Report- The blue outlined area is the result of your selections from the Field List. Row and Column fields usually contain text values, where a data fields usually contains numeric data.

Screenshot of a window with Microsoft Exel in it. There is text to the left of the screenshot that points to rows 2-9 and the text says Row Fields. To the right, theres text that says Field List and it points to the PivotTable Field options on the right side of the screen.

PivotTable Tools

Some commands are available only when completing a task, in the examples below, the task is a PivotTable Report.

Analyze Tab

Screenshot of Microsoft Excel menu options. Across the top is a gray bar. In the top right there is a green Excel icon (green book with an "x" on it), a purple floppy disk icon, a blue curved arrow pointing left with a black triangle pointing down, a grayed out curved arrow pointing right, a hand with a finger pointing to a orb with a black triangle pointing down, and a black triangle with a horizontal line across the top. In the middle of the gray bar, there is text that says PivotPractice file - Excel. Further over to the right is text that says PIVOTTABLE TOOLS. To the right, there is a question mark icon, a box with an arrow on it, a thin gray line, a box with a gray header line across the top, and a "x" icon. Below that there are header options that read: FILE (with a black background), HOME, INSERT, PAGE LAYOUT, FORMULAS, DATA, REVIEW, VIEW, ACROBAT, ANALYZE, and DESIGN. The Analyze tab is open. The first icon in the window is a white square with two overlapping squares and text that reads PivotTable with a black triangle pointing down. There is a vertical line that separates it from the next section. The next section to the right has text that reads Active Field: Sum of Sales. Below that, it says Field Settings. To the right is a black arrow pointing down and text that reads Drill Down. To the right of that is a black arrow pointing up with text that says Drill up. To the right is an icon with a plus icon next to a ruler. Below that is a minus icon next to a ruler. Below this section, the text says Active Field. There is a vertical line separating this section from the next section. The next section is all grayed out. The first option is Group Selection (with an arrow pointing to the right); Ungroup (with two boxes overlapping); and Group Field (with overlapping documents). Below this entire section is text that reads Group. There is a vertical line separating this section from the next section. At the top of the next section, there is a table with a cone icon and text that says Insert Slicer. Below that, is an icon with a long cell and a cone-like icon and text that reads Insert Timeline. Below that, there is a grayed out icon and text. The icon has two overlapping squares and text that says Filter Connections. The entire section has text underneath that says Filter. There is a line between this section and the next. In the next section there are two icons. The first is a white document icon with the top right corner folded and a green arrow icon. The text underneath is Refresh with a black triangle pointing down. To the right, there is a paper with a table on it in an icon. Below that is text that reads Change Data Source with a black triangle pointing down. Below these two icons is text that reads Data. There is a line between this section and the next. The first icon is a table with a red pencil and text that says Clear with a black triangle pointing down. Below that is a table icon with a cursor. The text next to it says Select with a black triangle pointing down. Below that, there is a table icon with a blue arrow pointing right and text that reads Move Pivottable. Below this section there is text that reads Actions. There is a horiztonal line between this section and the next. The next section shows a document icon with a lowercase "f" and a lowercase "x" on it. The text to the right of it says Fields, Items, & Sets with a black triangle pointing down. Below that, the next two icons and text are grayed out. The first icon is a document with lines on it with lowercase "f" and lowercase "x" and text that reads OLAP Tools with a black triangle pointing down. Below that, there are three square icons with lines connecting them. There is text to the right that says Relationships. The entire section has text underneath that says Calculations. There is a vertical line between this section and the next. The first icon in the next section has a blue vertical bar, a yellow vertical bar, and a red vertical bar with text that says PivotChart. To the right, is an icon that looks like a document with a gray horizontal bar and a gray vertical bar with a blue question mark on top. The text below is Recommend PivotTables. The text below this section reads Tools. There is a vertical line between this sectoin and the next. The first icon is a rectangle with graph-type lines on it and text that says Field List. Below that are squares with a plus and a minus on it. To the right is text that says "+/-" Buttons. Below this is a chart with a blue header in an icon. To the right, is text that says Field Headers. Below this, there is text that says Show. There is also a vertical line after this section. To the right of the line is a triangle pointing upwards.

Design Tab

The tab is divided into three groups: select any cell in the PivotTable and click on the Design tab.

Layout Group- add subtotals and grand totals and modify the basic layout.
PivotTable Style Options- improve or perfect the style you selected.
PivotTable Styles-a gallery of styles can be applied to the active table, click on thumbnail.

Screenshot of Microsoft Excel Pivottable Tools. In the top right, there is a green book with an "x" on it (Microsoft Excel), a purple floppy disk icon, a blue curved arrow pointing to the left with a black triangle pointing down, a grayed out curved arrow pointing left, a hand with a finger pointing to a circle with a black triangle pointing down, a black triangle with a horizontal line across the top. To the right of that, there is text that reads PivotPractice file - Excel. To the right of that, there is text that reads Pivottable Tools. In the top right, there is a question mark icon, a square icon, a thin line icon, a square with a header icon, and a "x" icon. Below that is a row of header options. They are FILE (with a black background), HOME, INSERT, PAGE LAYOUT, FORMULAS, DATA, REVIEW, VIEW, ACROBAT, ANALYZE, and DESIGN. The Design tab is open and there are three sections. The first section is a white square with two blue lines and black icons. The text below it is Subtotals with a black triangle pointing down. To the right, there is a white square with black and blue icons. The text below is Grand Totals with a black triangle pointing down. There is a line going half way down the column separating this section from the next section. The next section has a white document with blue text and black icons. The text below is Report Layout with a black triangle pointing down. To the right, there is an icon with a white square with blue text and black icons. The text below is Blank Rows with a black triangle pointing down. Below the four icons and text is the text, Layout. To the right of this section is a vertical line. To the right of the vertical line are four checkmark boxes with text. The text options are Row Headers (selected), Column Headers (selected), Banded Rows, Banded Columns. Below this section is text that reads PivotTable Style Options. There is a vertical line to the right. To the right of the vertical line is a scrollable field with text that reads, PivotTable Styles. The styles in the field are green, purple, blue, orange, and yellow. They look like horizontal bars with black icons on them.

Data - This is the most important step

Each column has to have a unique header name, ensure that there are no empty columns, and each cell has data in it and NO blank cells or rows- because Excel includes the blank rows in the PivotTable Report, delete any blank rows.

Consistent Data- ensure that each column contains the same type of data. For example, in a column of part numbers, be sure each value is numeric; in a column model names, make sure each value is text.

Accurate Data- values used are accurate, check there are no typos or irrelevant data.
Automatic Subtotals- Excel will not create a PivotTable that has subtotals displayed, remove all subtotals. Click on Data, Subtotals, select Remove All.
Filtered Data- subset of the worksheet data, Excel will still use some or all the hidden rows in PivotTable report, the report will not be exact. Use Excel’s advanced filter Feature, copy results to a different worksheet and use the copied data as the source for PivotTable Report.
Column Labels- a PivotTable uses column labels to classify and name data fields.

Create a PivotTable

A data source is needed to create a PivotTable, click a cell within your Excel table that you want to use as the data source for the PivotTable.

From the Insert tab, within the Tables group, click on the PivotTable icon.
Within the Create PivotTable dialog box, ensure that the displayed range is correct, by default New Worksheet is chosen to place the PivotTable report.
Click OK.

By default, an empty PivotTable is created and is located in a new tab in the current workbook.

Screenshot of Microsoft Excel Pivot Table Options. Across the top is a white background. In the top right, there is black text that says Create Pivot Table. In the right corner there is a question mark icon and an "X" icon. Below the white header, there is a gray box with the rest of the content in it. The first text in the gray box is Choose the data that you want to analyze. Below that is a radio button that is selected that says Select a table or range. Below that there is text that reads Table/Range: and a blank field box. To the right of the empty field box is the upload icon (a black arrow pointing upwards with a horiztonal line at the base). Below that, there is another radio button (not selected) with text that says Use an external data source. Below that is a grayed out button that has text that reads Choose Connections. The text below that is also grayed out and reads Connection name:. Below that is another radio button that is grayed out that says Use this workbook's Data Model. Below that there is text that says Choose where you want the PivotTable report to be placed. There is a thin black horizontal line that goes from the end of this text to the right of the screenshot. Below this, there are two radio buttons. The first is New Worksheet (not selected) and Existing Worksheet (selected). Below that, there is text that reads Location and an entry field that has Sheet1!$A$1 in it. To the right of the entry field, there is an upload icon (a black arrow pointing upwards with a black line at the base). Below that, there is text that says Choose whether you want to analyze multiple tables. Below that is a checkmark box with text that says Add this data to the Data Model. In the bottom right of the screenshot, there is a gray button with a blue outline that says OK. To the right is a gray button with Cancel on it.

Populate PivotTable

Within the PivotTable Field List pane, Drag and Drop fields into one of the four pane areas below. A green checkmark indicate that a field is in use.

To remove a field, simply click and drag it back up to the field list.
- Or click on down arrow of the field in use and select Remove Field.
- Remove all fields, from the Analyze tab, within the Actions group, click on Clear , and select Clear All.
Show or Hide the PivotTable Field List Pane, from the Analyze tab, within the Show/Hide group click on the Field List icon to display PivotTable Field List Pane.

Field List Pane

PivotTables can have multiple fields per area and some PivotTables will not need to have a field in each area. A good way to learn how the fields act together is by experimenting with different combinations.

Row Labels- make up the horizontal (X) axis. Fields normally include: Products, Names, Locations
Values- make up the vertical (Y) axis. Fields normally include: Sales, Units, Measures
Column Labels- different types or series of data that are compared in a PivotChart. Fields Normally include: Dates, Months, Years & Time

Screenshot of Microsoft Excel workbook. Along the left side, there are rows one to fifteen. In the first row, there is text in the first cell that says Item. The cell to the right has a dropdown entry field with a black triangle pointing down. The text in this box is (All). In row three, there is Sum of Total in the first cell, a dropdown entry field with a black triangle pointing down with text that says Region. There is a red arrow and text that reads Column Label that points to the Region text. The text in row four is Rep (with a gray button with a black triangle pointing down). The next cell is Alberta. The next cell is Ontario. The next cell is Quebec. The next cell is Grand Total. Row five through fifteen show names and the amounts associated with each header as well as their grand totals. To the left of the axis, there is red text that says Row Labels (X) axis with arrows pointing to rows one through fifteen. Row sixteen has the text, Grand total in the first cell. The other four cells show grand totals for each of the header options. Below that, there is red text that says Values (Y) axis and red arrows pointing to the values in row sixteen.

Change PivotTable Design

From the PivotTable Tools Design tab, within the PivotTable Styles group, and click on a thumbnail from the gallery to select a new style. Use the down arrow icon to open the gallery window and view more options.

With the PivotTable report selected, from the Design tab, within the Layout group, click on Report Layout down arrow and select one of the following:
- Show in Compact Form – keep data from spreading horizontally off the screen
- Show in Outline Form – outline data in classic PivotTable style
- Show in Tabular Form – traditional table format, easy to copy to another worksheet
- Repeat All Item Labels or Do Not Repeat All Item Labels
Highlight every other row or column in a table will make it easier to read the data within a large table.
- With the PivotTable report selected, from the Design tab, within the PivotTable Style Options group, click Banded Rows and/or Banded Columns.
Draw attention to row or column headers in a PivotTable and highlighting these areas in a table.
- With the PivotTable report selected, from the Design tab, within the PivotTable Style Options group, click Row and/or Column Headers.

Format a PivotChart

With the PivotChart selected, from the Design tab, within the Type group, click on the Change Chart Type icon. This will launch the Change Chart Type dialog box, select type and click OK.
From the Design tab, within the Chart Styles group, select from the gallery a new visual style. To view all the options available click on the icon to the right of the gallery list.
From the Format tab, within the Current Selection group, click on the icon to select the chart element you want to format.( i.e., Legend)

Screenshot of Microsoft Excel menu and workbook. Across the top, there are menu items that include FILE (with a black background), HOME, INSERT, PAGE LAYOUT, FORMULAS, DATA, REVIEW, VIEW, ACROBAT, ANALYZE. Below that, there is a dropdown search box with a green highlight. Within the search box, there is text that says Chart Area. Below that, there is a list of items that include Button Department, Button Quarter, Button Sum of Sales, Chart Area, Horizontal (Category) Axis, Legend, Plot Area, Vertical (Value) Axis, Vertical (Value) Axis Major Gridlines, Series "50's Fishbowl", Series "Cardinal Cafe," Series "Cyber Cafe," Series "Murphy Hall," Series "Ward-Haffrey Hall." Behind that dialgo box there are various text options at the top. Down below, there is a workbook open with seven columns and six rows of data. There is a header row at the top in red. To the bottom right of the screenshot, there is a bar chart titled Sum of Sales. The vertical bars are gray, blue, red, green, and yellow. There is a key in the bottom right titled Department. Blue is 50's Fishbowl, Red is Cardinal cafe, Gray is Cyber Cafe, Yellow is Murphy Hall, and Blue is Ward-Haffrey Hall.

Adding and Removing Elements

To add or remove the chart title element:

From the Design tab, within the Chart Layouts group, click on the Add Chart Element button, and select an option from the menu.

To add or remove the axis title element:

Click on the Plus sign next to the chart and select which options you want to add or remove from the chart.

Screenshot of a bar chart titled Sum of Sales. The background is black. The vertical bars are gray, blue, red, green, and yellow. There is a key in the bottom right with the title of Department. Blue is 50's Fishbowl, Red is Cardinal cafe, second gray is Cyber Cafe, Yellow is Murphy Hall, and Blue is Ward-Haffrey Hall. To the right of the chart is a box that says Chart Elements. Below it are a series of checkmark boxes and text. The text reads Axes (selected) Axis Titles, Chart Title, Data Labels, Data Table, Error Bars, Gridlines (checked), Legend (checked), and Trendline

Slicers

A visual tool applied to a PivotTable or PivotChart allowing data to be quickly and easily filter in an interactive way.

With the data selected i.e., PivotTable, from the Analyze tab, within the Filter group, and click on the Insert Slicer button.
From the Slicers dialog box, check which fields to create a slicer for and click on OK.
Within the slicer box, click a button to filter the data, hold CTRL or SHIFT while clicking on multiple buttons.
For more options with the slicer selected, from the Slicer Tools Options tab, within the Slicer Styles group and select an option from style gallery.
Right-click on the slicer box and select Remove from the drop down menu to delete a slicer box.

Format Slicers

Customize button size and layout – select slicker and click on Slicer Tools Options tab.
Customize behavior and Captions – right-click slicer and select Slicer Settings Option.

When using educational technology tools, be FERPA aware. To learn more, visit the Registrar's FERPA Guidelines site.