Filter in Power Pivot: Ways to Narrow PivotTable Data

To filter Power Pivot data, add the Data Model to a PivotTable and use its field drop-downs, report filters, slicers, or timelines. For a filter that changes which rows are imported into the model, use Power Query or the data source instead; filtering the Power Pivot window itself does not remove rows from the model.

What “filter in Power Pivot” can mean

Power Pivot is Excel’s data-modeling feature: it stores tables, relationships, and calculations that can feed PivotTables and PivotCharts. The word “filter” can refer to several different operations, and they do not all have the same effect. A PivotTable filter changes what the report displays. A Power Query filter limits which source rows are loaded or refreshed. A filter in the Power Pivot Data View helps you inspect records but does not, by itself, permanently exclude them from the model.

That distinction is useful when a result seems surprising. Hiding a product in a PivotTable does not delete it from the model, and it does not change the source workbook. Conversely, filtering the source query can mean that excluded rows are not available to any PivotTable after the model refreshes. Decide whether you want a temporary report view or a smaller model before choosing a method.

Filter a Power Pivot PivotTable

For most report questions, filter the PivotTable rather than the underlying data. In Excel for Microsoft 365 on Windows, click inside the PivotTable and use the drop-down beside a row or column field. Select or clear values, or use the available label and value filters. A label filter acts on the field’s displayed names; a value filter selects items based on their summarized values, such as products whose total sales exceed a threshold.

You can also place a field in the Filters area of the PivotTable Fields pane. Excel then shows a report-filter control above the PivotTable. This is handy for a simple choice such as one region or one year, especially when you want the selection visible without taking up space in the row labels. It is less convenient when readers need to select several values quickly or compare choices interactively.

To apply a basic field filter:

Click a cell in the PivotTable so the PivotTable tabs and field list are available.
Open the field’s drop-down, then select the items to include or choose an appropriate label or value filter.
Click OK. Check the report’s totals and any other PivotTables that share the same model.

To remove a field’s selection, open its drop-down and choose the option to clear the filter for that field. To clear PivotTable filters more broadly, use PivotTable Analyze > Clear > Clear Filters. The precise options shown in a field menu depend on whether the field is in Rows, Columns, Filters, or Values and on the kind of data it contains.

Use slicers and timelines for interactive filtering

A slicer is a clickable control that displays the available values in a field. It is often clearer than a drop-down for a dashboard because users can see the active choices and select multiple items directly. To add one, select the PivotTable and choose PivotTable Analyze > Insert Slicer, then select the fields to show. Click a slicer button to filter the report; use the slicer’s multi-select control or the applicable keyboard selection method when you need more than one item.

For a date field, a timeline can provide a visual way to select a period. Select the PivotTable and choose PivotTable Analyze > Insert Timeline, then select the date field. The timeline lets a reader move among supported time levels and adjust the displayed date range. It requires a suitable date field in the model; dates stored as text may not behave as a real date field.

A slicer or timeline can control more than one PivotTable when those reports use the same Data Model and are compatible with the connection. Select the slicer, open the Slicer tab, and choose Report Connections (called PivotTable Connections in some Excel interfaces) to select the PivotTables it should control. If a PivotTable is missing from that list, check whether it was built from the same model; visually similar reports can still use different data sources.

How filters travel through the Data Model

A Data Model can contain separate tables joined by relationships, such as a Products table related to a Sales table through a product key. When a PivotTable uses a field from Products to filter sales, the relationship allows that selection to affect the related rows in Sales. This is why a slicer made from a lookup table can filter measures calculated from a much larger transaction table without putting every descriptive column into the transaction table.

In a typical one-to-many relationship, a filter travels from the unique, lookup side toward the table with repeated matching keys. For example, selecting a category in a Products table can narrow sales records for products in that category. Relationships need compatible key values, and the lookup side should contain unique keys. If keys are missing or do not match, some transactions may appear under a blank or unknown item rather than the expected category.

Filters also affect calculations through DAX filter context. A measure such as total sales is evaluated for the current PivotTable cell, including its row, column, report-filter, and slicer selections. Thus the same measure can show a different total by region or year without having to create a separate calculation for every selection. A filter changes the context in which the measure is evaluated; it does not normally rewrite the stored source rows.

Filter rows before they enter the model

If the goal is to exclude records from the model rather than hide them in one report, filter the query or source. In Excel for Microsoft 365 on Windows, use Data > Get Data to open or create a Power Query connection. In Power Query Editor, apply a filter from a column’s drop-down, then use Close & Load To to choose how the result is loaded, including the option to add it to the Data Model when appropriate.

