Step 1 – Formatting Data in Excel to Calculate Cut and Fill Volume
 To make the data more understandable, we’ll format it appropriately.
 Assume that we’ve divided a place into sections, and each section has a uniform distance of 30 meters between them.
 If our reference point is 0 (zero), the first section would be 0+30 meters long.
 To format the distances, follow these steps:
 Select the range where you want to store these distances.
 Go to the Number menu and choose More Number Formats…

 In the Format Cells window, select Number and then choose Custom.
 Type 0+000 in the Type field.
 Enter the distance between the first two stations in cell B5 (you’ll see it displayed as Road (Rd)).
 Fill the lower cells with the distances (keeping them uniform is recommended).
Step 2 – Measuring Distance between Each Section
 In this step, we’ll calculate the distance between each pair of adjacent sections.
 Enter the following formula in cell C5:
=B6B5
 Press ENTER to calculate the distance between the first two sections.
 Fill the lower cells to get the distances between every two sections.
Step 3 – Calculating Cut and Fill Volume Using Excel IF Function
Background:
 We’re calculating the Cut and Fill volume using two methods: the Average Area End method and the Pyramid method.
 If any cut or fill area becomes 0 for a section, we consider that section as a pyramid.
 Here are the formulas for both methods:
 Average Area End Method:
 Pyramid Method:
 Average Area End Method:
Where:


 V = Volume
 A_{1} & A_{2 }= Areas of the adjacent two sections
 d = distance between two sections

Implementation:
 Create the necessary columns in your Excel sheet.
 In cell F6, enter the following formula:
=IF(D5*D6>0,(D5+D6)*C6/2,(D6+D5)*C6/3)

 This formula uses the IF function to return the volume of the space occupied by the first two sections.

 Press ENTER to see the fill volume in cell F6.
 Use the Fill Handle to AutoFill the lower cells. This will show you the fill volumes for every two adjacent sections.
 In cell G6, enter the following formula:
=IF(E5*E6>0,(E5+E6)*C6/2,(E6+E5)*C6/3)

 This operation returns all the Cut Volumes for every two sections.
 To calculate the volumes between every two places, enter the formula:
=F6G6
 To determine the overall volume of that place, select a cell and enter:
=SUM(H5:H11)
 The SUM function will give you the total volume.
You can now calculate the Cut and Fill volume of a place using Microsoft Excel.
Read More: How to Calculate Volume in Excel
Practice Section
Below, you’ll find the dataset from this article. Feel free to use it for practicing the methods on your own:
Download Practice Workbook
You can download the practice workbook from here:
<< Go Back to Formula List  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!