By default, when you add Report Filters to a pivot table, they are shown in a single vertical list, above the body.
If you add several filters, that list can get rather long, and it pushes the data down the worksheet.
To save space at the top of the worksheet, you can change the Report Filter layout. You can either:
- Set a limit for the rows in the vertical list, and create more vertical lists, if necessary
- Switch to a horizontal layout, with the Report Filters across the row
Change the Layout
- To change the Report Filter layout
- Right-click a cell in the pivot table, and click Pivot Table Options
- Click the Layout & Format tab
Set Limit for Rows in the Vertical List
- The ‘Display Fields in Report Filter Area’ is set for ‘Down, Then Over’
- In the ‘Report filter fields per column’ box, select the number of filters to go in each column.
NOTE: The default setting is zero, which means “No limit” - Click OK
In the screen shot below, the number of fields per column is set at 4. The 5th field – Product — is at the top of the second set of filters.
Switch to Horizontal Layout
- In the ‘Display Fields in Report Filter Area’ drop down list, click ‘Over, Then Down’
- In the ‘Report filter fields per row’ box, select the number of filters to go across each row.
NOTE: If the number is set at zero, all the filters will be shown in one row. - Click OK
In the screen shot below, the number of fields per row is set at 3. The 4th field – Category – begins the second row of filters.
Watch the Video
To see the steps for changing the Report Filter layout, please watch this short video tutorial.
_______________
Far out, Deb…it’s astounding what you know. That is a new one on me. Just yesterday I was looking at those settings thinking “I wonder what those do?” But didn’t wonder enough to get on google and find out.
That’s the beauty of your blog…something for everyone.
Thanks Jeff, glad you found it useful!
This site is amazing it has a lot of excellent tips to develop new projects Congratulations my dear Debra¡¡¡¡ Regards
Thank you Mario!
I’m totally on Jeff’s side
This Debra’s blog is so beautiful that her article and video are just right there where we are really wondering 🙂 . Always Thanks, Debra. – From Korea.
Thank you Chung Ah! I’m glad you find the articles and videos helpful.
After using Excel for 20+ years, I continue to be amazed by what I don’t know! I use pivot tables all the time but never knew this existed. Thanks Debra!
Hallelujah! It took forever to find this great tidbit. Thank you!!
how do I delete cells in between fields in the filter field in a pivot table.
Excel sees those cells as part of the pivot table, so you can’t delete them, unfortunately.