Results 1 to 8 of 8

Thread: Pivot Table - How to group by filter value?

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Pivot Table - How to group by filter value?

    Hi,

    I am kinda new to excel and I have this pivot table that has 2 filter values. I need the rows to show both filter value but it should be grouped by value or at least if I could sort them depending on their filter and add a blank row in between so I could label which is which depending from their filter. I have tried searching how can I do this on google and on here and I am not seeing any concrete answer or I could be also not using the correct search words. Thanks in advance.

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,819
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Pivot Table - How to group by filter value?

    post a link to the shared excel file with your example and expected result
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table - How to group by filter value?


    https://imgur.com/a/cMuPA97

    Hi. Thanks for the reply. Please check link above. I am not able to upload any file due to restrictions.sorry

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,819
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pivot Table - How to group by filter value?

    use OneDrive, GoogleDrive, DropBox or any similar and post a link to the shared excel file
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,674
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pivot Table - How to group by filter value?

    Have the value fields in the Row area and apply the filter there. You may like using the Slicer Options to have a Filter selection outside the filter.
    In the Field options, you can have a blank line between values.
    In the Design Ribbon for Pivot Tables, select the Report Layout to Tabular
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table - How to group by filter value?

    https://www.dropbox.com/s/gpm467eiwp...ook1.xlsx?dl=0 this would be the test file. Thanks

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table - How to group by filter value?

    Quote Originally Posted by SpillerBD View Post
    Have the value fields in the Row area and apply the filter there. You may like using the Slicer Options to have a Filter selection outside the filter.
    In the Field options, you can have a blank line between values.
    In the Design Ribbon for Pivot Tables, select the Report Layout to Tabular
    Thanks for this! This is what I wanted. The issue now would be the field that was moved from filter to rows causing the main filter to disappear. Checking your advise about the slicer and figuring out how it works. Thanks again

  8. #8
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,674
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pivot Table - How to group by filter value?

    Slicers are filters. The slicer is its own object and can be formatted for size and layout (number of items wide). Also a slicer can be linked to multiple Pivot Tables.
    The Filter Area cannot share any fields with Rows or Columns, so deciding the best mix may be difficult. Sometimes I resort to duplicating a field to get my desired results despite the agony it causes data-OCD.

    I think Mike Girvin has a good video on Slicers. See ExcelIsFun channel on YouTube.
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •