MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Create In Cell Column Chart

November 27, 2001 - by Bill Jelen

A reader from the Czech Republic asked about creating an in-cell chart to illustrate a percentage.

If the value in a cell was 37%, how can I fill 37% of the cell from the bottom with a color?
Sample Percentage Data with Bar

Mala Singh from our Graphics division came up with this interesting solution to produce the desired effect. Mala built a worksheet where each row is actually comprised of 2 merged rows. Cells B2 & B3 are merged together in a single cell. When the value in B2 is changed, a bit of VBA code in the worksheet pane will automatically adjust the height of row 2 & row 3. Cell C3 is colored blue and cell C2 is colored white. The effect is that column C appears to show a column chart in the cell. This image shows various heights of the blue bar in cells C2 through C13.

Merging Cells

The first step is to merge cells B2 & B3 into a single cell. You will select cells B2 & B3. From the menu, select Format, Cells. Go to the Alignment tab. Check the box for Merge cells. This will cause B2 & B3 to act as a single cell called B2.

Leave cell C2 with no fill and use any color fill for cell C3.

Code for this technique is not placed in a regular module. It is "event handler" code and must be placed on the code module for this particular worksheet. Read Event Macro to Add Path and Filename to Excel Header article for a visual of how to open the code module for a worksheet.

Enter the following code on the code module for Sheet1 (or whatever sheet you are working with).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim cell As Range
  For Each cell In Target.Cells
    If cell.Column = 2 And cell.Row > 1 Then
      If cell.Value > 1 Or cell.Value < 0 Then
        MsgBox ("Value must be between 0 and 100%")
      End If
      If IsEmpty(cell) Or cell.Value = 0 Then
        cell.Range("B1:B2").RowHeight = 25
        'cell.Range("B2").Interior.ColorIndex = xlNone
        On Error Resume Next
        cell.Range("B1").RowHeight = 50 * (1 - cell.Value)
        With cell.Range("B2")
          .RowHeight = 50 * cell.Value
          '.Interior.Color = RGB(0, 0, 255)
          On Error GoTo 0
        End With
      End If
    End If
End Sub

You can repeat the process of merging pairs of cells in column B.

Thanks to Mala Singh for this solution. Mala can design custom charting solutions to meet any need. He contributed to the charting chapter in VBA and Macros for Excel.

Bill Jelen is the author / co-author of
Excel Insights – A Microsoft MVP Guide to the Best Parts of Excel

There are fewer than 100 Excel MVPs worldwide. 24 of them have contributed to this book. Written, edited, reviewed and printed by Excel MVPs, this is practical Excel passion undiluted, with each MVP highlighting some of their favorite topics.