Last Updated: 5/1/2014



Free Excel/VBA Spreadsheets for Heat Transfer

(and Fluid Mechanics, PDE's, Thermodynamics and Numerical Methods, too)

 

Usage, Disclaimer, Notes


Two-Dimensional, Steady-State Conduction (Updated: 4/16/2013)

This workbook evaluates the analytical solution for steady-state conduction in a unit square with one boundary held at a different temperature than the other three and returns a raised contour plot of the results. This particular problem is used frequently to demonstrate separation-of-variables as a solution technique for PDE's. The user can change the number of terms to be included in the evaluation of the infinite series. A new, second worksheet animates the process of adding terms to the series. Quite a few are needed in this particular problem to get a good solution here - because of the discontinuity at two of the corners and the animation shows the Gibbs phenomenon clearly.   This workbook was updated in April 2013 to work better in more recent editions of Excel.


Periodic Conduction in a Semi-Infinite Body (Updated: 9/26/2013)

This one "animates" the analytical solution for transient conduction in a semi-infinite body subject to a periodically varying temperature at the exposed face. The input parameters used for the plot shown correspond to the annual temperature cycle in the ground (about 3x10E7 seconds), but may be readily changed.


Planck's Law (Updated: 4/12/2013)

This workbook computes and displays the spectral blackbody emissive power for a number of source temperatures. This new version also allows the user to display the spectral blackbody emissive power for a particular temperature and evaluates the integral over a wavelength range selected by the user (replicating the tabulated blackbody radiation functions). (See the cyan-colored lines in the figure below.) Another sheet is this workbook includes tabulated data for the spectral transmissivity of two types of glass, one of them a standard glass, the other a "low-E" (low-emissivity) glass. The function for blackbody emissive power, this tabulated data and Simpson's Rule may be used to find the total transmissivity of the two glasses.   This new version has been updated to run better in newer versions of Excel.


 

Radiation View Factors (Updated: 2/21/09)

 


Air-Water Properties (Updated: 4/20/12)

Five state and transport properties of air and seven of water, all of which are functions of temperature, are included in this module. These user-defined functions may be invoked in a worksheet exactly as are the supplied functions (sine, cosine, sum, etc.) and allow the design engineer to do multiple calculations readily without cumbersome table lookups. These functions were developed for use in the Sweaty Runner and Lister Bag projects. A discussion of the former project assignment may be found in: Ribando, R.J. and Galbis-Reig, V., "Convective Heat and Mass Transfer from a Runner Using Some Modern Spreadsheet Features," Computers in Education Journal, Vol. VIII, No. 4, Oct. - Dec. 1998, pp. 22-28.

As with all items in the HTT software collection, the user is urged to verify the accuracy of these property functions before using them. The NIST Chemistry WebBook is an excellent resource.


Solution of Blasius Equation (Updated: 11/22/05)

This workbook performs a numerical solution of the Blasius equation for flow in a laminar, self-similar, flat plate boundary layer. The Runge-Kutta integration scheme and shooting algorithm used to solve this third-order, non-linear, ordinary differential equation were taken from An Introduction to Computational Fluid Mechanics by C.Y. Chow and embellished with Excel graphics. In the 5/11/04 version tabulated results from a classical source (Howarth's results as reported in Schlicting) were added for comparison with the current solution.


Internal-Flow Convection Correlations (Updated: 3/16/09)

This workbook computes the Nusselt number for forced convection in a circular pipe as a function of the Reynolds (based on diameter) and Prandtl numbers (and where appropriate one or two other parameters). It includes subroutines for laminar, transition and turbulent flows, and for liquid metals. Results for a range of Reynolds and Prandtl numbers are show in this plot. This spreadsheet was developed to aid in verifying our internal flow module


Iteration Demonstrations (Updated: 5/1/2014)

This workbook includes three separate demonstrations of Gauss-Seidel (Liebmann) iteration for the solution of systems of linear equations. The first one, shown in the figure, demonstrates using G-S to solve the system of linear equations arising from the finite-difference discretization of Laplace's equation in 2-D. Another shows application of the Scarborough criterion to a set of two linear equations. The third shows the application of G-S in one-dimension and highlights the difficulty of applying pointwise iterative methods to large systems. The first and third demonstrations are animated.


Advection of a Passive Scalar in One and Two Dimensions (Updated: 5/2/2014)

This workbook is intended for use as demonstrations in an introductory course in Computational Fluid Dynamics and Heat Transfer at the introductory graduate and advanced undergraduate level. It consists of two sheets. On the first one the user can select one of five separate differencing schemes for the transient, one-dimensional advection equation and input the Courant number. Numerical diffusion and dispersion are vividly illustrated as the initially-square wave is advected across the plot. Differencing schemes included are simple upwind, weighted upwind, Quick (third order), Flux-corrected Transport (FCT) and Total Variation Diminishing (TVD).

The second sheet demonstrates use of the upwind and weighted upwind schemes in two dimensions. A cone representing a passive scalar is advected for one orbit in a velocity field corresponding to solid body rotation. The resulting distribution is plotted at 22.5o intervals so as to give the effect of animation.

 


Projectile Motion (Updated: 1/2/04)


Elliptic Grid Generation (Updated: 3/12/04)


One-Dimensional Compressible Flows (Updated: 5/1/2014)

This first workbook includes worksheets for: (1) Isentropic flow with area change, (2) Normal shock functions, (3) Flow with friction (Fanno Line), and (4) Flow with heat addition (Rayleigh Line). Results are given in both tabular and graphical form and the functions used may be used in other calculations. A user form allows you to input derived quantities, e.g., the ratio of static to stagnation temperature, Mach number after a shock, etc., and find the Mach number.

The second workbook is for oblique, planar shocks.  The user will have to become familiar with Excel’s Solver Add-in in order to use this workbook.


Vortex Panel Method (Updated: 4/29/2014)

This workbook implements the vortex panel method for a lifting airfoil.   It closely follows the algorithm in Kuethe and Chow (1986).


 

Vortex Motion above a Runway (Updated: 4/30/2014)

This spreadsheet is an Excel/VBA translation of a Fortran program from C.Y.Chow's An Introduction to Computational Fluid Mechanics. The program tracks the motion of wingtip vortices and shows their motion as induced by each other and influenced by the nearby ground and the ambient crosswind. The user can change the crosswind speed, wingspan, mass of the aircraft and aircraft velocity and watch the impact on the subsequent motion of the vortices. The simple model includes no dissipation of the vortices, but it is well known that trailing vortices may be long-lived and dangerous to following aircraft.


1976 U.S. Standard Atmosphere (Updated: 11/14/2003)

This spreadsheet calculates the temperature, pressure and density corresponding to the 1976 U.S. Standard Atmosphere. The user can input any altitude and the three values are returned. Plots like that seen below for temperature are provided for pressure and density as well. The basics are covered in this slide show.


One-dimensional Wave Equation (Updated: 5/1/2014)

This spreadsheet applies a 3-time-level differencing scheme to the one-dimensional, linear wave equation. The user can watch the time dependence of the wave as a function of spatial position or can see the complete transient as a function of space and time as seen in the contour plot below. The user can choose the Courant number to give an exact solution, a solution demonstrating numerical dispersion or a solution demonstrating a numerical instability. The algorithm is from An Introduction to Computational Fluid Mechanics by C.Y. Chow.


Solution of Linear Equations (Updated: 1/11/06)

The first part of this spreadsheet is an exact implementation of the procedure discussed on page 155 of Engineering with Excel, 2nd Edition, by Ronald W. Larsen, Prentice-Hall E-Source (2005). A 3x3 system of linear equations is solved using the Excel MINVERSE function for the inverse of a matrix. The second part uses a home-made VBA subroutine to accomplish the same thing.


 

Brayton Cycle Gas Turbine Cycle Template (Updated 6/21/2010) – with sound and regeneration!

 


Rankine Cycle Steam Turbine Cycle Template (Updated 6/21/2010)

 


Otto Cycle Spark-ignition, Internal Combustion Engine Template (Updated 5/12/2009) – with sound!

 


Diesel Cycle Compression-ignition, Internal Combustion Engine Template (New 5/28/2009)

 


 

HTTdemosub.xls A spreadsheet demonstrating a lot of features of Excel and VBA (Updated 7/12/2012)

 

        This one includes buttons, scrollbars, functions, subroutines, named ranges and even narration.

 

 

General Reference on Use of VBA with Excel (VBAPrimer.pdf) – Updated 6/21/2010:

Ribando, R.J., "An Excel/Visual Basic for Applications (VBA) Primer," Computers in Education Journal, Vol. VIII, No. 2, April-June 1998, pp. 38-43. A version of this article updated for Excel 2007 may be found here.


 

Greeting Cards  Excel/VBA Greeting Cards for Halloween, Valentine’s Day, Thanksgiving and Groundhog Day

 



Back to Heat Transfer Today Main Page

R. J. Ribando Home