Hi, so i am missing the pivot table value filter option of "not between". i need it for an assignment but there doesnt seem to be an settings page to enable filters, and i am at the current newest version of excel.
@Autumn5420 How odd! I hadn't discovered this yet (never had a need for this feature), but it seems that the 'Not between...' filter only exists in the Windows version (see picture).
In a regular Excel table filter you can combine the 'Less than' with an OR 'greater than' to achieve the same, but that's not possible in a Pivot Table filter.
The only way I can imagine to achieve this on a Mac is to create a calculated field. Name it 'NotBeween' with a formula similar to the picture below. change the 'Value' field and limits to your specific needs.
Then place the NotBetween field into the value area of the pivot table and filter it for the value 1 (being TRUE). I attach the file that I used in the example.
@L z. @Autumn5420 Now it becomes really odd. I created a workbook on-line with the filter 'not between', saved it and then opened in the desktop on a Mac. Then I did see the 'not between' option when I filtered a value column.
Closed Excel and created a new sheet. Same result. It seems you must right-click on a label from the Row field and then choose Value filter. Now you can choose 'is not between'. Didn't see that earlier. ???
The Automate tab on your picture indicates you run 365 on your Mac. Below is an alternative that routes the data Table to Power Query and pre-filter the records according to the 2 parameters (in blue) on the sheet:
Of course this has a cost as it requires an aggregration (what PivotTables do) in Power Query to filter out [States] that are Not Between x and y