Add an area to a scatter plot in Excel

2

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

danpo

Posted 2015-04-30T15:31:54.007

Reputation: 23

I think this http://peltiertech.com/fill-under-between-series-in-excel-chart/ might get you with some idea.

– vembutech – 2015-04-30T18:43:57.010

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 – 2015-04-30T20:19:26.203

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 – 2015-05-01T12:39:35.100

Answers

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

dav

Posted 2015-04-30T15:31:54.007

Reputation: 8 378

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 – 2015-05-04T08:22:43.640

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 – 2015-10-16T17:13:50.887

Asked: 2015-04-30T15:31:54.007

Viewed: 2 632 times

Active: 2015-05-01T13:09:57.610