+ Reply to Thread
Results 1 to 7 of 7

How to select all options in pivot table filter greater than a specific value

  1. #1
    Registered User
    Join Date
    03-13-2017
    Location
    Berlin, Germany
    MS-Off Ver
    2016
    Posts
    4

    How to select all options in pivot table filter greater than a specific value

    I've got a pivot table with a number of filters. One of these filters has hundreds of options (all numerical). It is far too tedious to check all the relevant boxes (say, if I want to select all greater than 22), so I was wondering if there is a way I can select all options greater/less than a given number?

    When I google this , I keep seeing solutions to a different problem, one concerning making the values in a pivot table's output display only when they are greater than/less than a given number.
    Last edited by erikh92; 12-17-2017 at 11:29 PM.

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to select all options in pivot table filter greater than a specific value

    IMHO you can't
    you can do that if it is in PivotTable from field ROW/Column but not as Filter

    here is text but the same is with numbers

    pt.jpg

    You can try with VBA so ask moderator to move your thread to this section: Excel Programming / VBA / Macros (use Report Post from own post#1)
    Last edited by sandy666; 12-17-2017 at 11:43 PM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Registered User
    Join Date
    03-13-2017
    Location
    Berlin, Germany
    MS-Off Ver
    2016
    Posts
    4

    Re: How to select all options in pivot table filter greater than a specific value

    Thanks for clearing that up. Never worked with VBA/Macros so I don't know how much I would get of that, but a tedious workaround would be to introduce a new column for each possibility you want to consider (for example, if you wanted to show all options greater than 20, you can introduce an indicator column for whenever that happens to be the case).

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to select all options in pivot table filter greater than a specific value

    one solution is with the first column (like on the picture above) - also you can Group first column sometimes to make size smaller
    second solution is VBA - on VBA part of forum - where you need describe all what you need with details as much as you can. Maybe there will be someone who can do this for you.
    Last edited by sandy666; 12-18-2017 at 04:29 PM.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    Re: How to select all options in pivot table filter greater than a specific value

    Another approach is to created a helper column with a formula that references the time. Suppose the time you want is in Cell B1 on Sheet Sheet2, then the formula would be =CellWithTime > Sheet2!$B$1. This will be true when the time you are checking is greater than your specified time. Then use this column as a report filter in the pivot table.

    Obviously you can set the helper column to look up between times as well.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  6. #6
    Registered User
    Join Date
    03-13-2017
    Location
    Berlin, Germany
    MS-Off Ver
    2016
    Posts
    4

    Re: How to select all options in pivot table filter greater than a specific value

    Thanks guys. Although it will be a bit tedious, I'll go with the helper column approach I mentioned.

    sandy666 -- you're right, this type of problem does lend itself pretty readily to a VBA solution. That's something I will definitely look into.

  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to select all options in pivot table filter greater than a specific value

    good choice

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] select options in pivot table dropdowns
    By xi603 in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 09-07-2017, 10:48 PM
  2. [SOLVED] How to get pivot table filter options to update?
    By Slurry Pumper in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-12-2016, 08:45 AM
  3. [SOLVED] Select Automatic Report Filter in Pivot where Filter exist in Data table else select Blank
    By vinaynaran in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-02-2016, 08:47 AM
  4. Replies: 0
    Last Post: 03-20-2015, 11:39 AM
  5. Pivot Table not showing the value filter options - only Top 10!
    By abhi.ko in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2014, 07:31 PM
  6. Greater than or equal to filter in Pivot table
    By Jonathan9 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-24-2013, 12:27 PM
  7. Pivot Table Data Filter Options Disappear
    By Pepikins in forum Excel General
    Replies: 1
    Last Post: 06-16-2005, 08:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1