Introduction to Pivot Tables

Pivot tables are a data processing tool used to summarize, analyze, explore, and visualize large amounts of data. Pivot tables can help you quickly analyze data, identify patterns and relationships in the data, and better understand the data.

Pivot tables are commonly used in the following scenarios:

• Summarizing data: Pivot tables can help you quickly summarize data to better understand the overall situation.
• Analyzing data: Pivot tables can help you analyze data to identify patterns and relationships.
• Exploring data: Pivot tables can help you explore data to discover rules and trends.
• Visualizing data: Pivot tables can help you visualize data to better understand it.

Pivot tables typically include the following elements:

• Row labels: Row labels in a pivot table are used to group data.
• Column labels: Column labels in a pivot table are used to group data.
• Values: Values in a pivot table are used to summarize data.

In a pivot table, you can adjust the row labels, column labels, and values as needed to better analyze the data.

Data Source

In Univer Sheet, the data source for a pivot table can be a worksheet or a data range. You can choose different data sources as needed to better analyze the data. We use the first row of the data source as the default column labels, and the rest as the data source. Therefore, we require the data source to be as organized as possible, avoiding empty rows and columns. In the same column of data, we automatically recognize the data type to better support the pivot table’s functionality. If the data types in a column are inconsistent, we default to setting the data type of that column to string.

In Univer Sheet, pivot tables automatically respond to changes in the data. When the data source changes, the pivot table automatically updates to better analyze the data.

Filtering

Pivot tables support filtering row labels and column labels, and provide filter fields that do not participate in the main data classification and summarization but can be used to filter data.

Pivot tables support two types of filtering:

• Label filtering: Label filtering directly acts on the items of the current label dimension. For example, in the province dimension, we can filter out some provinces.
• Value filtering: Value filtering requires specifying a value dimension, and then filtering the summarized results of the value field. For example, we can filter out sales less than 1000.

Sorting

Sorting in pivot tables only takes effect on row/column fields. Sorting methods support ascending and descending order, using the localCompare method. We will support more sorting methods in the future, such as pinyin sorting.

Pivot tables support two types of sorting:

• Label sorting: Label sorting directly acts on the items of the current label dimension. For example, in the province dimension, we can sort by province name.
• Value sorting: Value sorting requires specifying a value dimension, and then sorting the summarized results of the value field. For example, we can sort by sales.

Summarizing

Pivot tables support 11 types of summarization methods supported by Excel, including: sum, count, data count, average, maximum, minimum, product, standard deviation, variance, population standard deviation, and population variance.

Pivot tables support multiple value dimensions, and you can customize the area (value position) and position (value index) of multiple value dimensions.

This is a very complex layout logic, so we only support table view. We will support more views in the future as needed.

Drilling Down

Pivot tables support the drill-down feature, which is not yet supported in Univer Sheets but will be soon.

Drill-down means that in the value area of the pivot table, users can double-click a cell to view the detailed data corresponding to that cell. For example, users can click a cell in the pivot table to view the detailed data of that cell to better trace the original data.

Grouping

Pivot tables support the grouping feature, which is not yet supported in Univer Sheet.

There are three main types of grouping:

• Date grouping: Date grouping means grouping date data by year, month, day, etc.
• Number grouping: Number grouping means grouping number data by certain intervals.
• Element grouping: Element grouping means grouping element data according to certain rules.