Excel is more than just functions, and the autofill handle alone can make calculating the area under a plot much easier. In this article, we’re going to calculate the area under a plot in Excel using mathematical formulas. Read on to learn what information the area under a plot can give you, and how to calculate it.
How to Calculate the Area Under a Plot in Excel
As a concept, the area under a plot is roughly the x-axis multiplied by the y-axis. However, there is no Excel function that calculates the area under a plot.
To calculate the area under a plot, you’ll have to separately calculate the area between every two values and then sum them to get the total area. This might sound a bit arduous, but don’t be daunted. It’s easier than it looks.
The area under a plot consists of triangles, trapezoids, and rectangles. Once you calculate the area for each of these shape types, you can use the autofill handle in Excel to automatically calculate the area of the rest of the shapes of that type.
As an example, suppose we have the speed of a car at specific times. If you create a chart for this data, the area under the plot will show the distance traveled by the car. That is because the x-axis is seconds, and the y-axis is meters per second. If you multiply these two together, you’ll get meters, or the distance traveled. Let’s get started by creating the cart.
1. Creating the Chart
In this spreadsheet, we have the speed of a car at specific time points. The car starts moving at time zero, so its speed is 0 at that time. An area chart is the best way to see the area under a plot, so let’s make one.
Select the data series. In this example, that will be the car speed, so we will select cells B2 to B9. Go to the Insert menu and then click the line chart symbol in the Charts section. Select 2-D Area. You have your chart now, but the X-axis isn’t quite right as it’s not set to our timeline. Right-click the chart and click Select Data. Under Horizontal Axis Labels, click Edit. Select the categories from your spreadsheet. That will be cells A2 to A9 for this example. Click OK. Now your X-axis starts from zero.
If you’re a total beginner at creating charts in Excel, you can read our guide on how to make a chart in Excel to get a quick head start.
2. Customizing the Chart
Your chart is accurate now, and it gives an okay measure of the area under the plot, but it’s still not good enough. Before getting into calculating the areas, let’s make a couple of adjustments to the chart so that the shape of the areas and their measurements are more distinctive.
Select your chart. Go to the Chart Design tab from the ribbon. Click Add Chart Element, and then go to Gridlines. Select Primary Major Vertical.
Now you have vertical gridlines on your graph, which makes reading the graph easier. Unfortunately, the gridline goes behind the area, and the area isn’t transparent, so right now, you can’t see the gridlines under the plot. This can be easily fixed.
Select your chart. Double-click the data series. This will open the Format Data Series bar on the right. In Format Data Series, under Series Options, select Fill & Line. Change the Fill from Automatic to Solid. Set the Transparency to 30%. This will let you see the gridlines.
Finally, to see the height for each input, you can add data labels to your chart.
Select your chart. Go to the Chart Design tab. Click Add Chart Element, and then go to Data Labels. Select Show.
Now your area chart is ready for further analysis. With these adjustments that you just made, reading the height and the base of the different shapes in the area is much easier.
3. Calculating the Area
If you take a look at the example chart’s geometry, it’s actually made up of a triangle, a bunch of trapezoids, and a rectangle. So if you calculate the area for these shapes and sum them, you’ll get the total area under the plot.
Let’s write the formulas now. The first segment is going to be the area in time zero, which is naturally a zero.
Select the cell where you want to show the area for the first segment. In this example, that will also be the distance traveled, so we’re going to use column C for this purpose. For the first segment, input 0. That is because the area at time zero is zero as well. Now let’s calculate the area for the next segment. Select the cell below, and enter the formula below in the formula bar: =(A3-A2)(B3)/2 + C2 This segment is a rectangle. The area of a triangle is the height multiplied by the base and then divided by two. The height of this triangle is on the X-axis and is A3-A2. The height of this triangle is on the Y-axis, and it’s B3. Once the formula calculates the area, it then sums it with the previous cell, to get the total area. Select the cell below and enter this formula: =(B3+B4)(A4-A3)/2 + C3 This time, the segment is a trapezoid. A trapezoid’s area is the sum of the two bases, multiplied by the height and then divided by two. The bases are B3 and B4, and the height is A4-A3. Again, when the formula has the area of the segment, it sums it with the previous cell to get the total area so far. Grab the fill handle and drop it on the cells below. This will automatically calculate the area for the cells below, since they’re all trapezoids.
If you’re wondering about the rectangle, the area for a rectangle can also be calculated using the trapezoid area formula. That is because the rectangle’s area is simply the length multiplied by the width.
If you look at a rectangle as a trapezoid, the two bases summed together make 2x widths, whereas the height is actually the length. Finally, the division by two nulls the multiplication by two, and you’ve got the rectangle’s area.
Calculate the Area Under a Plot With Excel
The area under a plot provides valuable information. Though there’s no Excel function to directly calculate this, you can still use Excel for this purpose. All you need to do is to break down the area into simpler shapes and calculate the area of those shapes.
Graphs and charts are wonderful visualizations that can make analysis and calculations much easier if you use them properly. There are lots of charts other than the area chart, and learning to use them will definitely give you an advantage in Excel.
