How to Simulate a “Broken axis” Value Axis

This type of chart is often used when a few data points greatly exceed the others. When plotted on a standard chart the other values are dwarfed. The chart actually consists of two charts. Both charts use the same data but have different value axis scaling. Creating this type of chart is a manual process and may require a fair amount of tweaking to position the charts.

broken axis 1

broken axis 2

Making the Chart

Step 1 – Create your chart.

  • Select cells A4 through C9.
  • Select the “Insert” tab.
  • In the “Charts Section” click the down arrow under “Column”, then select the first selection under 2-D bar for “Clustered Column” chart type.

Please see example in Excel Worksheet.

broken axis 3

Step 2: Delete the legend. Click the legend and press delete.

broken axis 4

Step 3: Format Y Axis.

  • Right click on the Y axis (vertical).
  • Select “Format Axis”.
  • Under “Axis Options”:
    • For “Minimum” select “Fixed” and type 0 in the box.
    • For “Maximum” select “Fixed” and type 2000 in the box.
    • For “Major Unit” select “Fixed” and type 400 in the box.
    • Click “Close”.

broken axis 5

Step 4. Delete Grid Lines: Click on horizontal grid lines and press delete.

broken axis 6

Step 5. Size Chart. Click the chart and drag the handles to the desired size.

Step 6. Make a copy of the chart. Press CTRL and D together to make a copy of the chart.

Step 7. Position the new chart over original. Click on the new chart and drag to location above original chart.

broken axis 7

Step 8. Format the red bars of new chart.

  • Click on red bars then click the first red bar associated with “Vigorous”.
  • Right click and select “Format Data Point”.
    • Under “Fill” click on “No Fill”.
    • Under “Border Color” click on “No Line”.
  • Click on the next red bar.
  • Repeat the above for all red bars EXCEPT the last red bar for “Inactive”.

broken axis 8

Step 9. Format the Blue bars of the new chart.

  • Click on blue bars then click the first blue bar associated with “Vigorous”.
  • Right click and select “Format Data Point”.
    • Under “Fill” click on “No Fill”.
    • Under “Border Color” click on “No Line”.
  • Click on the next blue bar.
  • Repeat the above for all blue bars EXCEPT the last blue bar for “Inactive”.

broken axis 9

Step 10: Format Y Axis of new chart.

  • Right click on the Y axis (vertical).
  • Select “Format Axis”.
  • Under “Axis Options”:
    • For “Minimum” select “Fixed” and type 5000 in the box.
    • For “Maximum” select “Fixed” and type 7400 in the box.
    • For “Major Unit” select “Fixed” and type 400 in the box.
    • Click “Close”.

broken axis 10

Step 11. Format X Axis of new chart.

  • Right click on the X axis.
  • Select “Format Axis”.
  • Under “Axis Labels” click the down arrow and select “None”.
  • Under “Line Color” select “No line”.
  • Click “Close”.

broken axis 11

Step 12. Position New Chart over Original Chart. Click on new chart and drag to a position over the original chart lining up the X axis.

broken axis 12

Step 13. Add the Free Form Shape. This shape indicates the column is not continuous between the two sections of the column for “Inactive”.

  • Click on the Insert tab.
  • Click on the Shapes icon then select Scribble.
  • Draw the shape between the two graph lines.
  • Then right click on the shape.
  • Select ‘Format Shape’.
  • For Line Color, select a dark grey.
  • For Line Style,
    • For Dash Type, click the down arrow and select a type.
    • For Width, enter 1.5.

broken axis 13

Step 14. Type title of chart in a cell above the 2 charts and center.

broken axis 14

broken axis 15

Share
This entry was posted in Editing Charts & Graphs, Excel Tips & Tricks, Graphs. Bookmark the permalink.

6 Responses to How to Simulate a “Broken axis” Value Axis

  1. Kathy says:

    I received the following comment in an email from Meic. It contains great ideas that should be shared.

    Hi Kathy

    Nice article, but one that raises some issues.

    Firstly, When I read the blog post earlier in the week I first thought of log scales, depending on the intended audience. You cover that in the article, though I wonder whether an audience assumed to be familiar with the significance of p-values might not also be assumed able to read log axes on charts.

    Secondly, though the broken axis makes its point, it’s still open to similar objections to shifting the origin in column/bar charts – that the info is coded by the size of the bars. If the bars were replaced by markers from a line chart or parallel coordinates plot that would I think get round the problem. (I think you’ve left a digit off the light/women without children cell – surely something I the 1300 region?)

    Thirdly, I would bring out the differences in absolute and percentage terms as a small multiple:

    small multiple example

    (I’m still constrained by Excel 2003, hence the unsatisfactory axis labelling).

    Fourthly, when I read the blog post, which I think does not state what the data series are, I naively assumed from the colour coding (blue and dark pink) that the comparison was between males and females, rather than between two groups of women. This seems an important point to me. It’s all too easy to overlook the implicit assumptions we all as readers bring to our interpretations. Not sure how to compensate for this, though.

    All the best

    Meic

  2. M.Iqbal says:

    It has solved my problem upto some extent “Thanks”

  3. aa says:

    The split graph solution solved my problem perfectly.
    Used it in Tableau.
    Thank you!

  4. rj says:

    thanks for solving my problem

  5. rj says:

    But how do I put back my legend

  6. Lee fun Jye says:

    Hi Kathy, came across for this article, do you have similar article but is use for excel 2013? I couldn’t find the fixed button in format axis for excel 2013.

Leave a Reply

Your email address will not be published. Required fields are marked *


2 + = five