How to Filter a Pivot Table with a Custom List in Excel?

Sharing is caring!

Pivot tables in Excel help you summarize and analyze large amounts of data quickly. One useful skill is learning how to filter a pivot table to show only specific items.

While standard filters let you pick items from a list or use conditions, sometimes you need to filter based on your own set of values, such as a selected group of products, regions, or categories. This is where a custom list becomes helpful.

In this guide, you will learn how to filter a pivot table with a custom list, step by step.

What Is a Custom List in Excel?

A custom list lets you define your own order for items. Excel already has built-in lists like days of the week or months. You can create your own for things like department names, priority levels, or specific customer groups.

To create one:

1. Go to File > Options.
2. Select Advanced.
3. Scroll to the General section and click Edit Custom Lists.
4. In the Custom Lists dialog box, type your items in the List entries box (one per line or separated by commas).
5. Click Add, then OK.

This list now becomes available for sorting in pivot tables when the option Use Custom Lists when sorting is enabled (found in PivotTable Options > Totals & Filters tab).

Method 1: Add a Helper Column to Your Source Data (Recommended for True Filtering)

The most reliable way to filter a pivot table with a custom list is to add a helper column in your source data. This column checks if each row matches your custom items and marks it (for example, with “Yes” or “Include”).

Steps:

1. Create your custom list of items you want to show (e.g., in a separate sheet or range, list cities like New York, London, Tokyo).
2. In your source data table, add a new column named something like Custom Filter.
3. Use a formula to check if the value is in your list. For example, if your list is in Sheet2!A1:A10 and the field to check is column B (e.g., City):
   =IF(ISNUMBER(MATCH(B2, Sheet2!$A$1:$A$10, 0)), "Include", "Exclude")
   Drag the formula down for all rows.
4. Refresh your pivot table (right-click inside it and select Refresh).
5. In the PivotTable Fields pane, drag the new Custom Filter field to the Filters area.
6. Click the filter dropdown in the pivot table and select only Include.

This method acts as a true custom filter because it limits the data shown in the pivot table to rows that match your list.

Here is a simple example table of what your source data might look like after adding the helper:

After filtering on Include, your pivot table shows only New York and London data.

Method 2: Use Custom List for Sorting and Manual Selection (For Simple Cases)

If your goal is to organize and then filter items in a preferred order:

1. Define your custom list as explained earlier.
2. Ensure Use Custom Lists when sorting is checked in PivotTable Options > Totals & Filters.
3. Build or refresh your pivot table.
4. Drag the field (e.g., Region) to Rows or Columns.
5. Right-click a label > Sort > More Sort Options.
6. Choose Manual or select your custom list from the sort order dropdown.
7. After sorting, use the filter dropdown to uncheck items not in your desired group.

This does not hide items automatically like a true filter, but it groups your preferred items at the top for easier selection.

Method 3: Use Slicers with a Helper Column

For a visual and interactive way:

1. Follow Method 1 to add the Custom Filter helper column.
2. Select any cell in the pivot table.
3. Go to PivotTable Analyze tab > Insert Slicer.
4. Choose the Custom Filter field.
5. Click the Include button on the slicer to filter the pivot table.

Slicers make it easy to toggle the custom filter on and off.

Tips for Better Pivot Table Filtering

• Always refresh the pivot table after changing source data or custom lists.
• Use Excel Tables (Insert > Table) for your source data so ranges update automatically.
• Enable Allow multiple filters per field in PivotTable Options > Totals & Filters if you combine label, value, and manual filters.
• For large datasets, avoid very long custom lists in formulas to prevent slow calculations.
• Test your filter by checking row counts before and after applying it.

Common Mistakes to Avoid

• Forgetting to refresh the pivot table after adding a helper column or custom list.
• Not enabling Use Custom Lists when sorting in pivot table settings.
• Using custom lists directly in filter dropdowns—they work best for sort order, not direct selection.
• Applying filters without a helper column when you need exact matches from a predefined list.

By using a helper column with a match formula tied to your custom items, you gain full control over what appears in your pivot table. This approach works in most Excel versions, including Excel 2016, 2019, 2021, and Microsoft 365.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.