Add filter option for all your pivot table columns

Last Updated on 08/10/2024
Reading time: 2 minutes

This article provides a handy trick for adding filter options to all columns in your Pivot Table.

It walks you through the technique step-by-step, ensuring you can easily enhance your data analysis capabilities. Whether you're new to Pivot Tables or looking to streamline your workflow, this guide offers a practical solution for managing and filtering your data more efficiently.

Only the first column has the filter option by default

When you create a pivot table, only the first column offers the option of filtering or sorting.

Only the first columns has a filter option by default

Now, if we want to add filters for the other columns, the Filter menu (Data > Filter) is disabled 😡😡😡

Filter Option disabled

But there is a trick to change this behavior 😉

Add a filter option for all your pivot table columns.

  1. Select the first empty cell after the header column of your pivot table
  2. Click on the Filter menu Data > Filter (the menu is enabled)
  3. And then, all your columns have the filter option 😍😍😍
Add filter to all pivot table columns

All the filter options are now available for all the columns of a pivot table

All the filter option are available now

Users can narrow down the necessary information by filtering data in all the columns and focusing on relevant details. This saves time and effort that would otherwise be spent manually sorting and sifting through data. Overall, filters make working with Excel more efficient and productive.

Related articles

31 Comments

  1. Wraind
    01/11/2023 @ 22:15

    Click PivotTable Options
    Display Tab
    Put Check on Classic PivotTable layout

    hope this helps

    Reply

    • Frédéric LE GUEN
      02/11/2023 @ 03:07

      But it doesn't include the column Total. Only the column in the Row field

      Reply

  2. som
    20/06/2023 @ 08:32

    Thank you very much for the info. It helps.

    Reply

  3. Harith Khan
    23/05/2023 @ 03:54

    Thank you very much for this info!

    Reply

  4. fi
    06/04/2023 @ 07:08

    Thank you very much
    You saved my life

    Reply

    • Frédéric LE GUEN
      06/04/2023 @ 07:30

      You're welcome 🙂

      Reply

    • Sarah
      06/06/2023 @ 22:36

      Sending you massive THANK YOU THIS IS GENIUS!!!

      Reply

  5. Jude
    23/09/2022 @ 02:49

    Column filters still doesn't if work for the entire table. if you click the filter it doesn't make any change. Only first column filter works. Thank you.

    Reply

    • Frédéric LE GUEN
      23/09/2022 @ 12:53

      That's weird because it works for many years (what ever the Excel version)

      Reply

  6. Linnie
    12/09/2022 @ 07:58

    This is fantastic. Thanks for the tip.

    Reply

  7. Gina Dowling
    02/09/2022 @ 17:00

    I used to be able to do this in desktop excel all the time. Now i have excel 365 and this trick doesn't work. Any updated tips to help?

    Reply

  8. Andrew
    30/08/2022 @ 15:37

    This is cool, but seems to always leave in the last pivot table "row" - independent of the filter. This has no direct link with grand totals and subtotals AFAICS, although there may be something behind the scenes. Feels like an incredibly near miss to replicate functionality that should always be there. Of couse you could, more crudely, put an autofilter above your pivot table, but I think this suffers from the same problem.

    Reply

  9. karen
    18/08/2022 @ 08:01

    YOU ARE THE BEST BEST BEST

    Reply

  10. Ruchika
    19/01/2022 @ 20:40

    I have date field which I want to use as filter in pivottable report. But i see only dates, i want to have multiple filters in same dropdown for year,month, quarter,day . Please share how to achieve that.

    Reply

    • Frédéric LE GUEN
      19/01/2022 @ 20:56

      Very good question. Tomorrow I will do an article or video for that

      Reply

  11. Vivek
    15/01/2022 @ 12:43

    hi
    thanks for the great tip.

    Now, I'm trying to figure out how to see the value filter just below the Number filters in the right-click menu, and I dont see in it my Pivot.

    I have downloaded a sample excel with a pivot which already has the value filter. I'm trying to create my own pivot in this sample excel using the same data set, but I just dont see the value filer.

    can someone help?

    Reply

  12. Yusely Palacios
    13/10/2021 @ 01:25

    Thank you, I was going crazy looking for a way to do this.

    Reply

  13. Eric Meunier
    20/09/2021 @ 13:22

    hi,
    it works when I have one set of values, but with 2 sets, I can't get the filter across the whole pivot table. Note that it used to work with 2016 but since my recent 'upgrade' to 365, this feature seems to be lost.

    Reply

    • Frédéric LE GUEN
      28/09/2021 @ 13:49

      True it works only with 1 header not 2.
      But it works also with 365. That's what I use

      Reply

  14. Joy
    09/09/2021 @ 07:56

    wow, thank you! great help! =)

    Reply

    • Frédéric LE GUEN
      09/09/2021 @ 08:10

      You're welcome

      Reply

  15. Andreas
    06/08/2021 @ 09:10

    Wow, so simple! Thank you!

    Reply

    • Frédéric LE GUEN
      19/08/2021 @ 07:03

      You're welcome 😉

      Reply

  16. Prajjwal Agrawal
    13/07/2021 @ 12:51

    Awesome!!!

    Reply

  17. Predrag
    07/05/2021 @ 10:01

    such quick and simple solution, THANK YOU

    Reply

  18. jason
    20/04/2021 @ 16:32

    Oh my god, i've been searching for a solution to this for dayssss. All these guides and tutorials and forum posts and NO ONE seems to address this. It's such an easy solution I'm laughing at myself lol. Thank you so much!

    Reply

    • Frédéric LE GUEN
      20/04/2021 @ 17:06

      I don't have time to promote my articles on forums. But you can do it 😉

      Reply

  19. Beverly Cusack
    31/03/2021 @ 17:18

    simple and effective solution. Took 1 minute. Thank you

    Reply

  20. LM
    24/02/2021 @ 09:17

    Excellent (and so misterious), thank you

    Reply

  21. Sandy Browner
    05/11/2020 @ 23:52

    I have fought this FOREVER! Thanks!!

    Reply

  22. Julie R
    27/08/2020 @ 16:48

    THANK YOU!!!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Add filter option for all your pivot table columns

