Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I would like to create a chart that shows a value and how it has changed, like this one in Excel.

The importatnt thing (which I am not able to achive) is that there are two numers associated with each country:

  1. Current value (top of an arrow)
  2. Change since 1985 (arrow length)

Is it possible to achieve something like this in Excel? The result does not have to look exactly like this, but has to show those two numbers in one graph and be easy to understand.

Thank you!

(I am using Excel 2013 if it helps)

share|improve this question

2 Answers 2

up vote 1 down vote accepted

I got something a little bit like what you asked for (in Excel 2007):

                enter image description here

Steps:

  • Enter your data as shown in Columns A-C.
  • Set D2 to =C2-B2 and drag/fill down.
  • Select Columns A and B, and insert a Scatter chart.
  • Click anywhere in the chart.  Go to the “Layout” tab, “Labels” panel, and click on “Data Labels”.  A menu will pop up.  Select “More Data Label Options…” (in Excel 2007, this is the last option in the menu).
  • A “Format Data Labels” dialog box will appear.  Specify that “Label Contains” “X Value” (and not “Y Value”) and “Label Position” is “Below”.  Click “Close”.
  • Still on the “Layout” tab, in the “Analysis” panel, click on “Error Bars”.  A menu will pop up.  Select “More Error Bars Options…” (in Excel 2007, this is the last option in the menu).
  • A “Format Error Bars” dialog box will appear.  Set the “End Style” to “No Cap”.  Under “Error Amount”, select “Custom” and click on “Specify Value”.
  • A “Custom Error Bars” dialog box will appear.  Under “Positive Error Value”, specify Column D.  The easiest way to do this is to click the little grid icon at the end of the field, and then select cells D2 through the D cell in the last row that has data.   Under “Negative Error Value”,  change ={1}  to ={0}.  Click on “OK”.  Close the “Format Error Bars” dialog box.
  • When I do this, I get the vertical error bars that I showed above, and also horizontal error bars.  If you get them too, click on one of them, right-click, and select “Format Error Bars” from the pop-up menu.  A “Format Error Bars” dialog box will appear, but this time it will be for the horizontal error bars.  Set the “Error Amount” to a “Fixed Value” of “0”.  Click “Close”.

I assume you know how to do things like change the chart title, and that you can figure out how to do the negative changes.  If you figure out how to get the country names below the X axis, please post that information here.

share|improve this answer
    
For the record, I had finished writing my answer before I saw the earlier one. –  Scott Mar 2 '13 at 3:39
    
Thank you very much for your answer. You were slower, but you provided more details, so I am going to accept your answer. :) –  jakubka Mar 2 '13 at 3:45
    
No worries, Scott :-)) –  teylyn Mar 2 '13 at 9:00
    
Re country names as x axis labels in XY chart: add a data series where all y values are 0, add labels to the data points, change labels to show a cell, hide axis labels. –  teylyn Mar 2 '13 at 22:33
    
@teylyn: Great trick! Thanks! –  Scott Mar 4 '13 at 18:18

Yes, you can do that with Error Bars. Chart one of the number ranges as a Line or XY chart, then select the series and add vertical Error Bars. (note: when you add error bars, Excel will add horizontal and vertical. You then need to delete the horizontal ones) You can then specify a cell range as the source for the Error Bar values by selecting "Custom" and then pointing to a range. The look and feel can be achieved by formatting the error bars with end arrows.

This screenshot was taken with XL 2013, but the same applies for 2010. The error bar values are in column D.

enter image description here

share|improve this answer
    
Thank you very much. It works perfectly and looks good. –  jakubka Mar 2 '13 at 3:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.