In this post I’m tackling something I’ve been wanting to do for some time now. That is setting up and solving a simple heat transfer problem using the finite difference (FDM) in MS Excel. The aim is to solve the steady-state temperature distribution through a rectangular body, by dividing it up into nodes and solving the necessary equations only in two dimensions. I’m going to illustrate a simple one-dimensional heat flow example, followed two-dimensional heat flow example, all programmed into Excel.

Finite difference analyses (FDA’s) are generally performed to predict the values of physical properties at discrete points throughout a body. In the case of a stationary body where heat transfer is primary phenomena, the temperature could be determined throughout as a function of heating or cooling on the boundaries, and the physical properties (heat transfer coefficient) of the material.

By definition, the FDM refers to a method for the numerical solution of differential equations. The solution to the solving the FDA as a whole that is described here is more methodological, relying rather on Excel’s ability to solve iterative loops, than performing matrix algebra.

A spreadsheet containing both the 1D and 2D samples can downloaded here.

**Purpose**

Now why would I use Excel for FDM’s? There are primarily two reasons, and defines the purpose of this post:

Using Excel for to perform a finite difference analysis (FDA) does however have is its downsides. The system will be greatly simplified, and in the simplest form, the domain is only defined with fixed sized nodes being rectangular in shape.

**Definition**

**1D Heat Transfer**

Heat is flowing through it in the x direction, and because we are only considering the steady state at the moment, the heat flux over the left boundary should be equal to the heat flux over the right boundary. Or in general, the heat flux into the node should be the heat flux out of the node.

The heat balance for the node x can be written as follows, discretized, and reduced to a single equation for the temperature of node x as function of the temperatures to the left and right:

**Boundary conditions**

The above sections covered nodes inside the body, but the nodes on the boundaries need different equations to be solved. For illustration, the problem was defined to have convective heat transfer (cooling) on the left side with a heat transfer coefficient of 5 W/m2K, and have a fixed temperature of 1600°C on the right side. The temperature equation for first node can be derived defining the heat flux into the first node as a function of the heat transfer coefficient:

**Application in Excel**

The next step is to program these equations into Excel and model the temperature profile in one dimension. The following two pictures show how to setup the solution, and then firstly how to enter the equations for node 0, and then the equations for nodes 1 to 8. Node 9 is simply given the value of 1600.

The above is then all that’s required to have Excel solve the heat transfer for our body. Ensure just that “iterative calculations” are enabled in the Excel options, and press F9 until the values in cells does not change anymore. To enhance the display of the results, the colors of the cells can be conditionally formatted to the value in the cells:

**2D Example**

The principles illustrated above in one dimension, can now simply be applied for two dimensions. The following illustrates our example domain. It is a square body, with a fixed temperature at the bottom, convective heat transfer at the top, no heat transfer in the x-direction on the right, and a heat loss value in the x-direction on the left.

**General internal node**

The domain is now divided into nodes in both the x- and y-directions, and the first step is to derive the temperature equation of a general internal node taking into account the heat fluxes in both directions. To follow is the equation that will be programmed for any node not on the boundary:

**Right boundary with no x-direction heat flux**

The temperature equations for the nodes on the right boundary is very similar to the internal nodes, except starting off that the qx is zero:

**Left boundary with heat flux**

The left boundary has a heat flux of -5000W in the x-direction, and the temperature equation can again be derived for all the nodes on the left boundary:

**Top boundary with convective heat transfer**

Finally the temperature equation is derived for nodes on the top boundary, including a term for convective heat transfer to the top:

**Application in Excel**

The above temperature equations define the temperature to be calculated for the boundary nodes, and the nodes on the inside, which could now be programmed into Excel. The equations are programmed for each cell as before, but part of an IF-statement linked to a cell with a TRUE or FALSE value. If the value is TRUE then the formula will be executed, else the value of the cell will be an arbitrary value (in this case 100). This is reset the iterative loops should an error occur and propagate to all cells.

The results above can be interpreted considering the colors provided by the conditional formatting. The warmer colors at the bottom show the body is hotter because of the high fixed temperature, and colder to the top due to the convective cooling. It is also cooler to the left due to the negative heat flux in the x-direction (heat loss to the left).

**Next steps**

This simple example of heat transfer should provide an understanding of the principles to derive and apply the governing equations to perform a FDA. This could now be extended to mass transfer in the same way, and instead of heat flux the mass rate could be modelled using the Navier Stokes equations as basis. Furthermore, chemical reaction in each node could be computed producing another energy source (or sink) term for the heat transfer equations. On the application side, countour plots can be added of the property matrix, and also XY plots of the flux or property value at sections or on the boundaries of the body.