Using Pivot Table
A pivot table is a useful tool that helps you to summarize and study big amounts of data. It allows you to make reports that suit your needs and quickly see trends and patterns in your data.
Getting Started with Pivot Tables
To create a pivot table, first, ensure that your data is organized correctly. Your data should be in a table format, where each column represents a category and each row represents an item. Also, each column should have a header row that explains the data in that column.
To create a pivot table, follow these steps:
- After selecting a range in a spreadsheet, click the Insert menu > Pivot Table.
- You may adjust the range in the Cell range field by clicking the grid icon.
- Choose whether you want to place the pivot table in a new worksheet or in the current worksheet.
- Click OK.
Once you create a pivot table, a Pivot Table Editor will appear on the right side of the screen. The left part of the editor shows a list of the available fields, while the right part is split into four sections: columns, rows, values, and filters. These sections help you select the fields you want to include and build the pivot table.
Note:
- If you make changes to the source data of a pivot table, the pivot table will not change automatically. Click the refresh icon in the upper-right corner of the Pivot Table Editor to update the pivot table every time you make changes to the source data.
To edit a pivot table, do any of the following:
- To add fields, drag them from Fields into the Columns, Rows, Values, or Filters sections. Alternatively, click Add in the four sections and select a field you want to add.
- To remove fields, click X or drag them out of the four sections.
- To rename a row or column header in a pivot table, double-click on the name.
- To view the source data of a cell, double-click on the cell. This is only available for cells containing values generated by the Values section.
- To show the total of a row or column, select the Show totals option.
Note:
- If there are multiple fields in the same row or column section, the pivot table allows for nesting of rows or columns.
Working with Pivot Table Values
In the Values section, choose how you want to display the data in the pivot table.
- Summarize by: By default, a pivot table will summarize numeric data placed in the Values section using the SUM function. For non-numeric data, the pivot table will count the entries using the COUNTA function. To modify the default calculation, select a different option from the drop-down menu under Summarize by.
- Show as: In addition to summarizing data using calculations, you can also display it as a percentage of a particular field. To show values as percentages, select an option from the drop-down menu under Show as.
- To display the data as both a calculation and a percentage at the same time, drag the same field into the Values section of the pivot table twice. Then, adjust the Summarize by and Show as options for each of the fields accordingly.
Grouping Data in a Pivot Table
By grouping data in a pivot table, you can quickly analyze and gain insights into your data based on different categories or dimensions.
To group selected data in a pivot table, follow these steps:
- Select the the rows or columns you want to group.
- Right-click and select Create pivot group.
Note:
- When a date or time field is dragged into the Rows or Columns section, Synology Spreadsheet will automatically group the data based on the time period (such as days, months, or years).
To customize date and time groups, follow these steps:
- Right-click on the row or column and select Create pivot date group.
- Specify the Start on and End on values for the group.
- Choose the grouping interval:
- Year
- Quarter
- Month
- Day
- Hour
- Minute
- Second
- Click Create.
To group number data in a pivot table, follow these steps:
- Right-click on the row or column and select Create pivot number group.
- Specify the Maximum value, Minimum value, and Interval for the group.
- Click Create.
To ungroup your data in a pivot table, follow these steps:
- Select the the rows or columns you want to ungroup.
- Right-click and select Ungroup.
Specifications and Limitations
The pivot table function has the following limitations:
- Pivot table is only supported on Synology NAS with 4 GB or higher RAM. Refer to the "RAM" column of your Synology NAS.
- Excel files containing pivot tables can be converted into Synology spreadsheets. If your pivot table has settings that are not supported by spreadsheet, click the Convert Format option to continue using it. You may need to re-configure some pivot table settings after the conversion process.
Additional limitations:
| Function | Maximum Limit |
|---|---|
| The number of pivot table reports in a spreadsheet | Limited by available memory |
| Unique items per field | Limited by available memory |
| Row or column fields in a pivot table report | Up to 4 fields for the row and column sections combined if the source data of the pivot table has over 10,000 records |
| Report filters in a pivot table report | Limited by available memory |
| Value fields in a pivot table report | Limited by available memory |
| Cells of data source in a pivot table report | 1,000,000 |
| Rows or columns in a pivot table report | 200,000 |
| Cells in a pivot table report | 1,000,000 |