---
title: Cross Tabulation
description: Create pivot tables with categorical row and column variables. Aggregate with SUM, AVG, COUNT, MIN, or MAX. Visualize with percentage display, heatmaps, or in-cell bar charts.
priority: 0.6
---

# Crosstab (Cross Tabulation) {#crosstab-cross-tabulation}

Crosstab is a pivot table feature that arranges categorical variables in rows and columns for aggregation. Quickly get an overview of distribution patterns in your data.

## Basic Usage {#basic-usage}

### Opening Crosstab {#opening-crosstab}

Select **Analysis > Crosstab Analysis...** from the menu bar to open a new Crosstab tab.

### Creating a Cross Tabulation {#creating-a-cross-tabulation}

Specify fields in the settings panel on the left.

**Rows**: Select categorical variables to place in rows. Multiple selections create hierarchical row headers.

**Columns**: Select categorical variables to place in columns. Multiple selections are also possible.

**Values**: Select fields and aggregation functions (SUM, AVG, COUNT, MIN, MAX). Only numeric and boolean columns can be added as value fields. To create a frequency table from categorical variables only, add the **Row #** field, which holds the row number. The aggregation function is set to COUNT automatically. COUNT counts rows regardless of field values, so the result is the same no matter which field you aggregate with COUNT.

When configuration is complete, click the **Generate Crosstab** button. The cross tabulation table is displayed in the preview area on the right.

![Basic cross tabulation table](../shared/images/crosstab-basic.webp)

### Saving Results {#saving-results}

Click **Save as Dataset** to save the cross tabulation's aggregated values as a new dataset. Percentage, Value sorting, and visual mode colors are not saved.

Click **Add to Report** to add the cross tabulation to a [report](report). Percentage, visual mode colors, and total-row settings are kept, but Value sorting is not reflected.

## Aggregation Functions {#aggregation-functions}

The following aggregation functions are available for Values fields:

- SUM (total)
- AVG (average)
- COUNT (count)
- MIN (minimum)
- MAX (maximum)

When you place a boolean column as a value field, true is counted as 1 and false as 0, so SUM gives the count of true values and AVG gives the proportion of true values.

## Display Options {#display-options}

### Showing Totals {#showing-totals}

Control total display in the Options section.

Turn on **Show row totals** to display row totals on the right edge.

Turn on **Show column totals** to display column totals at the bottom.

Turn on **Show grand total** to display the overall grand total. The grand total appears at the right end of the column totals row, so **Show column totals** must also be on.

For AVG, MIN, and MAX, a total does not re-aggregate the cell values. It reapplies the same aggregation function to all records in that row, column, or the whole table.

### Percentage Display {#percentage-display}

**Percentage display** can show values as percentages.

- **None** - Do not display percentages
- **Row %** - Display percentage of row total. If a cell value is 10 and the row total is 50, Row % is 20%
- **Column %** - Display percentage of column total. If a cell value is 10 and the column total is 40, Column % is 25%
- **Total %** - Display percentage of grand total. If a cell value is 10 and the grand total is 200, Total % is 5%
- **All** - Display Row %, Column %, and Total % at the same time

Row %, Column %, and Total % appear only when **Show row totals**, **Show column totals**, and **Show grand total** are on, respectively.

Note: Percentage calculation is not available for AVG, MIN, MAX aggregations.

## Sorting {#sorting}

Set sorting options in the Display tab.

### Row Sorting {#row-sorting}

- **Original Order** - Sorts by category value in ascending order, not the input data order. Strings sort lexicographically, [Enum](enum-definitions) columns follow their definition order, and NULL for missing values comes last
- **Label (A-Z)** - Sort by label in ascending order
- **Label (Z-A)** - Sort by label in descending order
- **Value** - Sort by aggregated value

### Column Sorting {#column-sorting}

Same sorting options as rows are available.

For Value sorting, you can specify which column/row value to use as the basis, and whether ascending (Low to High) or descending (High to Low).

Label and Value sorting use the first row field and the first value field as the basis. You cannot sort by the second or later level in hierarchical rows (multiple row variables) or with multiple value fields.

## Visual Mode {#visual-mode}

Change display style with **Visual Mode** in the Display tab. Available only when there is one value field.

- **Table** - Display in standard table format
- **Heatmap** - Express value magnitude with cell background color. Colors are mapped based on the range from the minimum to the maximum value across the cells
- **Text + Heatmap** - Display numbers and heatmap simultaneously
- **Bar in Cell** - Display bar charts within cells

![Heatmap display](../shared/images/crosstab-heatmap.webp)

![Bar in Cell display](../shared/images/crosstab-bar.webp)

### Color Scheme {#color-scheme}

A color scheme can be selected in all visual modes except Table. Heatmap and Text + Heatmap apply it to cell background colors, and Bar in Cell applies it to the bar color.

- **Blue** - Blue gradient
- **Red** - Red gradient
- **Green** - Green gradient
- **Diverging (Red-White-Blue)** - Gradient from red through white to blue. White represents the median of the cell values

The minimum, maximum, and median used for the color scale exclude row totals, column totals, and the grand total.

## Cell Operations {#cell-operations}

### Cell Selection {#cell-selection}

Clicking a cell selects the original data rows corresponding to that cell. Selected rows are highlighted in other views as well.

### Drill Down {#drill-down}

Double-clicking a cell opens a [Filtered Data](filtered-data) tab with only the data matching that cell's criteria.

![Filtered Data display via drill down](../shared/images/crosstab-drilldown.webp)

In the Crosstab on the left, double-clicking the "44" cell (Adelie x Biscoe) opens a Filtered Data tab with the 44 rows matching those criteria. The right side shows the Filtered Data tab.

## Multiple Value Fields {#multiple-value-fields}

Adding multiple fields to Values expands each column by value field. For example, if you specify SUM(Sales) and AVG(Profit), the aggregated values for each are arranged under each column header.

When using multiple value fields, visual modes (heatmap, etc.) are not available.

## Missing Values {#missing-values}

When row or column fields contain missing values, they are aggregated as an independent category labeled NULL. This NULL category is included in row totals, column totals, and the grand total, so it also enters the denominators of Row %, Column %, and Total %. To compute percentages that exclude missing values, filter out NULL beforehand.

When value fields contain missing values, COUNT counts all rows including missing values, while SUM, AVG, MIN, and MAX exclude missing values from calculation.

## Limitations {#limitations}

Visual modes (Heatmap, Text + Heatmap, Bar in Cell) are only available when there is one value field. When there are multiple value fields, the **Visual Mode** selection is disabled.