Change in Position – Which Chart Should I Use?

As an analyst, I often need to keep track of the change in position of various players in the industry. More often than not, the position change needs to be tracked between two specific points in time – say quarter 1 vs. quarter 2 or perhaps year 1 vs. year 2. In this article we take a look at a few alternative approaches to plotting position changes and find out which one serves us best. For this example let’s take some freshly “cooked” up data.


The first alternative is a line chart. Not the best of ways to represent the data at hand. Representing multiple categories using a single line is never a good idea especially when they don’t share a relationship. Although one must acknowledge the fact that a line chart does try to provide a sense of the proportion of companies whose position went up versus the number of companies that went down. But then again, that may be entirely due to the data set used for this example … (or is it just me πŸ™‚ ).


The second alternative is the ubiquitous bar chart. Surprisingly in this case, the ever helpful bar chart also does not offer much help – it simply gets bogged down by the sheer number of categories that it has to handle.


With the line and the bar gone, lets turn our focus on the radar chart. Naah … that doesn’t look good either. Neither the value of the data points nor the proportion of companies moving up vs. down can be established without spending a disproportionate amount of time deciphering the chart.


Let’s look at some charts outside the usual assortment provided by excel. The first one is the bump chart.


This does look line what we were looking for? So well … ladies and gentleman, let me introduce you to our hero of the day … the bump …hey ..wait a minute … its a rank order chart masquerading as a position change chart. DISQUALIFIED ! Just for the sake of curiosity, what would this bump chart have looked like if we had used the absolute numbers to show positional changes rather than the rank.


That’s not half as good as the previous one, is it. Looks like we’ll have to search a bit more for this one.

Here’s something that I picked up from the Economist. The bars give the reader an idea of the quantum of change with the arrows pointing in the direction of the change. You might’ve already guessed – its a stacked bar chart. With a bit of color coding (red for decrease, blue for increase), this does look like a reasonable alternative. A visual comparison of the number of arrows of each color, allows the reader to get a fair idea of the proportion of companies that went up versus those that went down.


In case you were wondering how to get this one going – here are the steps:

1. Transform the basic data into four series. The first series represents the smaller of original (two) data points. The third series represents the larger of the two. The second and the third series are simply static portions of the – each one either having a small values (say 50 in this case) or #n/a (nothing) depending on whether (of the two original points) the first one was greater than the second or not.


2. Create a stacked bar chart using the charting interface in Excel.


3. Make the first series disappear by removing the fill.

4. Create three shapes from the drawing toolbar – two arrows and a rectangle and fill them with appropriate colors.

5. Select and copy the first shape (ctrl + c). Now go to the chart and carefully select the second series in the chart – the one shown in Red color and paste (ctrl + v) the shape over it. This step will transform the bar into an arrow.

6. Repeat the same step for the second and the third series in the chart.

If you are interested, you can download the worksheet with all these chart here.

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Neil wrote:

    Love the vizualisation with the arrowheads to indicate direction of change. There really put a bit of shine into my Friday. One small point though, I’m not sure about the calculation in Series C. The way it works at the moment it gives the impresion of bigger change than really is. For example, graphically Intel looks to have dropped by 600 whereas the data show it as 165. Would it better to switch the formula to =MAX(E4;F4)-MIN(E4;F4) instead of =MAX(E4;F4)? Cheers.

  2. Jon Peltier wrote:

    Here’s a very simple alternative.

    Take your line chart, the first on on the page, format both series so they do not appear (no lines and no markers), then add up/down bars. You get one set of bars connecting a lower value to a new higher value, and another set connecting higher values to a later lower value. Color one green for up and the other red for down.

    And by the way, as Neil points out, your upper stacked series has to display the difference between the high and low values, nut the high value itself.

  3. Ajay wrote:

    Neil – My bad πŸ™‚ The changes have been made. You were absolutely correct. Thanks and welcome to da TaB !

    Jon – I played around with the two color bar idea when constructing the arrow one. One that it would not work without a descriptive legend and second (and I am not sure if that’s just how my mind works), I naturally tend to read the bars as growing from left to right (if on the positive side of the axis) and vice-versa. A slight variation of your idea is shown below:

    I agree that the this one IS an equally worthy alternative and does a far better job at providing a contrast between the two series – but then again the coloring (two distinct series) tends to overshadow the direction (the ones which went up / down).


  4. chrisham wrote:

    Ajay, that’s a very useful chart and a first time I have seen a representation like that. What is the logic for you to subract a 25 to get the first series?

  5. Ajay wrote:

    Chrisham – My string of follies continues unabated πŸ™ Another typo. While experimenting with the width of the arrow, the very first cell was left with a difference of 25 whilst it should have been 50.

    I have now crawled under a rock and sulking πŸ™‚

  6. chrisham wrote:

    Ah great! Poor me, I racked my head for a good 30 mins trying to figure that one! Well as a consolation I am not bad as I was thought I was afterall! Tks for the tutorial. Your doing a good job! Keep it up!

  7. Jim wrote:

    I’ve played with various cell settings to try this out with a series of very small numbers (between 0 and 1, such as 0.24, 0.47, and so on). I can’t seem to get the chart and gridlines to work with those numbers. Any guidance? Also had the same issue with the butterfly chart.

  8. Jim wrote:

    OK, got it after “one last try” for this chart. Still struggling with butterfly chart, though.

  9. Ajay wrote:

    Jim – Glad that worked for you πŸ˜€ But what about the butterfly – anything I can do πŸ™„ ?

  10. Jim wrote:

    Ajay – I’ll switch my butterfly chart question over to that page. Thanks, Jim

  11. Maciej wrote:

    There’s a problem with arrow chart, when I put negative values, eg change from 200 to (200), the series is not drawn in negative area of chart. Any idea?


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel