Create an arrow chart

I’ve already posted how to make a bridge / waterfall graph where you can show with up and down bars the positive and negative impact of each category against the final sum.  However, what if you simply wanted to show the progress from one year to the next (both positive and negative) of completely different data sets that have no accumulate effect?  Perhaps using up and down arrows?  You can download the sample excel sheet herearrows

Step 1: Set up your data table

Imagine you have the following data table:initial table

As you can see in the table, the first value has fallen from 2011 to 2012, the other 4 have increased.  What we want however is to create several new columns as shown below:new columns

In essence what we will eventually create, is a stacked bar chart.  We’ll use custom shapes to create the arrow heads, and columns to create the bodies of the arrows.  In order for the arrows to float, we will also need a Blank column.

Decide the default size of the arrow head.  I have chosen 0.05, but you can alter this to meet your needs. I have placed this value in cell J2.

In the first row of the “Blank” column type in the formula: =MIN(B2,C2)-IF(B2=C2,$J$2/2,0)  Basically this formula will find the minimum value of the “2011” and “2012” columns, then if the two are identical, remove enough space for the arrow head or if they are different remove nothing.

In the first row of the “Down head” column type in the formula: =IF(B2>C2,IF(B2-C2>2*$J$2,$J$2,(B2-C2)/2),IF(B2=C2,$J$2/2,0))  Again we have an IF statement.  This checks to see if the difference between the values in the “2011” and “2012” column is positive or negative.  If positive, it will return a value of zero, if negative it will calculate the size of the DOWN arrow head series (a value between 0 and 0.05 – the maximum size of the arrow head).

In the first row of the “Down arrow” column type in the formula: =IF(B2>C2,B2-C2-E2,0)  This will create the series we will use to show the down arrow body.

In the first row of the “Up arrow” column type in the formula: =IF(C2>B2,C2-B2-H2,0)  This will create the series we will use to show the up arrow body.

In the first row of the “Up head” column type in the formula: =IF(C2>B2,IF(C2-B2>2*$J$2,$J$2,(C2-B2)/2),IF(C2=B2,$J$2/2,0))  Again we have an IF statement.  This checks to see if the difference between the values in the “2011” and “2012” column is positive or negative.  If negative, it will return a value of zero, if positive it will calculate the size of the UP arrow head series (a value between 0 and 0.05 – the maximum size of the arrow head).

first row calculations

Once you’ve added the formula for the first row, copy and paste the first row to fill the other remaining rows.

Step 2: Create a stacked bar chart.

On the ribbon, click on Insert > Charts > Column > Stacked Columnstacked column

Right click on the chart area that appears and click on Select data

Add the five columns, (Blank, Down head, Down arrow, Up arrow and Up head).  Then add the horizontal axis labels and press the OK button.select data source

A chart similar to the one below should appear.chart 1

Step 3: Format the chart

First, delete the legend, you will not need it.

Then left click on the blank series and press Ctrl+1.  Change the fill to none and press the close button.format data series

You should now have only the arrow and arrow head series visible on the graph.chart 2

Next we need to create an arrow shape.  First create a triangle for the head (click on insert > shapes > isosceles triangle), and then a rectangle for the body (click on insert > shapes > rectangle).isosceles triangle

Format the triangle and rectangle as you wish.  For example I have chosen a grey fill with no border for both.

Then select the triangle and press Ctrl+C.  Then select the down head series (shown red in the graph above) and press Ctrl+V.  The graph should update automatically:chart 3

Repeat the process for the rectangle and the down arrow series.chart 4

Then create a duplicate of the rectangle and triangle and format them as you would wish the up arrows to appear.  This time I chose a blue fill with no border.  Use Ctrl+C and Ctrl+V again on the up head and up arrow series accordingly.

Then format the chart as you wish.chart 5

You can download the sample excel sheet here.  Enjoy

Alesandra Blakeston

Advertisements

2 thoughts on “Create an arrow chart

  1. I have been looking for a long time to find out how to do this,with a set of instructions I can understand,now lets put it to the test.Thank you for posting this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s