• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please, an alternative to slicer clear filter

L

lg215

New Member
Hi there,

Please help if you can, I cannot work this out. I have enclosed an example file showing my issue.

I have 3 columns of data: area, location, and value.
I have two values in area: 'neighbour', 'not neighbour'.
What i'd like to be able to have is a slicer that can show the options 'neighbour', 'all areas'.
I know I could use the clear filter icon, however this is not as user friendly as another slicer button.
I could add a Macro button below the slicer and have that clear filters and name it 'all areas'. However they do not look alike and i'm a tiny bit terrified of Macro's as beyond recording and reusing I'm a bit of a novice.
Additionally, I'd like to know how (or if) you can hide an option on a slicer. In this example 'not neighbour'.

Thank you for reading and I a very grateful for your help.

Laura
 

Attachments

  • slicer help.xlsx
    15.2 KB · Views: 5
@lg215 Interesting question, but that is not how slicers work. One trick is to duplicate your data, so you have 3 rows for neighbour "area" and 8 rows for all "area". Then your Area slicer will show two buttons - neighbour and all. If you have just a few rows of static data, this is simple - copy and paste. If you have a dynamic source of data, then you could use Power Query to create such duplication.

Hope that helps.
 
Hi ,

Use a helper column in your data with the formula :

=IF([@Area] = "neighbour", [@Area], "All Areas")

Name this column Areas , and use this in the Slicer.

Narayan
 
r2c2 said:
@lg215 Interesting question, but that is not how slicers work. One trick is to duplicate your data, so you have 3 rows for neighbour "area" and 8 rows for all "area". Then your Area slicer will show two buttons - neighbour and all. If you have just a few rows of static data, this is simple - copy and paste. If you have a dynamic source of data, then you could use Power Query to create such duplication.

Hope that helps.
Click to expand...

RC2C, thank you that actually makes complete sense and no one will ever see the duplicated data because the filters will always be on. Thank you, I appreicate your help. My data is fairly static and so this will work for this project. :)
 
NARAYANK991 said:
Hi ,

Use a helper column in your data with the formula :

=IF([@Area] = "neighbour", [@Area], "All Areas")

Name this column Areas , and use this in the Slicer.

Narayan
Click to expand...

Thank you Narayan for your reply, I'm not sure that would work as when I choose the slicer "All Areas" I wont see the data for "Neighbour". Perhaps I've misunderstood and I am grateful for your help. Luckily RC2C has given me an option I can use. Thanks again, Laura
 
lg215 said:
Thank you Narayan for your reply, I'm not sure that would work as when I choose the slicer "All Areas" I wont see the data for "Neighbour". Perhaps I've misunderstood and I am grateful for your help. Luckily RC2C has given me an option I can use. Thanks again, Laura
Click to expand...
Hi ,

Sorry , I re-read your original post , and it is clear ; my post was a result of my misunderstanding.

Narayan
 
You must log in or register to reply here.
Back
Top