Summary of Content
Creating a Pivot Table
Plotting a Pivot Table
Aggregating Columns
Adding Subtotals
Formatting a Pivot Table
Sorting Columns and Rows
Define Sort Order and Sort By
Sorting Pivot Tables with Groups
Exporting a Pivot Table
Export Results & Limitations
Related Resources
Creating a Pivot Table
- Open the Visualization tab in the right hand panel of your worksheet.
- Click + button to add a new visualization. This will add a new visualization and open the visualization editor.
- Under ‘VISUALIZATION TYPE’, select pivot table by clicking on the
icon.
- Under the ‘Plot’ tab, select worksheet columns to include as fields in your pivot table.
Your table must include at least one Row or Column and one Value.
Columns added to the ‘Value’ field will automatically be aggregated.
- After populating the above fields, you will have a pivot table! The ‘Plot’ and ‘Format’ tabs in the visualization editor will allow you to further customize it.
Plotting a Pivot Table
To begin plotting your pivot table, follow the instructions for creating a pivot table.
Pivot tables must include at least one Row or Column and one Value. Multiple columns can be added to any of these fields.
To customize your plotted fields further, open the caret menu on the field. From here, you can take actions such as column formatting, sorting, adding subtotals, and aggregating.
The actions you can take on a column depend on which type of field it belongs to. For example, you can add subtotals to columns under the ‘Row’ and ‘Column’ axis fields but not under ‘Values’.
Aggregating Columns in a Pivot Table
Columns added to a pivot table’s ‘Value’ field will automatically be aggregated. Numeric columns receive a ‘Sum’ aggregation, while text and date columns are aggregated by ‘Count’.
To change an aggregation:
- Open the caret menu on the field.
- Hover your cursor over ‘Aggregation’ to open the aggregation sub-menu.
- Select a new aggregation type.
Adding Subtotals to a Pivot Table
Add an extra level of analysis to your pivot tables by creating row and column totals. These subtotals will inherit the aggregation used on the Value field in your pivot table.
- Open an existing pivot table or create a new one.
- Open the ‘Plot’ tab in the visualization editor.
- Click to open the caret menu on the plotted column you would like to see totals for. This column must belong to the table’s ‘Row’ or ‘Column’ field.
- In the dropdown menu, select ‘Show Totals’.
- Your new subtotals should now display on your pivot table.
Repeat steps 3 and 4 to generate any additional Row or Column subtotals.
Formatting a Pivot Table
To begin plotting your pivot table, open the ‘Format’ tab in your visualization editor.
This tab will allow you to customize the visual aspects of your table. Examples of customizations you can make include renaming the table, setting color schemes, and adding conditional formatting.
Sorting Columns and Rows
Define Sort Order and Sort By
By default, pivot table columns and rows are sorted Ascending by data source order.
- Open the dropdown menu on the column or row field you wish to sort.
This can be done from either the Visualization panel Plot tab or the pivot itself.
- In the dropdown menu, you can select a Sort Order and Sort By criteria. Suggested "sort by" options include the original data source order, row count, and the table's Value field (see "Store Total Sales" in the screenshot above).
To instead create a custom sort, click 'Custom Sort...'. This will open the pivot table sort modal. - Select a Sort Order.
- Under Sort by select 'field'.
Note: The Custom Sort Modal also provides the same Sort by parameters as are available in the field's dropdown menu. - Select a worksheet column or visualization field from the provided list.
- If you selected a worksheet column, you will be prompted to select an aggregation.
- Click Apply to sort your pivot table.
Sorting Pivot Tables with Groups
Some pivot tables contain multiple grouped worksheet columns plotted on the Column and/or Row field(s). The screenshot below shows an example of this scenario.
In this example, the fields “Company” and “Customer Name” are both plotted as Rows with “Company” positioned higher than “Customer Name”. In the case of companies “Proin LLC” and “Ac Consulting”, there are multiple customers per company. The Value cells indicate each customer’s total invoices (“sum(Invoice Id)”) per year (“extractYear(Invoice Date)”).
When sorting your pivot table, it is important to consider how data groupings impact sort results. If we return to the example above, sorting “Company” by the Value field “sum(invoice Id)”) will sort the list of companies by the total customer invoices for each company.
Please note: Default sorting is based on the rows’ max aggregations. Other aggregations can be applied on worksheet pivot tables from the Customer Sort modal. Dashboards DO NOT support custom sort.
The screenshot below demonstrates this sort order. A row Total column has been added to display the aggregated max values used in determining sort order.
Rows within groupings can also be sorted in relation to other rows within the group. In our example, we will now sort "Customer Name" by “sum(invoice Id)”. The sort order on “Company” is retained, while each group's rows respond to their new sort order.
Exporting a Pivot Table
You can download pivot tables and other visualizations from Worksheets and Dashboards.
Scheduled exports of your visualizations can also be created from the schedule modal in your Worksheet.
Learn how to download visualizations from a worksheet.
Learn how to download visualizations from a dashboard.
Learn how to schedule exports from worksheets.
Compare this table in Sigma to the export results below.
Export Results & Limitations
Pivot tables exported for Excel (.xlsx) will reflect most structural and formatting options applied to your pivot table in Sigma. The only limitations are:
- Data bars, conditional formatting and colored styling
- Custom column widths
Pivot tables exported as CSVs will reflect the applied structural formatting. The limitations are:
- Data bars, conditional formatting and colored styling
- Custom column widths
- Value formats (eg $ %)
