How to Calculate Area in Excel Sheet (Area under Curve & More)

It is a very common need for us to calculate the area of objects of different shapes. Besides, sometimes we need to calculate the area under graphs too. In this article, I will show you several formulas to calculate area in an Excel sheet. Go through the full article below to enhance your knowledge in this regard.


Download Practice Workbook

You can download and practice from our workbook here for free.


2 Examples to Calculate Area in Excel Sheet

Generally, we need to calculate the area for 2 criteria most frequently. One is for regular shapes and another one is for scatter plots/areas under curves.

Example 1: Calculate Area under a Scatter Plot in Excel Sheet

Now, letโ€™s say, you have a random dataset from which you can get a scatter plot only but no regular shapes. Now, you need to calculate the area enclosed by the given data points. Follow any of the methods below to accomplish this. ๐Ÿ‘‡

Dataset to Calculate Area Under a Scatter Plot


1.1 Using Trapezoidal Rule

You can use the trapezoidal rule to calculate the area of a scatter plot. Follow the steps below to use this rule for finding the area.๐Ÿ‘‡

๐Ÿ“Œ Steps:

  • First and foremost, you need to insert a scatter plot for the dataset. To do this, select the dataset >> go to the Insert tab >>ย  click on the Insert Scatter or Bubble Chart tool >> select the Scatter with Smooth Lines option.

Insert Scatter Plot

  • Consequently, you will see that the dataset has a scatter plot now.

Scatter Plot of the Given Dataset

  • At this time, select the D5 cell and insert the following formula.
=(C5+C6)/2*(B6-B5)

Calculate Area Under a Scatter Plot Using Trapezoidal Rule

  • As a result, you will get the enclosed area between the 5th and 6th-row datasets. Now, place the cursor in the bottom right position of the D5 cell.
  • Subsequently, the fill handle will appear. Following, drag the fill handle downward to copy the formula to all the cells up to row 8.

Drag The Fill handle to Copy Formula

Note:

Here, the formula isnโ€™t copied to row 9. Because it is the last point of the plot. So it does not enclose any mentionable area.

  • Now, at the D10 cell, write the following formula to sum all the enclosed areas individually between two intervals.
=SUM(D5:D9)

Find the Total Area Under a Scatter Plot

Note:

The SUM functionย is a function that is used to sum multiple numbers. It has the arguments which require the numbers that you want to sum up.

Finally, you can find the total area enclosed by the scatter plot using this method. The overall result sheet will look like this. ๐Ÿ‘‡

Calculated Area Under A Scatter Plot


1.2 Using Excel Chart Trendline and Mathematical Integration

Another useful way to calculate the area enclosed by a scatter plot is using the chart trendline and mathematical integration features. Follow the steps below to achieve the result using this method. ๐Ÿ‘‡

๐Ÿ“Œ Steps:

  • Initially, select the required columns of your dataset which are column B and column C.
  • Following, go to the Insert tab >> click on the Insert Scatter or Bubble Chart tool >> select the Scatter with Smooth Lines option.

Insert Scatter Plot

  • As a result, you will get the scatter plot of your dataset.

Scatter Plot of Your Dataset

  • Now, click on the chart. Following, click on the Chart Elements icon >> click on the rightward arrow beside the Trendline option >> choose the More Optionsโ€ฆ option from the context menu.

Access the Chart Trendline Options

  • Consequently, a new ribbon named Format Trendline will appear on the right side. Here, put the tick mark on the Display Equation on chart option.

Extract the Trendline Equation

  • Now, you can see a trendline will appear along with the equation on your chart.

Trendline along with Equation

  • Now, for calculating the area, you need to find the integral function of the equation. For better visualization, write the trendline equation and integral function respectively.

Trendline equation and Integral Equation

  • Now, build a new table for xโ€™s value and the integral value of the function concerning x.
  • After making the table, click on the C16 cell and write the following formula which we have got earlier by integrating the trendline equation.
=1/2*(0.5046*B16^2)+(0.1651*B16)

Calculate Area Integral Under a Scatter Plot

  • Now, you will get f(3) by applying this formula. Because you have used 3 as xโ€™s value.
  • Following, put your cursor in the bottom right position of the cell. You will see the fill handle will appear. Subsequently, drag the fill handle downward to get all the other f(x) for all the xโ€™s values.

Drag Fill Handle to Copy Formula

  • Now, you have got the last xโ€™s f(x) and the first xโ€™s f(x). Now, for calculating the area, click on the C21 cell and write the following formula.
=C20-C16

Find the Enclosed Area between First and Final Points of the Plot

Finally, you can get the area enclosed by the scatter plot successfully by doing this. And, the result sheet should look like this after the result. ๐Ÿ‘‡

Calculated Area under a Scatter Plot

Read More: How to Calculate Area of Irregular Shape in Excel (3 Easy Methods)


Example 2: Calculate Area of Different Geometric Shapes

Some common regular shapes are triangle, rectangle, square, circle, trapezoid, etc. Follow the article below to calculate the area of these regular shapes.

