Excel Fill Area Under Line Chart

Excel Fill Area under Line Chart

0:00 Introduction
0:26 Inserting Normal Distribution Data
0:46 Inserting a Series
1:23 NORM.DIST function
2:05 Creation and Customization of Graph
2:57 Adding Vertical Lines to Chart
5:13 Adding Text to Chart
5:48 Coloring Area under Line Chart
10:03 Adding Arrow to Chart

https://softtechtutorials.com/microso...

In this video we explain how to fill the area under a curve or line chart. To illustrate this, we will create a normal distribution and color the area that covers 68% of the population, 95% and 99%. This corresponds to the intervals that are 1, 2 and 3 standard deviations away from the mean.
We start by creating the graph without the colors.

Inserting Normal Distribution Data

We can easily insert this series by first typing 135 in the cell A2 and then navigating to the Home tab. Here, we select Fill under Editing and then navigate to Series. A box opens where we can indicate how we want the series to appear.

Excel NORM.Dist function

Let’s insert the y-values now. Here, we want to create a normal distribution so we will make use of the NORM.DIST function in Excel. We type NORM.DIST of our x-value.

The mean of our observations is 171 and the standard deviation is 8. The final element in this function should indicate whether you are interested in the probability or cumulative distribution function.

Creation and Customization of Graph

Let’s add all our wanted features to the chart. First, we will change the line’s color. To do that, you double click on the line. Then the “Format Data Series” side panel opens.

Adding vertical lines to chart

Next, we add all vertical lines to highlight the positions of the average, average – 1, 2 and 3 standard deviations and the average + 1, 2 and 3 standard deviations. The table here shows all required elements for this distribution. Recall that we used mean 171 and standard deviation 8 to compute our y-values.

To do that, we will add some lines to the chart. A line can be defined by only two points, so for each line we enter two x coordinates and two y coordinates.

All other values are defined similarly.

When both points are defined for each line, we can add the lines to the chart. You do this by clicking on the graph, navigating to Chart Design and go to Select Data. A menu opens where you can add data.

By clicking on the line and navigating to Color under the paint icon, you can change the line’s color to whatever color you like.

Excel Fill Area under Line Chart

Now that we have created all vertical lines we want, we are ready to color the areas. To do so, we have to add a new set of data. For each area we want to color we set all data points equal to the corresponding y-values and all other data points will be 0.

We show this for the area between the green bars.
Using these data points we will create this curve on the graph and then color it. We click on the graph, navigate back to Chart Design and press Select Data. We add a new series by clicking on the Add button and selecting the newly defined x- and y-values.

You press OK now and the curve appears on the chart. We will quickly turn it red to make it appear a little clearer on the screen. This can be done in the Format Data Series side panel as shown before.

You can see that the curve is as we want now and it’s time to color the area below. This can be done by clicking on the graph, selecting Chart Design and click on Change Chart Type. Here, you can select Combo from the side panel where you can select different types of graphs for each data series.
Here we make sure that all graphs are set to Scatter with Smooth Lines except for the red line.

Finally, we show how to change the area color and line color of the area chart. First, we change the line color by clicking on the area chart, navigating to the paint bucket in the side panel and selecting the color we want.

The final two elements we add to the graph are a double-arrow and a text box indicating the width of the line. To do that, you go to Insert Shapes under Format. Then you select the double-arrows by clicking on it in the Lines section.

This concludes our Excel Fill Under Line Chart tutorial. I'm inspired by content creators as Leila Gharani and Teacher's Tech.

#Excel #Tutorials #Statistics