Horizontal Cylindrical Tank Volume Calculator in Excel

Formulas for Horizontal Cylindrical Tank Volume Calculator

The volume of a cylinder can be of two types: fully and partially filled. Partially filled volume can also be of two types during calculation: filled less than 50% or more than 50%.

Formula for Volume of a Fully Filled Tank:

V = πR²L

Here,

R = Radius of the cylinder base

L = Length of the cylinder’s horizontal axis

Formulas to Calculate Volume of a Partially Filled Tank:

Filled Less Than 50% (F<R)

Segment Height (S) = Filled Height (F)

Segment Area = ((R²cos^-1(1-S/R)-(R-S)×√(2RS-S²))

Volume (V) = Length × Segment Area = L ×((R²cos^-1(1-S/R)-(R-S)×√(2RS-S²))

Here,

R = Radius

S = Segment Height

L = length

Segment Area:

In the figure, the black part is the segment area. When the filled height (F) is less than Radius(R), the segment area is equal to the filled area. And when the filling height is greater than the Radius(R), the segment area will be the remaining unfilled part of the cylinder.

Filled More Than 50% (F>R)

Segment Height (S) = Diameter(D) – Filled Height (F)

Segment Area = ((R²cos^-1(1-S/R)-(R-S)×√(2RS-S²))

Volume (V) =Full volume – Length × Segment Area = πR²L – L ×((R²cos^-1(1-S/R)-(R-S)×√(2RS-S²))

Horizontal Cylindrical Tank Volume Calculator in Excel: 2 Examples

To compute the volume of the cylindrical tank, we have to convert all the units to S.I. units to get the volume in m³ units. We can use other units instead, but here we’ll use S.I. units. To use other units, at the bottom of this article is a handy measurement unit conversion calculator.

Example 1 – Volume Calculator for Fully Filled Horizontal Cylindrical Tanks

Assume we have all the units converted to S.I units. Now we can calculate the full volume of a cylinder tank.

Steps:

Enter the value of the length and diameter in cells C5 and C6 respectively.

Enter the following formula in cell C7 to calculate the full volume of the cylinder:

=PI()*(C6/2)^2*C5

Method 2 – Volume Calculator for Partially Filled Horizontal Cylindrical Tanks

When a tank is partially filled, there may be two circumstances: either it is filled to less than half of its capacity, or it is filled to more than half its volume. For these two situations, the volume has to be calculated differently.

When Tank Is Filled Less Than 50% (F<R):

Steps:

Enter the value of length, radius, and the filled height of the cylinder.

Enter the following formula in cell C11 to calculate the segment height, which is equal to the filled height when F<R:

=C8

Enter the following formula in cell C12 to calculate the segment area:

=($C$7^2)*ACOS(1-C11/$C$7)-($C$7-C11)*SQRT(2*$C$7*C11-C11^2)

Enter the following formula in cell C13 to calculate the partially filled volume:

=IF(C8<=C7,C12*C5,"N/A")

The If function will return a result when the filled height is less than the radius, otherwise it will return “N/A”

When Tank Is Filled More Than 50% (F>R):

If the cylindrical tank filled to more than the half volume, we subtract the segment volume from the full volume. The segment height will be the difference between the filled height and the full height.

Steps:

Enter the following formula in cell C16:

=C6-C8

Use a similar formula to calculate the segment area in cell C17:

=($C$7^2)*ACOS(1-C16/$C$7)-($C$7-C16)*SQRT(2*$C$7*C16-C16^2)

The partially filled volume will be the subtraction of segment volume from the full volume. To calculate this, enter the following formula in cell C18:

=IF(C8>C7,PI()*C7^2*C5-C17*C5,"N/A")

The If function returns a result when the filled height is higher than the radius, otherwise it returns “N/A”

The calculator is ready to use.

Note: In the above formulas, the radius of the tank is required. If cell C7 contains the diameter, you must use C7/2 instead of C7. If you define C7 as radius directly, then you can keep the formulas as written.

Things to Remember

During the calculation of the volume of a cylinder or any other thing, you must use constant units. If you have values in any other kind of unit, you must convert the measurement units to the specific constant unit required. To assist you with this process, included in the downloadable sample workbook below is a free Measurement Unit Converter that can convert between different unit types.

There is a drop-down option in each cell where you can select which units to convert to and from.

Download Sample Workbook

Horizontal Cylinder Volume Calculator.xlsx

<< Go Back to Excel Area & Volume Calculator | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!

Option Explicit Sub BuildUllageChart() Dim wsIn As Worksheet, wsOut As Worksheet Dim D As Double, L As Double, stepIn As Double Dim r As Double, totalIn3 As Double, totalGal As Double Dim h As Double, i As Long Dim A As Double, fillIn3 As Double, fillGal As Double, pct As Double, ullGal As Double Const GAL_PER_IN3 As Double = 1# / 231# ' --- set input sheet and read inputs --- On Error Resume Next Set wsIn = ThisWorkbook.Worksheets("Input") On Error GoTo 0 If wsIn Is Nothing Then MsgBox "Please create a sheet named 'Input' and place:" & vbCrLf & _ "B2 = Diameter (in), B3 = Length (in), B4 = Step (in).", vbExclamation Exit Sub End If D = CDbl(wsIn.Range("B2").Value) ' inches L = CDbl(wsIn.Range("B3").Value) ' inches stepIn = CDbl(wsIn.Range("B4").Value) ' inches If D <= 0 Or L <= 0 Or stepIn <= 0 Then MsgBox "Check inputs in B2:B4 (must be positive numbers).", vbExclamation Exit Sub End If r = D / 2# totalIn3 = WorksheetFunction.Pi() * r ^ 2 * L totalGal = totalIn3 * GAL_PER_IN3 ' --- make/clear output sheet --- On Error Resume Next Set wsOut = ThisWorkbook.Worksheets("UllageChart") On Error GoTo 0 If wsOut Is Nothing Then Set wsOut = ThisWorkbook.Worksheets.Add(After:=wsIn) wsOut.Name = "UllageChart" Else wsOut.Cells.Clear End If ' --- headers --- With wsOut .Range("A1:F1").Value = Array("Sounding_h (in)", "Ullage (in)", _ "Filled Vol (in^3)", "Filled Vol (US gal)", _ "% Full", "Ullage Vol (US gal)") .Rows(1).Font.Bold = True End With ' --- fill rows for h = 0 to D by stepIn --- i = 2 h = 0# Do While h <= D + 0.0000001 ' Circular segment area at height h (in^2): ' A(h) = r^2*acos((r - h)/r) - (r - h)*sqrt(2*r*h - h^2) If h <= 0 Then A = 0# ElseIf h >= 2# * r Then A = WorksheetFunction.Pi() * r ^ 2 Else A = r ^ 2 * WorksheetFunction.Acos((r - h) / r) _ - (r - h) * Sqr(Application.Max(0#, 2# * r * h - h ^ 2)) End If fillIn3 = A * L fillGal = fillIn3 * GAL_PER_IN3 pct = IIf(totalGal > 0, fillGal / totalGal, 0#) ullGal = totalGal - fillGal With wsOut .Cells(i, 1).Value = Round(h, 3) ' Sounding (in) .Cells(i, 2).Value = Round(D - h, 3) ' Ullage (in) .Cells(i, 3).Value = Round(fillIn3, 3) ' in^3 .Cells(i, 4).Value = Round(fillGal, 3) ' gal .Cells(i, 5).Value = pct ' % .Cells(i, 6).Value = Round(ullGal, 3) ' gal End With i = i + 1 h = h + stepIn Loop ' --- pretty up --- With wsOut .Columns("A:F").AutoFit .Range("D2:D" & i - 1).NumberFormat = "0.000" .Range("E2:E" & i - 1).NumberFormat = "0.0%" .Range("A1:F1").Interior.Color = RGB(230, 230, 230) .Range("A1:F1").Borders.Weight = xlThin .Range("A2:F" & i - 1).Borders.Weight = xlHairline End With MsgBox "Ullage chart generated on sheet 'UllageChart'. Total capacity ≈ " & _ Format(totalGal, "0.0") & " US gallons.", vbInformation End Sub

8 Comments

A.NIRMALA RANI Apr 10, 2024 at 2:36 PM

If possible, pl. send HT tank with dish calculation for each cm (1 cm to 200cm)for 15KL, Length-500cm, Diameter:200cm
- Reply
  Shamima Sultana Apr 18, 2024 at 10:42 AM
  
  Hello A.Nirmala Rani,
  
  You can the following formula for the volume of a cylindrical segment:
  
  Where:
  V is the volume in liters,
  L is the length of the cylinder in meters,
  R is the radius of the cylinder in meters,
  h is the height of the liquid level in meters.
  
  Given the specific dimensions:
  L=5.0 meters (500 cm),
  R=1.0 meter (100 cm),
  h varies from 0.01 meters (1 cm) to 2.0 meters (200 cm)
  
  This formula calculates the volume of the liquid based on the height from the base up to the liquid level within a horizontal cylindrical tank. To use this formula, ensure to convert all measurements (radius, length, and height) to meters before applying them in the calculation.
  
  At 1 cm height: Approximately 9.41 liters
  At 2 cm height: Approximately 26.59 liters
  At 3 cm height: Approximately 48.77 liters
  At 4 cm height: Approximately 74.97 liters
  At 5 cm height: Approximately 104.62 liters
  At 10 cm height: Approximately 293.63 liters
  ———————————————————
  To see the full list of calculated volumes for each centimeter increment from 1 cm to 100 cm for the horizontal cylindrical tank, Download the Excel File.
  List of Calculated Volumes.xlsx
  
  Regards
  ExcelDemy
Mehdi Razavi Aug 19, 2025 at 8:35 PM

Hi, Dear OSMAN GONI RIDWAN,
Thank you for your calculation and excellent file.
Just please note that some of parameters C7 (D) should be modified as to C7/2 (D/2) in your formula to get the right answer.

Partially filled (less than 50%): =C5* ( (C7/2)^2*ACOS(1-C8/(C7/2)) – (C7/2-C8)*SQRT(2*C7/2*C8-C8^2) ) (assuming length is in C5, diameter is in C7, and fill height is in C8)

Partially filled (more than 50%): =C5*PI()*(C7/2)^2 – C5*( (C7/2)^2*ACOS(1-(C7-C8)/(C7/2)) – (C7/2-(C7-C8))*SQRT(2*C7/2*(C7-C8)-(C7-C8)^2) ) (assuming length is in C5, diameter is in C7, and fill height is in C8)
- Reply
  Shamima Sultana Aug 20, 2025 at 10:48 AM
  
  Hello Mehdi Razavi,
  
  Thank you for your valuable feedback and for sharing the corrected formulas. You made a great point, since C7 represents the diameter, it should be divided by 2 to use the radius in the calculation.
  We have now added a note in the article to clarify this, so readers can apply the formulas correctly.
  Your contribution will definitely help others get accurate results. Much appreciated!
  
  Regards
  ExcelDemy

EHBowen Oct 15, 2025 at 1:59 AM

Hello. I’m needing to draw up a tank sounding/ullage chart for our Diesel fuel storage; it’s 63 inches internal diameter and 179.5 inches internal length (calculated total volume just a little over 2422 US gallons [1 USG/231 in^3]). I can work with your formulas just fine, but I don’t have any experience with Excel macros. Any suggestion for drawing up a macro to repeatedly calculate every sounding/ullage from 0/63 by 0.5 inch steps to 63/0, and then to output the results to the next page of the workbook?

Shamima Sultana Oct 16, 2025 at 11:34 AM

Hello EHBrown,

You can automate the whole sounding/ullage table with a simple VBA macro. Here’s a drop-in approach that uses your tank’s inside diameter (63 in) and length (179.5 in), steps every 0.5 in from 0 → 63, and writes a clean table to a new sheet.

How to use:

1. Put these on any sheet (say, a sheet named Input):
B2 = Diameter (in) → 63
B3 = Length (in) → 179.5
B4 = Step (in) → 0.5
2. Press Alt+F11 → Insert → Module → paste the code below.
3. Run BuildUllageChart. It will create/refresh a sheet named UllageChart with:
Sounding (in), Ullage (in), Filled Vol (in³), Filled Vol (US gal), % Full, Ullage Vol (US gal)

Notes:
1. The geometry uses the standard circular-segment formula, then multiplies by length to get volume; gallons = in³ ÷ 231.
2. You can change the step in Input!B4 (e.g., 1.0, 0.25).
3. If you prefer no macros (Excel 365), you can also build this with a height column =SEQUENCE(1+63/0.5, , 0, 0.5) and a single formula for the segment area, but the macro is fastest for a full 0.5-inch table.

Regards,
ExcelDemy

EHBowen Oct 16, 2025 at 8:45 PM

Thanks much. That worked perfectly. I made a few minor tweaks to get it into a format that I’m more familiar with, but that took only minutes and a re-sort. I’d happily upload a copy, but I don’t seem to have that option here. I did give your website credit in the page footer. Thanks again.
- Shamima Sultana Oct 17, 2025 at 4:28 PM
  
  Hello EHBowen,
  
  You’re very welcome! I’m glad it worked perfectly for you. Thank you so much for mentioning our website in your file footer — really appreciate that!
  If you’d like to share your version, you can upload it in our ExcelDemy Forum so others can benefit too.
  
  Regards,
  ExcelDemy

Horizontal Cylindrical Tank Volume Calculator in Excel

Formulas for Horizontal Cylindrical Tank Volume Calculator

Horizontal Cylindrical Tank Volume Calculator in Excel: 2 Examples

Example 1 – Volume Calculator for Fully Filled Horizontal Cylindrical Tanks

Method 2 – Volume Calculator for Partially Filled Horizontal Cylindrical Tanks

Things to Remember

Leave a reply Cancel reply