Create and Modify PivotTables

PivotTables are the primary tool in Excel for aggregating and analyzing large datasets without using complex formulas. For the MO-211 exam, you must be able to create them from scratch, manage their underlying data source, and configure how values are calculated and displayed.

Part 1: Creation and Placement

Basic Creation

1. Select any cell within your data range or Table.
2. Go to Insert > PivotTable.
3. Choose the data: Ensure the "Table/Range" box correctly identifies your source.
4. Choose the placement: * New Worksheet: Standard for isolated analysis.
   • Existing Worksheet: Required if the exam asks you to place the table at a specific cell (e.g., "Cell G5 of the Summary sheet").

Part 2: Working with Fields

Once created, use the PivotTable Fields pane to structure your report.

• Rows/Columns: Define the headers and categories.
• Values: The numerical data to be calculated.
• Filters: Global filters for the entire table.

Value Field Settings

By default, Excel sums numbers and counts text. To change this:

1. Right-click any value in the PivotTable > Value Field Settings.
2. Summarize value field by: Choose Sum, Count, Average, Max, or Min.
3. Show Values As: This is a high-frequency exam task. Use this tab to display data as a % of Column Total, % of Grand Total, or Running Total.

Part 3: Expert Grouping

As seen in professional workflows, grouping allows you to "bucket" data without modifying the source table.

Numerical Grouping

• Scenario: Grouping orders by price ranges (e.g., 0-100, 101-200).
• Action: Right-click a value in the Row labels > Group. Set the Starting at, Ending at, and By (increment) values.

Date Grouping

• Scenario: Converting daily transaction dates into Months, Quarters, or Years.
• Action: Right-click any date in the PivotTable > Group. Select the desired time intervals.

Part 4: Defer Layout Update (Performance)

When a PivotTable is built on a very large data source (Power Pivot model, hundreds of thousands of rows, or a slow connection), Excel recalculates after every single drag in the PivotTable Fields pane. This can make layout work painful.

The fix is the Defer Layout Update checkbox at the bottom-left of the PivotTable Fields pane:

1. Open the PivotTable Fields pane.
2. Tick Defer Layout Update (bottom-left, next to the Update button).
3. Drag fields into Rows / Columns / Values / Filters freely — the visible PivotTable will not change.
4. When the layout is final, click Update to refresh once.
5. Untick Defer Layout Update to return to live mode.

Part 5: Show Details (Drill-Through)

Any value cell in a PivotTable can be expanded back to the source rows that produced it.

• Fastest method: Double-click the value cell.
• Alternative: Right-click the value cell > Show Details.

Excel inserts a new worksheet containing a copy of the underlying records as an Excel Table. This is invaluable for auditing surprising totals.

To disable drill-through (e.g., on a shared dashboard): PivotTable Analyze > Options > Data tab > untick Enable show details.

Part 6: GETPIVOTDATA Function

When you reference a cell inside a PivotTable from a formula on another sheet, Excel does not write a regular =A1-style address. Instead, it auto-generates a GETPIVOTDATA formula that asks for the value by field name and item, not by cell coordinates.

Syntax

=GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], …)

• data_field — the value field name in quotes, e.g., "Sum of Sales".
• pivot_table — any cell reference inside the target PivotTable (typically the top-left cell).
• field/item pairs — narrow the result to a specific row/column/filter combination.

Example

=GETPIVOTDATA("Sum of Sales", $A$3, "Region", "West", "Year", 2025)

This returns the West / 2025 cell of the PivotTable rooted at $A$3, no matter where that cell visually sits.

Pros vs. Cons

Disabling Auto-Generation

If you prefer plain cell references, turn the auto-feature off:

• Click any cell inside a PivotTable.
• PivotTable Analyze tab > Options dropdown (left side of ribbon, next to the PivotTable Name box) > untick Generate GetPivotData.

Alternatively: File > Options > Formulas > untick Use GetPivotData functions for PivotTable references.

Part 7: Modify Fields and Options

After creating a PivotTable, you must be able to refine its structure and logic. The MO-211 exam tests your ability to change how data is aggregated, how it is displayed (percentages vs. totals), and how the overall table behaves.

Managing the Field List

The PivotTable Fields pane is your primary interface for selection.

• Adding/Removing: Drag fields between the four areas (Filters, Columns, Rows, Values) or check/uncheck the boxes.
• Field Order: The order of fields in the Rows or Columns area determines the hierarchy. Moving a field up or down changes the nesting of your data.
• Renaming Fields: You can rename a field by clicking it in the Values area > Value Field Settings > Custom Name.

Value Field Options

This is a high-frequency Expert task. You must distinguish between how data is calculated and how it is visualized.

1. Summarize Values By — the mathematical operation performed on the data.

• Common Options: Sum, Count, Average, Max, Min, Product, Distinct Count.
• Path: Right-click a value > Value Field Settings > Summarize Values By tab.

2. Show Values As — the value relative to other data in the table.

• % of Column Total — each row's contribution to its column.
• % of Grand Total — the weight of each cell against the whole dataset.
• Difference From — compares values against a "Base Item" (e.g., every month against January).
• Running Total In — cumulative growth analysis.
• Path: Right-click a value > Value Field Settings > Show Values As tab.

PivotTable Options Dialog

To change the behavior of the entire table, use the PivotTable Options dialog (right-click anywhere in the table > PivotTable Options).

Technical Checklist for the Exam

Official Resources:

• Create a PivotTable to analyze worksheet data
• Summarize values in a PivotTable
• Group or ungroup data in a PivotTable
• GETPIVOTDATA function
• Show or hide details in a PivotTable
• Show different calculations in PivotTable value fields
• PivotTable Options