Peltier Technical Services, Inc.
- Custom Excel Solutions
Jon's Excel and Charting Pages
- Jon's Charts
- Tutorials
- Excel
- Site Index and Search
- RSS
|
Peltier Technical Services, Inc. Jon's Excel and Charting Pages |
|
Broken Y Axis.A frequently asked question is "How do I break an axis so that one or two very large values don't drown out the rest of my chart?" Two common responses are:
But these aren't always satisfactory: some readers won't understand the log scale, while the secondary axis disconnects the large value from the others and requires more work for the reader to figure out. Also creating a column chart with a secondary axis is a lot of extra work to make the series line up. Here is an alternative approach which displays an actual break in the axis and in any columns that extend above the break. In my original data, shown below, the two values highlighted in red are much greater than the rest of the numbers. The column chart below the table shows how these values overwhelm the rest of the points.
I've set up another table below the first in the worksheet. I put the following formula into cell B13, copied the cell, and pasted it into the entire range B13:D18. =IF(B3>8000000,B3-13000000,B3) I will put my break at 8,000,000 and continue my axis above the break as if the break is at 21,000,000. The difference in values at the break results in the 13,000,000 subtracted from values greater than 8,000,000 in the formula.
Here is how the adjusted data in A12:D18 looks in a clustered column chart. There is a definite separation between the outlying points and the rest of the data, but the smaller numbers are not obscured as above.
I set up a range for the custom Y axis in F1:H13 of the worksheet. The X values in column F are all zero, the desired axis tick labels are in column H, and in cell G2 I put this formula to compute the Y values for the axis ticks. Note its similarity to the formula in cell B13. =IF(H2>8000000,H2-13000000,H2)
I copied range F1:G13, selected the chart, and used Paste Special from the Edit menu. I used the Paste as New Series, Values in Column, Series Names in First Row, and Categories in First Column options. I got another column series, which I'll change in a minute.
I right-clicked on the new series, picked Chart Type from the popup menu, and picked XY Scatter, Markers Without Lines. The series now appears as red X's on the vertical axis, and Excel has added secondary X and Y axes for me.
I don't need the secondary Y axis, so I right clicked on the chart, selected Chart Options from the popup menu, clicked on the Axes tab, and unchecked the Secondary Y Axis checkbox. I also want to freeze the scale of the secondary X axis (top of the chart), so I double clicked it, clicked on the Scale tab, and unchecked the Auto box next to Minimum and Maximum, locking these parameters at 0 and 1, respectively.
To make the secondary X axis disappear, I double clicked it again, and on the Patterns tab, I selected None for Line, Major Ticks, Minor Ticks, and Labels. I double clicked the Y axis, and selected None for Major Ticks, Minor Ticks, and Labels, so just the line remained.
Double click on the Markers series, click on the Patterns tab, and change the markers to a black cross, size 6. Using Rob Bovey's Chart Labeler (a free addin from http://appspro.com), I added the labels in H2:H13 to the Markers series along the Y axis. I selected the Left position option; they didn't come out quite right, but 'll fix that in a minute.
To make room for the new axis labels, I had to select the plot area, and resize the top of it downwards and the left edge of it to the right.
Now it's time to add the breaks in the axis and in the columns. I will do that by adding a series with points where we want to show breaks, and then using a shape as a custom marker for this break (see Custom Chart Series Markers). I set up the following table in J1:O4.
The colored regions in this table are explained below:
I copied N1:O4, selected the chart, and used Paste Special from the Edit menu to add this data as a New Series, again using By Columns, Series Names in First Row, and Categories in First Column. The points are plotted here as red diamonds. If necessary, you may have to change this series into an XY Scatter series with markers and no lines, and you may have to rehide the secondary X axis.
Draw a Parallelogram Autoshape. The fill color of the shape should match the plot area fill color (white in this chart). It is shown here with a red border, although the real shape has no border. Use the Alt key to align it with the cell boundaries.
Draw two lines, using Alt key to align them with the sloped sides of the parallelogram.
Use white fill and no lines for the parallelogram. Select the parallelogram and both lines, and group them, using the Group command from the Draw submenu of the Drawing command bar.
Rotate the grouped shape once left or right and flip it once horizontally or vertically, using the appropriate commands on the Drawing > Draw > Rotate or Flip menu.
Finally, shrink it down to a convenient size. After shrinking it, you may have to ungroup the individual pieces and move each slightly. Don't try using the mouse to move them. Select one and use the arrow keys.
Use this shape as a custom data label for the Cut Off series. Copy the shape, select the Cut Off series in the chart, and Paste.
Select the legend, click on the Markers series text, and press the Delete key. Repeat with the Cut Off series legend text. Don't select the marker in the legend before pressing Delete, or you'll delete the series, not the legend entry.
That's it, a chart with a broken Y axis. You can get a slightly different look if you use no markers for the Markers series, along the Y axis, set the major spacing of the Y axis to 1,000,000, and use the Outside Major Tick Mark.
If you're a bit more artistic, you can make a shape more like a tilde than a slash:
Click here for a zipped worksheet that runs through this procedure.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Peltier Technical Services, Inc. Jon's Excel and Charting Pages Peltier Technical Services, Inc., Copyright © 2008. All rights reserved. |
Waterfall Chart
Box and Whisker
