Filter complete records, preview multi-column sorts and deduplicate selected keys with before/after audits and undo, then practice named-table formulas in a real worksheet.
Teaching · read-only Your turn · edit this sheet
DatasetQuarter A — sales and a refund Quarter B — smaller totals and a zero Restore source data
Step 1 / 5 Read every complete source record
← Previous 1 2 3 4 5
Next step → Inspect A1:F13 Focus A2
Demonstrate step More Inspect the highlighted cells. This step has no automatic change to apply. Teaching is read-only; switch to Your turn to edit.
A2 · (blank)No formula appliedEach row carries its Record, Month, Region, Product, Status and signed Amount together. The source includes records excluded from the displayed report.
Your reporting task Show only East / Paid records. Sort amount from highest to lowest, preserving each record and its fields.
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 Amount orderRecord ID Smallest first Largest first
12 of 12 records in this report
Record Month Region Product Status Amount R1 2026-01 East Cedar Paid 120 R2 2026-01 West Cedar Paid 80 R3 2026-01 East Lumen Pending 50 R4 2026-02 East Lumen Paid 200 R5 2026-02 West Cedar Paid -20 R6 2026-02 North Harbor Paid 90 R7 2026-03 East Cedar Paid 60 R8 2026-03 West Lumen Paid 140 R9 2026-03 North Harbor Pending 30 R10 2026-01 East Cedar Paid 40 R11 2026-02 North Cedar Paid 0 R12 2026-03 North Lumen Paid 110 Visible-record sum 900
All-source amount: 900. A filter changes the visible-record total, while the source stays intact.
3. Check your reporting choices Show only East / Paid records. Sort amount from highest to lowest, preserving each record and its fields.
Check this dataset Reusable filters for an ordinary Table Start from all 12 original source rows. Select multiple items within a field (OR); fields combine with AND. These controls are independent of the report filters above. Replacing or editing the report source resets this selection to All.
Open Table slicers Use table names instead of fixed row endpoints The complete source is named Orders. Its 12 data rows occupy A2:G13; A1:G14 also includes the header and physical totals row. Edit rows or display headers below while retaining your applied formulas. This complete source is independent of report filters.
=IF([@Status]="Paid",[@Amount],0) calculates the qualified amount in each data row. =SUBTOTAL(109,Orders[Paid amount]) totals that column. Column references include data rows by default; #Headers, #Totals and #All select different sections.
Open named-table practice Microsoft: structured Table references ↗
Report unique Table combinations without removing records Two records can share a Region and Product while recording different transactions. UNIQUE returns a new list outside the Table and leaves those source rows intact. By default it keeps the first occurrence of each complete pair. Exactly once keeps only pairs that occur in one source record.
Compare Orders[[Region]:[Product]] with a single column or the whole Table: each selects a different identity. Keep by_col FALSE for row combinations. Text matching here is case-insensitive, spaces remain significant and error-looking labels stay text. A refund or zero Amount is still a complete record.
Open unique-combinations project One formula → nine results
Build a region-by-month report Copying a Table formula and filling it are different operations. Keep the Region column fixed while Jan advances to Feb and Mar; then let the report’s region row move down.
Open copy & fill project Choose a field, keep the record
Look up a Table value by its header A column number describes a position. A header describes a field. Compare both, then move the Record column to see why the lookup direction matters.
Open dynamic-field lookup project The first row is a reference
Build a running total that follows its Table A running total depends on record order. Compare a moving cell range, a locked first cell and INDEX’s first-Table-row reference, then add a refund and change the order.
Open running-total project Change a Table’s columns Use a separate Lines Table with Item, Units, Rate and Charge. Insert an input or calculated column, move it, rename it or remove it. This copy stays independent of the report and seven-role exercise.
Open column lab 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 → 