Pivot Tables in Row Zero

Pivot tables make it easy to summarize, analyze, and explore large datasets. Row Zero pivot tables are dynamic and automatically update as source data changes. The documentation below shows how to create and work with pivot tables in Row Zero.

• How to create a pivot table
• Values calculation options
• Group date fields in rows or columns
• Working with pivot tables
• Build on top of your pivot table
• Create pivot table slicers
• Video tutorial

How to create a pivot table

You can create pivot tables from both cell ranges and data tables. Here's how:

1. Select the data you want to create a pivot table from and go to 'Insert' in the header navigation and select 'Insert pivot table'. You can also right-click on a cell in a data table or a selected cell range and select 'Pivot'.
2. Select your pivot table location. You can insert to a new sheet or a specific cell on an existing sheet.
3. An empty pivot table will be created. Drag fields to Rows, Columns, Values, and Filters to set up your pivot table. Here's a quick overview:
   • Rows and Columns: Think of these as "a row for each" or "a column for each" of whatever you drag to it. Both are optional, but it's more common to use Rows.
   • Values: Drag to Values the things you want the rows and/or columns to be measured on and the type of calculation (i.e. Sum, Average, Count, etc). View all calculation options.
   • Filter: Optionally filter the source data used in the pivot table. Pivot table filters support the full suite of filter options.

Here's an example pivot table that summarizes a dataset of 7.2 million U.S. flights with Rows grouped by month, and Values for Count of flights (by month), Sum of cancellations (by month), and Average delay (by month).

Note, you can also use a keyboard shortcut to insert a pivot table (Alt, N, V on Windows or Option, N, V on Mac).

Value calculations

You can set Values as Columns or Rows. Values have the following built-in calculations:

• Average - Average across values for each row/column grouping
• Count - Counts the number of values for each row/column grouping
• Count unique- Counts the number of unique values for each row/column grouping
• First - First instance of a value for each row/column grouping
• Last - Last instance of a value for each row/column grouping
• Max - Maximum value for each row/column grouping
• Median - Median value for each row/column grouping
• Min - Minimum value for each row/column grouping
• Percentiles - Lets you specify a percentile value to return for each row/column grouping. Percentile options include 10, 25, 50, 75, 90, 95, 99, 99.9.
• Std dev - Standard deviation of values for each row/column grouping
• Sum - Sum of all values for each row/column grouping
• Variance - Variance of values for each row/column grouping

Group date fields in rows or columns

Pivot tables have built-in date grouping. You can group date fields in rows or columns as seconds, minutes, hours, days, weeks, months, quarters, or years.

Working with pivot tables

Edit and update pivot tables

Pivot tables dynamically update as source data changes. If you edit source data, add or delete source data, or filter source data, your pivot table updates automatically.

To edit a pivot table, double-click on the pivot table to re-open the 'Configure pivot table' window. Pivot tables dynamically change as you make edits.

Cells in pivot tables cannot be edited within the pivot table. If you'd like to edit pivot table data, right-click on the pivot table and select 'Explode table' to convert the pivot table to cells that are fully editable. Note, this is similar to copy and 'paste values' and the data is no longer in a pivot table. Conversely, you can select one or more cells in a pivot table and copy and paste them elsewhere, where they are fully editable.

Filter, sort, and format pivot tables

Pivot tables are data tables with filtering and sorting built-in. Click the down arrow in a column header to sort or filter your pivot table. You can also apply any formatting to your pivot table including conditional formatting. Note that formating is applied to the entire column. To hide pivot table columns, right-click on the pivot table and select 'Manage columns' and unselect whatever columns you'd like to hide. To change the order of columns in a pivot table, double-click on the pivot table to re-open the editor and re-order the fields in Rows or Values.

Moving, copying, and deleting pivot tables

You can drag pivot tables around a sheet or cut/copy and paste to another sheet. To cut/copy a pivot table, right-click on the pivot table and select 'Cut' or 'Copy' and then use 'Ctrl + V' to paste. To delete a pivot table, just click a cell in the pivot table and use your delete key or right-click on the pivot table and select 'Remove table'.

Exporting pivot table data

In addition to copy/paste, you can right-click on a pivot table and select 'Export to' to export pivot table data to CSV or a connected data source like Snowflake, Databricks, or Postgres.

Build on top of your pivot table

You can use pivot tables as source data for calculated columns, formulas, pivot charts, and even more pivot tables. Everything built on top of pivot table data updates in sync with your pivot table.

Add calculated columns to pivot tables

To add a calculated column to your pivot table, write a formula in the first column to the right of the pivot table and reference a pivot table column in the formula. The formula dynamically fills in every row in the column with the formula applied.

Reference pivot table columns in formulas

You can reference pivot table columns in formulas using the cell location of the top-left corner of the pivot table and the column name. In the example below, the pivot table starts in cell A1 and has a column named "Cancel Rate". To use this in a formula, use A1["Cancel Rate"], which you can see in the example below.

Create charts from pivot tables

To create a chart from a pivot table, select a cell in each pivot table column to include in your chart and go to 'Insert', 'Chart' in the header navigation. Charts built from pivot tables update dynamically in sync with the pivot table, as the pivot table changes or is updated with new data. Read more about charts options here.

Create pivot tables from pivot tables

You can easily create a pivot table from a pivot table. Right-click on your pivot table and select 'Pivot' to create a new pivot table using your current pivot table as source data.

Create pivot table slicers

Slicers are filters that can be moved anywhere in the workbook. Slicers filter the underlying source data, which dynamically filters everything that references the source data, including pivot tables and charts. Slicers make it easy to create dashboard filters that can filter multiple pivot tables and charts at once.

Here's how to create pivot table slicers:

1. Click a cell in your pivot table's source data and go to 'Data' in the header navigation and select 'Create slicer'. Select the columns you'd like to create a slicer for and click 'Apply'.
2. A slicer is created for each column selected. Click the slicer dropdown to open the filter. You can select from the list of values, search for a value, or filter by one or more conditions (e.g. >=5). Click 'Apply' and the source data will filter accordingly. The pivot table dynamically updates to reflect the filters applied to the source data.
3. Double clicking on your slicer to to rename it. You can also click on the source icon next to the sliced column to go back to the source range. Learn more about slicers here.

Video tutorial of pivot tables

Watch the pivot table tutorial video below to see how to create and work with pivot tables in Row Zero:

Explore our 10 best pivot table features to see how to get the most out of Row Zero pivot tables.