Reading time: 2 minutes
Last Updated on 08/10/2024

This article provides a handy trick for adding filter options to all columns in your Pivot Table.

It walks you through the technique step-by-step, ensuring you can easily enhance your data analysis capabilities. Whether you're new to Pivot Tables or looking to streamline your workflow, this guide offers a practical solution for managing and filtering your data more efficiently.

Only the first column has the filter option by default

When you create a pivot table, only the first column offers the option of filtering or sorting.

Only the first columns has a filter option by default

Now, if we want to add filters for the other columns, the Filter menu (Data > Filter) is disabled 😡😡😡

Filter Option disabled

But there is a trick to change this behavior 😉

Add a filter option for all your pivot table columns.

  1. Select the first empty cell after the header column of your pivot table
  2. Click on the Filter menu Data > Filter (the menu is enabled)
  3. And then, all your columns have the filter option 😍😍😍
Add filter to all pivot table columns

All the filter options are now available for all the columns of a pivot table

All the filter option are available now

Users can narrow down the necessary information by filtering data in all the columns and focusing on relevant details. This saves time and effort that would otherwise be spent manually sorting and sifting through data. Overall, filters make working with Excel more efficient and productive.

Related articles

31 Comments

  1. Wraind
    01/11/2023 @ 22:15

    Click PivotTable Options
    Display Tab
    Put Check on Classic PivotTable layout

    hope this helps

    Reply

    • Frédéric LE GUEN
      02/11/2023 @ 03:07

      But it doesn't include the column Total. Only the column in the Row field

      Reply

  2. som
    20/06/2023 @ 08:32

    Thank you very much for the info. It helps.

    Reply

  3. Harith Khan
    23/05/2023 @ 03:54

    Thank you very much for this info!

    Reply

  4. fi
    06/04/2023 @ 07:08

    Thank you very much
    You saved my life

    Reply

    • Frédéric LE GUEN
      06/04/2023 @ 07:30

      You're welcome 🙂

      Reply

    • Sarah
      06/06/2023 @ 22:36

      Sending you massive THANK YOU THIS IS GENIUS!!!

      Reply

  5. Jude
    23/09/2022 @ 02:49

    Column filters still doesn't if work for the entire table. if you click the filter it doesn't make any change. Only first column filter works. Thank you.

    Reply

    • Frédéric LE GUEN
      23/09/2022 @ 12:53

      That's weird because it works for many years (what ever the Excel version)

      Reply

  6. Linnie
    12/09/2022 @ 07:58

    This is fantastic. Thanks for the tip.

    Reply

  7. Gina Dowling
    02/09/2022 @ 17:00

    I used to be able to do this in desktop excel all the time. Now i have excel 365 and this trick doesn't work. Any updated tips to help?

    Reply

  8. Andrew
    30/08/2022 @ 15:37

    This is cool, but seems to always leave in the last pivot table "row" - independent of the filter. This has no direct link with grand totals and subtotals AFAICS, although there may be something behind the scenes. Feels like an incredibly near miss to replicate functionality that should always be there. Of couse you could, more crudely, put an autofilter above your pivot table, but I think this suffers from the same problem.

    Reply

  9. karen
    18/08/2022 @ 08:01

    YOU ARE THE BEST BEST BEST

    Reply

  10. Ruchika
    19/01/2022 @ 20:40

    I have date field which I want to use as filter in pivottable report. But i see only dates, i want to have multiple filters in same dropdown for year,month, quarter,day . Please share how to achieve that.

    Reply

    • Frédéric LE GUEN
      19/01/2022 @ 20:56

      Very good question. Tomorrow I will do an article or video for that

      Reply

  11. Vivek
    15/01/2022 @ 12:43

    hi
    thanks for the great tip.

    Now, I'm trying to figure out how to see the value filter just below the Number filters in the right-click menu, and I dont see in it my Pivot.

    I have downloaded a sample excel with a pivot which already has the value filter. I'm trying to create my own pivot in this sample excel using the same data set, but I just dont see the value filer.

    can someone help?

    Reply

  12. Yusely Palacios
    13/10/2021 @ 01:25

    Thank you, I was going crazy looking for a way to do this.

    Reply

  13. Eric Meunier
    20/09/2021 @ 13:22

    hi,
    it works when I have one set of values, but with 2 sets, I can't get the filter across the whole pivot table. Note that it used to work with 2016 but since my recent 'upgrade' to 365, this feature seems to be lost.

    Reply

    • Frédéric LE GUEN
      28/09/2021 @ 13:49

      True it works only with 1 header not 2.
      But it works also with 365. That's what I use

      Reply

  14. Joy
    09/09/2021 @ 07:56

    wow, thank you! great help! =)

    Reply

    • Frédéric LE GUEN
      09/09/2021 @ 08:10

      You're welcome

      Reply

  15. Andreas
    06/08/2021 @ 09:10

    Wow, so simple! Thank you!

    Reply

    • Frédéric LE GUEN
      19/08/2021 @ 07:03

      You're welcome 😉

      Reply

  16. Prajjwal Agrawal
    13/07/2021 @ 12:51

    Awesome!!!

    Reply

  17. Predrag
    07/05/2021 @ 10:01

    such quick and simple solution, THANK YOU

    Reply

  18. jason
    20/04/2021 @ 16:32

    Oh my god, i've been searching for a solution to this for dayssss. All these guides and tutorials and forum posts and NO ONE seems to address this. It's such an easy solution I'm laughing at myself lol. Thank you so much!

    Reply

    • Frédéric LE GUEN
      20/04/2021 @ 17:06

      I don't have time to promote my articles on forums. But you can do it 😉

      Reply

  19. Beverly Cusack
    31/03/2021 @ 17:18

    simple and effective solution. Took 1 minute. Thank you

    Reply

  20. LM
    24/02/2021 @ 09:17

    Excellent (and so misterious), thank you

    Reply

  21. Sandy Browner
    05/11/2020 @ 23:52

    I have fought this FOREVER! Thanks!!

    Reply

  22. Julie R
    27/08/2020 @ 16:48

    THANK YOU!!!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *