How to Calculate Area Under Curve in Excel (3 Methods)

In this article, we will learn how to calculate the area under the curve in Excel according to three methods

1. Calculate the Area Under the Curve by Trapezoidal method
2. Calculate the Area Under the Curve by Integral method
3. Calculate the Area Under the Curve by Simpson’s method

Method 1. Calculate Area Under Curve by Trapezoidal Method in Excel

Theory You should know to calculate the area by Trapezoidal Method

Step 01: Create a data table containing the values of coordinates (X and Y) as shown below.

Step 02: Select both columns named “Value of X” and “Value of Y.”

Step 03: Then go to the Insert tab.

Step 04: From the chart section, select the Scatter chart

The result is

Step 05: Enter the following formula in the cell D4

=0.5*(C3+C4)/(B4-B3)

This formula will calculate the area of the marked trapezium in the image.

Step 06: Press Enter and AutoFill the remaining cell in column D

Here, we have taken into account the nine trapeziums that make up the entire area(to understand, see the image given below). No trapezium was made by the first ordinate alone. Therefore, we started the calculation in cell D4.

Step 07: Then to sum all the areas of trapeziums, enter the following formula in cell D13

=SUM(D4:D12)

Method 2. Calculate Area Under Curve by Integral Method in Excel

Step 01: Double-click on the curve. The Chart Design tab will appear

Step 02: From the Charts layouts section, Click on Add Chart Element

Step 03: Keep the mouse pointer on the Trendline option

Step 04: Click on the More Tradelines options.

Step 05: Format Trendline tab will appear from the right side of your monitor

Step 06: Click on the Trendline option

Step 07: Select the Polynomial option, and set order 2

Step 08: Then, scroll down and check the Display Equation on Chart option.

The trendline and equation of the curve will be displayed with the curve.

Step 09: Find the integral of the polynomial equation.

The polynomial equation is,

y = -0.0341x² + 1.2417x + 6.9833

According to the integration method, the integral of the polynomial equation is 

Step 10: Let’s write the integrated polynomial equation in excel and enter into cell D14

=(-0.0341*C14^3)/3 +( 1.2417*C14^2)/2 + 6.9833*C14

Where C14 contains the value of X

Step 11: AutoFill the formula to cell D15

Step 12: Now, in order to determine the area of the chart covered by the curve for X = 1 to X= 10, we need to deduct the answer obtained by substituting X = 1 into the equation we developed in step 10 from the result of substituting X=10 into the equation obtained in step 10

So, enter 1 in cell C14 and 10 in cell C15.

Step 13: Enter the following formula into cell D16 to get the area under curve.

=D15-D14

Method 3. Calculate Area Under Curve by Simpson’s method in Excel

The theory you should know 

Here, we took the even ordinate (X = 10) number. So, we have to apply the Simpsons 1st rule

Step 01: Create a new column and input the Simpsons multiplier

Step 02: Then input the following formula into the cell E3  and use AutoFill 

=C3*D3

Step 03: To get the sum of the products of Simpson’s multiplier and the value of Y, Apply the following formula in cell E13

=SUM(E3:E12)

Step 04: Finally, get the area by applying the formula given below in cell E15

=(3/8)*E13

Comparison Between the Area Obtained From the Three Methods