Often you may want to filter the values in a pivot table in Excel using an OR condition.
The following example shows exactly how to do so.
Example: Filter Pivot Table Using OR Condition
Suppose we have the following dataset in Excel that shows the number of points scored by various basketball players:
Now suppose we’d like to create a pivot table to summarize the total points scored by team and position.
Also suppose we eventually want to filter for players who are on the Mavs team or have a position of Guard.
We can create a helper column by typing the following formula into cell D2:
=IF(OR(A2="Mavs", B2="Guard"), "Show", "Hide")
We can then drag this formula down to every remaining cell in column D:
Now that we’ve created this helper column, we can create the pivot table.
Highlight the cells in the range A1:D16, then click the Insert tab along the top ribbon, then click PivotTable.
In the new window that appears, we’ll choose to insert the Pivot Table in cell F1 of the current worksheet:
In the PivotTable Fields window that appears on the right side of the screen, drag Team and Position to Rows and Points to Values:
The following pivot table will automatically be populated:
To filter the pivot table to only show data for players on the Mavs team or in a position of Guard, click the PivotTable Analyze tab along the top ribbon, then click Insert Slicer within the Filter group.
In the new window that appears, check the box next to Filter and then click OK:
In the new slicer that appears, click the “Show” button.
This will automatically filter the pivot table to only show players on the Mavs team or who have a position of Guard.
By creating a helper column before we created the pivot table, we were able to filter the pivot table using an OR condition.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Filter Top 10 Values in Pivot Table
Excel: How to Sort Pivot Table by Grand Total
Excel: How to Calculate the Difference Between Two Pivot Tables
