For a quick filter, open the PivotTable field’s drop-down, clear Select All, check the values you want, and click OK; for a list that changes regularly, add a helper column to the source data and filter the PivotTable to rows marked as included. The options below cover manual selection, slicers, formulas, Power Query, the Data Model, and VBA, so Excel users can choose an approach that fits their list size and refresh routine.
1. Select the Values in the PivotTable Field Menu
In the PivotTable, open the drop-down beside the row or column field, clear Select All, tick each required item, and click OK. This is the quickest built-in option when the list is short and changes infrequently, such as showing only the East and Central sales regions for a one-time report. The filter applies to that PivotTable, not the source table, and you can revisit the field menu to change the selection; if the list of items is long, use the menu’s search box or a slicer instead of scrolling through every checkbox.
2. Use the Field Menu Search Box
When a PivotTable field contains hundreds of labels, type part of a label into the search box at the top of its filter menu, then select the matching items and apply the filter. Searching for “North” can quickly locate labels such as North and North-East, but search results are not a substitute for checking exact values: confirm the checkboxes before clicking OK, especially if similar names exist. This method is useful for an occasional selection from a large field, while a saved list of exact values is better handled with a helper column or a query.
3. Add a Slicer for Clickable Filtering
Select a cell in the PivotTable, then choose PivotTable Analyze > Insert Slicer and check the field you want to filter, such as Product or Region. Click a slicer button to filter to one item, or use its Multi-Select control to select several buttons; the clear-filter icon restores all items. Slicers make the active selection visible to report readers and are convenient on dashboards, though selecting a very long list of individual buttons is slower than maintaining a separate criteria list.
4. Put the Field in the Filters Area
In the PivotTable Fields pane, drag the field you want to filter into Filters, then open the filter above the PivotTable and enable Select Multiple Items before checking the desired entries. This creates a compact report filter above the table, which works well when a user needs to choose several values without adding another row of labels to the report. It is still a manual selection: new source values may appear after a refresh, but Excel will not automatically match them to a separate allowed-values list.
5. Create a Helper Column with COUNTIF
For a reusable, editable list, put source values in column A, the allowed values in G2:G20, and add a header such as Keep? in B1; with the first source value in A2, enter =AND(A2<>"",COUNTIF($G$2:$G$20,A2)>0) in B2 and fill it down. The formula returns TRUE when A2 appears in the allowed list and FALSE otherwise, so a PivotTable can include the helper field and filter it to TRUE; for example, an allowed list containing East and Central marks those source rows TRUE when their region matches. Add new source records to the Excel Table so the formula and PivotTable source can expand, then refresh the PivotTable after changing the list; note that COUNTIF treats * and ? as wildcards, so this version is not an exact-match solution for list entries containing those characters.
6. Merge the Source and List in Power Query
On Excel for Microsoft 365 on Windows, convert the source and allowed-values range to tables, select each table and choose Data > From Table/Range, then use Home > Merge Queries in the Power Query Editor. Select the matching key column in both queries and choose Inner as the join kind; the resulting query contains only source rows whose key occurs in the list, and you can load that result to a worksheet and build the PivotTable from it. This is a strong choice for larger datasets or repeatable imports because a refresh reruns the match instead of relying on hand-selected PivotTable checkboxes; Power Query features vary by Excel for Mac release, so check that your Mac build offers the merge steps before designing around them.
7. Use an Advanced Filter to Make a Matching Extract
Excel’s Advanced Filter can copy source rows matching a criteria range to a separate location: put the source field’s exact header above the permitted values, then choose Data > Advanced, select the source list and criteria range, and choose Copy to another location. Multiple criteria listed vertically beneath the same header mean “match any of these values,” which makes this a practical way to create a clean extract before inserting a PivotTable. The extract is a snapshot, not a live link to the criteria list, so repeat the Advanced Filter when the source or permitted values change and then refresh the PivotTable based on the extract.
8. Build the PivotTable with the Data Model
For a workbook where an allowed-values table is a maintained dimension, load both it and the fact table into the Data Model, make sure the allowed table has one unique row per key, and create a one-to-many relationship from that key to the matching fact-table column. Build the PivotTable using the Data Model and use the allowed-values table’s field in the rows or a slicer; selecting entries from that table filters related facts without manually hiding each PivotItem. This design suits recurring analytical workbooks with related tables, but it requires a clean unique key and is more setup than needed for a one-off report; Power Pivot and Data Model authoring options differ on Mac, so Windows is the safer platform for creating this model.
9. Apply an Exact List with a VBA Macro
A macro can loop through a standard, non-OLAP PivotTable field and hide each PivotItem not found in a control list, which is useful when the allowed values are generated elsewhere or a report must be rebuilt repeatedly. In this example, the PivotTable is named PivotTable1 on the Pivot sheet, its field is Region, and the permitted labels are on the Control sheet in A2:A20; the field must contain at least one value from that list. Save a copy of the workbook before running a macro, because VBA changes cannot be undone with Ctrl+Z.
Sub FilterRegionToControlList()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim cell As Range
Dim allowed As Range
Dim found As Boolean
Set pt = ThisWorkbook.Worksheets("Pivot").PivotTables("PivotTable1")
Set pf = pt.PivotFields("Region")
Set allowed = ThisWorkbook.Worksheets("Control").Range("A2:A20")
pt.RefreshTable
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
For Each pi In pf.PivotItems
found = False
For Each cell In allowed.Cells
If Len(CStr(cell.Value2)) > 0 Then
If StrComp(CStr(cell.Value2), CStr(pi.Name), vbTextCompare) = 0 Then
found = True
Exit For
End If
End If
Next cell
If Not found Then pi.Visible = False
Next pi
End Sub
Put the code in a standard VBA module and update the sheet names, PivotTable name, field name, and list range to match the workbook; it compares text without distinguishing letter case. This item-by-item approach is for ordinary PivotTables, not OLAP or Data Model PivotTables, and a very large field may take longer to process than a Power Query merge.
10. Combine a Helper Field with a Slicer
When both list-driven matching and an easy report control matter, use the helper-column method to identify the permitted rows, then add a slicer for the helper field or another useful category. For instance, the TRUE helper filter can keep the PivotTable limited to approved regions while a Product slicer lets colleagues explore products inside that approved subset. This separates the fixed rule from the user’s interactive choices and reduces the risk that someone accidentally includes an unapproved value, but the helper formulas and PivotTable still need to be refreshed or recalculated after source changes.
How to Choose
For a short list used once, select checkboxes in the PivotTable field menu; it is fast and needs no changes to the source. Choose a slicer when people need to interact with a report and see what is selected, or put the field in the Filters area when a compact control above the table is preferable. If the allowed values live in a worksheet range and change over time, a helper column is usually the simplest repeatable solution; use exact-match logic or Power Query if wildcard characters, larger data, or a more controlled refresh process matter.
Use Advanced Filter when you need a separate filtered extract and are comfortable recreating it after changes. Choose a Data Model relationship for a workbook built around connected tables and reusable dimensions, rather than just one filtered PivotTable. VBA makes sense when the selection process must be automated and macros are permitted, but it adds code maintenance and cannot be undone; for shared workbooks, consider whether recipients can run macros before committing to that design.
Frequently Asked Questions
Can I paste a list of values directly into a PivotTable filter?
Not into the standard checkbox filter as a pasted criteria range. Use a helper column, Power Query merge, or a Data Model relationship to match a worksheet list automatically.
Will filtering the PivotTable remove rows from the source data?
No. A PivotTable filter changes what the report displays, not the underlying source records. To create a physically filtered extract, use Power Query or Advanced Filter to output matching rows elsewhere.
Why did new values appear after I refreshed the PivotTable?
A refresh updates the PivotTable’s cache from its source; newly encountered field items can become available in the filter menu. A manual selection does not mean “always select only values in my separate list,” so use a helper field or query for that rule.
Can a PivotTable field filter match values from another worksheet range automatically?
The ordinary filter menu does not link its checkboxes to a range. Add a source helper column that checks the range, merge the tables in Power Query, or relate tables through the Data Model.
What is the easiest approach for a list that changes every week?
For a modest list, use a helper column that refers to the allowed-values range and filter the PivotTable to TRUE after refreshing. For larger recurring imports, a Power Query inner merge can apply the updated list when the query refreshes.
Final Thoughts
For a handful of values, use the built-in PivotTable checkboxes or a slicer. For a maintained list, make the list part of the data workflow with a helper column, Power Query, or a Data Model relationship; reserve VBA for cases where automation justifies maintaining code.
Related
- Excel Data Validation List: How to Exclude Blanks in Excel
- Excel Data Validation List From Another Sheet: Steps
- Excel Data Validation List From a Table Column Explained
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Stacked Bar Chart Total Labels: How to Add ThemTo show a total at the end of each stacked bar in Excel for Microsoft 365 on Windows,…
- Excel Pivot Table Filter Multiple Values: Quick GuideTo filter a PivotTable to several values in Excel for Microsoft 365 on Windows, open the drop-down for…
- Excel Sparklines: Create and Format Column Charts in CellsYes—Excel can make a column sparkline, a compact chart of vertical bars placed inside a worksheet cell. In…
- Correlation Graph in Excel: Create and Interpret OneTo make a correlation graph in Excel, select two columns of paired numeric data and choose Insert >…
- Excel Power Pivot: Add Rows to a Table and RefreshTo add a row to a Power Pivot table, add it to the table or other source that…
- Excel: How to Turn On Power Pivot and Build Data ModelsTo turn on Power Pivot in Excel for Microsoft 365 on Windows, select File > Options > Add-ins.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.