Add filter button to all columns in a pivot table (Excel)

239 views Asked by At

It is usually not possible to add "filter drop-down buttons" on the columns of a pivot table: in the Home > Editing > Sort & Filter menu of the ribbon, the "Filter" option is greyed out if a pivot table is selected.

I found a way to bypass this:

  1. Select a range of cells where the pivot table will be (but is not there currently), and use the Home > Editing > Sort & Filter > Filter menu to make the filter drop-down buttons appear.
  2. Move the pivot table to that range of cells. The filter drop-down buttons remain present and allow you to filter the rows of the pivot table as if it was a regular table.

Example of the result

My questions are:

  • This seems to be a trick. Is there a risk that it will break some other functionality in my Excel workbook or even end up corrupting the file itself?
  • Is there a more elegant / straightforward way to do this, for example using a macro (AutoFilter) or another menu?
0

There are 0 answers