Filter Data in a Table

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on

You can apply filters when you import data to control the rows that are loaded into a PowerPivot table. For more information on filtering during import, see Change the Rows that Are Imported from a Data Source. After you have imported the data, you cannot delete individual rows. However, you can apply custom filters to control the way that rows are displayed. Rows that do not meet the filtering criteria are hidden. You can filter by one or more columns. Filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data.


In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.

To filter data based on column values

  1. In the PowerPivot window, select a table, and then click the arrow in the header of the column that you want to filter by.

  2. In the AutoFilter menu, do one of the following:

    • In the list of column values, select or clear one or more values to filter by, and then click OK.

      If the number of values is extremely large, individual items might not be shown in the list. Instead, you will see the message, "Too many items to show."

    • Click Number Filters or Text Filters (depending on the type of column), and then click one of the comparison operator commands (such as Equals), or click Custom Filter. In the Custom Filter dialog box, create the filter, and then click OK.

To clear a filter for a column

  1. Click the arrow in the header of the column for which you want to clear a filter.

  2. Click Clear Filter from <Column Name>.

To clear all filters for a table

  1. In the PowerPivot window, select the table for which you want to clear filters.

  2. On the Home tab, in the Sort and Filter group, click Clear All Filters.

See Also

Other Resources

Filter and Sort Data in PowerPivot