Calculating beam deflection in a spreadsheet, with a worked example
Calculate beam deflection and moment in a spreadsheet with standard formulas, consistent units, superposition, a worked steel example and the L/360 limit.
For a simply supported beam of span L carrying a uniform load w, the maximum deflection is at midspan: delta = 5*w*L^4/(384*E*I), with a maximum bending moment of w*L^2/8. For a single point load P at midspan the deflection is P*L^3/(48*E*I) and the moment is P*L/4. A cantilever with a uniform load deflects w*L^4/(8*E*I) at the free end, and with an end point load P*L^3/(3*E*I).
Those four formulas fit in a spreadsheet in a few minutes. Most of the real work is keeping units consistent, which is where wrong answers come from. This article gives the formulas, a unit scheme that avoids mistakes, a worked example with every step, and the limits of the method.
What assumptions do beam deflection formulas make?
The standard equations come from Euler-Bernoulli beam theory. They are accurate when these hold:
- The material is linear elastic with a constant modulus of elasticity
E. - The beam is prismatic: the second moment of area
Iis the same along the span. - Deflections are small compared with the span, so curvature is approximately the second derivative of the deflection curve and
E*I*y'' = M(x). - Plane sections stay plane and perpendicular to the neutral axis. In practice this means shear deformation is ignored, which is reasonable for slender beams and understates deflection in short, deep ones.
- Loads act in the plane of symmetry, so the beam bends without twisting.
- Supports behave as idealized: pinned and roller, or fully fixed.
What are the standard beam deflection and moment formulas?
| Case | Maximum deflection | Maximum moment |
|---|---|---|
Simply supported, uniform load w |
5*w*L^4/(384*E*I) at midspan |
w*L^2/8 at midspan |
Simply supported, midspan point load P |
P*L^3/(48*E*I) at midspan |
P*L/4 at midspan |
Cantilever, uniform load w |
w*L^4/(8*E*I) at the free end |
w*L^2/2 at the fixed end |
Cantilever, end point load P |
P*L^3/(3*E*I) at the free end |
P*L at the fixed end |
Fixed at both ends, uniform load w |
w*L^4/(384*E*I) at midspan |
w*L^2/12 at the supports |
Fixed at both ends, midspan point load P |
P*L^3/(192*E*I) at midspan |
P*L/8 at supports and midspan |
For a simply supported beam with a uniform load, the full deflection curve is y(x) = w*x*(L^3 - 2*L*x^2 + x^3)/(24*E*I). For a midspan point load, use y(x) = P*x*(3*L^2 - 4*x^2)/(48*E*I) for x up to L/2 and mirror it for the other half. Put x in a column from 0 to L and chart y to see the deflected shape. At x = L/2 both reduce to the maximum formulas in the table, which makes a good check cell.
Superposition
Because the beam is linear elastic, the deflection from several loads is the sum of the deflection from each load on its own. Moments add the same way. Maxima add only when they occur at the same point; here both loads peak at midspan, so they do. When they do not, compute each curve at the same set of x values and add them row by row.
Why do beam deflection calculations need consistent units?
Beam formulas have no built-in units, so any mixed set gives a wrong answer with no error message. Choose one set and convert everything into it:
| Quantity | Use | Conversion |
|---|---|---|
Span L |
millimeters | meters times 1,000 |
Point load P |
newtons | kilonewtons times 1,000 |
Uniform load w |
newtons per millimeter | 1 kN/m equals 1 N/mm |
Modulus E |
megapascals (N/mm²) | gigapascals times 1,000 |
Second moment of area I |
mm⁴ | cm⁴ times 10,000; in⁴ times 416,231 |
Moment M |
newton-millimeters | kN·m times 1,000,000 |
For structural steel, E is about 200 GPa (200,000 MPa). AISC uses 29,000 ksi, which is close to that, and Eurocode 3 uses 210 GPa. The difference is about 5%, and deflection changes by about the same amount, so state which value you used. Aluminum is about 70 GPa. Timber and concrete vary widely by grade and by how the code treats cracking and creep, so take their values from the governing standard.
The two classic errors are entering the span in meters while everything else is in millimeters, which is wrong by a factor of 10¹² because the span is raised to the fourth power, and entering I in cm⁴ without converting, which makes the beam appear 10,000 times too flexible. Put the conversions in their own cells, as in the layout below, and the formulas stay clean.
What does a worked beam deflection example look like?
A simply supported steel floor beam spans 6 m. Its strong-axis second moment of area is 8,356 cm⁴, the tabulated value for an IPE 300 section, and E is 200 GPa. Service loads, unfactored: dead load 4 kN/m, live load 6 kN/m, and a 20 kN live point load at midspan from equipment. Include the beam's own weight in the dead load in a real calculation; steel weighs about 77 kN/m³.
Convert. L = 6,000 mm. E = 200,000 N/mm². I = 8,356 x 10,000 = 83,560,000 mm⁴. E*I = 1.6712 x 10^13 N·mm². Dead load 4 N/mm, live load 6 N/mm, P = 20,000 N.
Live uniform load. 5*6*6000^4/(384*1.6712e13) = 3.888e16/6.4174e15 = 6.059 mm.
Live point load. 20000*6000^3/(48*1.6712e13) = 4.32e15/8.0218e14 = 5.385 mm.
Superpose the live loads. 6.059 + 5.385 = 11.444 mm, or L/524. The limit for live load on a floor is commonly L/360 = 16.67 mm. The beam uses 69% of the allowance and passes.
Add the dead load. 5*4*6000^4/(384*1.6712e13) = 4.039 mm. Total deflection is 4.039 + 11.444 = 15.483 mm, or L/388. A total-load limit of L/240 is 25 mm, so this passes too.
Moment and stress. The total uniform load is 10 kN/m, so M = 10*6^2/8 = 45 kN·m. The point load adds 20*6/4 = 30 kN·m, giving 75 kN·m. With 150 mm from the neutral axis to the extreme fiber (half the 300 mm depth), the service-level stress is 75e6*150/83560000 = 134.6 MPa. That is a serviceability stress from unfactored loads, not a strength check; strength uses factored load combinations and the code's resistance rules.
Work backwards. To find the smallest I that meets L/360 under the live loads, rearrange: I = (5*w*L^4/(384*E) + P*L^3/(48*E)) / (L/360). That gives 57,375,000 mm⁴, or 5,737.5 cm⁴. A section with about 5,790 cm⁴ (an IPE 270 in the European series) would pass narrowly at 16.52 mm, which shows why deflection rather than strength often decides the size of floor beams.
Sensitivity. Using E = 210,000 MPa instead of 200,000 reduces the live-load deflection from 11.444 mm to 10.899 mm.
The spreadsheet layout
| Cell | Label | Value or formula |
|---|---|---|
B1 |
Span, m | 6 |
B2 |
E, GPa | 200 |
B3 |
I, cm⁴ | 8356 |
B4 |
Uniform load, kN/m | 6 |
B5 |
Point load, kN | 20 |
B7 |
L, mm | =B1*1000 |
B8 |
E, MPa | =B2*1000 |
B9 |
I, mm⁴ | =B3*10^4 |
B10 |
w, N/mm | =B4 |
B11 |
P, N | =B5*1000 |
B13 |
Uniform load deflection, mm | =5*B10*B7^4/(384*B8*B9) |
B14 |
Point load deflection, mm | =B11*B7^3/(48*B8*B9) |
B15 |
Total, mm | =B13+B14 |
B16 |
Limit L/360, mm | =B7/360 |
B17 |
Utilization | =B15/B16 |
B18 |
Check | =IF(B15<=B16,"OK","Too flexible") |
With the example inputs, B15 returns 11.444, B16 returns 16.667 and B17 returns 0.687.
How do support conditions change beam deflection?
Supports change the result far more than arithmetic does. Keep the same 6 m beam and the full 10 kN/m uniform load:
| Support condition | Maximum deflection |
|---|---|
| Simply supported | 10.10 mm |
| Fixed at both ends | 2.02 mm |
| Cantilever, fixed at one end | 96.94 mm |
The cantilever deflects 9.6 times as much as the simply supported beam, and a fully fixed beam one-fifth as much. Real connections sit between pinned and fixed. Treating a partly restrained joint as pinned is conservative for deflection; treating a flexible joint as fixed is not.
What deflection limits apply to a beam calculation?
Deflection limits protect finishes, partitions, doors and the occupants' confidence in the floor. The usual figure for live load on a floor is L/360. The International Building Code, Table 1604.3, lists L/360 for live load on floor members and L/240 for dead plus live load, with different values for roofs and for members supporting brittle finishes, and it takes L as twice the length of a cantilever. Use the edition your jurisdiction has adopted, and apply any stricter limit in the project specification, such as L/480 where partitions are fragile. For a 6 m beam, L/360 is 16.7 mm and L/240 is 25 mm.
When is a beam deflection spreadsheet not enough?
The formulas stop being enough when:
- The section changes along the span, or the beam is continuous over several supports.
- Shear deformation matters, as in deep beams or short spans.
- The material is not linear elastic, for example cracked reinforced concrete, where an effective
Iand long-term creep dominate, or timber with creep. - Loads are dynamic, or floor vibration governs rather than static deflection.
- The structure is a frame, where joint rotation affects the beam.
- Deflections are large enough that the geometry changes.
For those cases use structural analysis software or a hand method such as moment distribution, and keep the spreadsheet as an independent check on a simple case.
Beam deflection checklist
- Convert every input into millimeters, newtons and megapascals in separate cells before calculating.
- Match the formula to the real support conditions and load position.
- Add the beam's self-weight to the dead load.
- Superpose loads only for the same beam and the same point on it.
- Check the result against the limit for the right load case: live load alone, or total.
- Confirm one case by hand or against a published table before trusting the sheet.
The same layout of inputs, unit conversions, calculations and a pass-or-fail check suits other engineering formulas, such as pipe pressure drop (the Darcy-Weisbach equation) or tolerance stack-up.
The beam deflection calculator on Sheet Reserve is a template for these checks, with example data you replace with your own. For a shorter reference, see how to calculate beam deflection.