Name and format the value fields

Set a caption and number format for each Amount summary. The raw report and its native Values fields retain these settings when the source changes. Rounding is for display; records, totals and query results keep their actual numeric values. Separate grouped, percentage and repeated-Values exercises have their own settings.

Example −1234.567: -1,235

Applied captions identify value fields in queries. Renaming a caption invalidates the old caption; the numeric result is unchanged.

Practice: choose Sum above, name it Revenue, use US dollars with two decimal places and parentheses, then apply. Compare the formatted total with an unrounded GETPIVOTDATA result. A Percentage format displays 0.25 as 25%; it does not calculate a share of total. Additional filter measures retain their own captions and formats. Linked chart values stay exact and series names follow captions; chart axis formats remain separate.

Choose the value fields to compare

Choose Amount summaries in Values: how much, how many numeric records, or how much per record. Keep at least one field. This separate report uses the current product rows, Region/Status filters and raw Top/Bottom selection. Values occupies the columns.

Compare Revenue, Cost and Units side by side →

Combine items into named groups

Learn named grouping on the working worksheet →

Select two or more items on either axis and name the group. Unselected items stay separate. Groups change this practice report; your original source labels stay unchanged.

In Excel, select the item labels, choose Group, then rename the group. See the Microsoft grouping workflow ↗.

Report filters and raw Top/Bottom item filters run before grouping. Groups are not reranked. Each item belongs to at most one group on each axis. Equality is case-insensitive and literal; spaces, * and ? are not removed or treated as patterns.

12 included records from 12 source records. All measures use original records; group means are weighted by record count.

This grouped report has its own item order.

Formula/frozen copies include all original source rows, fixed-range calculations and membership definitions. The grouped PivotTable download uses native derived fields and retains the original source. If a raw Top/Bottom field is grouped, its saved item membership becomes a manual filter; native refresh does not rerank it. Changing source records or axes clears groups; filters and measures recalculate them. A Mac Excel refresh-on-open check passed for a named-group mean/query example. Other native refresh paths and edit/save/reopen remain unverified; numeric/date grouping has its own lab.

Compare bases, shares, running totals and ranks

The raw report above keeps its original measure. Compare a second view below. Percentages use the current filtered report; signed amounts can give negative shares or shares above 100%.

Row, column and grand margins are recalculated from the underlying records. Average percentages need not add to 100% across detail cells. Missing average combinations stay blank. Division by zero remains #DIV/0!.

I14: =IF(I6="","",I6/L9)

The native PivotTable download retains this value calculation and its base field. Refresh after editing Source. Native refresh, formatting and edit/save behavior have not been certified.

Retrieve a value with GETPIVOTDATA

Use field and item names to retrieve the current report’s grand total, subtotal or intersection. A4 identifies this PivotTable in the downloaded Report sheet. The result is calculated in B12.

This practice uses this report’s numeric sum, count or average, one row axis, one column axis and one item per field. Array item queries are not covered. It does not import arbitrary PivotTables or OLAP connections. Changing the report clears this query. In Excel, source edits require a PivotTable refresh before GETPIVOTDATA reflects them.

Create items inside a PivotTable field

Learn on the actual calculated-item worksheet

Add synthetic Product or Region items from the current original source. This Sum report has its own visibility and solve order; main-report filters, groups and ranks do not apply.

Compare each item with its parent

Build both row and column hierarchies from the current original source. This report uses its own optional item filter. Main-report ranks and filters do not apply. Inspect the denominator before interpreting a share.

Create and reuse a Pivot style

Style the raw report above and its linked chart’s native report. Source records, selected items and calculations stay the same. Separate percentage, grouped and hierarchy exercises keep their own appearance.

Column bands apply first; row bands override them. Row headers and column headings override body bands. Grand columns then grand rows have the final format. This flat report has no subtotal styles. A style file contains only colors, borders, boldness and display options.

Applied recipe and style history belong to this exercise. Export a style file to reuse it after reload.

Practice: keep one appearance across reports

Copy a recipe as OceanReport, set column-heading fill to #1E3A5F and grandTotalRow fill to #E8F0F7, enable row bands and disable column bands, then apply it. Change the source to confirm the recipe is retained. Download and load the style file in a second exercise.

No current style exercise check.

Group numeric ranges and real dates

This separate lab uses dated transactions. The main workshop’s Month field is text; its source and report stay unchanged.

Simplify chart controls on the actual worksheetRearrange Year, Quarter and Month across worksheet axesLearn default Sum, nonempty Count and Count NumbersShow raw money and percentages together on one working worksheet

Learn interval membership on the actual worksheet →

Try date filters and weekday summaries on one working worksheet