Build a report with Excel Tables
Read the Table’s structure, connect its fields to a report, then keep the report working as records and rules change.
Start with cell addresses and ordinary formulas. Each step explains its prerequisites and opens worksheet teaching before editable practice. Move freely between steps; save a practice record only after its complete current check passes.
Reading local course records…
Saved checks describe past attempts. Reopening a workshop starts its current workbook without a restored passing grade. These records contain the step, date and check counts; they contain no worksheet values or formulas.
1. Read the parts of a Table
Before you start: Read a cell address such as C4.
A header names a field. Each body row is one complete record. A physical totals row summarizes those records. A column reference such as Orders[Amount] selects body values; #All also includes the header and totals row.
- Identify header, body, totals and the complete rectangle at two worksheet locations.
- Compare a body-only amount sum with a sum that also includes the physical total.
2. Create a Table and grow its report
Before you start: Read headers and body rows.
Name the selected rectangle Orders. Quantity and Unit price are inputs; Amount is a calculated column. Link Audit to the named fields so an added record can affect the report without editing its row endpoints.
- Create Orders, apply its Amount column and link Audit.
- Include at least five body records. Try conversion to a normal range and recover the same Table with Undo before the manual-exception exercise.
- Replace E3 with a manual number, change C3 and observe the fixed exception. Restore the Amount column, then Undo and Redo that repair.
- Explain the manual exception and whole-column repair, then check the complete current report and five changed sources before saving.
3. Choose a Table scope before using a shortcut
Before you start: Create a real Table and distinguish its header from its records.
Table selection keys depend on focus, starting cell, platform and repetition. Predict exact column, record and whole-Table scopes, then select them in the actual browser grid. Desktop key references and browser selection controls have separate roles.
- Create Orders, calculate Amount and link Audit. Select your desktop reference platform.
- Start each of five selection tasks at its required cell. Predict its address and choose the reviewed key or pointer method before applying the actual selection.
- Answer the focus, outside-Table and repeated-press questions. Leave the Table with headers selected and pass the complete source report and selection checks.
4. Keep filter choices when hiding buttons
Before you start: Understand that filtering hides source rows rather than removing records.
Value choices in a column combine with OR; columns combine with AND. Whole-Table and per-column filter buttons control access to those choices without clearing them. External summaries retain an explicit clearing path.
- Create Orders over A1:E6, apply Amount and link Audit. Filter two columns so some records match.
- Hide all buttons and Undo; hide one column button and Undo. Clear all filters and Undo to the exact original selections.
- Predict the current count and signed Amount, then run the native filter proof, full report check and button-options check.
5. Compare visible totals with the complete source
Before you start: Use a real Table, calculated Amount and native value filters.
A physical Total Row belongs to the Table but lies outside its body references. SUBTOTAL follows visible source records, while Audit keeps the complete source total. Changing the aggregation or hiding the footer does not remove source records.
- Use Orders with five complete records, apply Amount and link Audit. Show Sum totals and filter to at least two records while excluding others.
- Apply Average and use Undo to recover the exact Sum footer. Hide the Total Row and Undo to recover its cells and Table range.
- Predict the visible record count, signed visible Amount and complete Audit Amount. Check the current footer, full source report and totals lesson before saving.
6. Predict a complete multi-column sort
Before you start: Keep a complete source record together when it moves.
Sort priority defines which field is compared first. Region ascending groups records; Amount descending orders records within each region. Equal keys keep source order. Report filters do not limit this source operation.
- Choose the Order complete records learning task below the source-operation controls.
- Set Region ascending then Amount descending, with removal off. Predict first/last retained Record, counts and signed retained Amount.
- Preview and check every row with formulas, apply using the lesson button, restore the source and check predictions and recovery.
7. Choose a key and review retained records
Before you start: Predict a complete two-level sort and retain a source copy.
Keeping one representative per Region/Product is a reporting choice. Sort Amount descending within Region to retain the largest Amount per pair. Omitted fields can differ, and removing a refund changes the signed total. A repeated key does not make a transaction erroneous.
- Choose Keep one representative per chosen key. Use Region ascending then Amount descending.
- Enable removal, select only Region and Product, use case-insensitive text and exact stored amounts, and predict the retained rows and signed total.
- Check the complete preview, apply it with the lesson button, restore all original records and check the lesson.
8. Report distinct and exactly-once combinations
Before you start: Distinguish selecting a representative from changing source transactions.
A two-column UNIQUE report compares complete Region/Product pairs. Default uniqueness keeps the first spelling of every pair; exactly once excludes every repeated pair. Both reports spill outside the Table and preserve all source records.
- Open the unique-combinations project and enter both source-dependent report formulas at A2 and D2.
- Compare distinct and exactly-once results; include the staged next Table record and inspect their changed membership.
- Check every current cell, both exact spill shapes and eight changed-source calculations before saving.
9. Use named columns and Table sections
Before you start: Create a named Table and distinguish inputs from calculated cells.
Inside a Table, @ selects the current row. Outside it, include the Table name. Header, body, total and all-section references select different cells, so the same aggregation can produce a different answer.
- Inspect the read-only steps on the actual Orders worksheet, including the current-row formula outside the Table. Switch to Practice on that same sheet.
- Keep a source-dependent column, totals formula and every summary correct.
- Make an actual source or formula change, then check the complete current and retained source cases before saving.
10. Fill a region-by-month report
Before you start: Use explicit Table column references.
Copying a formula and filling a Table reference have different column behavior. Lock the Region field while month fields move, and keep the appropriate A1 row or column fixed.
- Open the copy-and-fill project. Build all nine report results from one formula.
- Compare ordinary copy with horizontal and vertical fill.
- Check the complete report and changed sources.
11. Choose a return field by its header
Before you start: Match an item and read a header row.
A fixed column number identifies a position. Matching a header identifies a field. Moving the key away from the left edge also shows the directional limit of VLOOKUP.
- Open dynamic-field lookup and solve all six current queries.
- Compare fixed VLOOKUP, VLOOKUP/MATCH and INDEX/MATCH.
- Check every current result and twelve changed-source calculations.
12. Anchor a running total to its Table
Before you start: Use INDEX to return a Table reference.
A running total starts at the first data record and ends at the current record. INDEX keeps that start tied to the Table. Record order changes intermediate balances even when the final amount is unchanged.
- Open the running-total project. Apply the first-amount and source-row references.
- Fill all fifty guarded report positions, retaining blank unused rows.
- Check the Table column, report and all seven changed-source cases.
13. Drive inputs and row rules from a Table
Before you start: Use named fields and complete the lookup workbook.
A named Table field can supply valid query choices and a row rule can compare each record with a named threshold. Inspect the actual current choices as records and thresholds change.
- Open the lookup project and its Table-driven input lists and row-rule section.
- Attach the named lists, apply a source-dependent review rule and check the full policy.
- Keep every current list, query and five changed-input calculations correct.
14. Filter complete records and reuse a style
Before you start: Distinguish a source record from a visible record.
Choices within a field combine with OR; different fields combine with AND. Availability counts consider the other fields. A no-data item can remain selected, and a saved style changes appearance without changing membership.
- Explore the worksheet steps, switch to Your turn and select at least two fields.
- Apply a registered custom style to at least two working slicers.
- Check all masks, availability counts, summaries, visible rows and spill shapes with real formulas.
15. Create and remove working slicers
Before you start: Use a field filter and distinguish it from the complete source.
A removed slicer must not leave an invisible filter in this project. Removing clears its field. Undo restores the control and exact selection; adding after removal begins at All. The source records remain complete.
- Explore the worksheet steps, switch to Your turn and select one Product item.
- Remove Product, Undo to restore its selection, Redo removal, then add Product again.
- Run the complete formula check, then Check control tasks before saving.
16. Recover a hidden selected item
Before you start: Understand OR within one field and AND across fields.
Caption visibility, item columns, sorting and no-data display affect the controls. They do not clear selected filters. An external selection summary lets you recover a selected item that the control currently hides.
- Explore the missing-pair worksheet step, then switch to Your turn and complete the current source task.
- Set Region selector, hide its caption, use two columns, descending order and no-data items last; hide no-data items while its no-data Region remains selected.
- Clear Region from the external summary. Predict the resulting count and signed amount, then run both checks.
17. Keep a slicer fixed or tied to its cells
Before you start: Distinguish a panel position from its width and height.
A fixed panel keeps its original rectangle. A movement-only panel follows the worksheet cells but retains its size. A panel tied to both endpoints moves and scales as grid dimensions change.
- Use original anchors and spans: Region (0,5), Product (5,5), Month (0,23), each four columns by sixteen rows.
- Set Region fixed, Product move-only and Month move-and-size. Enable grid view and change column/row dimensions to 100/24 px.
- Predict what changes for the three panels, inspect their real rectangles and run the formula and control-task checks.
18. Reuse a Table style and set a default
Before you start: Complete the native Table and Audit check.
An attached style controls an existing Table. A workbook default supplies the style for a newly created Table. Editing a shared definition changes its attached Tables; changing the default leaves their attachments alone.
- Create and check the native Table with at least five records.
- Apply a registered custom style and choose it as the workbook default.
- Inspect a second Table, shared editing and style-only transfer; save the current checked styled Table.
19. Remove a Table style or a local override
Before you start: Distinguish the Table object, its reusable style and direct cell formats.
Removing a Table style leaves the Table and cell overrides intact. Clearing direct cell formats reveals its attached style. Converting to a range replaces the Table object while retaining values and current appearance.
- Create Orders over A1:E6, apply its Amount column, link the Audit and apply an authored style.
- Apply a direct E2 fill, remove the Table style and Undo. Clear E2 formats, Undo and Redo.
- Convert to range and Undo. Complete the predictions and the full report and formatting checks.
20. Compare live charts with a refreshed Pivot
Before you start: Keep a complete current Table and Audit.
A Table-bound chart follows its supported source changes. A Pivot retains its last summary until refresh. Keep the complete source separate from grouped totals and compare their current identities before downloading.
- Bind the ordinary chart to the current Table.
- Refresh the Table-bound Pivot and inspect grouped Sum, numeric Count and record-weighted Average.
- Check the current source and complete formula cases before saving the current cache record.
Read the same Table parts at two locations
Open the actual two-location worksheet →Continue to creating a Table →This course connects the current workbook exercises. More Table operations and their guided lessons are still being added. Desktop Excel controls and saved-file behavior need their own practice.