How to Make Ranking Bump Chart

We are going to insert a ranking bump chart. I am using Microsoft Excel 2013, but you can use other versions to perform the insertion.

Rank charts are not often used in Excel. Due to their specificity, they are not suitable for most applications. However, the bump graph is a good choice to show:

  • ranking changes over time
  • results of changes in election polls
  • position changes in the table, e.g. football teams in the league

Prior to inserting the bump chart, I will assume that you have data available, which is something like this:

data set to create a bump chart in Excel

Note: The data can be as you like, it is about ranking the products or anything else, and you can place any data you want.

How to create a Bump Chart?

Copy the time and whatever you would like to rank in a new place on the same sheet. In this case, we are going with products.

click cell of rank chart

Mark the same quantities of cells with data as in the data you initially had.

data quantities for bump chart

Use the rank function to rank the columns.

The rank function syntax is:

=RANK(number, rank, [order])

Rank function arguments:

  • number - the value for which you want to find the ranking
  • rank - The range of values for which you are looking for a ranking
  • order - an optional parameter needed when you want to decide whether the ranking should be sorted in ascending or descending order

With the marked area, click on the formula bar, and type in =RANK(B2; all the cells in the same column, for example B$2:B$5). The full formula in my example is =RANK(B2,B$2:B$5,0). Press CTRL + Enter.

rank function

Click on any of the rank cells that are still marked.

copy rank

Click on the insert tab (1), and then click on the Line chart (2).

insert line chart

Choose a line chart without a marker in 2-D.

chart without marker

Right-click on the Y axis (1), and click on the format axis (2).

format axis of bump chart

In the bound, type 0 as the minimum (1), 4 as the maximum (2), 0 as the minor (3), 1 as the major (4), and then check values in reverse order (5).

bounds of bump chart

Note: If you have three different things you are ranking rather than four, you should set the maximum (2) to 3. You can also add an axis title if you wish.

Our Bump Chart:

bump chart example

You can download a free Bump chart template here