Skip to main content
SheetTutor.
← All practice

Compare base items, cumulative percentages and ranks → · Calculated itemsCompare parent shares across both hierarchies →

About this reporting workflow

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.

Combine items on the working sheet

Follow original records through real inclusion and group-mapping formulas. Practise in the same worksheet; source and definitions stay in this browser.

Teaching · read-only · Report!A1

Step 1 / 6Keep every original observation

Inspect
More

A:F is the original source. G marks included records; H:I maps original items without replacing source labels. Teaching is read-only; switch to Your turn to edit.

Grouping!F2 · (blank)
No formula applied

Preparing the actual named-group worksheet…

Correct source/controls or explicitly restore edited calculation helpers. Previous valid results do not certify edited cells.

Copy this working source and calculation
Applied source and report choices
Replace the complete source from CSV or TSV

Use Record, Month (YYYY-MM), Region, Product, Status and Amount headings with one to fifty complete records. Explicit replacement clears definitions; direct Amount edits retain valid groups.

Combine items into named groups

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.

Source items on region

    10 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.

    Order the displayed items

    Sorting changes positions, not the selected records. Raw values are each item's original-record total or mean after report and Top/Bottom filters. Equal scores use ascending labels. Running totals follow this displayed order.

    Create and reuse a business order

    A business list stores labels even while they are absent. Present listed labels use the saved priority; unlisted labels follow in label order. Missing labels never create records. Spaces and punctuation remain literal; matching ignores case.

    Registered lists for this report

    Editor drafts do not change applied lists. Downloads use the registered definitions and current attachments.

    Practice: on Product rows, register ProductPriority with Lumen, Cedar, New kit and Harbor in that order. Attach it and check the sorting formulas. New kit stays absent until an original source record uses it; then it appears before Harbor automatically. Apply filters and compare the source-record count. Resetting both orders keeps the reusable library.

    Lists and attachments stay in this browser exercise; reload starts a fresh session. Export a list-only file to reuse it elsewhere. Native downloads save current manual item positions. They do not install an Excel application list or promise automatic native resorting after refresh.

    Row items
    1. East: 105
    2. North: 66.66666666666667
    3. West: 66.66666666666667
    Column items
    1. Cedar: 46.666666666666664
    2. Harbor: 90
    3. Lumen: 150

    Manual order remembers hidden items; new labels follow the saved items. Changing an axis field resets that axis. These text labels use browser collation, including numeric-looking labels. Business lists apply automatically in this browser. Native copies save their current manual order; native locale and global custom-list behavior remain separate.

    Region / ProductCedarHarborLumenTotal
    East
    North
    West
    Total

    Working copies capture the actual source, applied settings, mappings and formulas. Native source edits retain still-valid definitions; missing renamed members need explicit correction or ungrouping. Source replacement and axis changes clear groups. Native exports retain complete source/cache facts; ranking on a grouped field remains a saved raw membership snapshot and native refresh does not rerank it. The GETPIVOTDATA inspector targets A4 in the exported native report; this working grid shows its underlying calculation formulas. This increment has no desktop Excel refresh/edit/save certification.

    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.

    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 ↗
    Apply raw value filters before named grouping

    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.

    Applied definitions and report choices are literal cells H60:H68. Mapping and rank helpers recalculate in this workbook. Current formula files retain a fixed displayed member set; changed axes, definitions, newly observed labels or raw-ranking candidates need this workshop to rebuild the projection. Native grouped files retain saved original-record membership and the existing refresh boundaries.

    Base report companion: original Region × Product source proof

    Both reports use the same original records. This companion retains the course's unfiltered Sum task and source-formula proof; specialist steps operate the primary sheet above.

    Step 1 / 3Read the actual complete source

    Inspect
    More

    Inspect the highlighted cells. This step has no automatic change to apply. Teaching is read-only; switch to Your turn to edit.

    F2 · (blank)
    No formula applied

    Sum measures recorded amount. Count measures the number of records, including a numeric zero. Average divides the underlying sum by the underlying count.

    Loading your interactive spreadsheet…

    Practice · F2

    Current source values, formulas and report checks
    CellStored valueWritten formula
    I2
    I3
    I4
    I5
    I6
    I7
    I8
    I9
    I10
    L61
    M61
    N61
    O61
    L62
    M62
    N62
    O62
    L63
    M63
    N63
    O63
    L64
    M64
    N64
    O64

    Source and generated formulas stay in this actual workbook. The current-sheet copy preserves written source formulas. The native Excel report below uses current calculated source values and report settings.

    Views of the same source

    Clone the current report, then change its fields or filters. Each view keeps its own report settings; source edits affect every view.

    Keep up to four views. This download carries their native Excel PivotTables and one complete internal Source table/cache. Recalculate the active worksheet separately before saving a course check.

    Field captions

    Captions identify the displayed fields. Their original source headers, grouping keys and item labels stay in place. Use the worksheet formula bar to edit the same literal captions in AE2:AE5.

    Explain and check this report

    Use the task settings above. Predict the record count after report/value filters and the raw grand measure (rounded to two decimal places is accepted). The complete calculation checks use unrounded values.

    1. Start with source records

    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.

    RecordMonthRegionProductStatusAmountRemove
    R1
    R2
    R3
    R4
    R5
    R6
    R7
    R8
    R9
    R10
    R11
    R12

    Up to 50 source records in this learning worksheet.

    2. Build the report

    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.

    Order the displayed items

    Sorting changes positions, not the selected records. Raw values are each item's original-record total or mean after report and Top/Bottom filters. Equal scores use ascending labels. Running totals follow this displayed order.

    Create and reuse a business order

    A business list stores labels even while they are absent. Present listed labels use the saved priority; unlisted labels follow in label order. Missing labels never create records. Spaces and punctuation remain literal; matching ignores case.

    Registered lists for this report

    Editor drafts do not change applied lists. Downloads use the registered definitions and current attachments.

    Practice: on Product rows, register ProductPriority with Lumen, Cedar, New kit and Harbor in that order. Attach it and check the sorting formulas. New kit stays absent until an original source record uses it; then it appears before Harbor automatically. Apply filters and compare the source-record count. Resetting both orders keeps the reusable library.

    Lists and attachments stay in this browser exercise; reload starts a fresh session. Export a list-only file to reuse it elsewhere. Native downloads save current manual item positions. They do not install an Excel application list or promise automatic native resorting after refresh.

    Row items
    1. Cedar: 6
    2. Harbor: 2
    3. Lumen: 4
    Column items
    1. 2026-01: 4
    2. 2026-02: 4
    3. 2026-03: 4

    Manual order remembers hidden items; new labels follow the saved items. Changing an axis field resets that axis. These text labels use browser collation, including numeric-looking labels. Business lists apply automatically in this browser. Native copies save their current manual order; native locale and global custom-list behavior remain separate.

    12 of 12 records in this report

    Amount count

    Select a value to show its filtered source records, including totals and empty intersections.

    Product / Month2026-012026-022026-03Total
    Cedar
    Harbor
    Lumen
    Total

    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.

    Value field 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.

    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.

    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 →

    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!.

    product / month2026-012026-022026-03Grand total
    Cedar
    Harbor
    Lumen
    Grand total

    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)

    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.

    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.

    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.

    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.

    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.

    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.

    Named recipes in this exercise

    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

    Use the idea in Excel

    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 →