2.1 Area of Triangle

Say, we have the base and heights of five different triangles. Now, follow the given steps below to calculate the area of the triangles. ๐Ÿ‘‡

Triangle Dimensions to Calculate Area

๐Ÿ“Œ Steps:

  • First and foremost, click on the D5 cell where you want to calculate your triangle area. Following, write the formula below on the cell.
=0.5*B5*C5

Calculate Area of Triangle in Excel Sheet

  • Consequently, you will get the area of the first triangle. Now, place your cursor in the bottom-right position of the D5 cell.
  • As a result, the fill handle will appear. Next, drag the fill handle below to copy the area formula for all the cells below.

Copy Triangle Area Formula by Fill Handle

As a result, you can find the area of all triangles. For example, the result sheet will look like this. ๐Ÿ‘‡

Calculated Area of Triangles


2.2 Area of Rectangle

Apart from calculating the area of triangles, you can also calculate the area of rectangles easily. Suppose, you have a dataset of rectangles with lengths and breadths given. Go through the following steps to calculate the area of the rectangles. ๐Ÿ‘‡

Rectangle Dimensions to Calculate Area

๐Ÿ“Œ Steps:

  • At the very beginning, click on the D5 cell where you want to put your result. Now, insert the following formula in the D5 cell.
=B5*C5

Calculate Rectangle Area in Excel Sheet

  • ย As a result, you will get the area of the first rectangle. Now, place your cursor in the bottom right position of the selected cell.
  • Consequently, the fill handle will appear. Next drag the fill handle below to all the cells.

Drag the Fill Handle to Copy Formula

Thus, you can calculate all the rectangleโ€™s areas. And, the result sheet should look like this. ๐Ÿ‘‡

Calculated Area of Rectangles


2.3 Area of Square

Another common regular shape is square. If you are given the sides of different squares, you can calculate the areas of the squares too. Follow the steps below to do this. ๐Ÿ‘‡

Square Dimensions to Calculate Area

๐Ÿ“Œ Steps:

  • Initially, click on the C5 cell and write the following formula.
=POWER(B5,2)

Calculate Area of Square in Excel Sheet

Note:

The POWER functionย is the function that is used to calculate a definite power of a definite number. It has two arguments. Such as number and power. The number argument takes the number which needs to be powered. And, the power argument takes the digit to which the following number should be powered.

POWER Function Syntax

  • At this time, you can get the area of a square. Now, place your cursor to the bottom right position of your selected cell, and upon the appearance of the fill handle, drag it downward.

Drag the Fill Handle to Copy the Formula

  • Thus, the formula will be copied to all the cells.

Consequently, you will get all the squaresโ€™ area. And, the result sheet would look like this. ๐Ÿ‘‡

Calculated Area of Squares


2.4 Area of Circle

Just like the other regular shapes, if you have a dataset containing the radius of circles, you can also find the areas of the circles by following the steps given below. ๐Ÿ‘‡

Circle Dimensions to Calculate Area

๐Ÿ“Œ Steps:

  • First, click on the C5 cell as you want your result in this cell. Then, write the following formula in the cell.
=PI()*POWER(B5,2)

Calculate Area of Circle in Excel Sheet

Note:

The PI function is a function that is used in Excel to return the value of ฯ€ which is 3.1416 approximately.

PI Function

  • As a result, you will find the area of the first circle of your dataset. Now, place your cursor in the bottom right position of your cell.
  • Subsequently, the fill handle will appear. Drag the fill handle downward to copy the area formula for all the cells below.

Drag Fill Handle to Copy Formula

Finally, you can calculate the area of squares in the Excel sheet. You can see the result like this. ๐Ÿ‘‡

Calculated Area of Circles


2.5 Area of Trapezium

Moreover, you can find the area of the trapezium in an Excel sheet by following some simple steps. Say, you are given the parallel sides and the distance between the parallel sides of a trapezium. Now, go through the steps below to find the areas of the trapeziums. ๐Ÿ‘‡

Trapezium Dimensions to Calculate Area

๐Ÿ“Œ Steps:

  • Firstly, click on the E5 cell where you want to calculate the area of the trapezium. Following, put an equal sign (=) to start the formula. Subsequently, insert the following formula in the cell.
=((B5+C5)/2)*D5

Calculate Trapezium Area in Excel Sheet

  • As a result, you will get the area of the first trapezium of your dataset. Next, place your cursor in the bottom right position of the E5 cell.
  • Consequently, the fill handle will appear. Last but not least, drag this fill handle downward to copy the trapezium area formula for all the other given dimensions of the trapezium.

Drag the Fill Handle to Copy Formula

Finally, you can find all the areas of all the trapeziums. For example, the result sheet will look like this. ๐Ÿ‘‡

Calculated Area of Trapeziums


Conclusion

So, in this article, I have shown you how to calculate area in an Excel sheet. Read and practice the full article carefully to understand it better and apply it afterward according to your needs. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo