Building a BumpChart in Excel (with VBA)

Bump Charts may not be the most common type of chart, but they are useful for plotting changes in rank. Consider, for example, political polling or winning percentage in sports; it’s the rank that’s particularly important, not the actual percentage. In this example from Andy Kriebel, he focuses on the rank of militarization and not the actual dollar amounts of spending. (Andy Cotgreave has a nice summary of Bump Charts here).

Andy Kriebel Bump Chart on Arms Trading

When I set out to create a Bump Chart in Excel, I found that it wasn’t particularly difficult to do, it just took a ton of time to format all the lines and data markers. Thus, I turned to my trusty (and sometimes frustrating) friend VBA code to automate the styling for me.

At its core, this Bump Chart is simply a line chart of ranks. It has four other identifying formatting characteristics: First, each marker is a circle. Second, each marker has the rank (number) located in the center of the circle. Third, the Category labels sit to the right of each line. And Fourth, the horizontal axis sits at the top (not absolutely necessary, but I sort of like it).

If you did this manually in Excel, you would first create the line chart. Then, you would select each line, change the marker type and size, add and center the data labels, move the x-axis to the top, and then add and format the category labels. My approach in this version is to add an extra data value equal to the last time period of the data (2017), add the data label of the Category name, and then turn off the marker and line. This is all tedious work and can be automated using VBA.

The code below does just that. I’ve commented each section so you can see exactly what’s going on. The only thing you need to do is make sure you add one final data point for the Category labels–those data points and the line to them will be set to missing and data labels placed on top. You then create a Line with Markers chart with the data–here from A1:M6. Then run the code and it’s all done (see below if you don’t know how to run the code).

Sub BumpChart
 Dim iseries As Long
 Dim seriescount As Long
 Dim pts As Points
 Dim bHasLabels As Boolean
 Dim srs As Series
 Dim pt As Point
 Dim cht As Chart

'Duplicate chart
 ActiveChart.Parent.Duplicate.Select

'Remove Legend
 If ActiveChart.HasLegend = True Then
 ActiveChart.Legend.Select
 Selection.Delete
 End If

'Set size
 With ActiveChart.Parent
 .Width = 750
 .Height = 450
 End With

'Remove horizontal gridlines
 ActiveChart.Axes(xlValue).MajorGridlines.Select
 Selection.Delete

'Reverse order of vertical axis
 ActiveChart.Axes(xlValue).ReversePlotOrder = True

'Delete vertical axis
 ActiveChart.ChartArea.Select
 ActiveChart.Axes(xlValue).Select
 Selection.Delete

'Count the number of series (lines) in the chart
With ActiveChart
 seriescount = .SeriesCollection.Count
 End With

'Loop over each series (line) to format
For iseries = 1 To seriescount
 Set pts = ActiveChart.SeriesCollection(iseries).Points

'Set the marker style to a circle and make it bigger (size 15)
ActiveChart.SeriesCollection(iseries).Select
 With Selection
 .MarkerStyle = 8
 .MarkerSize = 15
 End With

'Add data labels for all series
 ActiveChart.SeriesCollection(iseries).ApplyDataLabels ShowValue:=True
 With ActiveChart.SeriesCollection(iseries)
 .DataLabels.Position = xlLabelPositionCenter
 End With

'Add data labels for last point, which will be the series name
 pts(pts.Count).ApplyDataLabels ShowSeriesName:=True, ShowValue:=False
 ' pts(pts.Count).DataLabels.Font.Color = srs.Border.Color
 pts(pts.Count).MarkerStyle = -4142
 pts(pts.Count).Format.Line.Visible = msoFalse

'Color the category text labels the color of the lines
 ActiveChart.SeriesCollection(iseries).DataLabels.Select
 ActiveChart.SeriesCollection(iseries).Points(pts.Count).DataLabel.Select
 With Selection.Format.TextFrame2.TextRange.Font.Fill
 .Visible = msoTrue
 .ForeColor.RGB = ActiveChart.SeriesCollection(iseries).Border.Color
 End With
 With Selection.Format.TextFrame2.TextRange.Font
 .Bold = msoTrue
 End With

Next

End Sub

How to Run VBA Code

If you don’t know how to run VBA code, it may be time to pick it up. Excel Macros can save you tons of time and effort. First, you need to turn on the Developer tab. Go to the Excel Options menu and under the Customize Ribbon option, select the checkbox next to the Developer tab.

Now that you have the Developer tab visible in your Excel ribbon, you’ll be able to use the Macros button. If you select the Macros button, a Macro window will open up. Type BumpChart into the box (or whatever you would like to call the macro) and select Create. In the menu that pops up, paste in the code above. Note that your macro code will automatically start with “Sub BumpChart()” (or whatever you named it) and end with “End Sub”. You don’t need to repeat these statements, so you can paste the code above over what’s sitting there.

You can now close the window. Make your Bump Chart using a Line with Markers. Go back to the Developer tab, select the Macros button, select the BumpChart macro that is now listed in the window, and hit Run. You should now have a tidy little BumpChart.

Either way, here is my Excel file that you can download and use. Just change the data, insert the chart, and run the macro code.

If you want to learn more about writing VBA macros, I highly recommend Jon Acampora at Excel Campushttps://www.excelcampus.com/, Dave Bruns at ExcelJet, and Jon Peltier and PeltierTech.

Note: After I wrote this code and the blog post, I found this gem from Ben Collins where he creates an interactive bump chart in Excel. If you’re interested in going to the next level, I encourage you to read it.

Download the Excel file for this tutorial

Learn more advanced data visualization graphs in Excel with my new step-by-step guide, available in the PolicyViz Shop.