Pivot table "not equal to" filter

jzeunik

New Member
Joined
Mar 28, 2011
Messages
11
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?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,132
maybe
dne.png
 

jzeunik

New Member
Joined
Mar 28, 2011
Messages
11

ADVERTISEMENT

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.
 

jzeunik

New Member
Joined
Mar 28, 2011
Messages
11

ADVERTISEMENT

@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.
 

Attachments

  • lookup table.png
    lookup table.png
    48.3 KB · Views: 3
  • filter choices.png
    filter choices.png
    22.5 KB · Views: 3
  • filter for y.png
    filter for y.png
    24.9 KB · Views: 3
  • data table.png
    data table.png
    251.7 KB · Views: 4

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
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:
1600303769380.png
 

jzeunik

New Member
Joined
Mar 28, 2011
Messages
11
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

Board Regular
Joined
Aug 15, 2012
Messages
156
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,661
Messages
5,549,284
Members
410,908
Latest member
Allen P
Top