Create arrow variance chart In Excel

In Excel, the arrow variance or different chart is a type of clustered column or bar chart with up or down arrow to indicate the increase or decrease percentage of the two sets of data.

For example, if you want to display the sale variance in compare of previous year, you can use green and red arrow to display the percentage change as below screenshot shown. This article, I will talk about how to create this complex type of chart in Excel.

Create arrow variance or difference chart in Excel

For creating arrow variance chart to compare the difference percentage change of year 2020 based on year 2019, first, you should insert some helper columns to calculate the basic data as below screenshot shown:

First, create the helper columns data

1. In cell D2 that beside the original data, type the following formula, and then drag the formula to cell D10, see screenshot:

=MAX(B2:C2)

2. In cell E2, please enter the following formula, and then copy the formula to cell E10, see screenshot:

=D2

3. Go on typing the below formula into cell F2, and copy the formula to cell F10, see screenshot:

=IF(C2>B2,C2-B2,NA())

4. Then, in cell G2, enter the below formula, and drag it to cell G10 to fill the formula, see screenshot:

=IF(C2<B2,B2-C2,NA())

Tips:The above four helper columns are used to insert the column bars with error bars, which will be formatted as the up and down arrows.

5. And then, please enter the following formula into cell H2, and drag the formula to cell H10, see screenshot:

=IF(ISERROR(F2),"","+" & TEXT(F2/B2,"0%"))

6. Then, go on entering the below formula into cell I2, and then drag the fill handle down to cell I10, see screenshot:

=IF(ISERROR(G2),"","-"&TEXT(G2/B2,"0%"))

Tips: The helper columns H and I are used to add the up and down data labels for the chart.
Second, create arrow variance chart based on the helper columns data

7. After creating the helper data, then, select the data in column A, B, C, D, E, and then, click Insert > Insert Column or Bar Chart > Clustered Column, see screenshot:

8. And a clustered column chart is inserted as below screenshot shown:

9. Then, click to select the chart, and then choose Select Data from the context menu, see screenshot:

10. In the popped out Select Data Source dialog box, in the Legend Entries (Series) list box, select Max Left series, and then click the up arrow button to move this data series to top, see below demo:

11. Then, click OK button to close the dialog, and then right click the Max Left data series, click Chart Elements button to expand the list box, in the list box, check Error Bars, see screenshot:

12. Go on right clicking the Max Right data series, and then click Chart Elements button to expand the list box, in the list box, check Error Bars, see screenshot:

13. And then, right click the Max Left Y Error Bars, and choose Format Error Bars from the context menu, see screenshot:

14. Then, in the opened Format Error Bars pane, under the Error Bar Options tab, in the Vertical Error Bar section, please configure the following operations:

  • Select Minus option under the Direction section;
  • Then, select No Cap from the End Style section;
  • And then, select Custom from the Error Amount section, then click Specify Value button, in the popped out Custom Error Bars dialog box, keep blank in the Positive Error Value text box, and select the data from the Up Arrow column in the Negative Error Value text box, and then click OK to close the dialog.

15. Keep the Format Error Bars opening, and then click to select the Max Right Y Error Bars from the chart, under the Error Bar Options tab, in the Vertical Error Bar section, please configure the following operations:

  • Select Minus option under the Direction section;
  • Then, select No Cap from the End Style section;
  • And then, select Custom from the Error Amount section, then click Specify Value button, in the popped out Custom Error Bars dialog box, keep blank in the Positive Error Value text box, and select the data from the Down Arrow column in the Negative Error Value text box, and then click OK to close the dialog.

16. Then, you should format the Max Left and Max Right data series bars invisible, right click the Max Left data bar, and choose Format Data Series option, in the opened Format Data Series pane, under the Fill & Line tab, select No fill from the Fill section, see screenshot:

17. Do the same operation for hiding the Max Right data bars, see screenshot:

18. After hiding the Max Left and Max Right data bars, now, right click the Max Left Y Error Bars, and then choose Format Error Bars from the context menu, in the Format Error Bars pane, under the Fill & Line tab, please do the following operations:

  • Select Solid line option and then specify a color you like for the up arrows from the Color drop down;
  • Then adjust the line width to 1.25 pt as you need from the Width section;
  • Then, select Arrow icon from the Begin Arrow type drop down list.

19. Keep the Format Error Bars pane still opening, then click to select the Max Right Y Error Bars, in the Format Error Bars pane, under the Fill & Line tab, please do the following operations:

  • Select Solid line option and then specify anther color you like for the down arrows from the Color drop down;
  • Then adjust the line width to 1.25 pt as you need from the Width section;
  • Then, select Arrow icon from the End Arrow type drop down list.

20. And you will get the chart as below screenshot shown:

21. Next, please right click the Max Left data bar, and choose Format Data Series from the context menu, in the opened Format Data Series pane, under the Series Options tab, select Secondary Axis option from the Plot Series On section, then click to select the Max Right data bar, do the same operation as below demo shown:

22. Then, right click any bar in the chart, and choose Format Data Series, in the Format Data Series pane, under the Series Options tab, enter 0% and 100% into the Series Overlap and Gap Width boxes separately.

And then, in the Format Data Series pane, go on selecting Series “Year 2019” or Series “Year 2020” from the Series Options drop down list, and enter 0% and 100% into the Series Overlap and Gap Width boxes separately as well. See below demo:

23. This step, you can add the data labels for the chart, click to select the Max Left data series, and then click Chart Elements, in the expanded Chart Elements list box, check Data Labels option, see screenshot:

24. Then, right click the data labels, and choose Format Data Labels, see screenshot:

25. In the opened Format Data Labels pane, under the Label Options tab, check Value From Cells option, and in the popped out Data Labels Range dialog box, select the data cells from the Up Data Label column, then click OK button, see screenshot:

26. Still in the Format Data Labels pane, uncheck Value and Show Leader Lines options, and you will get the below chart, see screenshot:

27. Then, go on adding the down data labels, right click the Max Right data bar, and click Chart Elements to expand the list box, in the Chart Elements list box, check Data Labels option, see screenshot:

28. Then, right click the new added data labels, and choose Format Data Labels, in the opened Format Data Labels pane, under the Label Options tab, check Value From Cells option, and in the popped out Data Labels Range dialog box, select the data cells from the Down Data Label column, then click OK button, see screenshot:

29. Still in the Format Data Labels pane, uncheck Value and Show Leader Lines options, and the percentage change data labels have been added into the chart, see screenshot:

30. At last, delete the unneeded chart elements as you need, such as delete the gridlines, chart tiles, secondary axis, and you will get the complete chart as below screenshot shown:

Create arrow variance or difference chart in Excel with a handy feature

If you are headache with the above complex steps, here, I will introduce an easy tool-Kutools for Excel, with its Difference Arrow Chart feature, you can create a bar or column difference arrow chart as you need with only several clicks.  Click to download Kutools for Excel for free trial!

Download Arrow Variance or Difference Chart sample file

Video: Create arrow variance or difference chart in Excel

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.