Thermal Property Tables in Excel: Build Them Right
The most reliable way to get thermal property tables into Excel depends on the material. For water and steam, use a function-based add-in or library built on the IAPWS-IF97 standard. For other fluids, use NIST’s REFPROP through its DLL. For solids, use a source that gives properties as temperature-dependent curves, then store those curves on a clearly labeled sheet with units and a source for every row. A pasted table of single room-temperature values is the weakest option, and it is the one most spreadsheets still use.
This guide shows how to choose among those options, how to structure the sheet, and how to check the numbers before they reach a design calculation.
TL;DR
Match the method to the material: IAPWS-IF97 functions for water and steam, REFPROP for other fluids, temperature-dependent curves for solids.
Static lookup tables are limited by fixed intervals and interpolation error. Functions or fitted equations return a value at the exact input you need.
A single thermal conductivity value is a common source of error in thermal and CFD work. Use a curve over the temperature range you actually model.
Every table needs units, a source, a formulation version and a validity range. Check against a primary source before finalizing safety-critical work.
Our Excel-based toolkit includes a built-in property database, so you can skip building the lookup layer yourself.
Table of Contents
Three kinds of property tables, three different problems
“Thermal property table” covers very different data. Treating them alike is where most spreadsheet errors start.
Material class | Typical properties | Best Excel approach | Main risk |
Water and steam | Enthalpy, entropy, density, cp, viscosity, conductivity | Add-in functions based on IAPWS-IF97 | Outdated formulation in a community file |
Other fluids and mixtures | Density, viscosity, conductivity, surface tension | REFPROP called from VBA | Using values outside the valid range |
Solids (metals, plastics, ceramics) | Conductivity, specific heat, expansion, modulus | Temperature-dependent curves or fitted equations | One room-temperature value used across a wide range |
The rest of this article takes each in turn.
Water and steam: use the international standard
If your spreadsheet touches steam, the first question is which formulation sits behind the numbers. IAPWS approved a new formulation in 1995 that replaced the 1984 formulation and now serves as the international standard for water and steam properties. The industrial version, IAPWS-IF97, is what most engineering software and add-ins implement.
You have a few ways to bring it into Excel:
Free university and open-source add-ins. X Steam provides water and steam properties with releases for MS Excel, MATLAB and OpenOffice. A Water97 add-in from a Hungarian university provides functions for thermodynamic and transport properties of water in SI units. The Chair of Thermodynamics at Ruhr University Bochum publishes software for the IAPWS-IF97 formulation, with licensing information on its page.
Commercial add-ins. These exist for several Excel versions. Check that the bitness (32 or 64 bit) matches your Office install before you rely on one.
Your own implementation. Possible, because the IF97 equations are published, but it takes real validation effort. Only do it if you can compare your output against a reference.
Whichever you pick, read the documentation for the formulation name. Older files built on the superseded IFC-67 formulation circulate widely, and a spreadsheet that does not say which formulation it uses is one to distrust.
Why a function beats a printed steam table
A classroom steam table lists values at fixed pressures and temperatures. Between rows you interpolate. That works for a homework problem, but it adds error, and it is slow if you need many states. With a function, you type something like =Enthalpy(P,T) in a cell and the sheet returns the value at your exact input. Change the pressure and every dependent cell updates. That is the shift from table to model, and it matters most when you sweep a parameter or run an optimization.
The same idea has a long history in teaching. Papers on spreadsheet tools for thermodynamic properties and on Excel steam table macros in thermodynamics courses exist because instructors wanted students to stop reading tables by hand. The Google results for this keyword are largely those papers.
Other fluids: REFPROP and its Excel link
For refrigerants, hydrocarbons, cryogens and mixtures, the standard reference is NIST’s REFPROP. Its documentation describes a program that calculates the thermodynamic and transport properties of industrially important fluids and their mixtures. It can be called from Excel through a DLL, and the documentation covers calling it from VBA or Python. Thermal conductivity, viscosity and surface tension are among the properties available.
Two practical points:
REFPROP is licensed software from NIST, so budget for it and check the terms for your organization.
Every fluid model has a valid range. If your cell asks for a state outside it, a good wrapper returns an error. Do not wrap the call in a way that hides that error with a zero or a blank.
For fluids you use rarely, a small hand-built table with a cited source may be enough. Then follow the structure in the next section.
Solids: the problem with one number
Handbook tables for metals and plastics usually list one thermal conductivity at about room temperature. That is fine for a quick check at room temperature. It is a problem when your part runs from cryogenic to several hundred degrees Celsius, because conductivity, specific heat and expansion all move with temperature, sometimes a lot.
A constant-value input in a thermal or CFD model is a frequent cause of results that look clean and are wrong. If you simulate a part between 25°C and 500°C, you want properties across that range, not the value at one end of it.
There are three common ways to store a temperature-dependent property in Excel:
A two-column table with interpolation. Simple and transparent. Use FORECAST, INDEX/MATCH or XLOOKUP for linear interpolation, and keep enough points that linear interpolation between them is defensible.
A fitted polynomial or other equation. Compact, smooth, and easy to use in a formula. Record the temperature range it was fitted over and never extrapolate past it.
A named range or lookup per material. Useful when a workbook handles several materials and you want one property function that takes a material name and a temperature.
We cover the full workflow in 5 steps to solver-ready temperature dependent properties, including how to format data so a solver can use it. For high-temperature cases, 600°C+ heat transfer for engineers explains why radiation and property drift change the picture.
A worked example: why interpolation interval matters
The numbers here are hypothetical, chosen to show the mechanism, not data for a real material.
Suppose a material’s conductivity follows a gently curved function of temperature, and your table lists it only at 300 K and 700 K. At 500 K, linear interpolation returns the midpoint of the two values. If the true curve bows above or below the straight line, which most real curves do, the midpoint is off by the amount of that bow. The error is zero at the table points and largest in the middle, so a table that looks perfect when you spot-check its own rows can still be wrong where your part spends most of its time.
Two fixes work. Add rows where the curve bends most, or replace the table with a fitted equation and check the fit against the original points. Either way, plot the table against your interpolated values before you trust them. A chart takes thirty seconds and shows the kink or the overshoot at once.
How to structure a property sheet
A good property sheet is boring and consistent. Here is a layout that holds up when someone else inherits the file.
Create a dedicated Properties sheet. Keep raw data away from calculations so a change in data is visible and traceable.
One block per material or fluid. Put the name, source, date retrieved and formulation or edition at the top of each block.
Put units in the header of every column. Write them in full: W/(m·K), J/(kg·K), kg/m³. Use one unit system across the workbook, and convert once, in one place.
State the valid range. Add minimum and maximum temperature cells next to each block, and have your lookup return a visible error outside them.
Name your ranges. A formula that reads k_steel304(T) or a named range is easier to audit than one with a block of cell references.
Add a check column. Compare a few lookups with a trusted reference value and show the difference. If the difference exceeds a tolerance you set, flag it.
The unit point deserves emphasis. Mixed units are a classic source of silent error in thermal and structural checks. Our article on a 60°C rise producing 144 MPa of thermal stress walks through unit-aware checks of exactly that kind.
Validate before you trust the numbers
Open-access repositories and personal spreadsheets are fine for learning and quick estimates. For safety-critical design, treat them as reference material only and confirm the values against manufacturer datasheets or a verified primary source before you finalize a calculation. IAPWS, which maintains the water and steam standards, publishes technical guidance you can check formulation names against.
Use this checklist for any table you did not build yourself:
Check | What to look for |
Formulation or edition | Named in the file, and current (for water and steam, IAPWS-IF97) |
Units | Stated for every column, and consistent with your model |
Valid range | Stated, and your inputs fall inside it |
Source | A standard, a datasheet or a publication, not “found online” |
Spot check | Two or three values match a reference you trust |
Behavior at limits | Returns an error out of range, rather than a plausible wrong number |
If the file fails more than one of these, rebuild the data from a primary source rather than patching it.
Finally, test the physics, not only the data. If you are building a heat transfer model in a spreadsheet, compare it against a case with a known answer. Our walkthrough of 2D finite difference heat transfer in Excel with validation shows how, and thick wall cylinder temperature, from log formula to FEM validation gives a closed-form benchmark.
Our perspective: tables are a means, not the goal
We build thermal and CFD tools for engineers and students, and the property question comes up in almost every model we see. Our view is plain.
Most people searching for property tables do not actually want tables. They want the right value at the temperature their part sees, with a clear source, inside a calculation that already works. A table is the oldest way to deliver that, and it shows its limits as soon as the temperature range widens or the model starts iterating.
That is why we built our Excel-based toolkit around variable material properties, a wide temperature range and a built-in property database, with conduction, convection and radiation in the same workbook. The properties feed the heat transfer models directly, so you are not copying numbers between a table and a calculation and hoping the units survive the trip. We also think the basics should not sit behind a licensing barrier, which is why some of our engineering tools are free.
Our advice, whichever tools you use: spend your effort on the first lookup, not the hundredth. Get one material’s property curve right, plot it, check it against a source, and only then scale the workbook up.
— Joel
Where our toolkit fits
If you would rather not assemble a property layer yourself, the Jewlz Engineering Toolkit is a downloadable Excel-based tool for thermal analysis and heat transfer, with a built-in property database and support for temperature-dependent properties. It also covers CFD and pressure vessel simulation. For a specific starting point, see thermal analysis and heat transfer tools or, for cold storage, the cryogenic pressure vessel and boil-off simulator. If you are also choosing materials, our material selection software guide compares the criteria that matter.
Common mistakes to avoid
Using a thermal conductivity from a single datasheet row for a whole temperature range. Plot the curve first.
Trusting an unlabeled steam table. If it does not name its formulation, assume it may be outdated.
Extrapolating a fitted equation. A polynomial that fits well from 300 K to 600 K can behave badly at 900 K.
Mixing °C and K inside a formula. Convert at the input and keep one convention inside the workbook.
Ignoring interface effects. In an assembly, contact resistance can matter more than a small error in bulk conductivity. See our thermal interface materials guide.
Skipping the heat transfer model choice. Good properties do not rescue the wrong model. Our heat transfer model guide covers methods and math.
FAQ
Does Excel have built-in thermal property tables?
No. Excel has no built-in data for steam, refrigerants or engineering materials. You add data yourself, as a lookup sheet, or through an add-in or DLL-based link such as the ones for IAPWS-IF97 and REFPROP. A toolkit workbook with a built-in database, like ours, also supplies it.
Which steam table formulation should I use?
Use IAPWS-IF97 for industrial calculations. It is the current international standard for water and steam in industry, and IAPWS describes it on its technical guidance page. If a file does not state its formulation, check a few values against a reference before using it.
How do I interpolate a property table in Excel?
For linear interpolation, use FORECAST on the two bracketing rows, or XLOOKUP with INDEX to find them. Keep the temperature column sorted. If the property curves strongly, add points or use a fitted equation, and chart the result against your source data.
Can I use free steam table spreadsheets for design work?
Free open-source and university add-ins, such as the ones linked above, are good for learning and estimates. For safety-critical design, validate them against a primary source or verified datasheet first, and confirm which formulation they use.
Why do my simulation results change when I switch to temperature-dependent properties?
Because conductivity and specific heat vary with temperature, a constant value is only right at one point. Over a wide range the difference can be large. That is expected, and it is usually the more accurate result.
Sources
Recommended
Comments