Slicers: How Can I Automate the Clear Filter Process?

jdanniel

Member
Joined
Jul 16, 2018
Messages
84
Reaction score
0
Points
6
Excel Version(s)
MS365
Hello, everyone, I'm using Excel 2016.

Two questions about slicers, if I may, please.


  1. Is there a keyboard shortcut or function that allows me to clear the filters of all my slicers? I currently have five slicers. I may add more. Do I have to record a macro to do this, or is there something already built into Excel that does this, regardless of how many slicers I have?
  2. Can I use conditional formatting with a slicer? To be specific, if a slicer contains five different buttons, can I use different conditional formatting for each of those buttons?

Thank you! Jack Danniel

PS: For future use, which forum section is the best to put questions about slicers?
 
Hi Jack
as slicers are associated to PT's and Tables, I'll move it to the Pivot Table forum. I hope it'll get your question a better visibility
I left a redirect on the original forum you posted in .
 
Thanks, Ali, for that link, but I couldn't get it to work. (The page is a few years old.) When I try to press F11, I don't see the Visual Basic editor. I see the Design/Chart Styles ribbon, and the display is garbled. So, I Googled what to do. It says to add the Developer menu, which I did. When I entered the Developer area and selected Visual Basic, I then tried to paste the code from that page into the editor. I got an Out of Memory error box and could not continue.
 
Hello, everyone, I'm using Excel 2016.

Two questions about slicers, if I may, please.


  1. Is there a keyboard shortcut or function that allows me to clear the filters of all my slicers? I currently have five slicers. I may add more. Do I have to record a macro to do this, or is there something already built into Excel that does this, regardless of how many slicers I have?
  2. Can I use conditional formatting with a slicer? To be specific, if a slicer contains five different buttons, can I use different conditional formatting for each of those buttons?

Thank you! Jack Danniel

PS: For future use, which forum section is the best to put questions about slicers?

Jack

You are a bloody trooper!

1 Yes use a macro. see attached https://www.screencast.com/t/2qLU03dcLUY and file below

2 I use conditional formatting extensively with pivot tables however not with slicers. Note that you need to reapply conditional formatting each time you change up a pivot table.
 

Attachments

  • Slicer Macro.xlsm
    27.9 KB · Views: 17
Last edited:
Ed,

Thank you so much for attaching that file. After looking at it, I understood what to do and how to do it. I recorded the macro successfully. After viewing your macro for this procedure, I realized how easy it is to do it. I also made a command button for the macro, which works.

One very minor and trivial question: I cannot figure out how to change the fill color of the command button. It isn't necessary, but I'm just curious about it. Can the "Clear Slicers" command button's fill (background) color be changed? If so, what's the procedure? I can figure out how to change the font, the font size, and the font color, but not the fill color.

Other than that, I think we're good. I'm attaching my current copy of the spreadsheet so you can see what I've done, and what remains. Thanks again! Jd

PS: I'm not going to worry about conditional formatting for the slicers right now.
 

Attachments

  • Clear Slicers Fill Color.xlsm
    58.2 KB · Views: 13
Last edited:
Ed,
We're almost there. One more thing I need help with: I created a shape, and assigned a macro to it. I was able to change the fill color, as well as the font and font size. However, I want to lock that button in place, and I cannot figure out how to do that. I was able to lock the command button in place, but not this button. Is it possible to lock this shape/button in place? If I cannot, then I have to go back to the original command button, but then, I won't be able to change the fill color. That's not a big deal. Locking the button in place is, right now, more important to me than the fill color. If you look at my revised file, you'll see that button disappear when I activate any slicer. How do I prevent that?

It's always something, isn't it?
Once again, thank you. Jack
 

Attachments

  • Button Fill and Lock.xlsm
    56.5 KB · Views: 9
Please disregard my previous question, because I just figured it out. I didn't exit Edit Text. After I exited, I right-clicked, I clicked Format Shape, then I clicked the third icon (size & properties). I expanded the Properties menu, then selected Don't Move or Size with Cells. That seems to have solved the problem. Let me run a few more tests to see if everything is good. Hopefully, it is. Thanks again! Jd
 

Attachments

  • Shape Lock.xlsm
    56.5 KB · Views: 8
Back
Top