You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s authors, Amit Velingkar, a Program Manager on the Excel team, and Bob Silverstein, a User Experience Researcher on the Excel team, talk about creating professional looking Color Ranges in Excel.
My fellow PM on Excel, Robin Wakefield, wrote a blog article on how to create professional looking charts in Excel 2007. Continuing with the same theme, we will investigate the idea of tweaking Color Ranges in Excel 2007. We will start with a default Conditional Formatting Color Range and modify it to give it more pastel look. Also, this would be an ideal forum to give us your feedback on existing conditional formatting colors used in Excel 2007.
As a part of my preparation for this article, I did two things – researched customer files that use conditional formatting and explored various options with Microsoft designers.
Looking at some customer files, I quickly observed a trend. I found out that many users wanted to highlight a small portion of their data. They were achieving this by changing some of the colors in their color ranges to white. Also, some customers preferred a pastel shade of the same color.
I discussed this trend with Microsoft designers and they taught me some neat tricks about manipulating colors. First, they told me that choosing the correct shade of white was critical while mixing colors. And last, an intuitive way of changing shades of colors was to use the HSL (Hue, Saturation and Luminosity) model.
My blog article attempts to bring all these lessons together. We will change some of the colors to white and tweak the colors shades to pastel. Finally, we will introduce a VBA macro that lets you quickly try out new color shades.
1. I started out with applying three of our default color combinations to some data. I choose the following color ranges: Red-Yellow-Green, Red-Yellow-Blue and Yellow-Green.
2. I replaced the yellow color with the appropriate shades of “white”.
Changing the yellow colors to pure whites created a high contrast. For creating whites with a good spectrum of colors, we crank up the RGB values all the way to 255 creating a pure white – Remember RGB (255,255,255) is white. Then, remove just a little amount of the participating color to create “target” white.
As an example while mixing Red and Green, we set our white to (250,250,255) by removing some red and green. The end result is a color range containing pure shades of Red and Green.
Remove some red and green
Remove some red and blue
Remove some red
Remove some green
3. Using HSL Model to change non-white colors
We can use the HSL model to manipulate color. HSL defines the color spectrum based on Hue (base color from the rainbow spectrum), Luminosity (brightness) and Saturation (purity of the color).
Select the color range. On the Home tab, click on Conditional Formatting, then Manage Rules. In the Conditional Formatting Rules Manager, select the Conditional Format and click on Edit Rule. In the Edit Formatting Rule dialog, click on the color dropdown and choose More Colors. Click on the Custom tab and choose HSL in the Color model dropdown.
In this example, we will change colors using the HSL models as follows:
o Hue: Unchanged, this leaves the base color unchanged. Hence our Reds stay Red.
o Saturation: Decrease the Saturation to 150. This makes the colors more pastel.
o Luminosity: Increase the luminosity to 200. The goal is to increase the brightness to make the color more visible and showcase the text behind the color ranges.
By using shades of white and by tweaking the luminosity and saturation of colors, we changed the color ranges as follows:
HSL Color Manipulation using VBA:
Since I wanted to try out a lot of combinations of Luminosity and Saturation values to tweak color ranges, I decided to write a VBA macro to do this. I quickly learnt that while Excel allows HSL manipulation through the UI, it does not support HSL manipulation through VBA. I looked around for ways to manipulate HSL and finally found a blog post by Tony Jollans that did something similar. Although most of the code used is directly from Tony Jollans’s blog post, I wrote some additional subroutines, added some comments and re-factored some of the subroutines for my purpose.
The entry function for my macro is called ChangeColorRangeColors().
Sub ChangeColorRangeColors() ...
For Each clrScale In Selection.FormatConditions For Each criteria In clrScale.ColorScaleCriteria ' get clr clr = criteria.FormatColor.Color
' split RGB SplitRGB clr, R, G, B
'skip whites - when all RGB componenst are above 240 If R < 240 Or G < 240 Or B < 240 Then
' get HSL RGBtoHSL clr, H, S, L
' increase luminosity L = (iLuminosity / 255) ' from 0.0 to 1.0
' decrease saturation S = (iSaturation / 255) ' from 0.0 to 1.0
' get RGB HSLtoRGB H, S, L, clr
' set new clr criteria.FormatColor.Color = clr
End If Next Next End Sub
At a high level, my HSL manipulator code works in the following ways:
I won’t go into the details of converting between RGB and HSL values. These conversion routines are well documented and can be freely found on the internet.
However, another tricky part was trying to split the encoded color value that is used by Excel into individual RGB components. VBA provides a function, aptly named RGB(), that returns an encoded long value when provided with the R,G and B components. I have used this function in my macro to set the Color Range colors. However, VBA does not provide a reverse function to split this encoded long value. I have used a custom function called SplitRGB to do this.
Sub SplitRGB(RGB As Long, R As Double, G As Double, B As Double) Dim HexString As String
' convert the long to Hex - bb:gg:rr HexString = Hex(RGB)
' in order to get r,g,b components out of the string, ' we have to make it is atleast 6 characters long - bb:gg:rr HexString = Right(String$(5, "0") & HexString, 6)
' get each individual color and convert to an double (range: 0 to 255) R = CDbl("&H" & Mid$(HexString, 5, 2)) G = CDbl("&H" & Mid$(HexString, 3, 2)) B = CDbl("&H" & Mid$(HexString, 1, 2)) End Sub
Since the R, G, B values span from 0 to 255 and can be accommodated in 1 byte. Excel uses a 4-Byte long to encode the RGB values. The first byte is used to store the red value, the second byte stores the green value and the third byte stores the blue values. An easy way to observe these values is to convert this encoded long into a hexadecimal string. We then split this string using the Mid function to isolate the R, G, B components. Finally, we use a conveniently provided CDbl function to convert hexadecimal text to a number. Observe that appending the “&H” enables the CDbl function to identify the text as hexadecimal.
To recap, We can create professional looking Conditional Formatting color ranges by choosing the correct shades of whites and using the HSL model to tweak Saturation and Luminosity values. I am hoping that by using these techniques and subroutines, you will be able to automate the look and feel of your Excel Spreadsheets.
The attached workbook contains these color ranges and the VBA macros. We would love to hear your feedback on these colors.
A logical next step, after using conditional formatting to mark specific rows with certain colours (or even after manually colour-coding some rows) would be to filter based on these colours (e.g. "show me all rows where the sales total is red").
I can't find a simple way to do this. Sorting by colour helps, but it's far from ideal. Some people I discussed this with suggested writing some VBA code to do this, but I'm not a programmer. Is there anything in Excel 2007 that can help with this?
"show me all rows where the sales total is red" - that’s relatively straightforward, as indeed is filtering predefined colours. You might be interested to try Ron de Bruin’s EasyFilter
The idea to “sort” by colour sounds easy enough, until you start to think about it. Which colours to place at the top, bottom, and the shades in between. The colour spectrum is a circle, sometimes called a wheel. Uncoil the circle and you’ve got (say) red at the top and red at the bottom, so that’s no good. Then of course there’s luminosity and intensity to consider. It gets even more complicated if you need to consider that colour, as the eye perceives it, does not change linearly.
Unless you want to sort particular colours in some order that makes sense to you, sorting by colour is really only viable with a limited range of colours involved, or a mono-chrome from light to dark or intense to grey. In other words colours represented by a 2 or 3 colour gradient.
For those of you trying to click through to Tony's original article, the link is: