October 06, 2005

Bar Graphs In Cells

I just saw a future conditional format coming up in Excel 12 via the J-Walk Blog and thought I would try to imitate it. Note: This should work fine with Excel 2002 and 2003, I'm not sure about earlier versions but you can give it a try. (The transparency feature might not work as well though)

Here's the real McCoy... (picture from David Gainer's Microsoft Excel blog)

And here's the code I used with Autoshapes...

Sub BarGraphsInCells()
    On Error Resume Next
    Dim LoopCount As Long, BarWidth As Long, Cell As Range, theRange As Range
    Set theRange = Selection
    LoopCount = 1
    For Each Cell In theRange
        BarWidth = (ActiveCell.Value / Application.Max(theRange)) * ActiveCell.Width * 0.7 ' Adjust to suit
        ActiveSheet.Shapes.AddShape(msoShapeRectangle, ActiveCell.Left, ActiveCell.Top, BarWidth, ActiveCell.Height). _
            With Selection
                .ShapeRange.Fill.ForeColor.SchemeColor = 12
                .ShapeRange.Line.Visible = msoFalse
                .ShapeRange.Fill.Transparency = 0.8
                .Name = "The Bars" & LoopCount
            End With
        ActiveSheet.Shapes.Range(Array("The Bars", "The Bars" & LoopCount)).Select
        Selection.Name = "The Bars"
        LoopCount = LoopCount + 1
End Sub

And the result...

Hmm, not too bad considering it's way past my bedtime. Night!

Write a Comment
Hi Andrew,

Both Excel 97 and 2K show 'semi-transparent' BarGraphs. If you would like to see an example:


Yet another reason for me to get me an Office 12 license as soon as it becomes available.

Posted by Rembo at October 06, 2005 06:06
Hi Rembo,

Thanks for the image file :-)

We've got a computer with 2000 at work which I use for "testing" on occasion and hopefully I'm going to get hold of one with 97 sometime soon. Unfortunately transparency isn't as good with these earlier versions, though I believe if it's available, then make the most of it!

That said, I can't wait to work with Excel 12 either, it will be great to see these new features ;-)
Posted by Andrew at October 06, 2005 08:45
Andrew, good job! it worked.
FYI, Office 12 has many other functions....I'd like tell you but it's still under NDA!
Posted by Colo at October 06, 2005 09:45
Two weeks ago I wrote a similar macro for a client, to compare values within a table. Last week I saw this feature demonstrated at the MVP Summit, and realized it's now built into conditional formatting. There are a lot of neat things in Excel 12.
Posted by Jon Peltier at October 06, 2005 10:46
Hi Colo and Jon!

Comments limit the number of words I can write but please have a look at my new post!

NDA...well I can respect that...guess I'll just have to wait to see what other goodies there are, reading David Gainer's blog is becoming more and more interesting!
Posted by Andrew at October 06, 2005 20:52
Another way to do a similar thing is using a horizontal bar chart. By adjusting sizing properties of the bar chart to match cell sizes, and fixing the chart to the vertical range containing the data, you get the same look.

The upside of that approach is it doens't require VBA and any chart formatting options are fair game.

The downside is that the chart isn't quite "in" the cell, or behind the data for space efficiency as with Excel 2007.
Posted by Wade at April 08, 2006 07:19
Hard to argue with something built-in, ...some of the time anyway.

All the same, rolling-my-own is kind of fun ;-)
Posted by Andrew at April 22, 2006 18:09
thanks for the nice code....

Posted by OJ.. at January 22, 2007 09:56
Hi Andrew,

This is a great website to share knowledge about VBA in Excel.

I am a big fans of excel but i am still a newbie in VBA,

I would like ask permission from you to use your code in my spreadsheet...

I hope the answer is yes... :-))

thanks for your reply

'Wish the best for you in this year
Posted by Ferdi Rizal at January 23, 2007 12:22
OJ, you're welcome :-)
Posted by Andrew at January 23, 2007 15:46
Hi Ferdi,

No problem using the code, please be my guest.

All the best for you this year too :-)
Posted by Andrew at January 23, 2007 15:48