Apply Conditional Formatting using Slicers


Have you ever wondered about applying different Spreadsheet Formats to reports which may be send to different people and so the styling may be different for each recipient?  The Boss may get a Formal report where the Art department may get a Funky version of the same data?

No, Neither had I until recently when somebody asked me for just that:

Boss Style

CFUS01

Black & White Style

CFUS02

Funky Style

CFUS03

Blue Style

CFUS15

Of course using Conditional Formats you can highlight cells based on criteria, so why not extend that to the Whole Report Styling?

This tutorial will detail just that.

Lets get started

Download the Sample File (Excel 2013 & 2016 + only): Download Sample File

Firstly Identify your Report Area

In this case it is B8:E28

CFUS04

Note: The area above includes the header row, Row 8, but you can actually apply different CF’s to hat independently of the data area if you require

Make a list of Style Names

I have used four namely: Boss, Blue, Black & White and Funky.

CFUS06

Add an Id next to each from 1 to x in this case 4 as there are 4 entries

Convert the Table to a Table by selecting the area E2:F6

Goto Insert, Table

CFUS05

Add a Style Link cell

In a spare cell H2: add a formula like: =SUBTOTAL(4,Style[Id])

This will extract the Maximum value from the Table when the non-selected rows are hidden.

I have also Named the cell Style_Link

CFUS07

Add a Slicer

Select any cell in the Style Table and goto the Insert, Slicer menu

An Insert Slicers dialog pops up, Select Style

CFUS08

You will now have a Slicer linked to the Styles table

CFUS09

You can format the slicer as appropriate, Resize and Rename it if required

CFUS10

Apply Conditional Formats to the Report

Now select the report area and apply four Conditional Formats which will be styles according to the Useage

You normally only apply 3 styles as the default is already a style

Select B8:E28

Goto the Conditional Format, New Rule, Use a Formula menu

Apply the formula and format to suit your needs

Boss

This is my Default style when Style_Link = 1

Hence I don’t need to apply a specific style

 

Blue

This is my Conditional Format style when Style_Link = 2

CFUS11

Note: the formula used is =AND(B8<>””, Style_Link=2)

So the Conditional Format will only apply this to cells in the area with a value in them and when the Style_Link cell = 2

Black & White

This is my Conditional Format style when Style_Link = 3

CFUS12

Note: the formula used is =AND(B8<>””, Style_Link=3)

So the Conditional Format will only apply this to cells in the area with a value in them and when the Style_Link cell = 2

Funky

This is my Conditional Format style when Style_Link = 4

CFUS13

Note: the formula used is =AND(B8<>””, Style_Link=4, ISODD(Row))

So the Conditional Format will only apply this to cells in the area with a value in them and when the Style_Link cell = 4 and the Row Number is Odd

Obviously we need to apply a second Conditional Format  for when the even numbered Rows

It will use the Conditional Format formula: =AND(B8<>””, Style_Link=4, ISEVEN(Row))

You should end up with four Conditional Formats listed as:

CFUS14

Closing Notes

Although in this post I have used a Slicer to supply the user a list of Styles for choice, you could simply use a single cell with a Data Validation Drop Down or a Combo Box to control the style selection process.

The client for which this technique was applied had a dashboard and wanted to have the control appear similar to other slicers on the dashboard hence maintaining the look and feel of the dashboard.

Conclusion

You now have a tool which allows you to dynamically change the styling of your worksheet reports.

You can add extra formatting by using the Style_Link cell to say change the Decimal Places of the numbers in Column E of the report

eg: Assuming my Sales Data is in Column AA, which it is.

In E9=TEXT(AA9,CHOOSE(Style_Link,”$A 0,000.00″,”A$ 0,000″,”AU\D 0,000″,”F$ 0,000″)))

Copy down

This will apply 2 decimals to Column E when the Boss Style (1) is chosen and zero decimals for everybody else

It will also apply different currency leaders for the different styles

Style 1: Boss $A

Style 2: Black & White A$

Style 3: Boss AUD

Style 4: Funky $F

As mentioned in one of the notes above, you can apply Conditional Formatting independently to the Headers, Footers or Summary areas of the Report, your imagination is the limit.

I’m sure you can think of other modifications to the layout that can be implemented using these techniques

Other Style Links

You may be interested in these other links to worksheet styling functionality:

http://datapigtechnologies.com/blog/index.php/getting-fancy-with-your-excel-slicers/

 

Written by Hui...
Tags: ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

11 Responses to “Apply Conditional Formatting using Slicers”

  1. Luke M says:

    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.

    • Hui... says:

      @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

      • Luke M says:

        @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. 🙂

  2. 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

  3. Gary Lundblad says:

    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

    • James H says:

      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.

    • Hui... says:

      @Gary
      You also need a Field named "Id"

      • Gary Lundblad says:

        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

  4. Varun says:

    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.

Leave a Reply