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).
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.
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.