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?
![Tables Tables](/uploads/1/2/5/4/125405404/466632779.gif)
And again, any idea how to make a Collapse All function? I have used VBA to reset all fields in a different spreadsheet, along the lines of.
Seems like a pretty standard need in a complex pivot table so I'm hoping someone has a nice easy solution. 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 6.5 50904.5 37329.5 515734.5 146747.5 5290.0 250.5 2214.0 144.5 718.5 30000.0 38205.5 1638120.0 9286.1 4000.5 207.9 745.5 41479.5 8080.0 64748.5 6226.0 291.5 13610.0 17712.5 5813.5 7923.50 PivotSheet. 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? 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.