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/
30 Responses to “Great News: Chandoo becomes MVP”
Congratulations! It's well deserved. 🙂
This is amazing. Hearty congratulations and a rocking new year ahead!!!
Congrats! I have learnt alot from your site... and the most important is learning how to achieve the most with the simplest concepts.
Thanks for the excel calendar. Is it possible to get a simple big fonted calendar printable on an A4 size paper without any distracive notes or visuals? BTW, I have already signed up for your newsletter. With warm regards and
Gratefully yours
50+ year old CHarish.
Hey Chandoo,
Great to hear that. Congratulations! The best new year gift, I would say. Keep it up, u've been doing extraordinary work for the excel users community.
Regards,
Pankaj Verma
Congrats dude... fantastic news!
congratulations! your site is great, this is well deserved
Rich
I recently found your site, I visit many. The tips that you provide are in the top 1% of all the sites I visit. Keep on Excelling.
Arnold
South Africa
Congratulations, Chandoo! That's a great way to start the year and make the PHD even better.
Congratulations, Chandoo.
Your site is one of most useful on the net. Happy new year and lot of ideas you will present for us.
Congrats.
Just read your name in an email from Abhishek. Well deserved.
Congratulations, and Happy New Year.
Greetings from Rio de Janeiro my friend! You trully deserve it!
Nive way to start 2009! Keep up the good work!
FC
That was quite forseeable , so you have now really got your PhD in excel.
Anyways Chandoo you have made excel a real Fun doo
I will like you to write some more on INDEX and MATCH function in near future.
@Hey Chandoo ! Congrates....
Ab to treat mangta hai !
Well deserved Chandoo!!
Congrat's!! Very well deserved 🙂
i always browsed mr.excel and used to see MVP writtne below names of people who used to solve queries in excel forum there......i just used to admire as to what they have special in them that they are MVP......
but now i got my answer...............u deserve it man..........
@All: thanks everyone 🙂
Congratulations Chandoo, nice job!
Chandoo,
A well deserved recognition and a good start to the New Year. Continue your good work.
Subbu
Many Congratulations.
You deserve a Ph.D. 🙂
congrats.....
Congrats dude. Rock on!
[...] charting community in 2007 and has been growing strongly ever since. In year 2009, I have received the MVP award from Microsoft. Just few days back I have become a dad [...]
Respected sir,
I am impressed!.... Good job done.. Keep it up...
Sir, How to be a MVP certified person. What level of knowledge is required for it? send me links if possible.
Please reply...
Regards,
Dipak Khalasi.
Dipak -
The first thing you need to cultivate is the ability to search the web effectively. You could start by Googling "Microsoft MVP".
[...] boy and girl which has been made hectic and incredibly fun ever since to their life.He has been awarded MVP status in 2009 by Microsoft(and renewed in 2010,2011 & 2012).His MVP profile is here.If you want to contact him direct then [...]
Congrats Chandoo!!
[…] Chandoo becomes MVP […]