Pivot table "not equal to" filter

[jzeunik]

I have a pivot table that is filtered based on a single value in a table column. Let's say it's Column G, which has either a Y or N for each row. The pivot table is filtered for the Y values. Each month, the table is refreshed with new data, and the pivot table is also refreshed. However, in some months, there are no rows with Y values, so the pivot table filter "breaks" when it is refreshed. So, the pivot table is effectively unfiltered, and includes all rows of data.

The ideal would be to filter Column G for values not equal to N, so the filter would be valid even in months with no Y values. Is there a way to do this kind of filtering in a pivot table?

 

[rondeondo]

Hi
You may need to make an additional column in your data to use as your filter. use something like =if(g2 = "N","N","Y")

 

[sandy666]

maybe

 

[jzeunik]

maybe
View attachment 22382

Thanks, Sandy - but I'm referring to a filter in a pivot table. In pivot table filters, those options are not available.

 

[jzeunik]

Hi
You may need to make an additional column in your data to use as your filter. use something like =if(g2 = "N","N","Y")

Thanks, @rondeondo. I'm a big fan of "helper" columns. But I don't see how this one would help. With the "if" formula you propose, the values would be identical to Column G, and I would have the same problem.

 

[rondeondo]

Perhaps some examples that show when there's a problem may help.

 

[jzeunik]

@rondeondo - Here you go. Screenshot names and details

data table.png - the main data table. Column T is a VLOOKUP against the lookup table. The Y value is pulled for matches. The data in this table changes each month.
lookup table.png - the policies that are $10MM+. The VLOOKUP above pulls from column G
filter for y.png - When the data table above has Y values, filtering for Y works fine. But if there are no Y values, the filter "breaks" and the pivot sums ALL values.
filter choices.png - This is a pivot table filter. No advanced filtering available.

Thanks for your help.

 

[rondeondo]

hi @jzeunik
Can you expand on the filter breaking concept.
Is it that you have a filter based on a value that is not in the data after the refresh so the filter cannot remain set for that value? There may be little you can do if the filtered value is just not present in the data.
if you right click on the pivot table and go to options for the pivot, there's and option, you may have some luck with changing this setting:

 

[jzeunik]

hi @jzeunik
Can you expand on the filter breaking concept.
Is it that you have a filter based on a value that is not in the data after the refresh so the filter cannot remain set for that value? There may be little you can do if the filtered value is just not present in the data.
if you right click on the pivot table and go to options for the pivot, there's and option, you may have some luck with changing this setting:
View attachment 22538

@rondeondo - Your assumption is correct - the value is not in the data, so the filter is "cleared". Which is why I was looking for a way to do the "not equal to" filter. I didn't think there was a way to do it in a pivot table, but I hoped someone would have knowledge that I don't. This is actually someone else's spreadsheet, and I was trying to give them a solution that didn't require knowledge or effort on their part. Thanks for your help, though.

 

[rondeondo]

the other option is to always have one row with a Y in it. the rest of the data would need to have the effect of zeros so as to no effect the results.