🛢️Tank Calculator
Excel & Google Sheets Reference

Tank Volume Formulas for Excel & Google SheetsCopy-Paste + Homemade Dip Chart

Four tank geometries, seven copy-pasteable formulas, and the segment-integral math behind every dip chart you have ever used. Paste them into Excel or Google Sheets, substitute your dimensions, and read capacity in US gallons — no VBA, no add-ins, no file downloads.

Jump to the Formulas Verify in the Live Calculator

Why Your Spreadsheet Needs These Formulas

Every tank capacity number — the one on the supplier sticker, the one in the calibration chart, the one the live calculator on this site returns — comes from a short chain of geometry. The good news is that the chain is short enough to fit in a single Excel cell. The better news is that once you paste these formulas into your own sheet you can tweak them, build lookup tables, cross-check the tank you are working on against the nameplate, and generate a printable dip chart at any inch increment you want — all without leaving Excel.

This page deliberately avoids two things. First, it does not ask for your email and it does not hide formulas behind a form. Everything on this page is copy-pasteable right from the code block. Second, it does not pretend the formula is the whole story — a warning panel below explains what each shape does not cover, where the common mistakes happen, and how much dished-head volume you are leaving on the table if you ignore it. Every formula comes with its limitations printed next to it.

Use the generator below the cards to substitute your own D, L, and FILL values on the fly — it writes a ready-to-paste cell formula with your actual numbers in place of the symbols. Then open the calculator on the homepage and type the same dimensions in both inputs; the two numbers should match. If they do not, you now know where to look.

Section 1 — The Formulas

Four Tank Geometries, One Copy Button Each

Pick the card that matches your tank. Copy the formula. Replace D, L, H, FILL, W with your dimensions in inches — or better, point the formula at cells that contain those numbers so you can change them later. The result is US gallons; the ÷231 constant handles the unit conversion for every formula here.

Vertical Cylinder

SimplePartial fill ✓

Tank type: Vertical cylindrical tanks (water towers, column vessels, farm tanks)

Simplest geometry — one circle times one height. Good for any tank that stands upright and fills from the bottom up.

Excel / Google Sheets
=PI() * (D/2)^2 * H / 231

Parameters

D
Diameter of the cylindrical shell (inches)
H
Fill height from bottom of shell (inches)
231
Cubic inches per US gallon (exact definition) (—)
  • Works for full or partial fill the same way — just change H
  • No segment math needed because every horizontal slice is a full circle

Horizontal Cylinder (Full)

SimplePartial fill ✗

Tank type: Horizontal cylindrical tanks (propane bullets, septic tanks, road tankers)

Full-tank capacity only — straight pipe geometry with flat ends. Use the partial-fill formula below if you need volume at a dip reading.

Excel / Google Sheets
=PI() * (D/2)^2 * L / 231

Parameters

D
Diameter of the cylindrical shell (inches)
L
Length of the straight cylindrical section (inches)
231
Cubic inches per US gallon (—)
  • Swap H for L from the vertical version — that is the only change
  • For the full tank this gives the geometric capacity of the shell only — add heads separately

Horizontal Cylinder (Partial Fill)

AdvancedPartial fill ✓

Tank type: Horizontal cylinders read by dip gauge — the formula behind every dip chart

Segment-integral formula from the geometry of a circular segment. Produces volume at any dip depth, from empty to full. This is the formula the calculator runs for horizontal partial fill.

Excel / Google Sheets
=(L/231) * (D/2)^2 * (2*PI()*ACOS((D-2*FILL)/D) - SIN(2*ACOS((D-2*FILL)/D)))

Parameters

D
Diameter of the cylinder (inches)
L
Length of the cylindrical section (inches)
FILL
Dip depth from bottom of shell (inches)
  • ACOS returns radians — Excel and Google Sheets trig functions use radians natively
  • Quarter depth (FILL = D/4) returns ≈ 19.6% of full volume, not 25%
  • Valid for FILL between 0 and D; errors outside that range

Oval / Stadium Tank

ModeratePartial fill ✗

Tank type: Horizontal oval cross-section (heating oil tanks, some skid tanks)

Oval tanks have an elliptical cross-section along the horizontal axis. For a full tank the capacity is ellipse area times length. Partial fill on an oval uses a more complex elliptical segment formula not shown here.

Excel / Google Sheets
=PI() * (W/2) * (H/2) * L / 231

Parameters

W
Width (major axis of the ellipse) (inches)
H
Height (minor axis of the ellipse) (inches)
L
Length of the cylindrical section (inches)
  • When W = H the oval collapses into a horizontal cylinder — same formula
  • Partial fill requires elliptical-segment integration; use the calculator for that
Section 2 — Compare the Formulas

Which Formula Should You Use?

Four tank geometries side by side. The table below tells you at a glance how long the formula is, how tricky it will be to read six months from now, whether it works for partial fill, and what you still need to do after it returns a number.

