Filter pivot fields and drill into summary records

Filter the pivot itself, then drill into a numeric result to see the records behind it. Filtering the source sheet with AutoFilter is not a substitute: Calc’s pivot analysis still uses its configured source list.

Start with a small, reconcilable pivot

This exercise follows LibreOffice Calc Guide 26.2, using English standard desktop menus on Windows/Linux. Native execution and save/reopen checks remain unverified. Use a local ODS practice copy, with no confidential source data.

Enter A1:D7 on Sales:

ID	Region	Channel	Amount
S1	North	Online	120
S2	North	Retail	80
S3	South	Online	90
S4	South	Retail	140
S5	North	Online	60
S6	South	Online	30

Use an existing practice pivot with Region in Row Fields, Channel in Filters and Sum of Amount in Data Fields. If needed, select this complete table, choose Insert > Pivot Table, use Current selection and place that layout on a new sheet. The pivot source-range tutorial covers creation and source maintenance in more detail; they are prerequisites, not this exercise’s main task.

With every channel included, North is 260, South is 260 and the grand total is 520. Keep these control totals written outside the pivot’s possible expansion area.

Filter without losing the meaning of the total

1. Click the arrow beside the Channel filter-field button above the pivot.
2. Select only Online and confirm. The status beside the filter should show Online rather than “- all -”.
3. Check North 180, South 120 and grand total 300. This is the online subtotal, not the total business amount.
4. Open the Region row-field drop-down and include only North. Now the displayed amount is 180. Multiple independent pivot filters can combine; remember both when reconciling.
5. Re-enable both regions but keep Channel set to Online. Confirm the 300 subtotal returns before drilling.

Field-heading menus allow individual item inclusion. The separate Filter command, available by right-clicking the pivot, offers criteria filtering; a visible Filter button requires Add filter in the pivot layout. Use one mechanism deliberately rather than stacking unexplained filters.

Drill into records, not just another category

Right-click within the pivot and choose Properties. Ensure Enable drill to details is selected in the layout options, then confirm. Double-click the numeric 180 for North, not its North label. Calc should create a new sheet listing S1 and S5, whose amounts 120 + 60 reconcile to 180.

Double-clicking a row/column category instead has a different purpose: it can subdivide that category by another field. The Show Detail dialog asks which field to use; Data > Group and Outline > Hide Details collapses that breakdown. A category drill is not the same as extracting underlying records.

Treat the new detail sheet as an inspection output. Editing its 120 does not repair the original S1 record or establish a live source link. For a correction, return to Sales.

Prove the extraction is a snapshot

Change Sales.D2 from 120 to 150. Refresh the original pivot with Data > Pivot Table > Refresh. With Online selected, North becomes 210, South remains 120 and the total becomes 330. Drill into North again and compare the new extraction with the old one. The new contributing amounts must be 150 and 60; do not use the old sheet as the current report.

Restore 120 in the source, refresh, then restore all channel and region selections. Confirm 520. Save and reopen the ODS; inspect the filter status as well as totals, and identify or remove obsolete practice extraction sheets after retaining any needed notes.

If drilling does nothing, check the layout option and that a numeric aggregate is selected. If a correct record appears absent, clear every pivot filter before investigating the source. If totals show record counts instead of amounts, inspect Sum versus Count and numeric source types. Never “fix” a subtotal by typing over pivot output.

Official references

• LibreOffice 26.2 Calc Guide

Sources were captured on 20 October 2026 and reviewed from retained snapshots. All outcomes above are expected checks, not a claim of native execution.