Tank Volume Calculator Litres Excel Sheet

This article explains how to calculate tank volume in litres using an Excel spreadsheet, covering the formulas for rectangular, cylindrical, and other common tank shapes, and how to set up a reusable calculator. You will learn the exact cell formulas to enter, how to handle unit conversions, and how to avoid common mistakes.

Formula

For a rectangular tank, the volume in cubic metres is:

V (m³) = L × W × H

where:

• L = internal length of the tank (metres)
• W = internal width of the tank (metres)
• H = internal height of the tank (metres)

To convert cubic metres to litres, multiply by 1000 (since 1 m³ = 1000 L). For a cylindrical tank, use V = π × r² × h, where r is the radius (half the diameter) and h is the height. For a spherical tank, V = (4/3) × π × r³. In Excel, you can use the PI() function for π.

Worked Example

Using the default values from the calculator above (L = 2 m, W = 1 m, H = 1 m):

1. Calculate the volume in cubic metres: V = 2 × 1 × 1 = 2 m³
2. Convert to litres: 2 m³ × 1000 = 2000 litres

The calculator above shows 2000 litres, matching this result. If you measure in centimetres, remember to divide by 100 to get metres, or use the direct formula: volume in litres = (length in cm × width in cm × height in cm) / 1000.

Why Use an Excel Sheet for Tank Volume?

An Excel spreadsheet is a practical tool for tank volume calculations because it allows you to:

• Change dimensions and instantly see the new volume
• Keep a record of different tanks or batches
• Automate unit conversions (e.g., inches to centimetres)
• Add formulas for partially filled tanks (e.g., volume at a given liquid height)

It is especially useful for aquaculture, water storage, fuel tanks, and chemical mixing, where you need quick and repeatable calculations.

How to Build a Rectangular Tank Calculator in Excel

Follow these steps to create a simple calculator for a rectangular tank:

1. Open a new Excel worksheet.
2. In cells A1, A2, A3, enter the labels: Length (m), Width (m), Height (m).
3. In cells B1, B2, B3, enter the dimensions (e.g., 2, 1, 1).
4. In cell A5, type “Volume (litres)”.
5. In cell B5, enter the formula: =B1*B2*B3*1000
6. Press Enter. The result (2000) appears.

For a cylindrical tank, use =PI()*(D/2)^2*H*1000 where D is the diameter and H is the height (both in metres). For a spherical tank, use =(4/3)*PI()*(D/2)^3*1000.

Common Mistakes and How to Avoid Them

• Unit mix-ups: Mixing metres and centimetres is the most frequent error. Always convert all dimensions to the same unit before multiplying.
• Using external dimensions: For liquid capacity, use the internal dimensions (subtract wall thickness if the tank is thick-walled).
• Forgetting the conversion factor: 1 m³ = 1000 litres, not 100. If you measure in cm, divide by 1000 to get litres.
• Rounding too early: Round only the final answer, not intermediate steps, to avoid significant errors.

How to Choose the Right Formula for Your Tank Shape

Different tank shapes require different formulas. Here is a quick guide:

• Rectangular or square: V = L × W × H
• Vertical cylinder: V = π × r² × h (r is radius, h is height)
• Horizontal cylinder: More complex – requires the segment area formula, but for full volume it is the same as a vertical cylinder: V = π × r² × L (L is length).
• Sphere: V = (4/3) × π × r³
• Elliptical: V = π × a × b × h (a and b are semi-axes)

For irregular shapes, consider using a liquid flow meter or a dip chart, but for most practical purposes, the above formulas are sufficient.

Partial Volume Calculations

Often you need to know how much liquid is in a partially filled tank. For a rectangular tank, the volume is simply V = L × W × h_liquid, where h_liquid is the liquid height. In Excel, if you have the liquid height in cell B4, the formula would be =B1*B2*B4*1000. For a vertical cylinder, use =PI()*(D/2)^2*h_liquid*1000. For a horizontal cylinder, the calculation is more complex and involves the circular segment area; you can use the formula: V = L × (r² × arccos((r-h)/r) – (r-h) × √(2rh – h²)) × 1000, where h is the liquid depth. In Excel, this becomes a long formula, but it is accurate.

Limitations and Accuracy

These formulas assume perfectly geometric shapes and uniform dimensions. Real tanks may have dished ends, internal baffles, or slight irregularities. For high-accuracy requirements (e.g., legal trade), you should use calibrated dip charts or professional measurement. Also, temperature can affect liquid density and volume, but for most water and fuel applications at ambient temperatures, the error is small.

Frequently Asked Questions

How do I convert cubic inches to litres in Excel?

Multiply the volume in cubic inches by 0.0163871 (since 1 in³ = 0.0163871 L). For example, =A1*0.0163871 where A1 contains cubic inches.

What is the formula for a horizontal cylinder tank in Excel?

For a horizontal cylinder with radius r (in metres) and length L (in metres), filled to height h (in metres), the volume in litres is: =L*(r^2*ACOS((r-h)/r) - (r-h)*SQRT(2*r*h - h^2))*1000. Ensure h is between 0 and 2r.

Can I use the same formulas for a tank measured in feet?

Yes, but you must convert feet to metres first. Multiply feet by 0.3048 to get metres, or use the conversion factor 1 ft³ = 28.3168 litres directly.

Why does my Excel formula give a negative volume?

This usually happens if you are using a partial volume formula and the liquid height is greater than the diameter or less than zero. Check that your inputs are within the valid range.

How do I make a drop-down list for tank shapes in Excel?

You can use Data Validation > List and enter the shape names. Then use an IF formula to select the appropriate calculation based on the chosen shape. For example, =IF(A1="Rectangular",L*W*H*1000,IF(A1="Cylinder",PI()*(D/2)^2*H*1000,...)).

Related

• Milliseconds to Hours Minutes Seconds? Time Converter
• Minutes Rx Guam Hours: What It Means & How to Use
• Minutes to Decimal Hours Converter

in l | Length | m | 2 | 0
in w | Width | m | 1 | 0
in h | Height | m | 1 | 0
out vol_m3 | Volume (m³) | m³ | l * w * h | 3
out vol_litres | Volume (litres) | L | vol_m3 * 1000 | 0