Tank GeometryFormula LengthComplexityPartial FillHeads Correction
Vertical Cylinder4 termsSimpleBuilt-in (H does the work)Typically flat bottom + top, small correction
Horizontal Cylinder (Full)4 termsSimpleNot supported — use segment formulaMust add dished/elliptical heads separately
Horizontal Cylinder (Partial)≈ 25 charactersAdvancedFull ACOS + SIN segment integralHead volume added only when shell is full
Oval / Stadium5 termsModerateNot supported — elliptical segment requiredUsually flat ends; small if any correction
Section 3 — Live Generator

Type Your Dimensions, Get Your Cell Formula

No more “replace D with 36”. Put your tank's diameter and length in the boxes below. We substitute them into every formula and show you a ready-to-paste cell expression — copy it, open Excel, hit paste, and the gallons appear.

Formula Parameter Generator

Type your tank dimensions below. We substitute them into each formula so you can copy the cell value directly into Excel.

Full shell capacity (geometric, no heads):

≈ 317.26 US gallons

Vertical Cylinder — copy-paste this:

=PI() * (36/2)^2 * 72 / 231

Horizontal Cylinder (Full) — copy-paste this:

=PI() * (36/2)^2 * 72 / 231

Horizontal Cylinder (Partial Fill) — copy-paste this:

=(72/231) * (36/2)^2 * (2*PI()*ACOS((36-2*9)/36) - SIN(2*ACOS((36-2*9)/36)))

Substitute oval W and H manually — the generator above uses D for diameter. The calculator on the homepage verifies any value you compute here.

Tip — ARCCOS is the segment key

The horizontal partial-fill formula would be half as long if ACOS returned degrees instead of radians — but Excel and Google Sheets trig functions are radians-native. If you ever try to reproduce this in another tool, remember to convert degrees to radians first. The SIN term corrects for the triangular cap that ACOS alone overcounts.

Warning — dished heads matter

Ignoring dished or elliptical heads on a horizontal vessel leaves 5–10% of total capacity unaccounted for. ASME F&D heads add roughly 0.0809 × D³ cubic inches per head. 2:1 elliptical heads add PI() × (D/2)³ / 3 per head. Both divide by 231 for gallons.

Don't — vertical formula on horizontal

A vertical formula at partial fill overestimates horizontal capacity badly. At 50% depth, a horizontal cylinder holds exactly half its volume — but at 25% depth it holds only ≈19.6%, and at 75% depth it holds ≈80.4%. The gap between “depth percent” and “volume percent” is the whole reason the segment formula exists.

Adding Dished Heads — The Missing 5–10%

A horizontal cylindrical shell is only the straight middle section. Most real vessels close each end with some kind of head — flat, hemispherical, ASME F&D, or 2:1 elliptical. Each head type contributes a calculable volume, and the difference is large enough to move delivery math. Below are the two most common head formulas, both expressed per individual head. For a complete vessel add two heads to the shell volume, but only when the shell is full — head volume at partial fill requires its own segment math.

ASME Flanged & Dished (F&D) Head

Most common on industrial horizontal tanks, road tankers, and older vessels. Approximated as a torispherical dish.

Head gal = 0.0809 * D^3 / 231
Shell + two heads total (gallons):
= PI()*(D/2)^2*L/231 + 2*0.0809*D^3/231

2:1 Elliptical Head

Standard on newer pressure vessels and pharmaceutical tanks. More volume than F&D at the same diameter.

Head gal = (PI() * (D/2)^3 / 3) / 231
Shell + two heads total (gallons):
= PI()*(D/2)^2*L/231 + 2*(PI()*(D/2)^3/3)/231

Flat heads add negligible volume — for all practical purposes they contribute zero above the shell. Hemispherical heads are the largest per diameter: (2/3) * PI() * (D/2)^3 / 231 per head. The calculator on the homepage has a head-type selector that runs each of these branch formulas behind the scenes.

Build a Printable Dip Chart in Excel (7 Steps)

No downloads required — just follow this table layout. Print to PDF from Excel's Print preview.

  1. 1

    Open a new workbook

    Start with a blank sheet. Rename it DipChart. Set Page Setup to Landscape, narrow margins (0.3 in), and fit-to-page width.

  2. 2

    Label your header row

    Row 1, columns A–E: Depth(in), Segment Area, Shell Vol(in³), Volume(gal), %Full, Ullage(gal). Column F is optional — a %Full bar as a quick visual check.

  3. 3

    Put your constants at the top

    Cells H1=D (diameter), H2=L (length), H3=231. Name them: select H1, click Name Manager, call it TANK_D. Repeat for TANK_L. All formulas below reference those names.

  4. 4

    Fill the depth column

    A2 = 0, A3 = A2 + 1, drag down until depth = TANK_D + 1. You now have one row per inch. Substitute 0.5 or 0.25 increments if you need finer resolution — the formula handles it.

  5. 5

    Paste the partial-fill formula

    =(TANK_L/231) * (TANK_D/2)^2 * (2*PI()*ACOS((TANK_D-2*A2)/TANK_D) - SIN(2*ACOS((TANK_D-2*A2)/TANK_D)))
  6. 6

    Add %Full and Ullage

    D column is Volume(gal). E2 = D2 / MAX(D:D) formatted as percentage. F2 = MAX(D:D) - D2. Drag all three down. Freeze row 1 so headers stay visible as you scroll.

  7. 7

    Print to PDF or laminate

    Highlight A1:F(n), Page Break Preview to confirm one page wide, print to PDF (save as DipChart-D36-L72.pdf) or send to a laser printer and laminate. Trim to business-card size for the truck console.

