Excel Pivot Table filter (label, value, Filters, multiple criteria or values, remove old)
We can also filter the data in Excel pivot table. There are Label, Value and Filters for filtering.
Chapters:
0:00 Excel Pivot Table label filter
0:53 Pivot Table label filter multiple criteria
1:45 Excel Pivot Table value filter
2:40 Excel Pivot Table value filter multiple criteria
3:13 Excel Pivot Table filter with Filters
3:48 Excel Pivot Table remove old filter values
3:49 How to change filter on Pivot Table?
Description:
Excel Pivot Table filter (label, value, Filters, multiple criteria or values, remove old)
When filtering, it can be either a condition or a multi-condition. Only a condition filtering can be performed by default, if we want to filter by multiple criteria, we need to add filter icons, and we cann't add filter icons to specified columns, but only add them to a blank cell, which is different from adding filters to ordinary Exce tables.
One. Excel Pivot Table label filter
(1) A condition
1. Suppose we want to filter all the shirts in a Clothing Sales Table. Click the drop-down list box icon to the right of "Row Labels", and select "Label Filters" → Ends with" from the pop-up menu, open the "Label Filter" window, enter "T-Shirt" to the right of "ends with", and click "OK" to filter out all shirts.
2. In addition, we can also use the "search" method. Similarly, click the drop-down list box icon to the right of "Row Labels", enter "T-Shirt" in the "Search" box, and click "OK" to filter out all shirts.
(2) Excel Pivot Table label filter multiple criteria (Pivot Table label filter multiple values)
Suppose we want to filter all the clothes that are neither black nor white in the Clothing Sales Table. Select the "Data" tab, select the A2 cell above the field name, click the "Filter" icon, click the drop-down list box icon to the right of "Row Labels", and select "Text Filters"→ Custom Filter" in the pop-up menu to open the "Custom AutoFilter" window, click the first drop-down list box under "Row Labels", drag the slider on the right to the bottom, select "does not certain", enter "Black" on the right, select "AND" to indicate that both conditions are met, click the second drop-down list box, also select "does not contain", enter "White" to the right of it, and click "OK" to filter out clothes that are neither black nor white.
Two. Excel Pivot Table value filter
(1) A condition
1. Suppose we want to filter for clothes that cost more than $20. Click the drop-down list box icon to the right of “Row Labels”, and select “Value Filters → Greater Than" to open the "Value Filter" window, enter 20 to the right of "is greater than" and click OK to filter out all clothing priced above $20.
2. The same is true for filtering "sales". In the same way, open the Value Filter window, click the drop-down list box containing Sum Item, and select "Sum of Sales (Pieces)" to the right of "is between", enter 700 and 900, click OK, and filter for all clothes with "Sales" between 700 and 900.
(2) Excel Pivot Table value filter multiple criteria (Excel Pivot Table filter contains multiple values)
1. Suppose we want to filter for all clothes with the Price greater than or equal to 20 and the Sales between 600 and 800. Select cell A2, select the ”Data“ tab, click ”Filter“, filter icons are added for the "Price" and "Sales", click the filter icon to the right of "Price", and select "Numeric Filters→ Greater than or equal to ” in the pop-up menu to open the "Custom AutoFilter" window, enter 20 to the right of "ia greater than or equal to", click the filter icon to the right of "Sales", and select "Numeric Filters"→ Between“ in the pop-up menu, open the ”Custom AutoFilter“ window, Enter 600 to the right of ”is greater than or equal to", select And, enter 800 to the right of "is less than or equal to", and click OK to filter for all clothes with the Price above 20 and a Sales between 600 and 800.
2. If the numerical filter is listed in more than three columns, continue to filter by conditions according to the above method.
Three. Excel Pivot Table filter with Filters
1. Suppose we want to filter for clothes that contain 9 in their sales values. Click "Sales" under "Pivot Table Fields" on the right side of the "Screen", uncheck, drag "Sales" to the list box under "Filters", then "Sales" will be automatically populated into the A1 cell and add a filter icon; click the filter icon, pop up "Search" panel, enter 9, then list all items containing 9, click "OK" to filter out only one record with 9 in sales values, and then click the "Filter" icon, check "Select multiple items", and enter again 9, not only all items containing 9 are listed and all are checked, click "OK" to filter out the clothes that contain 9 in the sales values.
2. You can drag multiple fields into the "Filter" at the same time, and each time you add a field, the field will be automatically filled under the A1 cell and a filter icon will be added.
Watch video Excel Pivot Table filter (label, value, Filters, multiple criteria or values, remove old) online without registration, duration hours minute second in high quality. This video was added by user Loinsure 04 March 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 422 once and liked it 4 people.