3

I'm desperately trying to layer an area chart on top of a a scatter plot in Excel 2013.

The scatter plot has been successfully used to visualize the deviation from the mean of different Key Performance Indicators (KPIs) to see how several business entities perform relative to each other. Basically, my x-axis is a percentage value, and my y-axis is a list of KPIs (or for the sake of the chart, some numbers from 1 to n). Works perfectly fine.

What I would like to do now is to add a "corridor" to see the minimum and maximum values. I can actually add a maximum and minimum line, but have failed to change it into an area.

Here's a screenshot and a demo file so you can see what I mean:

enter image description here

Improve this question
3
  • 1
    I think this peltiertech.com/fill-under-between-series-in-excel-chart might get you with some idea.
    – BDRSuite
    Commented Apr 30, 2015 at 18:43
  • Try creating an area chart then adding another series for your scatter, then change that series to a scatter chart type (leaving the area series in place).
    – dav
    Commented Apr 30, 2015 at 20:19
  • Just re-read your question and saw that you want it vertical. Since Excel doesn't easily do that, you can do a quick (and easy) cheat. Use the camera tool to take a picture of your chart and then orient the picture vertically. Then format your axis and labels on the chart for the perceived orientation and display the resulting camera picture instead of the original chart.
    – dav
    Commented May 1, 2015 at 12:39

1 Answer 1

Reset to default
1

As I mentioned in the comments above, you could do a quick "cheat" and use the Camera Tool to create a picture that could then be rotated to your desired orientation (i.e. vertical, although it could be any angle).

KPI example chart with camera

But, in looking at what you're trying to accomplish-showing your KPI's within a range of min/max-I suggest choosing a slightly different (and easier) chart. Consider using a Stacked Bar Chart. You can use the stacked bars to create your corridor (without needing to resort to the camera trick). It also is a slightly better representation of your data. Assuming your KPI's aren't chronological values (i.e. the same value repeated at some set interval and charted over time), your data is categorical. And line charts aren't good for categorical data because the line implies that your values follow that path-which isn't necessarily true for categorical data. So a stacked bar chart is easier and a better representation of your data.

KPI bar charts

Improve this answer
2
  • Thanks dav, the camera tool works like a charm! I also agree that a "semantic differential" doesn't make a lot of sense in this very case. However, we do use it with several lines to compare different business units' performances in which case those "lines" are a neat visual tool to distinguish between and visually track those units. While your suggestion for stacked bars does indeed make a lot of sense for the data I was presenting to you in the question, in a broader context it's nice to keep this very visualization for the sake of consistency throughout the report.
    – danpo
    Commented May 4, 2015 at 8:22
  • I wish I could upvote just the second part of @dav's answer. The camera tool is great, except with it gets clunky. It sometimes represents lines as much darker and thicker than they are in the original, and if you have more than a few of them in your worksheet, things get slow. The stacked bar approach is one I've used widely.
    – Jon Peltier
    Commented Oct 16, 2015 at 17:13

You must log in to answer this question.

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