PivotTables – Excel

Course Facilitator: DJ McDonald, IT Trainer

A PivotTable is a tool that summarizes information sourced from bigger tables. It allows you to calculate,

summarize, and analyze comparisons, patterns, and trends in your data. Some popular uses of PivotTables

include:

• Querying large amounts of data in many user-friendly ways.

• Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and

creating custom calculations and formulas.

• Expanding and collapsing levels of data to focus your results, and drilling down to details from the

summary data for areas of interest to you.

• Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the

source data.

• Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of

data enabling you to focus on just the information you want.

• Presenting concise, attractive, and annotated online or printed reports.

How do you create a PivotTable?

1. Select the cells you want to create a PivotTable from.

Note: Your data should be organized in columns with a single header row.

2. Select Insert > PivotTable.

3. This will create a PivotTable based on an existing table or range.

Note: Selecting Add this data to the Data Model will add the table or range being used for this PivotTable

into the workbook’s Data Model. Learn more

4. Choose where you want the PivotTable report to be placed. Select New Worksheet to place the

PivotTable in a new worksheet or Existing Worksheet and select where you want the new

PivotTable to appear.

5. Click OK.

Data format tips and tricks

 Use clean, tabular data for best results.

 Organize your data in columns, not rows.

 Make sure all columns have headers, with a single row of unique, non-blank labels for each column.

Avoid double rows of headers or merged cells.

 Format your data as an Excel table (select anywhere in your data and then select Insert > Table from

the ribbon).

 If you have complicated or nested data, use Power Query to transform it (for example, to unpivot your

data) so it is organized in columns with a single header row.

Design the layout and format of a PivotTable

After creating a PivotTable and adding the fields that you want to analyze, you may want to enhance the

report layout and format to make the data easier to read and scan for details.

To change the layout of a PivotTable, you can change the PivotTable form and the way that fields, columns,

rows, subtotals, empty cells and lines are displayed.

To change the format of the PivotTable, you can apply a predefined style, banded rows, and conditional

formatting.

After you create a PivotTable by selecting its data source, arranging fields in the PivotTable Field List, and

choosing an initial layout, you can perform the following tasks as you work with a PivotTable:

Explore the data by doing the following:

 Expand and collapse data, and show the underlying details that pertain to the values.

 Sort, filter, and group fields and items.

 Change summary functions, and add custom calculations and formulas.

Change the form layout and field arrangement by doing the following:

 Change the PivotTable form: Compact, Outline, or Tabular.

 Add, rearrange, and remove fields.

 Change the order of fields or items.

Change the layout of columns, rows, and subtotals by doing the following:

 Turn column and row field headers on or off, or display or hide blank lines.

 Display subtotals above or below their rows.

 Adjust column widths on refresh.

 Move a column field to the row area or a row field to the column area.

 Merge or unmerge cells for outer row and column items.

Change the display of blanks and errors by doing the following:

 Change how errors and empty cells are displayed.

 Change how items and labels without data are shown.

 Display or hide blank rows

Change the format by doing the following:

 Manually and conditionally format cells and ranges.

 Change the overall PivotTable format style.

 Change the number format for fields.

 Include OLAP Server formatting.

About PivotCharts

A PivotChart complements your PivotTable by adding visualizations to the summary data, and allows you to

easily see these comparisons, patterns, and trends.

PivotCharts provide graphical representations of the data in their associated PivotTables. PivotCharts are also

interactive. When you create a PivotChart, the PivotChart Filter Pane appears. You can use this filter pane to

sort and filter the PivotChart's underlying data. Changes that you make to the layout and data in an associated

PivotTable are immediately reflected in the layout and data in the PivotChart and vice versa.

PivotCharts display data series, categories, data markers, and axes just as standard charts do. You can also

change the chart type and other options such as the titles, the legend placement, the data labels, the chart

location, and so on.

Differences Between PivotCharts and Standard Charts

If you are familiar with standard charts, you will find that most operations are the same in PivotCharts.

However, there are some differences:

Row/Column orientation Unlike a standard chart, you cannot switch the row/column orientation of a

PivotChart by using the Select Data Source dialog box. Instead, you can pivot the Row and Column labels

of the associated PivotTable to achieve the same effect.

Chart types You can change a PivotChart to any chart type except an xy (scatter), stock, or bubble chart.

Source data Standard charts are linked directly to worksheet cells, while PivotCharts are based on their

associated PivotTable's data source. Unlike a standard chart, you cannot change the chart data range in a

PivotChart's Select Data Source dialog box.

Formatting Most formatting—including chart elements that you add, layout, and style—is preserved

when you refresh a PivotChart. However, trendlines, data labels, error bars, and other changes to data sets are

not preserved. Standard charts do not lose this formatting once it is applied.

Although you cannot directly resize the data labels in a PivotChart, you can increase the text font size to

effectively resize the labels.

Creating a PivotTable or PivotChart from worksheet data

You can use data from an Excel worksheet as the basis for a PivotTable or PivotChart. The data should be in

list format, with column labels in the first row, which Excel will use for Field Names. Each cell in

subsequent rows should contain data appropriate to its column heading, and you shouldn't mix data types in

the same column. For instance, you shouldn't mix currency values and dates in the same column.

Additionally, there shouldn't be any blank rows or columns within the data range.

Excel tables Excel tables are already in list format and are good candidates for PivotTable source data.

When you refresh the PivotTable, new and updated data from the Excel table is automatically included in the

refresh operation.

Using a dynamic named range To make a PivotTable easier to update, you can