This is useful for removing irrelevant history, excluding test transactions, or limiting a model to a defined set of records. It can reduce the volume of data that Excel has to store and work with, although the benefit depends on the source, model, and query. Keep in mind that a query filter affects the data returned on refresh. If next month’s rows meet the filter conditions, they can still be included; if the condition excludes them, refreshing will not bring them into the model.

Where an external database or other source supports it, filtering at the source can reduce the amount of data transferred. Power Query may fold some operations back to the source, but not every transformation or connection supports query folding. Do not assume that a filter is executed by the source: check the query and connection behavior when performance or data-transfer limits matter. Also preserve an unfiltered source or query if you may need to restore excluded records later.

Filter the Power Pivot Data View

To inspect records in the model, open the Power Pivot window from the Power Pivot tab by choosing Manage. In Data View, column drop-downs can be used to sort or filter the rows shown in that window. This helps when checking whether a particular key, category, or value exists in a table. It is a viewing aid, not a substitute for filtering the source query or a PivotTable, and it does not mean that other rows have been deleted from the Data Model.

The Power Pivot add-in must be available and enabled in desktop Excel for this workflow. On Windows, if the Power Pivot tab is absent, check File > Options > Add-ins and the COM Add-ins list for Microsoft Power Pivot for Excel; availability can vary by Excel edition and license. Power Pivot authoring is not available in Excel for the web, and Excel for Mac does not provide the same Power Pivot window and modeling workflow. A workbook with a Data Model may have different viewing or editing options across platforms.

Choose the right filter method

Match the method to the task rather than applying every filter in the same place. A temporary question from a reader usually calls for a PivotTable field filter, report filter, slicer, or timeline. A rule that should define the rows available to all reports belongs in the query or source. A quick check of what is stored in a model belongs in Data View.

Use a field drop-down for a quick selection or a focused label/value filter in one PivotTable.
Use the Filters area when a single report-level choice should appear above the PivotTable.
Use a slicer for visible, interactive selections, particularly on dashboards or reports that share a model.
Use a timeline when readers need to explore a proper date field by period or date range.
Use Power Query or the source when excluded rows should not be loaded into the model on refresh.
Use Data View filters to inspect records, not to define what the report or model permanently contains.

Common filtering problems and fixes

A slicer does not affect a PivotTable. Check Report Connections and confirm that the PivotTable uses the same Data Model. A slicer cannot automatically control a report built from an unrelated range or a different model. If the connection is shared but results still look wrong, check the relationship between the slicer’s table and the table used by the values.

Expected items are missing from the filter list. Refresh the PivotTable or its source connection, then check whether the query includes those rows. Also inspect spelling, spaces, data types, and key values. A date stored as text, for example, may not appear or group as expected in a timeline. A row removed by a Power Query condition cannot reappear through a PivotTable filter; the query itself must be changed.

The total does not equal the visible rows. A PivotTable may be displaying a measure evaluated in a broader or different context than the visible detail suggests. Review report filters, slicers, relationships, and the measure’s DAX definition. Also distinguish a grand total from the sum of visible subtotals: some measures are non-additive, so recalculating at the total level can produce a different result from adding displayed group results.

A blank item appears. Look for blank keys or unmatched foreign keys between related tables. A relationship can expose an unknown or blank category when a fact row has no corresponding lookup row. Repairing the source keys or adding the missing lookup record is usually more reliable than simply hiding the blank entry, which may conceal data-quality issues.

Frequently Asked Questions

Can I filter data directly in Power Pivot?
Yes, you can filter rows displayed in the Power Pivot Data View to inspect them. For report output, filter the PivotTable with its field controls, slicers, or timelines. For rows to be excluded from the loaded model, change the Power Query or source filter.

Does a PivotTable filter delete data from the model?
No. It changes the current report view and the filter context used by its calculations. The underlying model rows remain available, and another report can display them unless its own filters also exclude them.

Why can’t my slicer control another PivotTable?
The other PivotTable may not use the same Data Model, or it may not be selected in the slicer’s Report Connections. Select the slicer, open the Slicer tab, and inspect its connections. If the report is based on a different source, rebuild it from the shared model if that is appropriate.

Can I use a timeline with any date column?
A timeline needs a field Excel recognizes as dates in a PivotTable based on a compatible source. If the values are text or contain invalid dates, convert or clean the source data and refresh before inserting the timeline.

Is Power Pivot available in Excel for Mac or the web?
The full Power Pivot window and modeling tools described here are for supported desktop Excel for Windows editions. Excel for the web and Excel for Mac do not offer the same Power Pivot authoring experience, so use a supported Windows desktop version to manage those model features.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.