How to filter a pivot table with multiple values on Excel 2011 Mac?

[vanclute]

I imagine this must have been asked and answered before, but I couldn't seem to find an answer to this exact scenario, and I'm hoping someone else can point me to one.

I'm on Mac using Excel 2011. This means I don't have "slicers", which is all my Googling kept turning up.

I have a pivot table with 4 different value columns, and I want to be able to filter it the same way you would a normal table - i.e. remove everything below a specific number in one column, and filter for only specific strings in another column, etc.

How can this be done? Seems like a pretty standard need in a complex pivot table so I'm hoping someone has a nice easy solution.

 

[vanclute]

Wow nobody?

 

[Jim Gordon]

Click the Choose One button in the Filter > By Value section of the Category palette.

 

[vanclute]

Thanks, unfortunately that's not what I'm asking about. That would allow me to filter the Row Labels by value. I want to filter the VALUES by value.

 

[jamilm]

My two cents. I understood that you want to put filter in your pivot table like you do in normal table but when you select the value column header in your pivot table then the filter option is grayed out . In this case there is a trick to fool excel by selecting the next cell just right next to the header of your pivot table value column ( I mean outside the pivot table range ) then you can see that you can put the filter on and it works .

Hope it helps

 

[vanclute]

I'm afraid I don't understand your reply. When you say "the filter option is greyed out", the problem is *there is no filter option at all*. It's not that it's there but grey, it's that there is no filter, period. This is the crux of my problem - that there appears to be no way to filter the resulting value columns of a pivot table.

On a normal table I would just use the standard auto filters, but these don't work with pivot tables.

I hope this helps clear up my question...

 

[jamilm]

WHAT I MEAN BY FILTER IS THE AUTO FILTERS OPTION.

see the screenshot below. if your value column is wherever just click to the next cell of the header clumn in the picture below senario is dipicted as the yellow cell and then go to auto filter and click it and then you would be able to filter the value column.

Excel 2010
	A	B	C	D
7	ACCOUNT	VALUES column	CLICK HERE AND THEN TURN ON AUTO FILTER
12	21015	(0.00)
13	21030	(2094.02)
14	51005	(3080817.00)
15	61305	81446.88
16	62305	50904.30
17	63305	37329.82
18	71205	515734.93
19	71305	146747.72
20	71605	5290.00
21	71610	250.00
22	71615	2214.00
23	71620	144.00
24	71635	718.11
25	72105	30000.00
26	72120	38205.94
27	72165	1638120.00
28	72220	9286.01
29	72311	4000.50
30	72315	207.38
31	72399	745.04
32	72405	41479.88
33	72425	8080.79
34	72440	64748.60
35	72505	6226.90
36	72510	291.26
37	72805	13610.00
38	72810	17712.56
39	72815	5813.00
40	73105	7923.50
PivotSheet

I'm afraid I don't understand your reply. When you say "the filter option is greyed out", the problem is *there is no filter option at all*. It's not that it's there but grey, it's that there is no filter, period. This is the crux of my problem - that there appears to be no way to filter the resulting value columns of a pivot table.

On a normal table I would just use the standard auto filters, but these don't work with pivot tables.

I hope this helps clear up my question...

 

[vanclute]

Holy crap! Now I see what you were saying. That's how I've done this before. I thought I was crazy because I could swear I remembered having the autofilters on a pivot table before, but figured I must have just been imagining it. Thanks for the screenshot, I had not understood your previous comment about enabling the autofilters.

 

[vanclute]

Argh! There is unfortunately a limitation with this method. I have 3 pivot tables on a single worksheet, and you can only use this trick with one of them at a time, if the pivot tables don't actually touch each other. I have spacer columns (empty) between each table. Removing the spacer columns and adding them again after enabling the filter on all tables won't work, because then the filtering in one table affects all the others.

Is there no way to enable autofilters on multiple pivot tables that do not touch, on a single worksheet?

 

[vanclute]

On further reflection, I need to amend this entire question and explain why the AutoFilter functionality is not acceptable.

AutoFilters work by actually hiding rows that fit the filtering criteria. This is not going to work since it ends up hiding data in other columns of the worksheet. This is precisely why I originally was looking for a pivot table-specific solution. I need to be able to filter the values of a pivot table, without impacting anything else on the worksheet - just as one can do with the row labels, but for the value columns.

I hope this makes my needs more clear, and that someone can suggest a solution. Thanks!