Pivot tables are a powerful feature available in many BI tools, such as Microsoft Excel, that allow you to summarise, group, and display data in a structured and flexible format. In AWS QuickSight, you can easily create pivot tables to analyse and present your data by selecting and configuring the fields you want.
Accessing the Analysis Screen
To work with pivot tables, you’ll need to open the analysis
Select an existing analysis or create a new one.
Keep in mind that dashboards are not directly editable — you must make changes within the underlying analysis that powers the dashboard.
Note:
Author Users: If you can view a dashboard but do not have access to its corresponding analysis, please reach out to another author user who may be able to grant access. If they also do not have access, we recommend contacting our support team to request the necessary permissions.
Reader Users: Readers can only interact with published dashboards and do not have permissions to view or modify analyses.
Creating a Pivot Table
Open your analysis.
Click Add Visual (top left) and select Pivot Table.
You can also click + Add in the Visuals pane and choose Pivot Table.
Alternatively, if you already have a visual on the screen:
Click Visualise (top left) to open the Visuals pane.
Under Change visual type, select Pivot table.
You will now see a blank pivot table visual with field wells for Rows, Columns, and Values.
Adding Fields
You can add rows or values to your pivot table in several ways.
Note: Columns can only be added using the drag-and-drop method (Method 3).
Method 1: Click a Field
In the Data pane, click the field you want to add.
Dimension fields (blue) → added as Rows.
Measure fields (orange) → added as Values.
Method 2: Use the Field Options Menu
In the Data pane, click the three dots (…) next to the field name.
Select Add to visual.
Dimension fields (blue) → added as Rows.
Measure fields (orange) → added as Values.
Method 3: Drag and Drop
Drag the field from the Data pane.
Drop it into the desired well in the Visuals pane: Rows, Columns, or Values.
Note: Dragging a field on top of another will replace the existing one.
Understanding Rows, Columns, and Values
The Visuals pane provides three key field wells: Rows, Columns, and Values. Each determines how your data is structured and displayed.
Rows
Adding a field to Rows creates a header column on the left side of the table, listing each distinct value for that field (up to filtering). Each unique value becomes its own row.
Adding multiple fields creates multiple header columns, automatically grouped by hierarchy.
Example: If you use Agency Name and Invoice Numbers, all invoice numbers will be grouped under their corresponding agency.
For best results, place higher-level categories first (to the left). For instance, use Agency Name before Invoice Numbers, since one agency may contain many invoices, but an invoice belongs to only one agency.
Columns
Columns work the same way as Rows, but transposed. Instead of header columns on the left, fields added here create header rows across the top of the table.
Values
Fields in Values show aggregated results based on the row and column intersections.
Aggregation options depend on the field type:
Numbers → sum, average, min, max, etc.
Dates → count, distinct count, min, max.
Text (Strings) → count, distinct count.
Adding multiple fields creates multiple value columns in the table.
Example: Payslip Data
Imagine a table with:
Rows: Company Name, Name
Columns: Pay Date (Month)
Values:
Total Payment → aggregated by Max
Margin → aggregated by Sum
Here’s how it works in practice:
As you can see, each value field is displayed in a separate column under each row and column combination.
For the worker CIS One in March 2025:
Total Payment (Max): 21,000.00 → the highest single payment that month by aggregation.
Margin (Sum): 54.00 → the total margin across all payslips that month.
Because workers are grouped by company, subtotals are also displayed
For Runners High CIS in March 2025:
Total Payment (Max): 30,000.00 → the highest single worker payment that month across the company.
Margin (Sum): 216.00 → the sum of all workers’ margins for that month.
Expanding and Collapsing Rows or Columns
You can expand or collapse rows and columns to control the level of detail shown:
Expand: Displays all subfields beneath a selected field.
Collapse: Hides subfields and displays only the subtotals for that field.
To expand or collapse:
Click a field value in either a row or a column.
You can also use the “Rows” button above the header columns to expand/collapse all rows.
For columns, click the column field names at the top of the table.
Editing Rows and Columns
For details on reordering and editing rows/columns, please refer to our dedicated article: [Adding Columns to Tables].
Swapping Rows and Columns
You can quickly switch the rows and columns of a pivot table:
Select the table.
Click the “Swap rows and columns” button in the top-right corner of the visual.
Formatting Pivot Tables
To format a pivot table:
Select the table.
Click the “Format visual” button in the top-right corner.
The Properties pane will appear on the right, offering a variety of customisation options.
Display Settings
Edit Title / Subtitle: Change titles and apply formatting options. You can also toggle their visibility.
Pivot Options
Layout:
Hierarchy (default) – Displays all row fields in a single header column with subtotals grouped accordingly.
Tabular – Displays one header column per row field, with subtotals shown in a separate row.
(Hierarchy is more compact, while Tabular is better suited for Excel exports.)
Values Position: Choose to display value fields in separate columns (default) or separate rows.
+/- Buttons: Toggle the display of expand/collapse buttons. Even if hidden, you can still expand/collapse by clicking field values.
Single Metric: Removes redundant headers when only one value field is in use.
Collapsed Columns (Tabular only): Hide column headers that are fully collapsed.
Headers
Style Rows Label: Option to apply header formatting to row labels.
Column Field Names: Show or hide column field names above the header row.
Row Height / Column Width (pixels): Adjust header dimensions.
Text Styling: Customise font, size, colour, and style.
Alignment: Adjust text alignment.
Background & Borders: Set background colour, border colour, and thickness.
Cells
Formatting options are the same as headers but apply to table cells instead.
Totals
Rows/Columns: Show or hide totals.
Label: Rename total rows/columns.
Position: Choose where totals appear.
Text Styling, Background, Borders: Same as headers and cells, but applied to totals.
Apply Styling to Cells: Apply formatting to the entire total row/column or only the label cell.
Subtotals
Level: Decide which fields display subtotals:
Last – Subtotals only the last applicable field. For instance, if you had 5 fields as rows, it will subtotal only field 4 (since field 5 would already have its values as the normal values in the table, so they wouldn’t need subtotalling).
All – Subtotals all applicable fields.
Custom – This applies subtotals to any applicable field based on your dropdown choices.
Other formatting options match those available for totals.
Row, Column, and Value Names
Rename any fields in the pivot table.
Conditional Formatting
Begin by choosing a field that you want to use. You can set conditional formatting on one or more fields. You can format tables and pivot tables using background colours, text colours, or icons.
We hope you find the information helpful. If you require further assistance or have additional inquiries, please don't hesitate to reach out to our support team 😄