+ Reply to Thread
Results 1 to 12 of 12

Filter columns in Pivot table

  1. #1
    Registered User
    Join Date
    10-19-2017
    Location
    italy
    MS-Off Ver
    2010
    Posts
    37

    Filter columns in Pivot table

    Hi all. I'm kinda new to Pivot tables. As you can see in the attached document, I have this pivot table. What I'd like to have is a filter where I can dinamically turn on or off the various channels in the graph, but I can't figure out how.

    Can you help?

    Immagine 2021-01-11 122727.png
    Last edited by mikla90; 01-11-2021 at 09:43 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Filter columns in Pivot table

    Hi mikla,

    Have you considered 'slicers'?
    Whilst on the pivot table, go to insert > Slicer
    In the pop-up windiw select the items you want to filter.
    There you can select one, multiple (Ctrl) or all available options.
    You can use multiple Slicers and select for example specific channels and/or dats or date ranges.

    Hope this helps.
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

  3. #3
    Registered User
    Join Date
    10-19-2017
    Location
    italy
    MS-Off Ver
    2010
    Posts
    37

    Re: Filter columns in Pivot table

    Hi ORoos, I've tried that, but it gives me out the option to filter by values and not by columns as I'd like.

    Attachment 712682

  4. #4
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Filter columns in Pivot table

    When you insert the Slicer, you should get all the options in the source data; columns and rows and other fields not used in the pivot table.

    Was not able to open your attachment. It comes up as: Invalid Attachment specified

    It is always best to upload a sample workbook (without confidential data). See yellow banner at the top on how to do this. That makes it much easier for members to exacly see what you are working with and tesp possible solutions.

  5. #5
    Registered User
    Join Date
    10-19-2017
    Location
    italy
    MS-Off Ver
    2010
    Posts
    37

    Re: Filter columns in Pivot table

    Sorry for the inconvenice. Attached is an example file.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Filter columns in Pivot table

    Thanks for the sample file.

    I have added the data series also to the 'Filters' section of the pivot chart fields.
    This way they show up as 'buttons' on the chart and you can select values from there. Select 0 should hide the complete series.

  7. #7
    Registered User
    Join Date
    10-19-2017
    Location
    italy
    MS-Off Ver
    2010
    Posts
    37

    Re: Filter columns in Pivot table

    I can't get my head around this. Can you share the file?

  8. #8
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Filter columns in Pivot table

    Sorry, I though I did attach the file... Here it is.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-19-2017
    Location
    italy
    MS-Off Ver
    2010
    Posts
    37

    Re: Filter columns in Pivot table

    Yeah, maybe I'm making mistakes but if I apply the same thing on my sheet it doesn't work: as soon as I filter a channel, all the others are filtered and I only see the values related to the specific chosen value. What I'd like is a filter capable of turning on or off the entire channel in the graph.

  10. #10
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,956

    Re: Filter columns in Pivot table

    If your intention for the slicer is to select channels, then you have to restructure your database first, to put all the channels into the same single column.

    You can use Power Query to achieve this.
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Filter columns in Pivot table

    Hi again,
    I though of another approach. Add some check-boxes for each channel. The add an additional column to your source-data which will either show the data or nothing (ticked/un-ticked).
    You then use the new calculated fields for your chart.
    You will need to refresh the pivot data each time you mke changes to the check-boxes to refresh the data (his could be automated with an event macro).

    I did a quick example with your data in the attached file.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    10-19-2017
    Location
    italy
    MS-Off Ver
    2010
    Posts
    37

    Re: Filter columns in Pivot table

    Thanks, the second example is fine. I'll use that!

+ 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. Pivot table + added columns - HOW TO FILTER ALL
    By Scizzy in forum Excel General
    Replies: 5
    Last Post: 04-22-2020, 11:33 AM
  2. Pivot Table help - filter out Columns with data
    By CapitalH in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-10-2019, 03:47 AM
  3. How To Apply Data Filter On Pivot Table Columns!
    By Rajeshkumar R in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-28-2018, 07:51 AM
  4. [SOLVED] VBA code to apply same filter to adjacent columns of pivot table.
    By Ishwarind in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-25-2017, 07:00 AM
  5. Filter for Columns of Data in Pivot Table
    By chadom in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-10-2017, 04:13 PM
  6. [SOLVED] VBA filter columns of pivot table.
    By countryfan_nt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-02-2017, 07:20 AM
  7. VBA filter date in multiple pivot table columns
    By kahnailee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2015, 10:35 AM

Tags for this Thread

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