A heat exchanger design calculation in Excel starts with the heat balance, determines the temperature driving force, and estimates the required surface area using an appropriate overall heat-transfer coefficient. A useful sheet also checks temperature feasibility, units, fouling assumptions, and the limits of its flow-arrangement model.
This worked example cools a water stream from 90°C to 60°C and heats cooling water from 25°C to 40°C. You can reproduce the calculation in a blank Excel workbook using the cell references below. It is a preliminary thermal sizing calculation; final geometry, pressure drop, materials, and mechanical design require further engineering.
Quick answer: how do you calculate heat exchanger area in Excel?
Calculate duty from mass flow, specific heat, and temperature change. Determine both terminal temperature differences, calculate LMTD with Excel’s LN function, and apply the correct flow correction factor. Divide duty in watts by overall U, correction factor, and LMTD to obtain area in square metres.
| Output | Worked-example result |
|---|---|
| Heat duty | 627 kW |
| Required cooling-water flow | 10.00 kg/s, or 36,000 kg/h |
| Counterflow terminal differences | 50 K and 35 K |
| Log mean temperature difference | 42.055 K |
| Assumed design U-value | 800 W/(m²·K) |
| Flow correction factor | 1.00 for the ideal counterflow model |
| Required heat-transfer area | 18.64 m² |
What does this Excel sheet calculate?
The sheet answers a specific question: how much heat-transfer area is needed to meet a stated thermal duty when the terminal temperatures and a defensible U-value are known?
It calculates heat duty, cooling-water demand, log mean temperature difference, area, and a consistency check using effectiveness–NTU. It does not calculate tube-wall thickness, shell diameter, gasket suitability, baffle spacing, or a certified equipment rating.
Thermal sizing and mechanical design are connected, but they are different tasks. The selected geometry changes fluid velocities and heat-transfer coefficients, so the design normally requires iteration. For a refresher on the underlying mechanisms, see GrowMechanical’s heat transfer fundamentals guide.
Step 1: Define the design basis before opening Excel
Use these explicit assumptions for the example:
- Steady operation with two separate liquid-water streams.
- No boiling, condensation, chemical reaction, or mixing between streams.
- Negligible heat loss to the surroundings.
- Constant mean specific heat of 4,180 J/(kg·K) on both sides.
- Ideal countercurrent flow, represented by correction factor F = 1.
- Constant design overall coefficient U = 800 W/(m²·K), assumed to include the intended fouling allowance.
The water property and U-value are illustrative assumptions, not universal design values. For a real service, obtain properties at representative temperatures and pressures, document the fluid composition, and confirm whether it remains single phase throughout the exchanger.
Also record allowable pressure drop on each side, operating and design pressure, material restrictions, turndown, cleaning requirements, and the worst available cooling-water temperature. Those inputs influence whether the estimated area can be delivered by a practical exchanger.
Step 2: Enter the input cells and units
Create a worksheet named HX_Sizing. Use column A for the parameter, column B for the numeric input or formula, and column C for units. Keep the following cell assignments unchanged when copying the formulas.
| Cell | Input | Value | Unit |
|---|---|---|---|
| B2 | Hot-stream mass flow | 5 | kg/s |
| B3 | Hot-stream specific heat | 4180 | J/(kg·K) |
| B4 | Hot inlet temperature | 90 | °C |
| B5 | Hot outlet temperature | 60 | °C |
| B6 | Cold-stream specific heat | 4180 | J/(kg·K) |
| B7 | Cold inlet temperature | 25 | °C |
| B8 | Cold outlet temperature | 40 | °C |
| B9 | Design overall heat-transfer coefficient U | 800 | W/(m²·K) |
| B10 | LMTD correction factor F | 1 | dimensionless |
Temperature differences in degrees Celsius and kelvin have the same numerical value. However, do not mix Celsius and Fahrenheit inputs. Likewise, 4.18 kJ/(kg·K) must become 4,180 J/(kg·K) when calculating duty in watts.
If flow is supplied in kg/h, divide by 3,600 to obtain kg/s. If it is supplied as volumetric flow, convert using density at the relevant condition; cubic metres per hour are not interchangeable with kilograms per hour.
Step 3: Calculate the heat duty
For sensible cooling with approximately constant specific heat:
Q = ṁhcp,h(Th,in − Th,out)
Here Q is heat-transfer rate in W, ṁ is mass flow in kg/s, cp is specific heat in J/(kg·K), and T is temperature. The product has units kg/s × J/(kg·K) × K = J/s = W.
For the example:
Q = 5 × 4180 × (90 − 60) = 627,000 W = 627 kW
Enter the duty in watts in B14:
=B2*B3*(B4-B5)Enter the duty in kilowatts in B15:
=B14/1000Alfa Laval’s heat exchanger calculation method identifies flow, specific heat, temperatures, pressure drop, and heat load as key sizing information. Use a property-based enthalpy difference, Q = ṁΔh, when constant specific heat is inadequate.
Step 4: Calculate the required cooling-water flow
With negligible external losses, heat removed from the hot stream equals heat gained by the cold stream:
ṁc = Q / [cp,c(Tc,out − Tc,in)]
ṁc = 627000 / [4180 × (40 − 25)] = 10.00 kg/s
In B16:
=IF(OR(B6<=0,B8<=B7),NA(),B14/(B6*(B8-B7)))In B17, convert the result to kg/h:
=B16*3600The result is 36,000 kg/h. This is the flow required for the assumed 15 K water-temperature rise. It does not prove that the cooling-water network can supply that flow at the necessary pressure.
If both stream flows and all four temperatures are measured, calculate each side’s duty independently. Differences may reflect measurement uncertainty, heat loss, changing operating conditions, or incorrect properties. Do not force agreement by averaging inconsistent data without investigating.
Step 5: Calculate LMTD with the correct temperature pairing
For countercurrent flow:
ΔT₁ = Th,in − Tc,out = 90 − 40 = 50 K
ΔT₂ = Th,out − Tc,in = 60 − 25 = 35 K
The log mean temperature difference is:
LMTD = (ΔT₁ − ΔT₂) / ln(ΔT₁/ΔT₂)
LMTD = (50 − 35)/ln(50/35) = 42.055 K
The derivation is explained in MIT’s heat exchanger notes. Use the natural logarithm, LN, in Excel; LOG10 is incorrect here.
| Cell | Output | Excel formula |
|---|---|---|
| B18 | First terminal difference | =B4-B8 |
| B19 | Second terminal difference | =B5-B7 |
For B20, use a formula that also handles equal terminal differences:
=IF(OR(B18<=0,B19<=0),NA(),IF(ABS(B18-B19)<1E-8*MAX(B18,B19),(B18+B19)/2,(B18-B19)/LN(B18/B19)))If both differences are 30 K, LMTD is 30 K. The direct expression otherwise gives 0/0. The small relative tolerance avoids numerical instability when the two inputs are almost equal.
If either terminal difference is zero or negative, the formula returns #N/A for review. A zero approach requires special limit analysis and generally cannot deliver the specified finite duty with finite area under this model. A negative approach is incompatible with the assumed passive, single-phase counterflow temperature profile.
A cold outlet hotter than the hot outlet is not automatically invalid in counterflow: those outlets are at opposite ends. Check the correctly paired local terminal differences. For parallel flow, use hot inlet minus cold inlet and hot outlet minus cold outlet instead.
You can compare matching inputs using GrowMechanical’s LMTD calculator for heat exchanger design.
Step 6: Apply the flow-arrangement correction factor
The basic corrected design equation is:
Q = UAF × LMTD
F is dimensionless. It accounts for departure from the counterflow reference temperature-driving-force model for the chosen configuration. It is not a safety factor or a fouling allowance.
This worked example uses F = 1 because it assumes ideal counterflow. For a multipass shell-and-tube or crossflow design, obtain F from a validated method for the actual arrangement and terminal temperatures. A generic value such as 0.85 must not be assigned to every shell-and-tube exchanger.
In B21, calculate the effective driving force:
=IF(OR(B10<=0,B10>1),NA(),B10*B20)The result is 42.055 K. The ASHRAE heat exchangers chapter discusses correction factors and the relationship between exchanger configuration, heat-transfer area, and performance.
Step 7: Calculate the required surface area
Rearrange the design equation:
A = Q / (U × F × LMTD)
A = 627000 / (800 × 1 × 42.055) = 18.636 m²
In B22:
=IF(B9<=0,NA(),B14/(B9*B21))The preliminary required area is 18.64 m². Keep unrounded values in formulas and round only displayed results.
U and A must use the same surface-area basis. A coefficient based on the outside tube surface must be paired with outside tube area. For plate exchangers, confirm the manufacturer’s definition of effective area and the corresponding U-value.
This result does not specify a tube count or plate count. Those choices require channel geometry, flow distribution, velocity, pressure-drop limits, and a recalculated heat-transfer coefficient.
Step 8: Check the energy balance and physical limits
In B23, calculate the cold-side duty:
=B16*B6*(B8-B7)In B24, calculate the relative balance difference:
=IF(B14<=0,NA(),ABS(B14-B23)/B14)Format B24 as a percentage. It returns 0% because cold flow was calculated from the same heat balance. This checks spreadsheet consistency, not independent field performance.
Next, calculate heat-capacity rates and the maximum theoretical duty:
| Cell | Quantity | Excel formula | Result |
|---|---|---|---|
| B25 | Hot capacity rate, W/K | =B2*B3 |
20,900 |
| B26 | Cold capacity rate, W/K | =B16*B6 |
41,800 |
| B27 | Minimum capacity rate | =MIN(B25,B26) |
20,900 |
| B28 | Maximum capacity rate | =MAX(B25,B26) |
41,800 |
| B29 | Capacity-rate ratio Cᵣ | =B27/B28 |
0.5 |
| B30 | Maximum theoretical duty, W | =B27*(B4-B7) |
1,358,500 |
| B31 | Required effectiveness ε | =B14/B30 |
0.461538 |
The required effectiveness is approximately 46.15%, below the ideal upper bound of 100%. Passing this check is necessary, but does not establish mechanical or hydraulic suitability.
Optional effectiveness–NTU consistency check
For the same ideal counterflow model, NTU = UA/Cmin. Enter in B32:
=B9*B22/B27The result is 0.71335. The counterflow effectiveness relation gives B33:
=IF(ABS(1-B29)<1E-8,B32/(1+B32),(1-EXP(-B32*(1-B29)))/(1-B29*EXP(-B32*(1-B29))))Then in B34:
=B33*B30The predicted duty is 627,000 W, matching the LMTD result. This is an algebraic cross-check under shared assumptions, not a vendor rating. The counterflow NTU expression is not valid unchanged for a multipass exchanger.
How do U-value and fouling change the required area?
At fixed duty and temperature conditions, area is inversely proportional to U. The uncertainty in U can matter much more than an extra decimal place in LMTD.
| Assumed design U, W/(m²·K) | Required area, m² | Interpretation |
|---|---|---|
| 600 | 24.85 | Lower U requires more area |
| 800 | 18.64 | Worked-example basis |
| 1000 | 14.91 | Higher U requires less area |
These are sensitivity cases, not recommended coefficients for specific exchanger types.
If clean and fouled coefficients share the same area basis, an aggregate fouling resistance can be represented as:
1/Udirty = 1/Uclean + Rf,total
For an illustrative clean U of 1,000 W/(m²·K) and combined, area-normalized fouling resistance of 0.00025 m²·K/W:
Udirty = 1/(1/1000 + 0.00025) = 800 W/(m²·K)
Do not directly add inside- and outside-surface fouling resistances unless both have been converted to a common area basis. Do not apply the same fouling allowance again when the input U already includes it. Service-specific water quality, deposits, cleaning practices, and operating history should determine the actual allowance.
Worked sensitivity: warmer cooling water
Consider an Indian chemical-plant utility system where summer cooling water enters at 35°C instead of 25°C. For this illustrative case, retain the hot duty and specify a new cold outlet target of 45°C.
The cooling-water rise is now 10 K, so required flow becomes:
ṁc = 627000/(4180 × 10) = 15.00 kg/s
The counterflow differences become 90 − 45 = 45 K and 60 − 35 = 25 K:
LMTD = (45 − 25)/ln(45/25) = 34.026 K
Holding U = 800 W/(m²·K) and F = 1 for comparison:
A = 627000/(800 × 34.026) = 23.03 m²
The required area rises by approximately 23.6%, while cooling-water demand rises by 50%. Actual U and pressure drop will also change with flow, so a real summer-condition assessment needs a fresh thermal and hydraulic rating. Increasing water flow alone does not guarantee the original hot outlet target.
What still needs to be checked before ordering equipment?
| Check | Why it matters |
|---|---|
| Pressure drop on both sides | Available pump head and operating cost can limit velocity and geometry |
| Film coefficients and geometry | An assumed U must be replaced or validated for the proposed exchanger |
| Fouling and cleanability | Deposits can reduce heat transfer and restrict channels |
| Pressure, temperature, and materials | Thermal area does not establish pressure containment or corrosion resistance |
| Expansion and vibration | Thermal stress and flow-induced vibration can govern construction |
| Control and operating range | Startup, turndown, and seasonal utility conditions change performance |
For shell-and-tube equipment, TEMA publishes mechanical design standards; its official site identifies the 2026 edition of the TEMA Standards. ASME lists BPVC Section VIII, Division 1, 2025 for pressure-vessel construction. Apply the edition required by the project and governing jurisdiction. This thermal worksheet does not establish compliance with either publication.
Common Excel mistakes and troubleshooting
| Problem | Likely cause or next check |
|---|---|
| Area is 1,000 times too small | Duty entered in kW while U is in W/(m²·K) |
| LMTD gives a division error | Equal terminal differences need their limiting value |
| LMTD is negative or invalid | Wrong endpoint pairing or infeasible temperature targets |
| Area appears unrealistically small | Optimistic U, omitted fouling, or incorrect correction factor |
| Cold-side duty does not match hot side | Inconsistent data, unit conversion, heat loss, or property assumptions |
| Exchanger misses duty despite sufficient area | Check actual U, bypassing, fouling, utility temperatures, and flow distribution |
Protect formula cells, mark editable inputs clearly, and record assumptions beside the input table. Apply validation for positive mass flows, specific heats, and U; require 0 < F ≤ 1. Check that the hot stream cools and the cold stream heats for this model.
Keep a separate scenario sheet rather than overwriting the design basis. Excel installations that use semicolons as argument separators may require replacing commas in the displayed formulas.
Frequently asked questions
Can Excel perform a complete heat exchanger design?
Excel can support preliminary thermal sizing and more detailed calculations when the appropriate validated methods are implemented. This sheet calculates duty, flow, LMTD, and area. It does not replace geometry-specific thermal rating, pressure-drop analysis, or mechanical design.
Which formula calculates heat exchanger area?
Use A = Q/(U × F × LMTD), with Q in watts, U in W/(m²·K), and LMTD in kelvin. The resulting area is in m². U and A must refer to the same heat-transfer surface.
What should I enter when the two terminal differences are equal?
Use their common value as LMTD. For example, if both terminal differences are 30 K, LMTD is 30 K. The guarded Excel formula in this guide handles this limit without a 0/0 error.
Can the cold outlet be hotter than the hot outlet?
Yes, in a suitable counterflow exchanger, because those outlet temperatures occur at opposite ends. The correctly paired local terminal temperature differences must remain positive for this single-phase model.
What U-value should I use?
Use a coefficient justified for the fluid pair, geometry, flow conditions, material, and fouling basis. The example’s 800 W/(m²·K) is an assumption, not a universal value. Validate it through correlations, measured service data, or vendor calculations.
Can this sheet size a condenser or evaporator?
Not directly using the sensible-heat equations shown. Phase change requires enthalpy-based duty, appropriate boiling or condensation coefficients, pressure-dependent saturation temperatures, and sometimes separate zones for sensible and latent heat transfer.
Is this a downloadable Excel workbook?
This article provides cell-by-cell instructions for building the worked example in Excel. GrowMechanical also lists a separate Heat Exchanger Design Excel Sheet product; review its documented scope and sample material before purchasing or applying it to a project.
Use the calculated area as a starting point
The example produces 627 kW duty, 10 kg/s cooling-water flow, 42.055 K LMTD, and 18.64 m² required area under the stated counterflow and U-value assumptions. The most important next step is to validate those assumptions against the actual equipment geometry and operating envelope.
For a spreadsheet-based workflow, review GrowMechanical’s Heat Exchanger Design Excel Sheet. Its product listing is separate from this independently worked tutorial; confirm the included calculations and application limits before use.
Explore more engineering calculation resources at GrowMechanical to connect thermal sizing with piping, utilities, and equipment selection.
