How to Calculate Area in Excel Sheet: 2 Methods

Method 1 – Calculate the Area under a Scatter Plot in an Excel Sheet

1.1 Using Trapezoidal Rule

Steps:

  • Insert a scatter plot for the dataset. Select the dataset >> go to the Insert tab >>  click the Insert Scatter or Bubble Chart tool >> select the Scatter with Smooth Lines option.

Insert Scatter Plot

  • See that the dataset has a scatter plot now.

Scatter Plot of the Given Dataset

  • Select the D5 cell and insert the following formula.
=(C5+C6)/2*(B6-B5)

Calculate Area Under a Scatter Plot Using Trapezoidal Rule

  • Get the enclosed area between the 5th and 6th-row datasets. Place the cursor in the bottom right position of the D5 cell.
  • The fill handle will appear. 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:

The formula isn’t copied to row 9, because it is the last point of the plot. It does not enclose any mentionable area.

  • 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.

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

Steps:

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

Insert Scatter Plot

  • Get the scatter plot of your dataset.

Scatter Plot of Your Dataset

  • Cick on the Chart Elements icon >> click the rightward arrow beside the Trendline option >> choose the More Options… option from the context menu.

Access the Chart Trendline Options

  • A new ribbon named Format Trendline will appear on the right side. Put the tick mark on the Display Equation on chart option.

Extract the Trendline Equation

  • See a trendline will appear along with the equation on your chart.

Trendline along with Equation

  • Calculate the area, you need to find the integral function of the equation. For better visualization, write the trendline equation and integral function.

Trendline equation and Integral Equation

  • Build a new table for the value and the integral value of the function concerning x.
  • 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

  • You will get f(3) by applying this formula. Because you have used 3 as x’s value.
  • Put your cursor in the bottom right position of the cell. You will see the fill handle will appear. Drag the fill handle downward to get all the other f(x) for all the x’s values.

Drag Fill Handle to Copy Formula

  • The last x’s f(x) and the first x’s f(x). 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

Get the area enclosed by the scatter plot successfully by doing this. The result sheet should look like this after the result.

Calculated Area under a Scatter Plot


Method 2 – Calculate the Area of Different Geometric Shapes

Steps:

  • Click on the D5 cell where you want to calculate your triangle area. Write the formula below on the cell.
=0.5*B5*C5

Calculate Area of Triangle in Excel Sheet

  • Get the area of the first triangle. Place your cursor in the bottom-right position of the D5 cell.
  • Fill handle will appear. Drag the fill handle below to copy the area formula for all the cells below.

Copy Triangle Area Formula by Fill Handle

Find the area of all triangles. The result sheet will look like this.

Calculated Area of Triangles


2.2 Area of Rectangle

Steps:

  • Click the D5 cell where you want to put your result. Insert the following formula in the D5 cell.
=B5*C5

Calculate Rectangle Area in Excel Sheet

  •  Get the area of the first rectangle. Place your cursor in the bottom right position of the selected cell.
  • The fill handle will appear. Drag the fill handle below to all the cells.

Drag the Fill Handle to Copy Formula

Calculate all the rectangle’s areas. The result sheet should look like this.

Calculated Area of Rectangles


2.3 Area of Square

Steps:

  • 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 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. The power argument takes the digit to which the following number should be powered.

POWER Function Syntax

  • Get the area of a square. Place your cursor on the bottom right of your selected cell, and upon the appearance of the fill handle, drag it downward.

Drag the Fill Handle to Copy the Formula

  • The formula will be copied to all the cells.

Get all the squares’ area, the result sheet would look like this.

Calculated Area of Squares


2.4 Area of Circle

Steps:

  • Click on the C5 cell as you want your result in this cell. 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.

PI Function

  • Find the area of the first circle of your dataset. Place your cursor in the bottom right position of your cell.
  • 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

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

Steps:

  • Click on the E5 cell where you want to calculate the area of the trapezium. Put an equal sign (=) to start the formula. Insert the following formula in the cell.
=((B5+C5)/2)*D5

Calculate Trapezium Area in Excel Sheet

  • Get the area of the first trapezium of your dataset. Place your cursor in the bottom right position of the E5 cell.
  • 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

Find all the areas of all the trapeziums. For example, the result sheet will look like this.

Calculated Area of Trapeziums


Download Practice Workbook

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


<< Go Back to Area and Volume | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo