Pivot Table
The Pivot feature lets you analyze multi‑dimensional data directly in Excel without extracting raw data first. It creates a cube‑style structure that allows you to summarize, slice, and explore enterprise data quickly and interactively.
When to use Pivot
- Analyzing large datasets interactively: Explore your data model without loading raw data into Excel.
- Summarizing data in rows and columns: Compare values by account, product, region, and more.
- Comparing values across dimensions: Analyze trends such as month‑over‑month or year‑over‑year.
- Drilling down into totals or values: View the underlying records behind high‑level summaries.
- Applying dynamic filters: Filter results without writing formulas.
- Exploring data using Pivot Table tools: Rearrange dimensions using Excel’s native Pivot features.
- Combining totals, counts, and averages: Build complex summaries in a single table.
Create a pivot table
- In the Add‑ins tab, select Pivot Table.
- In the dialog, configure the Data Model and Environment.
- Under Dimension Settings, add filters to narrow the dataset as needed.
- Under Columns, select the fields to include and arrange them using the arrow buttons.
- Choose the output location for the pivot table.
- Select Ok to insert the pivot table.
- Use the pivot table pane to add Values, Filters, Rows, and Columns.
- To filter using a Reporting Tree node, select the reference cell from the Reporting Tree in the Reporting Tree node field.
note
If the formula uses a Reporting Tree node, it is automatically applied as a filter.
Pivot table properties
| Property | Description |
|---|---|
| Data Model | Select the data model to query. |
| Environment | Choose the environment for the pivot. |
| Dimension Settings | Adds filters to narrow the data. |
| Columns | Review the available fields you can add to the pivot. |
| Selected Columns | Arrange the fields using the arrow buttons. |
| Output Location | Choose where to insert the pivot table:
|
Apply dimension filters
- Select a Dimension to filter results for the chosen field.
- Choose a filter type:
- List – Include or exclude specific values. Wildcards are supported.
- From–To – Define a numeric or string-based range.
- Select All – Include all values in the dimension (default).
- Define filter values using a prompt, a cell reference, or manual entry.
- Select Ok to apply the filter. The complete formula appears in the Excel formula bar.
Use of wildcards for filtering
Use wildcards to filter text‑based values. Wildcards apply only to text—not numbers—and you can use only one filtering method at a time (wildcard, list, or range). For example, you cannot combine patterns like 4???;[4000:4999].
Supported wildcards
?replaces one character*replaces multiple characters!excludes characters or ranges;defines a list of values[ ]defines a numeric or text range
Filtering Examples
| Example | Description |
|---|---|
4000 | Match the value 4000. |
4000;5000;6000 | Match 4000, 5000, or 6000. |
4* | Match any value starting with 4. |
4??? | Match values starting with 4 and containing exactly four characters. |
[4000:4999] | Match values between 4000 and 4999. |
!4000 | Exclude 4000. |
!4* | Exclude values starting with 4. |
!4??? | Exclude values starting with 4 and containing four characters. |
![4000:4999] | Exclude values between 4000 and 4999. |
4???-???-10 | Match values starting with 4, followed by -, any three characters, another -, any three characters, and ending with 10 (total length: 11 characters). |
!1000;2000;7000 | Exclude 1000, 2000, and 7000. |
Refresh a pivot table
Use the Pivot Table Refresh button in the Add-ins tab to update all pivot tables in your Excel workbook with the latest data.
Refresh the pivots when:
- Filters or Reporting Tree nodes have changed – ensure the pivot reflects updated selections.
- You need the most recent data – reload values from the data model.
- Pivot tables have not updated automatically – pivots do not refresh when filters change, so a manual refresh is required.