I hadn’t come across bump charts until yesterday but now I am an expert (not really!!). However, I saw a good bump chart in which they used VBA to help users to make it look a bit more suave. I have never learned VBA so I took a different route to achieve the same result. Look at these two screenshots: basic chart and highlighted chart:
You can choose to highlight nothing, one of the series and two of the series: no more than two.
What are Bump Charts?
Bump charts are rank ordered charts. That is, take a table of raw data, sales by month in this case and convert them into ranked data, month by month. Here are parts of both tables combined for information:
The formula you need to convert raw data to ranked data is, for example: =RANK(K26,K$26:K$29,0) … see the spreadsheet file, link below, to see exactly how I used it.
Building the Chart
To be honest, I borrowed the basic chart from http://best-excel-tutorial.com/56-charts/306-bump-chart and then enhanced it in this way:
- I created a third table to hold the additional data for the revised chart
- I created two combo boxes: one for what I called the base product and another for a comparison product
- I used VLOOKUP() functions to fetch the product names from the Ranked Data table and another one to fetch the rank values from that table. It gave me this:
Notice how I used NA() in the formula for each month, the second VLOOKUP() formula.
Filling the second of the formulas right to fill all months’ columns completes the table for each product.
In the combo box I allowed for there to be no selection: so, no highlights, one highlight or two highlights. Again see my file to see how that works.
All basic data series are coloured light grey and they are always on the chart. The highlights overlay the basic series as appropriate and are coloured: choose your own colours, of course.
That’s it! Bump Charts illustrate rank orders over time in a really meaningful way and now you’ve heard of them too!
Download my spreadsheet file from here: bump_chart