Can You Calculate Area in Excel Under a Plotted Curve?

When it comes to calculus, calculating the area under a particular curve is an essential and reoccurring task. Although there are many math functions Microsoft Excel can perform, the standard version does not include the ability to do calculus. Instead, you must use a few different workarounds to calculate the area under a plotted curve.

Integrals on a Graph

Integration is used to find the area and volume of many different things, including the area under a chart in Excel. Integration involves breaking down the area in question into lots of small slices and adding up each slice to find the total area. The wider the slices, the less accurate the calculated area will be, as certain areas underneath the graph will go unaccounted for. The smaller the slices, the closer the calculated area will be to the true area. In Excel, the slices will be the space between listed data points on the X-axis.

Using the Trapezoidal Rule

1. Select the data you wish to graph: The data should be in two columns. Use one for the x-axis and one for the y-axis. If the data is not in this format, modify it prior to trying to calculate the area under the curve in Excel.
2. Locate the appropriate graph type: Click on "Charts" on the toolbar near the top and select the "Line" option to create a line graph using the information selected in Step 1.
3. Create a third column: You are going to make a third column in Excel where you will place the trapezoid formula. 
4. Calculate the area of the first trapezoid: To begin, you will want to find the area between X1 and X2. In the first row of data (C2 if you labeled your X-axis and Y-axis, C1 if you did not) insert the following formula: (A3-A2)*(B2+B3)/2. This is the area between the first two points on the X-axis.
5. Drag formula down all data points: Instead of redoing the formula for each trapezoid on the graph, you can simply click on the cell with the formula in it, take the cursor to the bottom-right corner of the cell and drag the formula down the list of data points. This will copy the formula, but replace parts of it with the relevant cells.
6. Add up areas: In a cell below the last data point in column C, calculate the sum of all trapezoid areas. This can be done using the sum function between the first and last points on the list. If you started at C2 and the list went down to row 35, the formula would be "=SUM(C2:C35)."

Using the Trendline

There is also a less popular method involving the trendline that can help you arrive at the area under a plotted curve. To begin, follow the first two steps from the integration method.

Then you would do the following:

1. Select the appropriate trendline: In the "Charts" tab there should be a "Format" tab. Once you click on "Format," there will be an option to select the type of trendline for the selected data. You can select linear, linear forecast, logarithmic, exponential or two-period moving average functions.
2. Find the integral function: There should be a box you can check to show you the formula of the trendline. Use this formula to find the integral function in Excel.
3. Plug in upper and lower limits and find the difference: Once you have the integral function, the next step is to plug in the upper and lower limits of the desired area. For example, if you want to find the area between X=2 and X=15, you would plug 2 and 15 into the equation. Once you have both functions evaluated, subtract the lower limit from the upper limit: F(15) - F(2). The difference should represent the area under the curve.