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.
How to Calculate Area in Excel Sheet: 2 Examples
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 the Area under a Scatter Plot in an 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. 👇
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.
- Consequently, you will see that the dataset has a scatter plot now.
- At this time, select the D5 cell and insert the following formula.
=(C5+C6)/2*(B6-B5)
- 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.
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)
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. 👇
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.
- As a result, you will get the 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.
- 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.
- Now, you can see a trendline will appear along with the equation on your chart.
- Now, to calculate the area, you need to find the integral function of the equation. For better visualization, write the trendline equation and integral function respectively.
- Now, build a new table for the 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)
- 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.
- 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
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. 👇
Example 2: Calculate the 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. 👇
📌 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
- 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.
As a result, you can find the area of all triangles. For example, the result sheet will look like this. 👇
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. 👇
📌 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
- Â 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.
Thus, you can calculate all the rectangle’s areas. And, the result sheet should look like this. 👇
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. 👇
📌 Steps:
- Initially, click on the C5 cell and write the following formula.
=POWER(B5,2)
Note:
The POWER function is the function that is used to calculate the definite power of a definite number. It has two arguments. Such as number and power. The number argument takes the number that needs to be powered. And, the power argument takes the digit to which the following number should be powered.
- 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.
- 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. 👇
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. 👇
📌 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)
Note:
The PI function is a function that is used in Excel to return the value of π which is 3.1416 approximately.
- 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.
Finally, you can calculate the area of squares in the Excel sheet. You can see the result like this. 👇
2.5 Area of Trapezium
Moreover, you can find the area of the trapezium to calculate 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. 👇
📌 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
- 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.
Finally, you can find all the areas of all the trapeziums. For example, the result sheet will look like this. 👇
Download Practice Workbook
You can download and practice from our workbook here for free.
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!
