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.

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.

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:

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.

Change the area chart series to an area chart type, then assign the area chart tot he 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.

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.

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

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

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

Related Posts:

• Area Chart – Invert if Negative
• Stacked Area Chart Challenge
• Mind the Gap – Charting Empty Cells
• Category Axis Tricks for Line and Area Charts – 2
• Fill Below an XY Chart Series (XY-Area Combo Chart)
• Date Axis with Centered Years