Break PivotTable Report Filters into Multiple Columns

October 26th, 2010 by datapig Leave a reply »

Did you know you can break up your PivotTable’s Report filters into multiple columns?

 

Take a normal PivotTable with multiple Report Filters (In this example – Region, Product Description, Business Segment, Customer Name).

 

 

Right click anywhere inside the PivotTable and select PivotTable options.

 

 

In the Options dialog box, you’ll see a setting called ‘Report filter fields per column’.

Change this setting to specify the number of filter fields you’d like to have in each column. In this example, I’d like break up my Report filters so that only two are showing in each column.

 

 

Once implemented, the Report filters are broken out so that only two show up in each column.

 

 

This is a nice feature if you want your Report filters conform to the general shape of your PivotTable.

Here is a PivotTable where the Report filters are set to show only one per column.

Advertisement

6 comments

  1. Rich says:

    Wow! Rock my world.

    This is one of those Eureka moments and why the heck didn’t I know about this before.

    Fantastic tip!

    It will even help on printing as it eliminates dead space above the table.

    THANK YOU!

  2. Gregory says:

    In Excel 2003 the option is labeled “Fields per column:” in the PivotTable Options dialog box, and is much less obvious, but works the same nonetheless.

  3. jeff weir says:

    Great tip, Mike.

  4. Oakhome says:

    Agree with Jeff

  5. Jim says:

    Great tip. Is there a way to change what is displayed in the filter when multiple are selected. When I do this it just shows “Multiple Selected” and I would like to have the ones that are selected displayed in the report something like this “Filter1; Filter3; Filter5″. Is there a way to do this?

Trackbacks /
Pingbacks

  1. how to break up with someone you love

Leave a Reply