Pivot

The Pivot feature lets you analyze multi-dimensional data directly in Excel without extracting raw data first. It creates a cube-style structure, allowing you to summarize and explore enterprise data quickly.

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 data by account, product, region, and more.
• Comparing values across dimensions: Analyze trends such as month versus year.
• Drilling down into totals or values: View the 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 one table.

Create a pivot table

1. In the Add‑ins tab, select Pivot Table.
2. In the dialog, configure the Data Model and Environment.
3. Under Dimension Settings, add filters to narrow the data as needed.
4. Under Columns, select the fields to include. Use the arrow buttons to arrange them.
5. Choose the output location.
6. Select OK to insert the pivot table.
7. Use the pivot table pane to add Values, Filters, Rows, and Columns.
8. To filter using a Reporting Tree node, select the reference cell from Reporting Tree in the Reporting Tree node field.
   Note: When you edit this field, the formulas are automatically refreshed.

Pivot Table properties

Property	Description
Data Model	Specifies the data model.
Environment	Specifies the environment.
Dimension Settings	Adds filters to narrow down the data. See Apply dimension filters for more information.
Columns	Lists the available fields that can be selected.
Selected Columns	Fields to include in the pivot structure. Use the arrow buttons to adjust their order.
Output Location	Defines where the pivot table is inserted: New Worksheet – Inserts data into a new sheet. Existing Worksheet – Inserts data at a specified starting cell.

Apply Dimension Filters

1. Select a Dimension to filter results for the specified field and operation.
2. Choose one of the following filter types:
   • 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).
3. Use a prompt or cell reference to define filter values, either by typing them manually or linking to another cell.
4. Select OK to insert the formula. The complete formula appears in the Excel formula bar.

Use of wildcards for filtering

Wildcards apply only to text values, not numbers. You can use only one filtering method at a time—either a wildcard, a list, or a range. For example, you cannot combine them like this: 4???;[4000:4999].

Supported Wildcards

• ? replaces one character
• * replaces multiple characters
• ! excludes characters or ranges
• ; defines a list
• [ ] defines a numeric or text range

Filtering Examples

Example	Description
4000	Must match 4000.
4000;5000;6000	Must be one of these values: 4000, 5000, or 6000.
4*	Must start with 4.
4???	Must start with 4 and contain exactly four characters.
[4000:4999]	Must be a value between 4000 and 4999.
!4000	Must not be 4000.
!4*	Must not start with 4.
!4???	Must not start with 4 and must not have four characters.
![4000:4999]	Must not be a value between 4000 and 4999.
4???-???-10	Must start with 4, followed by -, any three characters, another -, any three characters, and finish with 10 (total length: 11 characters).
!1000;2000;7000	Must not be in the list: 1000, 2000, or 7000.

Refresh a pivot table

The Pivot Table Refresh button, located in the Add-ins tab refreshes all Pivot tables in your workbook with the latest data.

Use this when:

• Filters or Reporting Tree nodes have changed.
• You want to ensure the pivot reflects the most recent data.

Pivot tables do not refresh automatically when filters change, so manual refresh is required.