How to Make Ranking Bump Chart
A bump chart is a type of chart that can be created in Excel. We are going to insert a ranking bump chart.
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:
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.
Mark the same quantities of cells with data as in the data you initially had.
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.
Click on any of the rank cells that are still marked.
Click on the Insert tab, and then click on the Line chart.
Choose a line chart without a marker in 2-D.
Right-click on the Y axis, and click on the Format Axis.
In the bound, type 0 as the minimum, 4 as the maximum, 0 as the minor, 1 as the major, and then check values in reverse order.
Note: If you have three different things you are ranking rather than four, you should set the maximum to 3. You can also add an axis title if you wish.
Our Bump Chart:
Leave a Reply