Pivot tables

A pivot table turns a long list into a short summary. Pick the column to group by, the figures to add up and how to add them up, and PlexiiSheets builds a table with one line for each group and a grand total underneath. Add a second column to spread the groups across, and each line breaks down into columns too.

Use one when a list has grown too long to read: sales by region, hours by person, spending by category.

Before you start

• Pivot tables are on every plan.
• Open a spreadsheet in PlexiiSheets, or click inside a Spreadsheet widget on a desk. See PlexiiSheets overview.
• Your list needs a heading row: the first row of the cells you select gives the names the pivot table uses.
• Each row below the headings should be one record, such as one sale, with the figures in a column of their own.

Make a pivot table

1. Select the whole list, from the first heading to the last figure. Drag across it, or click the first cell and Shift-click the last.
2. Click the pivot table button on the toolbar (Pivot table from the selection), or choose Insert > Pivot table. The Pivot table window opens and shows the range it will summarise.
3. Under Rows, choose the column to group by, such as Region.
4. Under Columns, choose a second column to spread the groups across, such as Quarter, or leave it at (none).
5. Under Values, choose the column of figures, such as Amount.
6. Under Summarise, choose how to combine them: sum, count, avg (average), min or max.
7. Click Create pivot.

Each list in the window shows the columns in your selection, by the names in the sheet's column headings. To close the window without making anything, click × or click outside it.

What you see

The pivot table appears below the sheet, in a card of its own:

• The title says what it shows, such as "sum of Amount", using the heading from the first row of your selection.
• The first column lists each value from the Rows column once, sorted A to Z, and numbers in number order.
• One column for each value of the Columns column, if you chose one.
• Total (or Count) at the end of each line, for the whole group.
• Grand total on the last line.

An empty value is shown as (blank). Results are shown to at most two decimal places.

The pivot table reads the sheet every time it draws, so it keeps up when you change, add or correct a figure inside the range.

How the figures are read

• Commas, spaces, dollar signs and percent signs are ignored, so 1,200 and $1,200 both count as 1200.
• With sum, avg, min and max, a value that is not a number is left out.
• count counts every row in the group, whatever is in its Values cell.
• A cell with a formula counts by its result.

Filter a pivot table with its slicers

When the Rows column, or the Columns column, has more than one value, the card shows a slicer above the table: a box of tick boxes headed by the column's name, one for each value.

• Untick a value to leave its rows out of the pivot table, totals included.
• Tick it again to bring it back.

Your choices are saved with the sheet. The slicer always lists every value, so you can bring back anything you have left out.

Change or remove a pivot table

• Remove it: click × (Remove pivot) at the top right of the card. The list itself is not touched. Undo brings the pivot table back.
• Change it: remove it and make it again with different choices.

Each pivot table belongs to the sheet tab you made it on.

Tips

• Tidy the list first. Trailing spaces and different spellings make separate groups. A query can trim and tidy a column for you. See Shape data with queries.
• Need a filtered list instead? To see the records themselves rather than totals, filter the sheet. See Sort and filter.

Common questions

The pivot table shows "Col 1" or a figure as a heading. The first row of your selection was not the heading row. Remove the pivot table, select again starting from the headings, and make it again.

Two groups look the same. Their values differ slightly, such as a trailing space or a capital letter. Make the values match and the groups merge.

A total is lower than I expected. Some values are not numbers, so they are left out of sum. Check for words or symbols typed in with the figures.

Does the pivot table go into an exported Excel file? The .xlsx file holds your cells, formulas and formatting. Pivot tables stay in PlexiiSheets. See Import and export Excel and CSV files.

Related tasks

• PlexiiSheets overview
• Charts and sparklines in a sheet
• Sort and filter
• Shape data with queries
• Spreadsheet widget