How to Calculate Cut and Fill Volume in Excel (3 Steps)

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.
• Type the following formula in cell C5:

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

Where:

• • • V = Volume
    • A₁ & A₂ = 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:

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

• • This operation returns all the Cut Volumes for every two sections.

• To calculate the volumes between every two places, use the formula:

• Finally, to determine the overall volume of that place, select a cell and enter:

• 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 Area and Volume | Formula List | Learn Excel