On her Effective Charts blog, Naomi Robbins presents the Arrow Chart as an improvement over the use of two pie charts to show how a set of values changes between one point in time and another. Here is an example of an arrow chart, showing anti-terrorism funding levels for 2002 at the base of the arrows and for 2003 at the tips of the arrowheads. While showing the values for each year, the arrows strongly emphasize the change in the values.
Here is the typical arrangement of dual pie charts. The reader is forced to spend a great deal of time moving back and forth between the pie on the left, the pie on the right, and the legend in between. Despite this investment of time and effort, the huge growth of the three largest components of funding is not obvious.
Some might “improve” on this arrangement by scaling the diameter (or rather, the area) of the pies to reflect the sum of each year’s funding. But I think this merely adds confusion. I don’t think the pies improve at all on a simple table of the funding levels.
Making a Simple Arrow Chart
An arrow chart is basically a floating bar chart, but the bars are replaced by arrows to emphasize the direction and amount of change. The data below shows the additional columns used to make a floating bar chart. The column Blank indicates where the floating bars should start, and it is simply the minimum of the 2002 and 2003 values for that row. The decrease column shows the difference between 2002 and 2003 if the value decreases in 2003, otherwise it shows zero. The increase column shows the difference between 2002 and 2003 if the value increases in 2003, otherwise it shows zero. I’ve applied some simple conditional formatting to the increase and decrease column for my own benefit while constructing the chart.
In formula lingo:
D3: =MIN(B3,C3) E3: =MAX(0,B3-C3) F3: =MAX(0,C3-B3)
Fill the formulas down as appropriate.
To make the chart, select A2:A8, hold Ctrl while selecting D2:F8 (so both areas are highlighted), and insert a stacked bar chart.
To make it into a floating bar chart, simply format the “Blank” series to be blank, that is, no border and no fill. This chart shows the 2002 and 2003 values well enough, but the arrows really emphasize the direction of change.
Orange and blue are better choices for plus and minus (or good and bad) than red and green, because red and green are indistinguishable by those who have the most common color vision deficiency.
Create one arrow by choosing Insert tab > Shapes, selecting a suitable arrow shape, and drawing the arrow in the worksheet (not in the chart). Format this arrow: I made it with no border and a blue fill. Create the other arrow by duplicating the first (select it and use Ctrl-D, or just copy and paste it), flipping it horizontally, and changing the fill to orange.
To get these arrows into the chart, copy the blue arrow, select the “Increase” series of bars, and use Ctrl-V to paste the shape into the bar. Repeat with the orange arrow and the “Decrease” series.
This is a nifty trick to embellish your charts. If you copy a shape, select a bar or area, and paste, the pasted shape fills the bar or area. In a bar (or column) you can set the repeat of the shape, so for example, one apple in a bar chart represents one bushel of apples in the data set. If you copy a shape, select a marker, and paste, the shape takes the place of the marker in a line or XY chart. If you do this with a bubble in a bubble chart, the shape fills the bubble, changing size as the bubble changes size.
If you select an entire series, the pasting is applied to all points in the series. If you select just one point in a series (which takes two single clicks), the pasting is applied to just the selected point.
Be careful with this trick, as it can be overused and abused, resulting in an unreadable chart.
The arrow chart above shows the funding levels and changes in funding, but it can just as easily show percentages.
Making a Compound Arrow Chart
The arrow charts above are pretty effective, but we can improve on them. Notice how the arrowheads are all different. When the arrows are pasted into the bars, they stretch to fill the bars. This means the longer bars have longer arrowheads, while the shorter bars have scrunched arrowheads. We can’t do much about the very short bars, but we can make arrowheads the same size on all but the shortest bars. All it takes is formulas.
Here is the adjusted data. Instead of two floating bars (increasing and decreasing), we’ll use four. When the value decreases we’ll show Left Arrowhead and Left Arrow, and when the value increases we’ll show Right Arrow and Right Arrowhead.
Okay, here are the formulas:
J3: Default Arrowhead Length (in same units as axis) D3: =MIN(B3,C3)-IF(B3=C3,$J$3/2,0) E3: =IF(B3>C3,IF(B3-C3>2*$J$3,$J$3,(B3-C3)/2),IF(B3=C3,$J$3/2,0)) H3: =IF(C3>B3,IF(C3-B3>2*$J$3,$J$3,(C3-B3)/2),IF(C3=B3,$J$3/2,0)) F3: =IF(B3>C3,B3-C3-E3,0) G3: =IF(C3>B3,C3-B3-H3,0)
E3 and H3 set the arrowhead size. The arrowhead will never be more than half the entire arrow length, so if the total floating bar is more than twice the default arrowhead length in J3, we’ll use the default arrowhead size. Otherwise we’ll use half the total arrow length for the arrowhead. You can fine tune your arrows by changing the default arrowhead length in J3 after you’ve made the chart.
F3 and G3 merely subtract the arrowhead size from the entire bar length to get the length of the shaft of the arrow.
The $J$3/2 in D3, E3, and F3 was added to provide a small down and up arrowhead in case a category’s value was unchanged. See comments for a short discussion.
Again, fill the formulas down as appropriate.
We also need to do some magic with the arrows. Here is a freshly drawn arrow, with no border and a blue fill:
There are two “handles”, those yellow diamonds. Dragging them with the mouse edits the shape of the arrow. The handle on the left end of the arrow modifies the width of the arrow’s shaft, while the handle at the edge of the arrowhead changes the relative size of the arrowhead.
We want the shaft to be all arrow and no arrowhead, so drag the arrowhead handle all the way to the right. This gives us a shaft which is only part of the width of the bar it will fill.
We’ll use a simple triangle for the arrowhead. Draw an isoceles triangle and rotate it to the right.
Then copy the two shapes, flip them horizontally, and change the fill to orange.
Select A2:A8, and hold Ctrl while selecting D2:H8, so both areas are highlighted. Insert a stacked bar chart.
Make the “Blank” series invisible by formatting it with no border and no fill. I’ve also color coded the increasing (blue) and decreasing (orange) bars, with the arrowheads a darker fill than the shafts. Don’t bother doing this on your chart, it’s only for this explanation.
Copy the blue arrowhead, select the “Right Arrowhead” series, and paste. Copy the blue arrow shaft, select the “Right Arrow” series, and paste. The blue increasing arrows are done.
Copy the orange arrowhead, select the “Left Arrowhead” series, and paste. Copy the orange arrow shaft, select the “Left Arrow” series, and paste. Now all of the arrows are done.
Alternatives to an Arrow Chart
The arrow chart is a great way to show how a set of values has changed from one time to another, while strongly emphasizing the direction and magnitude of the change.
Depending on the needs of your analysis, there are other ways to present the same data. It may not hurt to have some alternative charts on hand.
We’ve already seen the dual (or dueling) pie charts, which really isn’t better than any of the alternatives.
Here is a dot plot that shows the same values as the arrow chart. While the arrow chart emphasizes the changes, the dot plot emphasizes the values at the endpoints, without obscuring the changes.
Because of extreme foreshortening of the arrows in the arrow chart, or because of overlapping of points in the dot plot, it may be difficult to resolve categories which are relatively unchanged. In a slope graph (below) all values are shown at both time points. The change is indicated not just by the position of the endpoints along the value (vertical) axis, but also by the slope of the connecting line. Values which are relatively unchanged are represented by nearly horizontal lines, but neither endpoint is obscured. Of course, lines may converge at the endpoints, as the pairs HHS & DHS and NSF & Other do in 2002.
As always, you must consider your audience, your data, and your message when deciding which chart type(s) to use.