Create a Table and convert it back to a range

Automatic formula or manual exception?

Use at least five Table records and a correct calculated Amount column. Replace Records!E3 with a different number, change C3 and inspect E3. Reapply the Amount column, then Undo and Redo that repair. Check the full report last.

Make a manual exception · Change its Quantity · Restore the column · Undo needed · Redo needed

Append a record and follow the current Table

Include the staged fifth record first. Adding a complete record expands this actual Table and fills its declared Amount formula in one worksheet history action. Structured Audit formulas and a connected chart follow its current range. This page supports fifty records; it does not simulate a native typing gesture or refresh a PivotTable.

Refresh a Pivot from the current Table

Structured Audit formulas and the linked ordinary chart follow Table changes immediately. This Pivot keeps a cached summary until you refresh it. Compare Sum, numeric Count and original-record Average; its grand average is weighted by records. Refresh checks the actual Table and every grouped result with independent arithmetic and the worksheet formula engine.

Choose a default for new Tables

The workbook default applies when a new Table is created. Existing Tables keep their attached styles. Undo changes the workbook default; browser preferences are saved and forgotten separately.

Remember saves only this style’s settings on this device. The native Table workshop restores it after reset or reload. It does not save worksheet data. To test the current workbook default, select another non-overlapping rectangle with headers, give it a different Table name and create it.

Summarize the visible records in a real Total Row

The Table’s physical Total Row sits below its data. Quantity uses SUBTOTAL 109; choose the Amount calculation below. These 101–111 methods exclude filtered and manually hidden rows. Audit keeps its all-source totals.

Include all five staged records before showing totals. A nonempty destination is retained. Change source values or filters, reapply filters when needed, and check again. Hide removes the footer cells and keeps the chosen method; Undo restores the exact Table and its footer.

Create the complete Records Table and apply its Amount column first.

Compare and recover the same Total Row

1. Show Sum totals. Use a native value filter to keep at least two records while excluding others.
2. Apply Average and inspect the changed physical Amount formula. Use Undo to recover the exact Sum footer.
3. Hide the Total Row and use Undo to recover its cells, setting and Table range. Predict the visible count, visible Amount sum and all-source Audit Amount.
4. Check the current totals and complete source report, then check this lesson.

Sum footer shown: still needed

Average applied: still needed

Exact Sum footer recovered: still needed

Footer hidden: still needed

Exact visible footer recovered: still needed

Microsoft SUBTOTAL reference. Counts and zero-match errors depend on the selected function. This browser check does not certify native Excel edit/save.

Keep filter choices separate from their buttons

Choices in one column combine with OR; selected columns combine with AND. Hiding an arrow does not clear its choices. Use these external controls even when native header buttons are hidden. Values come from the complete Table, including filtered-out rows.

Practice button options and recover the same filters

1. Create Orders over A1:E6, apply Amount and link Audit. Select at least two columns so some, but not all, records match.
2. Hide all Table buttons and Undo. Hide one column button and Undo. Use the external summaries to confirm that the choices and matching rows stay unchanged.
3. Clear all Table filters and Undo to the exact prior state. Predict the current matching count and signed Amount. Run this formula check, the complete report check and the options check.

Two-column filter applied: still needed

All buttons hidden with criteria retained: still needed

Whole-Table setting recovered: still needed

Column button hidden with criteria retained: still needed

Column setting recovered: still needed

All filters cleared: still needed

Exact filters recovered: still needed

Separate the Table style from cell formatting

A Table style belongs to the table object. A direct cell fill takes precedence. Removing the style keeps the Table, its formulas and any direct formats. Clearing direct cell formats lets the Table style show again. Converting to a range removes the Table object and rewrites its references while retaining the current appearance.

This exercise clears direct cell formats, including number formats, within the selected Table. It does not delete values or formulas. It does not manage conditional-format rules.

No current Table. Undo conversion to restore it.

Compare and recover all three operations

1. Create Orders over A1:E6, apply its calculated Amount column and link the Audit. Apply an authored Table style.
2. Apply a direct fill to E2. Remove only the Table style and inspect E2 and the linked report. Undo to restore the exact styled Table.
3. Clear E2 direct formats; inspect the Table stripe. Undo to recover the direct fill, then Redo to clear it again.
4. Use Convert Table to range above. Inspect the ordinary formulas, then Undo to recover the exact Table. Complete the predictions and both checks.

Linked Audit