create a dynamic named

range, and use that name as the PivotTable's data source. If the named range expands to include more data,

refreshing the PivotTable will include the new data.

Including totals Excel automatically creates subtotals and grand totals in a PivotTable. If the source data

contains automatic subtotals and grand totals that you created by using the Subtotals command in

the Outline group on the Data tab, use that same command to remove the subtotals and grand totals before

you create the PivotTable.

PivotTable Options

Use the PivotTable Options dialog box to control various settings for a PivotTable.

Name Displays the PivotTable name. To change the name, click the text in the box and edit the name.

Layout & Format

Merge and center cells with labels Select to merge cells for outer row and column items so that you can

center the items horizontally and vertically. Clear to left-justify items in outer row and column fields at the

top of the item group.

When in compact form indent row labels To indent rows in the row labels area when the PivotTable is

in compact format, select an indentation level of 0 to 127.

Display fields in report filter area Select Down, Then Over to first display fields in the report filter area

from the top to the bottom, as fields are added to it, before taking up another column. Select Over, Then

Down to first display fields in the report filter area from left to right, as fields are added to it, before taking

up another row.

Report filter fields per column Type or select the number of fields to display before taking up another

column or row based on the setting of Display fields in report filter area.

Format section

For error values show Select this check box, and then type text, such as "Invalid", that you want to

display in the cell instead of an error message. Clear this check box to display the error message.

For empty cells show Select this check box, and then type text, such as "Empty", that you want to display

in the cell instead of a blank cell.

Autofit column widths on update Select to adjust the PivotTable columns to automatically fit to the size

of the widest text or number value. Clear to keep the current PivotTable column width.

Preserve cell formatting on update Select to save the PivotTable layout and format so that it is used each

time that you perform an operation on the PivotTable. Clear to not save the PivotTable layout and format

and resort to the default layout and format each time that you perform an operation on the PivotTable.

Totals & Filters

Show grand totals for rows Select or clear to display or hide the Grand Total column next to the last

column.

Show grand totals for columns Select or clear to display or hide the Grand Total row at the bottom of

the PivotTable.

Subtotal filtered page items Select or clear to include or exclude report-filtered items in subtotals.

Mark totals with * Select or clear to display or hide an asterisk next to totals. The asterisk indicates that

the visible values that are displayed and that are used when Excel calculates the total are not the only values

that are used in the calculation.

Allow multiple filters per field Select to include all values, including those hidden by filtering, when

Microsoft Office Excel calculates subtotals and the grand total. Clear to include only displayed items when

Excel calculates subtotals and the grand total.

Use Custom Lists when sorting Select or clear to enable or disable the use of custom lists when Excel

sorts lists. Clearing this check box may also improve performance when you sort large amounts of data.

Display

Show expand/collapse buttons Select to display the plus or minus buttons that you use to expand or

collapse row or column labels. Clear to hide the plus or minus buttons that you use to expand or collapse row

or column labels. You might want to hide the plus or minus buttons when you print a PivotTable or when

you display a PivotTable just for viewing.

Show contextual tooltips Select to display tooltips that show value, row, or column information for a field

or data value. Clear to hide tooltips that show value, row, or column information for a field or data value.

Show properties in tooltips Select or clear to display or hide tooltips that show property information for

an item.

Display field captions and filter drop downs Select or clear to display or hide PivotTable captions at the

top of the PivotTable and filter drop-down arrows on column and row labels.

Classic PivotTable layout Select or clear to enable or disable dragging fields on and off of the PivotTable.

Show items with no data on rows Select or clear to display or hide row items that have no values.

Show items with no data on columns Select or clear to display or hide column items that have no values.

Display item labels when no fields are in the values area Select or clear to display or hide item labels

when there are no fields in the value area.

Show calculated members from OLAP server Select or clear to display or hide calculated members in a

dimension. This check box does not affect calculated measures.

Field List section

The following two options are mutually exclusive.

Sort A to Z Select to sort the fields in the PivotTable field list in ascending alphabetical sort order.

Sort in data source order Select to sort the fields in the PivotTable field list in the order that is specified

by the external data source..

Printing

Print expand/collapse buttons when displayed on PivotTable Select or clear to display or hide expand

and collapse buttons when you print a PivotTable. This check box is not available if the Show drill

buttons check box is cleared in the Display tab of this dialog box.

Repeat row labels on each printed page Select or clear to repeat the current item labels of the row label

area on each page of a printed PivotTable.

Set print titles Select or clear to enable or disable the repeating of row and column field headers and

column item labels on each printed page of a PivotTable.

Note: To actually print the labels, you must still enter values in the Rows to repeat at the top or Columns

to repeat at left boxes under the Print titles section in the Sheet tab of the Page Setup dialog box. (On

the Page Layout tab, in the Page Setup group, click Print Titles.

Data

PivotTable Data section

Save source data with file Select or clear to save or not save the data from the external data source with

the workbook.

Notes:

 This setting should not be used to manage data privacy.

Enable expand to detail Select or clear to enable drilling down to detail data from the data source, and

then displaying the data on a new worksheet.

Refresh data when opening the file Select or clear to refresh or not refresh the data when you open the

Excel workbook that contains this PivotTable.

Retain items deleted from the data source section

Number of items to return per field To specify the number of items for each field to temporarily cache

with the workbook, select one of the following:

 Automatic The default number of unique items for each field.

 None No unique items for each field.

 Max The maximum number of unique items for each field. You can specify up to 1,048,576 items.