1

I'm trying to make a decent-looking chart using a 3d representation of three different datasets (3 years, 12 monthly values).

If I try with an area chart, I have peaks/valleys at the single data-point, whereby I would prefer having steps, with values changing only between the months.

Normally I would use a "column chart" with zero gap, and that would work. But having different series in 3d, I need to use transparency for readability and now I get the following result (see pic).

Chart sample

Is there any chance to make the "0-gap" really invisible, or what other solution could I have without big workarounds?

Cheers, -guardiano

Improve this question
1
  • I can’t reproduce your image — perhaps because I’m using Excel 2013, perhaps because I’m not very familiar with the 3D controls — but I was able to come close with “Style 7”.   If you’re already using “Style 7”, I have nothing to tell you.   If you aren’t already using it, I suggest that you try it.
    – Scott - Слава Україні
    Commented Oct 10, 2018 at 4:19

1 Answer 1

Reset to default
2

No matter how "decent-looking" your 3D chart may be, it will still be hard to read (and some people will tell you that a decent-looking 3D chart is an oxymoron).

I think there may be a better way. I extracted the data from your chart, and made a column chart, just to see how the data looked (below left). Obviously it's the wrong chart for this data. But I spread the data out so I could make a step chart (below right). Not bad, but the series all obscure each other.

column chart and overlapping step chart

So What if we stagger the data, and plot each step chart in its own layer of the chart. I'll add 16 to the Orange data and 32 to the Gray data, and recreate the step chart. This might work. The lines do not block each other. You can also clearly see where changes in level coincide, which was hard to see in the 3D chart. You can't compare the exact values of each horizontal line segment between series, but you couldn't really tell compare those in the 3D chart, either.

non-overlapping step chart

Now for those month labels. Here's some more data, month abbreviations for data labels and with X and Y values for points that will display these labels.

Copy the second and third of these added columns, select the chart, go to the Home tab of the ribbon, click the Paste dropdown arrow, and use Paste Special on the popup menu to add the data as a new series, series name in first row, categories in first column. I've formatted this as a new series with no line and with gold markers (top left chart below).

Add data labels below this added series (top right chart).

Format the data labels to use values from cells, and select the first column of the added data, with the month abbreviations (bottom left chart).

Finally, clean it all up. Don't remove the original X axis labels, instead apply a custom number format of "" (yep, double quote double quote). This leaves the margin below the chart for the data labels. Use no markers for the added series. Click to select the legend, then again to select the legend entry for the added series, and press Delete. Probably something needs to be done with the Y axis labels too, but you get the idea.

finished up chart

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .