Creating a Thermometer Chart in Excel Using VBA

A Thermometer Chart is a great way to visually represent progress towards a goal, such as tracking sales performance, project completion, or any percentage-based metric.

1. Understanding the Thermometer Chart Structure

A Thermometer Chart consists of:

• A background column representing 100% of the target.
• A filled column representing the actual progress.
• A properly formatted chart to resemble a thermometer.

The core concept behind this chart is using a Stacked Column Chart where:

• One data series represents the actual value.
• Another series represents the maximum possible value.

2. Preparing the Data for the Chart

We need a simple table with:

• Current Value (e.g., actual progress towards the goal).
• Target Value (e.g., 100%).

3. VBA Code to Create a Dynamic Thermometer Chart

The following VBA macro automates the process of creating and formatting the Thermometer Chart:

Sub CreateThermometerChart()
     Dim ws As Worksheet
     Dim chartObj As ChartObject
     Dim chart As Chart
     Dim rng As Range
     ' Set the worksheet where the data is stored
     Set ws = ActiveSheet
     ' Define the data range for the chart
     Set rng = ws.Range("A1:B3")
 ' Assumes data starts at A1 with headers
     ' Delete any existing chart in the worksheet
     For Each chartObj In ws.ChartObjects
         chartObj.Delete
     Next chartObj
     ' Add a new chart
     Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=50, Width:=300, Height:=400)
     Set chart = chartObj.Chart
     ' Set chart source data 
    chart.SetSourceData Source:=rng
     ' Change chart type to a Stacked Column Chart
     chart.ChartType = xlColumnStacke
     ' Format the chart to look like a thermometer
     With chart
         .HasTitle = True
         .ChartTitle.Text = "Thermometer Chart"
         .Legend.Delete ' Remove legend
         .Axes(xlCategory).Delete
 ' Remove horizontal axis 
  End With
' Format the data series
With chart.SeriesCollection(1)
.IsFiltered = False
.Format.Fill.ForeColor.RGB = RGB(255, 0, 0) ' Red color for the progress
.Format.Fill.Transparency = 0
End With
' Format the second series (background)
With chart.SeriesCollection(2)
.Format.Fill.ForeColor.RGB = RGB(200, 200, 200) ' Gray background
.Format.Fill.Transparency = 0.5 ' Slight transparency
End With
' Adjust the Y-Axis
With chart.Axes(xlValue)
.MaximumScale = ws.Range("B3").Value ' Set max scale to the target value
.MinimumScale = 0 ' Ensure the scale starts at zero
.TickLabels.NumberFormat = "0%"
End With
' Align the chart properly
chartObj.Left = ws.Range("D1").Left
chartObj.Top = ws.Range("D1").Top
End Sub

' Format the data series
With chart.SeriesCollection(1)
.IsFiltered = False
.Format.Fill.ForeColor.RGB = RGB(255, 0, 0) ' Red color for the progress
.Format.Fill.Transparency = 0
End With
' Format the second series (background)
With chart.SeriesCollection(2)
.Format.Fill.ForeColor.RGB = RGB(200, 200, 200) ' Gray background
.Format.Fill.Transparency = 0.5 ' Slight transparency
End With
' Adjust the Y-Axis
With chart.Axes(xlValue)
.MaximumScale = ws.Range("B3").Value ' Set max scale to the target value
.MinimumScale = 0 ' Ensure the scale starts at zero
.TickLabels.NumberFormat = "0%"
End With
' Align the chart properly
chartObj.Left = ws.Range("D1").Left
chartObj.Top = ws.Range("D1").Top
End Sub

4. Explanation of the Code

Step 1: Setting Up the Worksheet and Data

• The macro works on the active sheet.
• It assumes that column A contains labels and column B contains numerical values (Current & Target).

Step 2: Deleting Existing Charts

• Before adding a new chart, any existing charts in the sheet are deleted to prevent duplication.

Step 3: Creating a New Chart

• A ChartObject is inserted into the worksheet at a specified location.
• The source data for the chart is set using chart.SetSourceData.

Step 4: Changing Chart Type

• The macro sets the chart type to Stacked Column Chart (xlColumnStacked).

Step 5: Formatting the Thermometer Effect

• The first data series (Actual Value) is colored red to represent the progress.
• The second data series (Target Value) is colored gray to represent the full scale.
• The legend is removed for a cleaner look.
• The horizontal axis is deleted to give a thermometer appearance.

Step 6: Adjusting the Y-Axis

• The maximum scale is set dynamically to the Target Value.
• The axis labels are formatted as percentages.

5. Running the VBA Code

To run the macro:

• Open Excel and press ALT + F11 to open the VBA Editor.
• Insert a new Module (Insert → Module).
• Copy and paste the VBA code into the module.
• Run the macro by pressing F5.

6. Enhancements and Customizations
7. Make the Chart Update Automatically

• Instead of recreating the chart every time, modify the macro to update an existing chart when values change.

8. Add a UserForm for Input

• Allow users to enter values in a UserForm and dynamically update the chart.

9. Improve Aesthetics

• Add rounded edges and a glossy effect to the thermometer.
• Use gradient fills to enhance the visualization.

10. Conclusion

This VBA macro provides a structured way to create a dynamic Thermometer Chart in Excel. By following this guide, you can automate the visualization of progress tracking, making reports more interactive and insightful.