Graph with trend line and up-and-down arrows showing variation

Question

I want create a graph that shows the trend of population (number of residents in the city) across the years. Ideally I would show the trend with a line, but also columns should be fine. The hard part is that I want to add arrows (upward - two colors - for imigrants and newborn, downward - two colors - for emigrants and dead) that show what caused the variation for one year to another. The problem is that these arrows should start from the population trend line (or top of the columns) and not from a "zero" axis. I cannot figure how to do that. Here below is a screenshot of what I mean: in it I managed to show incoming and outgoing population with the gray piled area. I piled it on a population piled area that I made with red border and no fill so that outgoing population (down, negative) was visible. Instead of using piled area graphs, I would like to show the population as a line and the additional o removed units as arrows.

Answer 1

Okay, I made up some data. You can't really show this clearly with a simple set of arrows, since up and down arrows will obscure each other. So I have made some data to offset these arrows, making a mini waterfall chart for each year. The arrows are made using custom error bars.

Here is the data, with the ranges color-coded to match the chart colors. There are five pairs of data columns plus four columns of yearly population changes due to the four factors: births, immigrants, deaths, and emigrants. The first pair of columns show the year and population. The formula in B3 is =B2+SUM(K2:N2), and this is copied down to row 13.

The next four column pairs use fractional years and partial data. The fractional years are calculated using these formulas and filled down to row 13:
C2: =A2+0.2
E2: =A2+0.4
G2: =A2+0.6
I2: =A2+0.8

The fractional data is calculated to give the starting point of each arrow, calculated with these formulas, and filled down to row 12:
D2: =B2
F2: =B2+K2
H2: =B2+SUM(K2:L2)
J2: =B2+SUM(K2:N2)

Here are the charts in progress.

Create the chart. Select A1:B13 and insert a line chart or XY scatter chart (first chart).

Add the intermediate data. Select and copy C2:D13, then select the chart and use paste special to add the data as a new series by columns, with categories in the first column and series name in the first row. Repeat in turn with E2:F13, G2:H13, and I2:J13. I have left only the markers and no connecting lines for these series in the second chart.

Add the error bars. For each of the second through fifth series, select the series, and use the plus icon next to the chart to add error bars. If you used an XY chart, delete the horizontal error bars from each series (a line chart only has vertical error bars). Select the vertical error bars for each series, and press Ctrl+1 to format them. Select plus for direction, no cap for end style (I have kept the end cap for illustration purposes), then select custom for amount, and click the Specify Value button. In the Positive Error Value box, select the appropriate shaded column for the series (K2:K13 for Births, etc.), and in the Negative Error Value box, type a zero (simply clearing the box will not enter either value). I have formatted the error bar lines to match the circular markers of each series. These error bars are shown in the third chart.

Format the arrows. For each set of error bars, use a thicker line (1.5 points, which is 2 pixels). Then choose an end arrow design and size. I've used the small triangular arrowheads in the fourth chart. I've also formatted each of these series to use no markers.

Alternative bar appearance. In the fifth chart below I have omitted the arrowheads and instead selected a thicker line (3 points or 4 pixels).