FormulaView Help

Table of Contents

Introduction

FormulaView is an add-on to Microsoft Excel® that helps users understand complicated formulas by presenting multiple views that clarify their meaning and structure.

  • The Text View shows the text of a formula, as it appears in Excel’s formula bar.
  • The Math View shows a mathematically typeset, easier to read version of the formula.
  • The Tree View shows how Excel decomposes a formula into simpler elements.

Excel makes extensive use of parentheses, both to surround function arguments and to constrain the order in which it evaluates a formula. Without parentheses, Excel would not know to compute the sum A1+A2 in the numerator of (A1+A2)/(2*SQRT(B1)) and the product 2*SQRT(B1) in the denominator before before computing their quotient. However, so many parentheses in a formula can make it hard to read.

FormulaView’s math and tree views eliminate the need for users to deal directly with parentheses, thereby making formulas much easier to read.

Three views of a simple formula
Three views of a simple formula

Users can adjust the heights of the three views by clicking the cursor on one of the horizontal dividing lines and dragging that line up or down.

The tree view also provides useful information about the elements in a formula (e.g., their values), and it allows users to simplify formulas by collapsing elements in the tree view and abbreviating their display in the other two views (by replacing collapsed elements with their values or with ellipses).

Collapsed views of simple formula
Collapsed views of simple formula

Controls on the FormulaView tab on Excel’s ribbon allow users to specify whether or not the tree view displays values and how the other two views display collapsed elements.

The FormulaView ribbon
The FormulaView ribbon

Back to top or start of section.

Text View

The Text View displays the text of a formula, just as the user typed it into Excel, but without the leading = sign. For example, the Text View would display the familiar quadratic formula using Excel’s notations for powers and square roots.

Text view of the quadratic formula
Text view of the quadratic formula

It is not possible to edit the text in FormulaView’s text view.

Back to top or start of section.

Math View


The Math View presents formulas as they would appear in a mathematics text. For example, the Math View for the quadratic formula

(-B3 + SQRT(B3^2-4*A3*C3))/(2*A3)

employs the usual mathematical typesetting conventions for fractions, squares, and square roots.

Math view of the quadratic formula
Math view of the quadratic formula

Typesetting conventions for the Math View

The Math View uses the following conventions when typesetting formulas. Here, x, y, and z represent arbitrary expressions that are typeset as x, y, and z.

Expression Representation
Product: x*y x y
Simple fraction: x/y, (x)/y, x/(y), (x)/(y) x y
Fraction with fraction in numerator: x/y/z x y z
Simple power: x^y x y
Power of a power: x^y^z x y z
Less than or equal to: x<=y x y
Not equal to: x<>y x y
Greater than or equal to: x>=y x y
Range union: x, y x y
Range intersection: x y x y
ABS(x) | x |
EXP(x) e x
INT(x) x
LOG10(x) log10(x )
PI() π
SQRT(x) x

Typesetting technology for the Math View


Powered by MathJax

Back to top or start of section.

Tree View

Formulas in Excel specify computations to perform on values represented by constants and values found in spreadsheet cells. The Tree View portrays a formula in a way that clarifies the order in which these computations are performed. For example, the Tree View

Tree View for B3^2 - 4*A3*C3
B3^2 - 4*A3*C3

for the formula B3^2 - 4*A3*C3 shows that Excel evaluates the power B2^2 and the product 4*A3*C3 before it evaluates their difference. The lowest rectangles in the diagram contain labels for primitive values: constants (2 and 4) and references (A3, B3, and C3) to values found in spreadsheet cells. Above them are rectangles that contain labels for operators: exponentiation ^, multiplication ×, and subtraction -. Next to each rectangle for a reference or operator is its value in red.

The Tree View is particularly valuable when formulas become long and complicated, or when Excel does not evaluate them in the order one might expect. For example, the Tree View shows that Excel evaluates -A1^2 as if it had been written (-A1)^2 and that one must write -(A1^2) to get the operations performed in the other order.

Tree View for -A1^2
-A1^2
Tree View for (-A1)^2
(-A1)^2
Tree View for -(A1^2)
-(A1^2)

Structure of the Tree View

The name “Tree View” is motivated by that fact that such diagrams look like (upside down) trees. Each rectangle in the Tree View represents an element in a formula and contains a symbol that identifies the type of that element:

  • a constant (e.g., 2 and "New York"),
  • a reference to a cell or range (e.g., A1 and B1:B2),
  • a user-defined name (e.g., floor),
  • an operator (e.g., +), or
  • a function (e.g., SQRT).

For increased readability, the Tree View substitutes common mathematical symbols for the notations used by Excel:

  • × for *,
  • for <=,
  • for >=,
  • for <>,
  • for a comma used as a range union operator,
  • for a space used as a range intersection operator,
  • {...} for an array constant, and
  • ... for a row in an array constant.

Each rectangle that contains a symbol for an operator or a function represents a decomposable expression. Immediately beneath it in the Tree View are other rectangles, called its children and connected to it as in an organization chart or family tree, that correspond to the operator’s operands or to the function’s arguments. Thus elements representing 4, A3, and C3 are children of an element representing 4*A3*C3, and an element representing A1 is a child of an element representing SQRT(A1).

