FormulaView Tutorial

Spreadsheet formulas can be hard to get right, even when copied from published definitions, like the one for the familiar bell-shaped curve.

Picture of bell curve and defining equation
Mathematical definition of bell curve

A straightforward transcription of this formula into a spreadsheet fails to produce the expected curve.

Spreadsheet with incorrect bell curve
Problematic transcription into Excel

FormulaView, an add-on to Microsoft Excel®, helps diagnose problems like the one in this spreadsheet. To use FormulaView, we select cell B2 in the spreadsheet and click View Formula on the FormulaView tab on Excel’s ribbon.

FormulaView tab on the Excel ribbon
FormulaView tab on the Excel ribbon

This action causes FormulaView to display three views of the formula in cell B2.

FormulaView views of incorrect formula
FormulaView views of problematic formula

  • The Text View shows the formula’s text, as it appears in the formula bar. Nothing looks wrong here.
  • The Math View typesets the formula, making it much easier to read. Nothing looks wrong here either.
  • The Tree View shows how Excel decomposes the formula into smaller expressions, and it shows the values of those expressions in red. The top value looks wrong.

We trace the origin of the suspiciously large value by looking at other values lower in the Tree View. We find the problem when we get to the box containing the unary minus sign from -A2^2: Excel has computed -A2 and then squared the result. We expected it to square A2 first, as is customary in ordinary mathematical usage and most programming languages.

Excel decomposition of -A2^2
Excel decomposition of -A2^2

We fix the problem by going back to Excel and inserting parentheses around the expression A2^2 in the formula in cell B2. This forces Excel to square A2 first and only then to apply the unary minus operation to the result. Then we copy this corrected formula down from cell B2 through cell B32 to get exactly the graph we want.

Spreadsheet with corrected formula
Corrected transcription into Excel