Mimic 2007 Conditional Formatting Icons
XL2007 has a extended conditional formatting to allow the display of icon sets. The following will demonstrate some ways in which you can create a similar appearance in previous versions of excel.Here is how XL2007 would display a small data table. The shift arrows are based on the difference between 2007 and 2006 Performance figures for each Widget.
The 4 Rating columns are based on the comparison of the Sales of the Widgets.
The cell D3 contains the following formula
=MID("èéê",IF(C3-B3=0,1,IF(C3-B3>0,2,3)),1)
Lets break that formula down. The MID function returns a sub set of characters from a piece of text starting at a specific position and continuing for a specified number of characters. The piece of text is "èéê" which displayed using the wingdings font is èéê. The number of character we want displayed is always 1. The start position is determined by the compound IF function. The first test is for the 2 values being equal. If they are the number 1 if returned and used as the starting position. If not the second IF function is performed. If the difference between the 2007 and 2006 value is positive 2 is returned otherwise 3.
The Shift Colour column contains the same formula in order to get the correct arrow displayed. The colour is provided by using normal conditional formatting.
Start by creating 4 rectangular auto shapes increasing in size. Position these in front of a larger white rectangle with no border.
Note: For the purposes of this page I have coloured the background shape so you can see it against a white background.
Select all five shapes and group them. Now whilst holding the SHIFT key click the Edit menu. You should see a new menu item Copy Picture...
Lets start with the formula to calculate whether to display 1, 2, 3 or 4 bars. Note that with the 4 rating bar 1 is always displayed so we can already set the fill colour of the cells in column G to Blue.
This is the formula in G3
=VLOOKUP((F3-MIN($F$3:$F$6))/(MAX($F$3:$F$6)-MIN($F$3:$F$6)),{0,1;0.25,2;0.5,3;0.75,4},2,TRUE)
It calculates the percent value of the spread of a Sales figure in relations to all the Sales figures (I think!).
The percentage is then used as the lookup value in the table with the following outcome
4 bars when value is >= 75%
3 bars when value is >=50% AND < 75%
2 bars when value is >=25% AND < 50%
1 bar when value is <25%
Now we need to apply conditional formatting to H3:H6