The lowest elements in the Tree View represent indecomposable elements: constants, references, user-defined names, and functions without any arguments (e.g., PI()).

No parentheses appear in the Tree View.

Values in the Tree View

Next to each element in the Tree View (unless it represents a constant) is the value computed by Excel for the expression it represents. This display of values can be turned on and off by an option on the FormulaForge tab on Excel’s ribbon.

Labels that would contain more than 15 characters are truncated; an ellipsis (..) at the end of a label indicates that it has been truncated. To see the entire label, the expression represented by the element, or the value of that expression, hover the cursor over the element and look in the tooltip that pops up.

Manipulating the Tree View

An element in the tree may be collapsed or expanded by clicking the triangle just beneath it. A collapsed element is distinguished from an indecomposable element by the presence of the expand icon just beneath it.

Expanded element
Expanded element
Collapsed element
Collapsed element

Back to top or start of section.

Element selection

Click an element in the Tree View to select that element. FormulaView highlights the element in all three views.

Selecting an element in the quadratic formula
Selecting an element in the quadratic formula

Alternatively, click a symbol in either the text or math view to select the smallest element containing that element.

Click an empty space in one of the three views to clear the selection.

Element navigation

To change the selection, click another element or use the control key in conjunction with an arrow key, as follows.

Key pressed Action
Control-up arrow Select parent of current selection
Control-down arrow Select first child of current selection
Control-left arrow Select sibling before current selection (e.g., previous argument of a function)
Control-right arrow Select sibling after current selection

There is no change in the current selection if there is no element in the indicated direction (e.g., no parent for Control-up, no child for Control-down).

Back to top or start of section.

Installation

Downloading FormulaView

Go to formulaforge.com and follow the menu navigation to Products/FormulaView. At the bottom of the page click the button labeled Download Now. That will take you to a page where you will be asked to enter your name and an email address. This is simply to be able to contact you regarding product updates and other products. We will not give this information to any other company. Once you have entered that information, click the button labeled Download FormulaView to download a file named InstallFormulaView.zip.

Installing FormulaView

Double click the downloaded .zip file to extract its contents. This will create a directory named InstallFormulaView, which will contain two files and one directory.

  • setup.exe
  • Getting Started.pdf
  • FormulaView.vsto
  • Application Files (a directory)

You can move the InstallFormulaView directory anywhere you like, but you shouldn’t change its contents.

Double click setup.exe and follow the instructions. If your Microsoft Windows installation does not have either the Microsoft .NET Framework 4 Client Profile or the Microsoft Visual Studio 2010 Tools for Office Runtime packages, they will be installed for you along with FormulaView. These are standard Microsoft software packages that support third party applications. You may have to reboot your machine to complete their installation.

You can delete the InstallFormulaView directory after installation is complete.

Uninstalling FormulaView

To uninstall FormulaView, select Programs > Uninstall a program from the Windows 7 Control Panel, select FormulaView in the list of installed applications, and click the Uninstall button just above the list. For Windows XP, select Add or Remove Programs from the Control Panel, select FormulaView in the list of installed applications, and click Change/Remove in the highlighted area.

Terms of use

Permission is hereby granted, free of charge, to any person obtaining a copy of this software, to use, copy, and/or redistribute it, subject to the following conditions:

You may not alter, decompile, modify, or adapt this software in any way.

This software is provided “as is”, without warranties or conditions of any kind, express or implied, including without limitation any warranties or conditions of title, non-infringement, merchantability, or fitness for a particular purpose. Persons using this software are solely responsible for determining the appropriateness and safety of its use, and they assume the entire risk of its use. In no event shall the authors or copyright holders be liable for any claim, damages, or other liability, whether in an action of contract, tort or otherwise, arising from, out of, or in connection with the software.

These terms of use shall be conveyed with all copies of this software.

Back to top or start of section.

Limitations

The current release of FormulaView has the following limitations.

Expiration date

This release of FormulaView will not operate after September 1, 2013. Users should check the FormulaForge.com website for information about using FormulaView after that date.

Features requiring access to the Web

The Math View uses the web-based typesetting service provided by MathJax.org. Hence the Math View is available only if that service is available.

When the Help button on the FormulaView ribbon is clicked, FormulaView uses Internet Explorer to access help files at FormulaForge.com. Hence help is available only if Internet Explorer is installed on the user’s computer and the FormulaForge website is accessible.

Notations in formulas

FormulaView does not recognize R1C1 notations for references.

FormulaView treats identifiers such as ABCD2 and A1234567 as references to cells, rather than as user-defined names, even though they exceed Excel’s limits on the maximum numbers of rows and columns in a spreadsheet.

FormulaView does not recognize structured references, such as Inventory[InStock], to columns in tables.

Array expressions

FormulaView may display incorrect values for expressions that occur in array formulas or in functions like SUMPRODUCT, which treat their arguments as array formulas.

Back to top or start of section.