Statistics How To

How to Find the Area Under Curve in Microsoft Excel

Calculus > Area under curve in Microsoft Excel

How to Find the Area Under Curve in Microsoft Excel

Watch the video or read the steps below:

The area under a curve is the area between the curve and the x-axis. The curve may lie completely above or below the x-axis or on both sides. In calculus, you measure the area under the curve using definite integrals. Microsoft Excel does not have functions to calculate definite integrals, but you can approximate this area by dividing the curve into smaller curves, each tending to a line segment. Use the following steps to calculate the area under a curve in Excel as the total area of the trapezoids under these line segments:

How to Find the Area Under Curve in Microsoft Excel: Steps

Sample question:Find the area under curve in Microsoft Excel for the graph below, from x=1 to x=6.

area undercurve in Microsoft Excel

Step 1: Choose a few data points on the x-axis under the curve (use a formula, if you have one) and list these values in Column A in sequence, starting from Row 1. In this example from the graph on the left, your x-values are 1,2,3,4,5 and 6. Ensure that the first and last data points chosen on the curve are its starting and ending points respectively.

Step 2: List the corresponding y-axis data points in Column B, aligning them row-wise with the values in Column A. For this example, I’m going to assume that you don’t have the formula. In other words, I’m guessing where the values lie based on the graph. I’ll use y=1,0.5,0.33,0.225,0.2 and 0.19.

Step 3: Type the following formula into cell C1 “=(B1+B2)/2*(A2-A1)” and copy this for all Column C cells till the second-last row of data. To copy, click cell C1 and then click and drag the little black box in the right hand corner. In this example, you have 6 data points so you would drag the formula to cell C5.

Step 4: Calculate the sum of the totals in Column C. In this example, click cell C6 and then click the summation sign “Σ” on the ribbon. The solution will appear in cell C6.

Step 5: Delete the last row in column c (not the total!–see the image below). The correct approximation will not show in the summation cell.
For this example, the solution is 1.85.
area under curve in microsoft excel

That’s it!

Tip: When finding the area under curve in Microsoft Excel, keep the x-axis increments as small as possible. This improves the curve’s approximation and the accuracy of the area under the curve. In other words, the more values you input into columns A and B, the more accurate your results will be. By using trapezoids of equal width, i.e. equidistant data points on the x-axis, you can do away with the first column; the formula in Column C is simply C1=(B1+B2)/2. The total sum of the values in Column C can then be multiplied by this constant width to give the total area under the curve.

Check out our YouTube channel for more stats help!

------------------------------------------------------------------------------

If you prefer an online interactive environment to learn R and statistics, this free R Tutorial by Datacamp is a great way to get started. If you're are somewhat comfortable with R and are interested in going deeper into Statistics, try this Statistics with R track.

Comments are now closed for this post. Need help or want to post a correction? Please post a comment on our Facebook page and I'll do my best to help!
How to Find the Area Under Curve in Microsoft Excel was last modified: October 15th, 2017 by Stephanie Glen

18 thoughts on “How to Find the Area Under Curve in Microsoft Excel

  1. imminent_access

    Neat Method. It seems like it would be a little inaccurate with steep curves though. I added a trendline with equation, the then computer the integral in wolfram alpha. Like this:
    Wolfram Alpha

  2. Andale Post author

    Yes, Reimann sums are always going to be an approximation. with very steep curves, I agree they could be way off! For most general cases though (i.e. the ones you’re likely to encounter in an elementary calc course), Reimann sums will provide a good approximation.

  3. Beth

    I have a line graph that zig zags up and down, therefore the slope of the rhombus made is sometimes positive, sometimes negative, I want the total area underneath the line, how do I get around these negative values?

    Thanks

  4. Andale Post author

    The area is going to be the area between the graph and the x-axis. So, if you’re going to do this in Excel, fool the program by making negatives positive. For example, if one piece of your graph is below the axis with coordinates (-1,.5) and (-2,.5), put them in Excel cells as (1,.5) and 2,.5),

  5. Hadi Khodabandehloo

    Hi. thanks for your good explanation. I have a question and that is, is it possible to use this formula – “=(B1+B2)/2*(A2-A1)” – anywhere for AUC calculation? for example is it possible to use this formula in calculation of AUC of glucose and insulin secretion?
    Best Regards,
    hadi Khodabandehloo

  6. Andale Post author

    Hello, Hadi,
    Thanks!
    I don’t see any reason why you couldn’t use it. But as I’m not an expert with glucose & insulin secretion I can’t say for sure.
    Stephanie

  7. Karl

    You do realize that you could fill in column B using the formula for the curve which is 1/x, where X is column A. And it should be noted that the values you plugged into the formula for x = 4 and x = 6 are incorrect.

  8. Andale Post author

    Hi, Karl,
    Yes, you could use the formula. That’s definitely another way of doing it. This method (Reimann sums, essentially) works even if you don’t know the formula, which is what I was trying to convey. I’ll make a note though, that it’s a ballpark and if you know the formula, you could use it!
    Regards,
    Stephanie

  9. Andale Post author

    No. In calculus, the slope of a graph is a derivative. Finding the area under a curve is integration. In statistics, the area under a curve is probability; the slope of a graph is used to get one of the variables in regression equations (and other equations).

  10. Andale Post author

    Split it into two where the negative and positive areas meet. Find the area of each section and then add the two areas together.

  11. Darrien

    Hi can you suggest a formula for iAUC incremental area under the curve in excel? Especially when the time between the different timepoints it is not costant like minutes: 0 30 60 120 180?
    Thank you

  12. Jennifer Dean

    I have worked out the 2 AUC and they are obviously different.
    How do I present the difference between the AUC of 2 curves when I write it up..
    Is it as a percentage? ie taking the higher value as 100% and calculating the lowereAUC?

  13. Andale Post author

    That’s how I would write it up.
    But I wouldn’t take the higher as 100%. I’d take it as actual value.
    For example, AUC 1 = 90%, AUC 2 = 50%, so AUC1 – AUC2 = 40%

  14. Ahmed adebayo

    Hello there, I need someone to please help me with a task the involves calculating an incremental area under the curve