Skip to main content

Create arrow variance chart In Excel

Author: Xiaoyang Last Modified: 2024-11-06

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.

arrow variance 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:

steps of creating arrow variance chart in Excel

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)

steps of creating arrow variance chart in Excel

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

=D2

steps of creating arrow variance chart in Excel

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())

steps of creating arrow variance chart in Excel

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())

steps of creating arrow variance chart in Excel

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%"))

steps of creating arrow variance chart in Excel

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%"))

steps of creating arrow variance chart in Excel

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:

steps of creating arrow variance chart in Excel

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

steps of creating arrow variance chart in Excel

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

steps of creating arrow variance chart in Excel

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:

steps of creating arrow variance chart in Excel

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:

steps of creating arrow variance chart in Excel

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

steps of creating arrow variance chart in Excel

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.
steps of creating arrow variance chart in Excel steps of creating arrow variance chart in Excel steps of creating arrow variance chart in Excel

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.
steps of creating arrow variance chart in Excel steps of creating arrow variance chart in Excel steps of creating arrow variance chart in Excel

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:

steps of creating arrow variance chart in Excel

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

steps of creating arrow variance chart in Excel

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.

steps of creating arrow variance chart in Excel

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.

steps of creating arrow variance chart in Excel

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

steps of creating arrow variance chart in Excel

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:

steps of creating arrow variance chart in Excel

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

steps of creating arrow variance chart in Excel

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:

steps of creating arrow variance chart in Excel steps of creating arrow variance chart in Excel steps of creating arrow variance chart in Excel

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

steps of creating arrow variance chart in Excel

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:

steps of creating arrow variance chart in Excel

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:

steps of creating arrow variance chart in Excel steps of creating arrow variance chart in Excel steps of creating arrow variance chart in Excel

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:

steps of creating arrow variance chart in Excel

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:

steps of creating arrow variance chart in Excel


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

steps of creating arrow variance chart in Excel


Video: Create arrow variance or difference chart in Excel


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...


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, Edge and Firefox.