How to do FEA Analysis for Heat Transfer using Spreadsheet.

Magical Tool – The Spreadsheet

Sometimes the simplest tools are overlooked by engineers and extensive amounts of time and effort are put into setting up and running complex computations using complicated software and programs. With a little bit of innovation and creative thinking, a simple spreadsheet can be used to run some Finite Element Analysis. Again this is extremely dependent on your application and whether you can easily set up the spreadsheet to find the results you are looking for.

This specific example is regarding heat transfer in a metal plate. We have a heat diffuser with a thermocouple that maintains the temperature at 50 degrees C at one edge of the plate using fans. The other end of the metal plate is attached to a hot object that is 150 C. We would like to see how the plate behaves iteratively and eventually see how the temperature will stabilize in the metal plate. The set up is pretty straight forward and there are various methods and softwares that individuals use to generate these results. Matlab and actual programming can get very complex and will require extensive amounts of work. Using a spreadsheet to run some quick analysis will provide the results needed. You will need to enter the heat transfer coefficients and other relevant information for your FEA into the spreadsheet and pretty much link each box of the spreadsheet to each other. I’ll go through this process step by step.

First Establish Boundaries

The obvious first step is to set up your boundaries. I’ve drawn a section of the plate out in the spreadsheet using rows and columns based on the size and shape of the plate.

Make sure you enter the temperature that will be present at each boundary. The plate itself will be at room temp at the beginning of the 1st iteration.

The edges that are not heated or cooled will have a different boundary condition that will allow for some heat loss into the air but not direct cooling as at the end of the fin.

The critical concept to understand is that you need to make sure that you have proper setup. Understanding boundary conditions and also understanding the problem and properly setting up the analysis for useful results is critical. A lot of assumptions can be made and should be explained when presenting the results. Simplify the problem and make sure that the assumptions you are making are sound and justified. When presenting your results, be sure to discuss these assumptions and make sure you validate them.

Setup of Boundary

Boundary Conditions

Update Settings

In order for a spreadsheet to run iterative calculations, you’ll need to go into >tools > options and check the "Iteration" box. Change the number to whatever works for your application. In this case I am going to look at every 10 iterations and record a screen shot to see how the temperatures change in the fin.

Fix Settings under Tools > Options


Run Analysis

Change the boundary temperatures and the calculations should automatically start running. In the spreadsheet application that I am using, F9 is the key to re-calculate. Hitting this key will force the spreadsheet to update. The screen shots below show the temperature propagation through the plate. This propagation can be saved and converted into an animation if needed for a presentation. The colors change automatically since I used conditional formatting in the spreadsheet. This was a very simple break down where 50 to 75 degrees is green, 75-100 is yellow and 100 to 125 is light red and 125 to 150 is dark red.



Analysis Stabilization

The calculations will stabilize and the results show how the plate will behave with the prescribed boundary conditions. Although this analysis is very simple, it can provide some valuable results. The performance of the fins shows that they are cooling the plate as designed. This can easily be modified to see how wider or thinner gaps between the fins will affect the dissipation of the heat. The most important part of the entire analysis is to simplify the problem as much as possible and then utilize a simple tool like a spreadsheet to run analysis and get some detailed results about how the part designed will perform.

The use of simple circular references in a spreadsheet along with conditional formatting can convert your basic spreadsheet into a powerful FEA tool. Always make sound assumptions and simplify the problem and creative solutions will present themselves to you. Good Luck and Happy Computing!