I do a lot of work with Excel with statistics in various forms. Quite often I have to present data to customers in diagrams. In order to see small differences it is often necessary to cut a part of the diagram.
For example I may have trend data (in index form) for employment in a number of regions that varies ±10 percent. If I include the entire scale on the vertical axis (from 0 to 150 ) the differences will not be visible. If I however cut the scale on the vertical axis and only show the area from perhaps 80 to 150 the differences between the curves will be clear. The problem with doing this is that it can give the wrong impression that some things are twice as big when they in fact only differ by 10-20 percent.
The honest thing to do when not showing the entire scale is to zig-zag the part you cut out. But how do I do it in Excel? Is there some function that does this for me that I haven´t been able to find?
True enough. If you present the information in a Bar Chart, the area of the bars should be in ratio to the values (height). If you cut part of the bars out, you lose that ratio.
What to do, what to do…
Can you use a different format for showing them the numbers? Perhaps something other than a bar chart?
b) Can you use numbers instead of a graphical display?
iii) How about if you plot the points on the “trimmed” Y-axis and plot your +/- standard error around those points a la Box and Whisker plot (or whatever they might be called over yonder where you are). This would show the relative positions of the charted values and also show ranges to show “statistical significance” of the differences.
the next) Can you change the metric the values are measured on? (Don’t forget to rescale the SEMs also!!!)
last) If you have such a small difference between values, perhaps they aren’t different after all. It would not, therefore, be wise to adjust the scaling such that differences are exaggerated.
I suggest looking at an image based solition, rather than finding graphing function solution. Think of it as a photo layout.
Chart two graphs, one the full-scale, one the narrower range. Paste them into Powerpoint or Word (as pictures), or better yet, any Adobe image program. Crop them or resize them as necessary, and slap one over the other.
Your scales should be drawn and legend should be pasted in from Excel, but you might find it easier to create titles and explanatory text in the second program.
You could spend days trying to figure out whether Excel is capable of this on its own.