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.

For a detailed example, see Pivot Wizard Example.

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	Defines the environment for the pivot table.
Data Model	Defines the data model to use for the pivot analysis.
Pivot Name	Specifies the name of the pivot.
Fields	Adds dimensions, descriptions, and measures to the pivot. Group By – Groups data by the selected fields. Reset Group By – Clears all Group By, Order By, and Aggregation options. Order Group By – Sorts data in ascending, descending, or no order. Aggregation – Reduces the number of data 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	Defines the logical relationships between filters or groups using And or Or.
Add Filter, Add Group	Add Filter – Adds an individual filter. Add Group – Adds a set of filters that are grouped together. For each filter, specify the Field, Operator, and Value. You can enter manually or reference in a cell. Use the prompt icon to select a field. To reference a cell, click 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, click the A icon next to the value field. When active, the icon turns blue, indicating the input is treated as a text. If you use the prompt, it's automatically set as text.
Clear all filters	Removes all filters and groups.

Options

Property	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 extraction at the specified location in an existing worksheet. Location – Displays the sheet name and cell for the extraction. New Worksheet – Creates a new worksheet and inserts the extraction at the top-left cell.
Location	Sets 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 below based on their data type—numeric fields go to Values (for aggregation), while text or categorical fields go to Rows or Columns (for grouping). You can then 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 your pivot table.
Rows	Drag fields here to display data as rows in your 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. Click Update to apply changes.
Views	Switch between saved field layouts or select from predefined views of your pivot table.