Area Chart With Gap

In Mind the Gap – Charting Empty Cells I showed how the various Excel chart types treat blank cells, through various settings like Leave a Gap, Treat as Zero, and Interpolate.

In that article I wrote:

Area charts seem to offer both the zero and interpolation options, but in both cases, the chart plunges to zero without a corresponding data label. If we mess with the vertical axis, the chart still plunges exactly to zero in both cases.

Area Charts, With Empty Cells Treated as Zeros, or Supposedly Interpolated

A more suitable appearance for an area chart would be one that leaves a real gap, with vertical edges, as below. To get this I had to make a two-axis chart, with a hidden series on the primary axis to provide the A-B-C category axis labels, and an area chart on the secondary axis, with two points at the second category (Y=4 and zero) and two at the fourth category (Y=zero and 8). A date scale on the secondary category axis aligns these points above each other to produce the vertical-sided gap. This only took me three minutes to construct, but an average user might never quite get it.

Area  Charts, With Empty Cells Treated as Gaps, if We Had Our Way

How rude of me to hint at an approach, but not to share it!

For this chart appearance, you need a line chart series to provide the visible category axis, and an area chart on a hidden date scale axis to provide the visible area chart. Here is the data range:

Data for Area Chart With Gap Having Vertical Sides

Recall a date scale axis shows multiple Y values on the same category in a vertical line, which will provide the vertical sides to the gap in the area chart. Start by making a line chart with the red-shaded range. Copy the area chart data in the blue range, select the chart, and use Paste Special to add the data as a new series.

Line Chart, with Area Chart Data Added As Another Line

Change the area chart series to an area chart type, then assign the area chart tot he secondary axis.

Area Chart Series Changed to Area Type and Moved to Secondary Axis

Excel probably only provided a secondary Y axis, so add a secondary X axis, using a date scale rather than a category scale. Hide this secondary axis by using no line, no axis tick marks, and no axis tick labels.

Secondary Date Scale Category Axis Added, Then Hidden

Format the secondary Y axis so that the secondary X axis does not cross at the maximum (it will default to zero), then delete the secondary Y axis.

Secondary X Axis Moved to Y=Zero, Secondary Y Axis Deleted

Hide the line chart series by formatting it to have no lines and no markers. Add data labels to the area chart series.

Line Chart Series Hidden, Data labels Added to Area Chart Series

Use a number format like 0;;; for the area chart data labels, to hide labels for zero values.

Data Label Number format Changed to Hide Zero Labels

As I said, it’s easy, if you know how.

 

Peltier Tech Charts for Excel

Comments

  1. Jon, it may be slightly simpler to
    1. replace all blanks with (say) -999999, using a formula
    2. format the Y axis to have a minimum of 0

  2. Dermot –

    Yeah, that would work. I think I would always know about the vast reaches of false data down to -999999 though. Me and my OCD.

  3. I was attempting to recreate what you have done. But I am unsure how to add a secondary x-axis, excel 2007 can be tricky sometimes!. Could you please explain how to add the second x-axis?

  4. Barb –

    Select the chart, then on the middle Chart Tools tab, click Axes, and select the variation you need to add or remove.

  5. This is very helpful Jon, thank you. I have an area chart that needs to stack to 100% (http://postimage.org/image/2xga16o6c/), with the years where there are missing data dropping straight to zero like in your post. But I couldn’t get your technique to work with this type of chart – any suggestions?

    Thank you very much,

    Isik

  6. It works the same. In this tutorial, it shows how to convert your horizontal axis to a data-scale axis. It’s okay to still just use years. Excel treats them as days, but format as General or Number with no decimals, and it will look like years.

    Now, for the left vertical edge of the gap each series needs two points for that year, the first with the pre-gap values, the next with zeros. For the right vertical edge of the gap, each series also needs two points, the first for zero, the second for post-gap values.

  7. Perfect! Thank you very much.

  8. 3 years later and this post is still helping people. I spent hours trying to get rid of the stupid 0s in my graph for what appeared to be blank cells. I actually got this working perfectly, and natively, in Excel 2013 and then opened it in Excel 2010 only to be greeted with [CELL RANGE] wherever a 0 should have been. Custom formatting using 0;;; solved my issue! Thank you thank you thank you.

Trackbacks

  1. […] The protocol to produce an area chart with a gap that has vertical sides is given in Area Chart With Gap » Peltier Tech Blog […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0