# How to Create an XY Scatter Plot with Shaded Area Below a Curve in Excel

1. ## How to Create an XY Scatter Plot with Shaded Area Below a Curve in Excel

Hi everyone,

I need help creating an XY scatter plot in Excel where the area under the curve is shaded whenever the actual performance is below the planned performance. Here is my dataset:
Date | Planned Perf | Actual Perf
01-01-2024 08:00 | 9579.4 | 7736.4
02-01-2024 04:37 | 8906.6 | 8906.6
02-01-2024 08:00 | 8796.2 | 9098.5
02-01-2024 19:58 | 9095.2 | 9095.2
03-01-2024 08:00 | 9395.2 | 9091.8
04-01-2024 08:00 | 9579.4 | 9217.2
05-01-2024 05:13 | 9579.4 | 9579.4
05-01-2024 08:00 | 9579.4 | 9626.8
06-01-2024 08:00 | 9579.4 | 9710.0
07-01-2024 05:20 | 9579.4 | 9579.4
07-01-2024 08:00 | 9579.4 | 9563.0
07-01-2024 09:08 | 9579.4 | 9579.4
08-01-2024 08:00 | 9579.4 | 9904.8
09-01-2024 08:00 | 9579.4 | 9904.8
09-01-2024 19:14 | 9579.4 | 9579.4
10-01-2024 08:00 | 9579.4 | 9209.6
11-01-2024 05:56 | 9579.4 | 9579.4
11-01-2024 08:00 | 9579.4 | 9614.1
12-01-2024 08:00 | 9579.4 | 9706.4

Note that I've already tried several ideas in the web, I cant' use Line Chart since Excel automatically assumes that all data points are equidistant markers and that is not my case. I've already calculated the intersection between the lines so it's easier to plot. I'm trying to get a chart that shows in red the area when the actual performance is lower than the planned performance (this would be the green area in the example image).

2. ## Re: How to Create an XY Scatter Plot with Shaded Area Below a Curve in Excel

One big problem for the general case is that the lines cross in between data points, so you would have to interpolate the data to determine where those points are. And I don't know any way to do area fill with scatter plots.

I used to do something like this by creating two plots and coloring the lines, but it requires deriving new sets of data. Fortunately in your case the crossing points are all data points so no interpolation is required.

above below.jpg

3. ## Re: How to Create an XY Scatter Plot with Shaded Area Below a Curve in Excel

HI
I don't know if this can help you but look at Foglio 1
I set the two series as Stacked Area (without filling) while the differences always as Stacked Area (but with colored filling).
Look at the attachment.
Let me know. Bye
Mario

4. ## Re: How to Create an XY Scatter Plot with Shaded Area Below a Curve in Excel

Hi Mario,
Originally Posted by felipe552
I cant' use Line Chart since Excel automatically assumes that all data points are equidistant markers and that is not my case.
The area chart also uses equidistant markers.

5. ## Re: How to Create an XY Scatter Plot with Shaded Area Below a Curve in Excel

I haven't tried it for this exact scenario, but, if you are not required to use Excel, Gnumeric has a built in "fill" option among its scatter chart options that might fill between series automatically.

6StringJazzer's solution is obviously simpler. If you decide you just must have that fill effect in Excel, here's how you do it: https://peltiertech.com/fill-under-b...n-excel-chart/ Note that it ends up being a scatter + stacked area combo chart. Most of the work is building and interpolating the data for the area chart portion of the chart.

6StringJazzer's "conditional formatting" approach is certainly easier. If you must have the "fill" effect, it can be done with the right effort.

6. ## Re: How to Create an XY Scatter Plot with Shaded Area Below a Curve in Excel

Hi 6StringJazzer,

I used to do something like this by creating two plots and coloring the lines, but it requires deriving new sets of data. Fortunately in your case the crossing points are all data points so no interpolation is required.
Thank you, your answer is actually the next best thing to my problem. Indeed I interpolated the data points so I could determine where the data intersected.

7. ## Re: How to Create an XY Scatter Plot with Shaded Area Below a Curve in Excel

If you need to do this again with raw data, the interpolation could be included in the Excel formulas to generate the plotting data.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking \$A addreputationiconsmall.jpg below their profile in any of their posts.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1