How to Filter Excel Pivot Table Data - dummies

By Stephen L. Nelson, E. C. Nelson

One cool thing you can do with Excel pivot tables is filtering. To filter sales by month, drag the Month PivotTable field to the Filters box. Excel re-cross-tabulates the PivotTable. To see sales of herbal teas by state for only a specific month — say, January — you would click the down-arrow button that looks like it’s in cell B1.


When Excel displays a drop-down list box, select the month you want to see.


To remove an item from the pivot table, drag the item’s button back to the PivotTable Field List or uncheck the checkbox that appears next to the item in the PivotTable Field List. Also, to use more than one row item, drag the first item that you want to use to the Rows box and then also drag the second item that you also want to use to Rows Here.

Drag the row items from the PivotTable Field List. Do the same for columns: Drag each column item that you want from the PivotTable Fields to the Columns box.

Sometimes having multiple row items and multiple column items makes sense. Sometimes it doesn’t. But the beauty of a pivot table is that you can easily cross-tabulate and re-cross-tabulate your data simply by dragging those little item buttons.

Accordingly, try viewing your data from different frames of reference. Try viewing your data at different levels of granularity. Spend some time looking at the different cross-tabulations that the PivotTable command enables you to create. Through careful, thoughtful viewing of these cross-tabulations, you can most likely gain insights into your data.

You can remove and redisplay the PivotTable Field List in Excel 2013 by clicking the Field List button on the Analyze tab.


To remove and redisplay the PivotTable Field List in Excel 2007 or Excel 2010, right-click PivotTable and choose the Hide Field List command. To show a previously hidden field list, right-click the PivotTable again and this time choose the Show Field List command. Predictably, whether the PivotTable shortcut menu displays the Show Field List command or the Hide Field List command depends on whether the field list shows.