Pivot Wizard

The Pivot Wizard provides a simple way to create a pivot based on your data. It lets you aggregate, sort, and filter data to focus on what matters. With the Pivot Wizard, you can select the data model and fields to analyze and generate a pivot automatically.

You can access the Pivot Wizard from the Analysis tab of a workbook.

When to use the Pivot Wizard

• Providing versatility: Centralized access to summarization, quick analysis, aggregation, filtering and sorting—all in one place.
• Analyzing sales: Summarize sales data by product, region, or time period to identify trends and performance.
• Generating financial reports: Aggregate data for monthly, quarterly, or annual financial reports.
• Understanding customer behavior: Identify key demographics, purchasing behaviors, and loyalty metrics.
• Managing inventory: Summarize stock levels by category or location to optimize inventory.

Create a pivot table

1. Select an empty cell.
2. On the Analysis tab, select Pivot Wizard.
3. From the Environment and Data Model dropdown menus, select the environment and data model.
4. Enter a name in the Pivot Name text box.
5. On the Fields tab, search for or select the fields to include. Click and drag or double-click a field to add it.
6. To narrow the results, in the Filters tab, select Add Filter or Add Group. Adding filters helps target specific subsets of data, so you only create the pivot table you need.
7. Use And or Or to create logical relationships between filters or groups.
8. Click Create.
   The pivot table appears in the selected cell, and the PivotTable Fields panel opens on the right.
9. In the PivotTable Fields panel, drag fields to the Filters, Columns, Rows, or Values areas. The pivot table updates automatically based on your layout.
10. Click Save.

Edit a pivot table

You can edit the pivot table at any time. All changes are applied immediately and update the pivot.

1. On the Analysis tab, select Pivot Wizard.
2. Make the necessary changes.
3. Select Save.

Refresh a pivot table

Update a pivot table with the latest data from your data source to ensure your analysis reflects the most current information.

1. On the Toolbar panel, select Refresh.
2. Ensure the Pivots slider is toggled.
3. Select Entire Workbook to refresh all pivot tables, or Current Worksheet to refresh only those in the current worksheet.

Pivot Wizard properties

Fields

Property	Description
Environment	Define the environment for the pivot table.
Data Model	Define the data model to use for the pivot analysis.
Pivot Name	Specify the name of the pivot.
Fields	Add dimensions, descriptions, and measures to the pivot. Group By – Group data by the selected fields. Reset Group By – Clear all Group By, Order By, and Aggregation options. Order Group By – Sort data in ascending, descending, or no specific order. Aggregation – Reduce the number of rows returned by applying an operation (Min, Max, Count, Count Distinct, or None) to each field. Further calculations in the pivot table are based on this aggregation, not on raw data.

Filters

Property	Description
And, Or	Define the logical relationship between filters or groups using And or Or.
Add Filter, Add Group	Add Filter – Add an individual filter. Add Group – Add a set of filters grouped together. For each filter, specify the Field, Operator, and Value. Enter values manually or reference a cell. Use the prompt icon to select a field. To reference a cell, select the underscore in the Value field, then select the cell. This applies to both individual filters and groups. By default, character values are treated as cell references. To enter a text value instead, select the A icon next to the value field. When active, the icon turns blue, indicating the input is treated as text. If you use the prompt, it is automatically set as text. Use the prompt icon to select a field or value. You can also use wildcards to filter for specific values.
Clear all filters	Remove all filters and groups.

Use of wildcards for filtering

You can use wildcards to filter only to text values, not numbers. Wildcards help you find values that match specific patterns, such as those that contain, start with, or end with certain characters.

• ? replaces one character
• * replaces multiple characters

Wildcard exemple	Description
NA	Must match NA.
NA*	Must start with NA.
NA???	Must start with NA and contain exactly four characters.
NA???-???-A	Must start with NA, followed by any three characters, a -, three more characters, another -, and end with A (total length: 11 characters).

Options

Option	Description
Refresh on Open	Automatically refreshes the pivot when the workbook is opened.
Automatic Refresh	Automatically refreshes the pivot when a related filter is changed.
Top X	Limits the number of rows displayed in the pivot.
Worksheet	Specifies the worksheet where the pivot is created. Existing Worksheet – Inserts the pivot at the specified location in an existing worksheet. Location – Displays the sheet name and cell for the pivot. New Worksheet – Creates a new worksheet and inserts the pivot at the top-left cell.
Location	Specifies the cell location in the worksheet where the pivot is inserted.

Pivot Table Fields properties

After creating a pivot using the Pivot Wizard, the PivotTable Fields panel opens to the right. Use this panel to add, remove, and arrange fields, as well as filter and customize how your data is displayed in the pivot table.

Property	Description
Fields	Select fields from the data model using checkboxes. Fields are automatically added to the appropriate area based on their data type—numeric fields go to Values (for aggregation), while text or categorical fields go to Rows or Columns (for grouping). Drag fields to other areas as needed.
Filters	Drag fields here to add filters that limit data included in the pivot table.
Columns	Drag fields here to display data as columns in the pivot table.
Rows	Drag fields here to display data as rows in the pivot table.
Values	Drag numeric or calculated fields here to aggregate and display totals or other calculations.
Defer Layout Update	Select this option to pause updates while rearranging fields. Select Update to apply changes.
Views	Switch between saved field layouts or select from predefined views of the pivot table.