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
Black & White Style
Funky Style
Blue Style
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
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.
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
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
Add a Slicer
Select any cell in the Style Table and goto the Insert, Slicer menu
An Insert Slicers dialog pops up, Select Style
You will now have a Slicer linked to the Styles table
You can format the slicer as appropriate, Resize and Rename it if required
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
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
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
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:
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/
12 Responses to “Apply Conditional Formatting using Slicers”
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.
@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
@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. 🙂
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
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
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.
@Gary
You also need a Field named "Id"
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
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.
Would that be possible? Could someone help me on this ?
@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
thank you this example of slicers.
1. you don't explain what causes some of the table rows to become hidden.
2. if one condition requires a second Conditional Format, wouldn't there be a total of 5?
i haven't used slicers. i understand all of the formulas, including subtotal.
thank you