A pivot table PivotTable is a powerful tool that allows you to quickly extract and analyze large amounts of data–making seemingly endless data more manageable to analyze. Excel pivot tables allow users to summarize their data tables in a more meaningful way by allowing them to “pivot” (or rotate) data in tables to, for example, compare totals between different products, sort, summarize, group, and much more.
In this document, we go over how to use the Pivot Table feature, its application, setting utilization, and how to use the different layout options to view and analyze data seamlessly.
| Table of Contents | ||||
|---|---|---|---|---|
|
Prepare Data Source
Before diving into the pivot table's features, we will create a data source for our pivot table to use. You can create a pivot table by providing table data or by using existing table data. For this example, we will use an existing table and its data to create the new pivot table.
...
Add a Pivot Table
Now that we have our data in a table, we are going to add a pivot table from “Insert > PivotTable”.
...
There is now a blank pivot table.
...
To make data available to the pivot table–using the Pivot Table side panel to select and drag data to specified sections.
...
Setting Pivot Table Sections
Using our blank pivot table, we will make our data available by setting the pivot table sections: Row area, Column area, Value area, Filter area.
The pivot table will display depending on how you set these sections. Note that when you set a row or column field, it will become the row or column header of the table.
With these sections set, the table will now be displayed in your browser and can be interacted with to PivotTable to drill down into the data:
...
Apply Data Filters and Sorting
In this section, we set the Filter area for the entire pivot table, but there are even more ways you can drill down and apply to filter. The app allows users to apply and perform multiple filtering operations using Label Filters, Value Filters, and Manual filters. This section will show how to add a PivotTable Fields panel to interact with the data and also demonstrate how to use these different filters.
Please note in order to if use multiple filters when using the Pivot Tables, you must select “Allow multiple filters” in the Options dialog:
...
Manual Filters:
Manual filters allow users to filter through the different row or column fields. You might want to hide one or more items in a Row field or Column field. To do so, simply click the drop-down arrow for the Row or Column Labels, then remove the checkmark by the items you want to remove.
...
Label Filters:
Label filters are another way that users can specify the data they want to view. To hide even more items in the Row/Column fields, apply a Label Filter. Using the "less than" Label Filter operator, the example shows a user who is filtering the month column to only show the months from January to June:
...
Value Filters:
Value filters allow you to apply filtering to the values in the row fields to drill down specific data. The example below shows how to apply a value filter to show the top 3 items by cost:
...
Sorting:
The Pivot Table supports sorting by labels and values, even when a filter is already applied to a field. When sorting by labels, users can apply to sort to a field in the row area by selecting the sort/filter dialog and then choosing either ascending or descending order using the A-Z or Z-A options, similar to Excel.
...
When sorting values, the app gives you the options to sort from smallest to largest or largest to smallest:
...
Wrap up
Pivot Tables are a handy and interactive feature and can enhance your ability to calculate, summarize, and visualize their raw data to focus on comparisons, patterns, and trends.
If you have any questions, feel free to reach out to our team.
to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.
To create a PivotTable, push the 'PivotTable' button in the INSERT tab.
...
The details on how to use PivotTable are here.
The app uses Grapecity’s SpreadJS library.
...