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

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/