Views > Excel charts > How to create an arrow chart in excel

Sometimes the primary analytical task at hand is to understand what happened between two points in time. For example, did company expenses increase, decrease or stay unchanged between this year and two years ago?

There are different ways to present change between two points in time. Slopegraphs, Range Bar Graphs and Deviation Graphs (both in magnitude and percentage) are three different approaches that may be used to show that change. To each its advantages.

Stephen Few wrote a thorough paper explaining these three different approaches along with rating the strengths of each individual approach.

The top chart below is an example from Edward Tufte. The same dataset is shown on the bottom below using different approaches.

In this blog post I'm going to show you how to build an arrow chart in Excel. Basically, an Arrow chart is a Range Bar Chart but with an arrow at one end showing the direction of change as per the graph below. Here we see the operating expenses of a company in two years, 2018 and 2020, and an arrow showing the direction of change. Red arrow means the expense item has increased over the period, while a blue arrow indicates a decrease in operating expenses. The categories are ranked from highest to lowest by the magnitude of change.

In a previous blog post I showed you how to create the following Dumbbell Chart. So I’m going to start off from where we left it and transform the Dumbbell Chart into an Arrow Chart.

1. The first thing to do is to delete the excess number of rows from the data source (we have 51 categories while we only need 11 expense categories). To do that, select the range B15:G54, right-click over the selected range on the worksheet and then click Delete on the shortcut menu.
2. In the Delete dialog box, select the Shift cells up option and press the Ok button to close the Delete dialog box.
The data source and the chart should look like this:

HOW TO CREATE AN ARROW CHART IN EXCEL

3. Next, lets add the new data into the respective cells, adjust the chart title and data source, resize the chart and reset the boundaries of the horizontal scales. The chart should look like this:

4- Next, lets hide the data markers for the 2008 and 2020 data series. To do that, click one of the data markers of the 2018 data series in the chart.
5. On the Format tab, in the Current Selection group, click Format Selection.
6. Click the Fill & Line icon and then, under Marker, make sure that None is selected under Marker Options.
7. Repeat steps 4-6 above for the 2020 data series and the chart should look like this:

8. Select the chart on the worksheet. Then, on the Format tab, in the Current Selection group, select the Series "2018" X Error Bars from the drop down list and then click Format Selection.
9. In the Format Error Bars pane, make sure that Plus is selected under Direction.
10. Next, click the Fill & Line icon, then under Line, change Color to red, Width to 4.5, and End Arrow type and End Arrow size to an option of your liking. The chart should look like this:  

11. Repeat steps 8-10 for Series "2020" X Error Bars, while choosing Blue for color and setting the Begin Arrow type to an option of your liking. The chart should look like this:

12. Finally, lets delete the primary and secondary horizontal axis, the gridlines and the plot area outline. Add data labels, adjust their number formatting (Custom #,##0,"k") and manually align some of the Label Positions to Left and Right to match the arrow direction. The chart should look like this: