11 responses

  1. Luke M
    May 11, 2016

    From a design standpoint, is it really better to use a slicer like this (which could let you select multiple items), or use a Forms – ComboBox for similar effect? Overall, I love the idea of being able to switch Conditional Formats…just not sure if using a slicer is best way. Unless it’s for the simplicity of clicking a button.

    Reply

    • Hui…
      May 12, 2016

      @Luke

      I agree and possibly should have pointed that out in the post
      I did point it out to the client
      The client wanted to maintain the look and feel of the worksheet which had several other slicers doing other jobs which I couldn’t argue with
      The client also chose better layout styles than I used in the post

      I have added a discussion of this at the bottom of the post

      Reply

      • Luke M
        May 12, 2016

        @Hui

        Thanks for the follow-up. Overall, still a very cool technique which I can see being used in other applications; i.e. using a slicer as a control feature. 🙂

        Reply

  2. Duncan Williamson
    May 12, 2016

    I have done this in the past with a check box form control: switch on and switch off. However, I like this approach because it extends one’s thinking about the creation and use of slicers. For example, I think not many people realise that slicers can be used without the need for a pivot table!!

    One point: when I tried to download your sample file version one, I got an error message. This file, file two, downloaded without a hitch.

    Secondly, I think there is nothing wrong with your approach to number formatting in the Sales column since it works. However, it needs a helper column, AA, to make it work. For my own version, I split the conditional formatting into two with columns B to D as you had them. For column F, Sales values, I copied your CF PLUS I formatted the numbers inside CF and got the same results as your formula. No need for helper cells and the programming is neater this way. It also adds the need to use number formatting inside CF which, in fact, I had never done before!

    Anyway, as always, good work from chandoo.org

    Reply

  3. Gary Lundblad
    May 12, 2016

    I love the idea, but was not able to make it work. Whenever I tried to add the formula “=SUBTOTAL(4,Style[Id])” I received an error. Was there something I was supposed to do first that might not be in the instructions. I followed them in every detail, including using the same ranges. I thought maybe there was a named range I was supposed to create, but in looking at the sample workbook it didn’t look like it. Any assistance would be greatly appreciated.

    Thank you!

    Gary

    Reply

    • James H
      May 13, 2016

      Hi Gary,

      The formula “=SUBTOTAL(4,Style[Id])” is referring to a table called “Style”. If you do not have a table called “Style” the formula will return an error.

      To fix this: Click on your table. In the Table Tools – Design Table there is the option to set the Table Name. In this case rename it to Style and the formula should work.

      The instructions did not include a step where the table is named so that is my best guess as to why it isn’t working for you.

      Reply

    • Hui…
      May 13, 2016

      @Gary
      You also need a Field named “Id”

      Reply

      • Gary Lundblad
        May 13, 2016

        Thank you Hui! I’m confused- your example file does not have a named range or table called “Id,” only “Slicer_Style,” “Style,” and “Style_Link.”

        Gary

        Reply

  4. Varun
    September 17, 2016

    Hello,

    My idea is to create a spectrum.
    I have multiple items brands a,b,c,d,e.
    I will be having a slicer for these brands which would allow me to select a to ensure.
    My goal is to have a conditional formatting on the slicer.
    Say, a is rank 1(best) and e is rank 5(worst)
    I want the slicers to indicate the ranks via colors green to red and also sort the slicers based on the ranks.

    Reply

    • Varun
      September 17, 2016

      Would that be possible? Could someone help me on this ?

      Reply

      • Hui…
        September 18, 2016

        @Varun

        Can you please ask the question at the Chandoo.org Forums
        http://forum.chandoo.org/

        Please attach a sample file for a targeted response

        Reply

Leave a Reply

 

 

 

Back to top
mobile desktop