Choose row and column fields, compare sum, count and average, combine ordered Top/Bottom item, cumulative-percent or sum filters on both axes with ties, drill down to source records and extract a local detail copy, then inspect percentages, Index, base-item differences, cumulative percentages and competition ranks with real formula proofs. Build hierarchies on both axes and trace immediate or selected parent percentages to their original records.
Your reporting task Use Region as rows, Product as columns and Sum as the measure. Include all regions but only Paid records.
0 / 2 datasets checked successfully
1. Start with source records Use your own data Amounts can be negative or zero. Months use YYYY-MM text; these are category labels, not Excel date serials. Product labels match without regard to case.
Add a source record Up to 50 source records in this learning worksheet.
2. Build the report Region filterAll records East North West Status filterAll records Paid Pending Row fieldregion product month status Column fieldregion month status MeasureSum of amount Count of records Average amount
Keep top or bottom items Score items after Region and Status filters, across the other axis. Keep a number of items, or accumulate scores until a percentage or sum target is reached. Keep all exact ties at the crossing. Ordering controls separately set the displayed sequence.
Value filterAll items Top items Bottom items
12 of 12 records in this report
Amount count
Select a value to show its filtered source records, including totals and empty intersections.
Product / Month 2026-01 2026-02 2026-03 Total Cedar 3 2 1 6 Harbor 0 1 1 2 Lumen 1 1 2 4 Total 4 4 4 12
Chart the current PivotTable Rows become chart categories; columns become series. Every mark uses the same raw report above, including its source, report filters, Top/Bottom selection and saved item order. When ranking uses another summary, the chart includes every required Values field, so its browser and native series agree. Grand totals stay in the report and are excluded from marks. Separate grouped, calculated-item, parent and repeated-Values reports have their own settings.
Open linked chart 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 →
Open repeated values 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.
Group axisRow: product Column: month Group name
Source items on product Create group 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.
Product / Month 2026-01 2026-02 2026-03 Total Cedar 3 2 1 6 Harbor 0 1 1 2 Lumen 1 1 2 4 Total 4 4 4 12
Check grouped formulas in a copy Download formula report Download frozen report
Download grouped PivotTable
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.
Retrieve a value with GETPIVOTDATA Use field and item names to retrieve the current report’s grand total, subtotal or intersection. This query uses the grouped report. Its download includes the named groups and the current query in a native PivotTable. The result is calculated in B12.
Grand total First row total First intersection Try a missing item
Formula against this report Calculate query 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.
Microsoft GETPIVOTDATA reference ↗ 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%.
Show values asNo calculation % of grand total % of row total % of column total Index Running total % of base item Difference from base item % difference from base item % running total Rank smallest to largest Rank largest to smallest
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!.
product / month 2026-01 2026-02 2026-03 Grand total Cedar 25% 16.67% 8.33% 50% Harbor 0% 8.33% 8.33% 16.67% Lumen 8.33% 8.33% 16.67% 33.33% Grand total 33.33% 33.33% 33.33% 100%
Cedar / 2026-01
Raw count 3
Numerator 3
Denominator 12
Result 25% Raw cell: I6. Contributing raw cells: I6, L9.
I14: =IF(I6="","",I6/L9) Show this raw cell’s records Download calculation record
Download value calculation PivotTable
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.
Check this calculation with real worksheet formulas Source records are in A:F, raw aggregates start at I6 and the second matrix calculates this view from those raw cells. Opening and editing this copy does not change the report above.
Open calculation worksheet 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.
Grand total First row total First intersection Try a missing item
Formula against this report Calculate query 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.
Microsoft GETPIVOTDATA reference ↗ 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.
Open calculated items 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.
Open parent percentages Group records through several category levels Learn category hierarchy on the actual working worksheet →
Build a nested report from every valid source row above. This separate report has its own fields and measure; the main report’s filters, ranks and sorting do not apply. Labels match without case sensitivity, while spaces and punctuation remain literal. First source spelling names each item.
Open category hierarchy 3. Check your reporting choices Use Region as rows, Product as columns and Sum as the measure. Include all regions but only Paid records.
Check this dataset Use the idea in Excel Download Excel report
The download uses this report’s current data and controls. Source records are an editable Excel Table. Chart reports include an editable chart and live summary formulas. Pivot reports include a native PivotTable with the current grouping, measure and filters. Refresh the PivotTable after editing its Source table. Table/chart reports need a fresh export for added rows, new groups or filter changes.
The source and generated report formulas share the working worksheet. Follow the Microsoft desktop workflow ↗ to create the corresponding Table, PivotTable or chart in Excel.
Read the reporting guides → 