One of the nice things about Excel 2007 is the new conditional formatting options.
Let’s look at this briefly. First, enter some data in Excel, like this;
Then, select the data range A2:B14 and use the ‘Format as Table’ button on the Home ribbon. Make sure the ‘My Table has Headers’ option is checked and click OK. You should be able to get something like this after a bit of formatting.
Now, select just the rainfall data in the range B3:B14, and click the “Conditional Formatting’ button on the Home ribbon. Select light blue data bars from the options. That’s it, you now have a quick and simple visualisation of the data.
Can we simulate this in Excel 2003? The answer is yes, but we need a little bit of VBA code.
The code is shown below. You need to enter this in a module in the VB Editor.
Option Explicit
Sub BuildBar()
' Builds rectangle shapes in column C
' for this sample we assume that the range inpRange
' contains numeric data that we wish to chart
' the column next to the data should be empty
Dim inpRange As Range
Dim cell As Range
Dim barLength As Double
Dim total As Double
On Error Resume Next
Set inpRange = Application.InputBox(prompt:="Enter Data Range" & Chr(10) & _
Chr(10) & "CAUTION:" & Chr(10) & "Results will be shown in next Row or Column " _
& Chr(10) & "and will overwrite any existing data", _
Title:="Conditional Bars", Type:=8)
On Error GoTo 0
If inpRange Is Nothing Then Exit Sub
' clean up any previously built rectangles
CleanUp
total = Application.WorksheetFunction.Max(inpRange)
For Each cell In inpRange.Columns(1).Cells
barLength = cell.Value / total
Call AddRectangle(cell.Offset(0, 1), barLength)
Next cell
End Sub
Sub AddRectangle(dest As Range, barLength As Double)
' Adds a rectangle shape to fill the specified cell
Dim cL, cT, cW, cH As Single
Dim shp As Shape
With dest
cL = .Left
cT = .Top
cW = .Width
cH = .Height
End With
Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, cL, cT, cW, cH)
With shp
' name the shapes so that we can keep track of them
.Name = "fcRect" & dest.Address
' set a fill colour
With .Fill
.ForeColor.SchemeColor = 10
.BackColor.SchemeColor = 51
.TwoColorGradient msoGradientVertical, 1
End With
' size them to be proportional to barLength
.ScaleWidth barLength, msoFalse, msoScaleFromTopLeft
End With
End Sub
Sub CleanUp()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 6) = "fcRect" Then
shp.Delete
End If
Next shp
End Sub
The ‘BuildBar’ routine calls an input box to get the data range from the user, then it calls the ‘CleanUp’ routine to delete any shapes that have a name starting with “fcRect”. It then draws a series of rectangle shapes proportional to the data in the next column. You can add a button to the worksheet to call the ‘BuildBar’ routine if you like. When the routine is run, you will be asked to select the data.
Then clicking OK will build conditional bars in the next column. As shown.
Enjoy