Once you have the dip chart printed, keep a second copy in the truck and a third in the office. Taped to the calibration chart on the tank's catwalk, it settles delivery disputes before they start. The “Segment Area” column above is optional — if you skip it, the shell volume column can go straight to “Volume(gal)” with the full integrated formula.

Unit Conversions — The 231 Constant

Every formula above assumes your dimensions are in inches. The ÷231 at the end is what turns cubic inches into US gallons. If you measured in millimetres or centimetres, stop and convert to inches first — mixing unit systems in one formula is the single most common spreadsheet error on this topic.

Cubic inches → US gallons÷ 231
Cubic inches → imperial gallons÷ 277.42
Cubic inches → litres÷ 61.0237
Inches → millimetres× 25.4
Millimetres → inches÷ 25.4

Five Formula Rules to Live By

  • Use names, not symbols — replace D, L, FILL with cell names like TANK_D, TANK_L, DIP_FILL
  • Always reference cells, not hardcoded numbers — one tape update fixes every formula on the sheet
  • Test the edge cases: FILL=0 returns 0 gallons, FILL=D returns full tank
  • For oval tanks the partial fill is not shown here — use the live calculator's dip chart instead
  • Dished head volume only counts when the shell is full — segment math at partial fill is not additive

Frequently Asked Questions

In Excel, should I use PI() or 3.14159?▼

Use PI(). The Excel PI() function returns the built-in constant to 15 decimal places — significantly more accurate than a hand-typed approximation. Google Sheets uses the same PI() function with the same precision. For a 36-inch diameter, 15-digit π versus 6-digit π differs by about 0.01 gallons in a typical horizontal tank — tiny, but why waste the accuracy?

Do these formulas work in Google Sheets too?▼

Yes. Every function used on this page — PI(), ACOS(), SIN() — is available in both Excel and Google Sheets with identical names and radians-based convention. The cell references and arithmetic operators are identical as well. You can copy a formula from this page directly into either tool and expect the same result.

Why is the horizontal partial-fill formula so much more complex?▼

Because the cross-section is a circle, and a horizontal slice at a given depth is not a full circle — it is a circular segment. The formula integrates the segment area across the length of the cylinder. The ACOS term finds the angle defining the segment, and the SIN term corrects the area of the triangular cap. There is no way to write this formula without ACOS or an equivalent inverse trig function.

Do I need to calculate dished heads separately?▼

If your horizontal tank has dished or elliptical heads, the straight-pipe formula ignores the bulge. Typical errors run 5–10% of total capacity. ASME F&D heads add approximately 0.0809 × D³ per head; 2:1 elliptical heads add PI() × (D/2)³ / 3 per head. Both divide by 231 for gallons. Flat heads add essentially nothing.

How do I convert cubic inches to gallons?▼

Divide by 231. One US liquid gallon is legally defined as exactly 231 cubic inches. For imperial gallons, divide by 277.42. For litres, divide by 61.0237. Every formula on this page already performs the 231 division — you read the result in gallons immediately.

How do I print a dip chart from Excel?▼

Create columns Depth(in), Segment, Volume(gal), %Full, Ullage. Put your partial-fill formula in the Volume column, reference Depth for FILL, and fill Depth with 1, 2, 3... up to D. Highlight the table, choose Landscape orientation with 0.3-inch margins, confirm one page wide in Page Break Preview, and print. Laminate for outdoor use. Save as PDF from Excel's Print dialog for digital distribution.

Where do tank-capacity estimate errors usually come from?▼

Most errors are measurement errors: a tape pulled across a dished end instead of the straight shell, or an outside diameter used where inside diameter was meant. Next common is using the horizontal full formula for a partial gauge reading, or forgetting dished heads on a vessel that has them. A third category is unit mismatches — mixing inch diameters with millimetre fills or vice versa.

Verify Your Spreadsheet in One Click

These Excel formulas pair directly with the main tank volume calculator on the homepage — use the live tool to verify any Excel cell formula you build, and browse the guides hub for more reference pages. The calculator's published formulas are the exact math behind every cell formula on this page. Match the same dimensions in both places, and your spreadsheet value should equal the calculator's “total capacity” number — down to the nearest 0.01 gallon.

Open the Live Calculator Browse All Guides

Related Guides & Resources

🐟

Standard Aquarium Sizes Chart — Dimensions, Gallons & Weight

Complete standard aquarium sizes chart — dimensions, gallons, glass thickness and filled weight for Standard, Long, High, Breeder, Column and Corner variants 1 to 200+ gallons.