Create and Edit Pivot Tables in Workbooks

Pivot tables enable you to summarize and analyze the significance of large amounts of data. The Pivot Table Wizard and panel enable you to create and edit pivot tables interactively.

Notes:

The maximum number of data points that Worksheets can generate in a pivot table is 1 million (1,000,000).

Pivot tables containing live data rely on the report column names from the Workday report. If you change a report column name in the report, and that data is used as a field in a pivot table, you need to replace the pivot table field associated with the original name with the field that's based on the new report column name.

Pivot tables don't support calculations when the data range contains multiple currency units. Example: If you have a pivot table that includes salaries, and some salaries are in USD while others are in CAD, you can't do a SUM, AVERAGE, or other calculation on the data; if you hover over the error cell you see A unit conversion issue exists
. The only valid function in this situation is COUNTA.

You can't use the formula editor for pivot table formulas.

If you change the name of a pivot table, remember that the name must be unique in the workbook and also can't be the same as any defined names or live data table names.

If you change the name of a pivot table, and the pivot table is being used in a Slides presentation, the new name won't display in Slides until you refresh the data in the Linked Data panel.

Pivot tables require that all source data reside on the same workbook sheet. The sheet can include both live data and static data.

From the sheet containing the data that you want a pivot table for, select the range to include and click

Insert

Pivot Table

. You can move the popup around the sheet if needed to see your data.

(Optional) In the

Source Data

field, edit the range to use when creating the table.

(Optional) For pivot tables where all
the source data is in an array (live data): Select

Auto-update as unconstrained array data adds or removes rows

to automatically update the pivot table if an array size change causes rows to be added or deleted.

Note that the pivot table doesn't automatically update if columns
are added or removed from the Workday report.

Select whether to create a pivot table on a new sheet or an existing sheet.

If you choose

Existing sheet

, type the starting cell for the pivot table or click the

Select

icon and select the cell.

Click

. The pivot table displays and the pivot table panel opens.

Configure the table as desired using the following fields and options. When you're finished, click

Update

to make your changes take effect.

Field	Notes
Columns Rows	From the fields area, place one or more fields into the Rows or Columns area to include them in the pivot table. You can click the + icon and select an area, or drag the field. If you have lots of fields, you might want to use Search to find it quickly. Click the Filter icon to sort the displayed values or filter the data. You can either sort by the column or row, or by any of the value fields. Click the Menu Options (three dots) icon to type a custom Display Name in the header for the field, or to show subtotals.
Values	Click the Menu Options (three dots) icon to specify a Display Name , a Summarize by function, a Show data as option, and a Formatting option. Summarize by functions: AVERAGE COUNT: Returns the number of specified values, counting only numbers and string representations of numbers. COUNTA: Returns the number of specified values, counting all data types. COUNTNZ: Counts non-zero numbers. If a value is a string representation of a number, Worksheets converts it to a number. DISTINCT: Counts distinct (not duplicate) values. FIRST LAST MIN MAX STDEV: Estimates the standard deviation. SUM VARIANCE: Estimates the variance. In the Formatting drop-down menu, the available formats match the selections in the Format Number menu. Worksheets retains the formatting when the related live data refreshes.
Filter	Place one or more fields here to show only a subset of the data for those fields or to sort the data. Public, private, and shared filters aren't supported for pivot tables.

You can change the source data range for the table by clicking the

Source Data

icon.

Anyone with view access or higher for the workbook can view detailed information about a pivot table value. Select a single pivot value, right-click, and select

Show Details

. If you have edit or owner permission, you can optionally create a new sheet for the data by clicking

Create Sheet

If you're the owner or an editor for the workbook, you can quickly generate the formula that produces a specific pivot table value. This formula enables you to get related data from a pivot table without directly referencing cells, so the reference is preserved if you change the pivot table in the future. Select a single pivot value, right-click, and select

Copy Pivot Data Formula

. The resulting formula uses the GETPIVOTDATA function.

You can change settings for the entire table by clicking the Settings (gear) icon:

Field	Notes
Pivot Name	Pivot table names can contain letters, numbers, and underscores, and they must be between 1 and 255 characters long. Pivot table names must be unique across the workbook, and the name must not be the same as a live data area table name.
Table Style	Select a predefined color and highlighting pattern from the drop-down menu.
Source Data	Display only. To change the source data range, click the Source Data icon.
Grand Totals	Select whether to display row or column grand totals.
Repeat Labels	Select whether to repeat row or column labels.
Format	Select whether to display custom text for error cells or for empty cells.