Bump charts got their name from “bumps race’”, a term used to refer to a boat race where each boat tries to “bump” the one in front and move up the chart. Bump charts have become quite common of late and are typically used to represent changes in the position of a given number of competing entities over a fixed time duration.
Bump charts have been featured quite prominently on Kiaser Fung’s junkcharts blog. Over the years his blog has served as a trail room for a number of guilty charts :-). It is highly recommended read if you haven’t already had a chance. Reading through one of his posts which featured a bump chart, I wondered if a similar bump chart could be created in Excel. I toiled over it for a few agonizing minutes and then all of a sudden all the pieces fell in place.
Here are the steps to create a bump chart in Excel
1. Layout the data for the bump chart in the following manner. The chart labels are in the first column followed by the positions (before and after) in the next column.
(The data used is for illustration purposes only)
2. Select the entire data range and from the menu click ‘Insert’ ->’Chart’. In the Chart selection select a line chart with markers.
3. The bump graph at this stage will look something like this. Right click the chart and click ‘Source Data’. In the ‘Data Range’ tab change ‘Series In’ from columns to row. Press ok.
4. Remove the legend. Format the chart and clear the chartjunk. Format the chart border and the gridlines. Adjust the chart height and width as per requirement.
5. Double click the left axis of the chart and on the ‘Scale’ tab, change ‘Major unit’ to 1. Enable ‘Values in reverse order’ checkbox. Press ok.
6. Now right click on the chart window and select ‘Chart Options’. On the tab labeled ‘Data Labels’, turn on ‘Series Name’.
7. You bump chart now looks something like this.
8. Double click on any particular line which represents one data series. Change the line’s color and marker color to a grey (or anything that meets your requirement). Change marker shape to square or circle and give it a same fill color as the line.
9. Now one by one click on each line and press F4. This will format all lines in the same color and shape as the one we modified in step 8.
10. Now one by one, move the data labels away from the data points so that the bump chart look neat and clear. Provide a label to the chart.
Bump charts are an excellent way to plot changes in position when a large number of entities are concerned. They are simple to understand and adapt well to being used in dashboards especially when comparing changes in month over month performance.