Machine Design

Spreadsheet software — an unappreciated engineering tool

Like many engineers, I'm often busy and need to get answers fast.

— John Doyle

My program of choice to flesh-out calculations is Microsoft Excel. Windows and the Office Suite are ubiquitous, so Excel is always available and everyone can use and read it.

That said, with so many software utilities available, it would be easy to just slob-out and let "black-box solvers" take over. But those of us wanting to keep our engineering muscles in tone are better off thinking through problems by encoding spreadsheets and using trusted engineering-theory books.

In fact, working with spreadsheets forces users to completely understand the mechanics of a problem. This puts users in a better position to devise innovative solutions. And taking a fellow engineer through a few calculations gets to the nub of a problem more convincingly than any amount of complex data or analysis.

Excel lets users parameterize 3D models, sort databases, and postprocesses finite-element results. And it has other features that users sometimes aren't aware of. They include:

Goal Seek, basically a NewtonRaphson numerical algorithm. Imagine having a spreadsheet calculation for the second moment of area of a given shape with a particular width. Back-solving the calculation solves for the width that gives a particular value of the second moment of area. This problem is hard to solve algebraically with a pen and paper, but Goal Seek sorts results in seconds.

Formula Auditing lets users trace cell formulas to find dependant and precedent cells. Colored boxes and arrows make it simple to check formulas. Research shows that most spreadsheets contain at least one error, so it's wise to check and validate your solutions.

VBA Macros. The Excel VBA (Visual Basic for Applications) programming language lets users create macros or small procedures to perform complex tasks in applications such as Excel, Word, Power Point, and Access.

Add-ins developed using VBA make the base program better yet. For example, XlXtrFun.xll is a collection of functions that interpolate, extrapolate and curve-fit data. Another handy add-in, ChangeUnits, converts units directly in Excel. Both add-ins are free downloads from the Internet at and

On the downside, a lot of users complain that Excel makes underlying equations hard to view. The Show Formulas feature, for instance, displays X=AA415^2/(BT$6+S$3)^3, an equation far from transparent. And Microsoft's so-called Equation Editor might be good for certain tasks, but it can't interact with cell formulas.

Fortunately, a free download from dynamically displays cell formulas as equations. The XLC add-in makes it easy to check cell formulas directly against mathematical expressions so users can make sure they didn't screw up.

Excel, of course, comes from Microsoft Corp., One Microsoft Way, Redmond, WA 98052,

John Doyle runs MoreVision, a mechanical engineering consultancy, Doyle developed the XLC add-in tool.

The XLC add-in tool for Excel software dynamically displays cell formulas as equations.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.