Create and format sparklines in Excel VBA

step2_16x9

Sparklines are small in cell charts that were introduced in Excel 2010. They allow you to compare data in a visual way without creating full size charts to do so. Here we’ll show you how to work with sparklines using VBA code.

You can download the code from our website here.

step1Our solution uses some Australian Bureau of Statistics data for CPI values but you can use data of your choice.

We are assuming that the data for the sparklines starts in cell D3 and that it spreads over a number of columns to the right and rows below. Column C will contain the sparklines.

This is the code to automatically create, format and delete sparklines:

Option Explicit
Public mysparkers As SparklineGroup

Sub addSparklines()
Dim sparkersRange, sparkersDataRange As Range
Dim LastRow, LastColumn As Long
LastRow = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Rows.Count
LastColumn = ActiveWorkbook.ActiveSheet.Range("a1").CurrentRegion.Columns.Count
Set sparkersRange = Range("c3", Cells(LastRow, 3))
Set sparkersDataRange = Range("d3", Cells(LastRow, LastColumn).Address())
Set mysparkers = sparkersRange.SparklineGroups.Add(Type:=xlSparkLine, SourceData:=sparkersDataRange.Address)
mysparkers.SeriesColor.ThemeColor = 5
mysparkers.Axes.Vertical.MaxScaleType = xlSparkScaleGroup
mysparkers.Axes.Vertical.MinScaleType = xlSparkScaleGroup
End Sub

Sub deleteSparklines()
Dim rng As Range
On Error Resume Next
Set rng = ActiveWorkbook.ActiveSheet.UsedRange
For Each mysparkers In rng.SparklineGroups
mysparkers.Delete
Next mysparkers
End Sub

Sub flipSparklines()
Dim rng As Range
On Error Resume Next
Set rng = ActiveWorkbook.ActiveSheet.UsedRange
For Each mysparkers In rng.SparklineGroups
If mysparkers.Type = xlSparkColumn Then
mysparkers.Type = xlSparkLine
Else: mysparkers.Type = xlSparkColumn
End If
Next mysparkers
End Sub

step3The first procedure calculates the last row and column when it runs so the macro will work if the data grows or shrinks. The sparklines are created as lines (not columns) and both minimum and maximum vertical axes are configured the same for all the sparklines.

The code to delete the sparklines uses the Sparklinesgroups object to process all the sparklines in the worksheet and to remove them. The flipSparklines code toggles the sparklines between displaying as lines and as columns.

The deleteSparklines and flipSparklines code can be used with sparklines that you create using our code or with any worksheet which contains sparkline charts.

You can adapt this macro to your own needs. So, if your data has a totals column you can adjust the LastColumn value accordingly to omit this data from the sparklines.

We have chosen to add buttons to the worksheet using the Form tools on the Developer tab and to assign the macros to these buttons making it easier to run the macros when needed.

Creating sparklines using a macro like this ensures that they are created as a sparkline group. As a group they will be adjusted, formatted and deleted as a group. Creating them as a group and configuring the axes to be the same for all, also ensures more accurate data comparison.

  • I enjoyed reading your post. Thanks for sharing such a nice post… keep providing useful information.