Skip to content
DUOSHAODUOSHAO · Help

Pivot tables and charts

What it is

Section titled “What it is”

A pivot table collects totals from a table with headers, on a separate sheet. A chart draws the data of a range right on the sheet: column, bar, line, area, pie and scatter charts.

How to use it

Section titled “How to use it”

Pivot table

  1. Below the sheets, next to the sheet tabs, open the Spreadsheet menu (three dots) → Create pivot table.
  2. In the New pivot table window: Source is the sheet; Range is the area with headers in the first row (for example A1:D200); Result sheet is the name of the new sheet.
  3. Tick the fields for Rows and Values. For each value, choose the aggregate: SUM, COUNT, COUNTA, AVERAGE, MIN or MAX. Columns is an optional field for splitting by columns.
  4. Optionally: Filter (a field), Condition (Include or Exclude) and Value; Sort (Name or Value) and Order (Ascending or Descending). Click Create. If the pivot table cannot be built, the window does not close, and it states the reason. To close the window without saving, use the × button, Escape or a click on the dimming. On a phone the window is a bottom sheet: swipe its handle down, since there is no × button.
  5. A new sheet with the pivot table opens. It is read-only, and it recalculates itself when the source data changes. To change the settings, open the pivot sheet and choose Set up pivot table in the Spreadsheet menu: there are Save, Refresh and Delete.

Chart

  1. Select a range (or stand in any cell: the whole filled area is taken) and click Insert chart on the toolbar.
  2. The type is chosen from the data. The chart is placed to the right of the range, and the Chart editor opens next to it. Each change is applied at once; undo is Ctrl+Z. The chart lies on top of the cells: the selection and the copy frame are not visible under it, while the row and column headers stay above it. For the selected chart, the action panel (pencil, Download PNG, bin) stands above the frame. At the very top of the sheet, where there is no room above the frame, it is inside the frame in the top right corner.
  3. In the editor: Type; Data range (+ Apply); Series, either By columns or By rows; the checkboxes First row — series names and First column — category labels; Title; Legend (No legend, Right or Bottom); X-axis label; Y axis label; Minimum Y; Maximum Y; the series Colors; Width; Height; and Delete chart.
  4. On the sheet, drag the chart by its frame and stretch it by a corner. A double-click or Enter opens the editor. Delete removes the chart. The selected chart has the buttons Edit, Download PNG and Delete. On hover, the series is highlighted and the value of the point is shown.
  5. A mini-chart inside a cell is built by the SPARKLINE formula: a line, columns or a bar.

Limits

Section titled “Limits”
  • Pivot tables and charts are created and changed only by those who can edit the spreadsheet. Download PNG is also available in view mode, unless the owner of the link has forbidden copying.
  • Pivot table: the source can be up to 50 000 rows, with up to 10 fields in Rows and up to 10 in Values, and the result is up to 10 000 cells. The filter in the window is one field and one value. Sorting is by the first value.
  • A pivot sheet cannot be edited by hand. Neither the source sheet nor the pivot sheet can be deleted while a pivot table exists.
  • A sheet can have up to 50 charts, and up to 12 series colours. Width: 160–2000 points; height: 120–2000 points.
  • If the range has no numbers, the chart shows only No numeric data: there is no legend, either on the sheet or in the PNG.
  • A chart title can be up to 200 characters; axis labels up to 100.
  • On a pie chart, a share is labelled with a percentage if it is at least 6%.
  • Labels that the chart writes itself are in the interface language: the series name without a title (“Series 1” in English, “Серия 1” in Russian); axis abbreviations (“20K”, “3M” in English, “20 тыс.”, “3 млн” in Russian); TRUE/FALSE (ИСТИНА/ЛОЖЬ in Russian) in category labels; and the text of an empty chart. The same applies to Download PNG, to printing, and to chart pictures when exporting to Excel: the language is that of the person who downloads or prints.

If something goes wrong

Section titled “If something goes wrong”
What you see on the screen Why What to do
In the window: Choose a row field and a value field. A field in Rows or Values is not ticked Tick the fields
Invalid range: it needs a header row and at least one data row. The range is written incorrectly, or has no data Fix the range
Column headers in the range must be different. The header row has repeats Rename the repeated headers
The pivot is too large. Narrow the range or remove fields. The pivot table exceeded its limits (source rows, result cells) Reduce the range, or the number of fields
Could not build the pivot. Check the range and fields. The pivot table cannot be built for another reason Check the range and the fields
Remove the pivot table first The source sheet or the pivot sheet is being deleted Delete the pivot table in Set up pivot table, then the sheet
On a chart: No numeric data. In the editor: There are no numbers in the range - check the range and the orientation of the series. The range has no numbers for the chosen orientation Change the range, or Series
Specify the range of the form A1:B10 The range is written incorrectly Fix it
Check chart options A change did not pass the check of the chart parameters Restore the previous value of the field