# Formula Tools

Understand, fix and improve Excel formulas.

### View formulas as Math equations

#### Math Equation Viewer

Formulas can be viewed as Math equations, using standard Math notation - like you learned at school.

When the 'Math Equation Viewer' is enabled:

- Formulas are automatically displayed in Math notation.
- References (cells) can be displayed as their address, their name (if they're a named range), their value or the value in the cell to the left of them.
- If named ranges or 'cells to the left' contain the name of a Greek letter, such as alpha, beta, pi etc. then the equation will display the Greek symbol instead.
- Greek letters can also be written with a leading '\' eg: \pi, \alpha. In fact, if a cell's value is a sentence that includes Greek letters, then the letter names MUST be preceeded with '\', otherwise they will be ignored.

- Copy or save the current equation as an image by clicking the 'Copy' or 'Save' buttons on the menu above the equation.
- When editing a formula, the Math viewer highlights ranges in same colors as formula-bar.
- Save (or copy) any equation or shape on a worksheet by right-clicking it, then clicking 'FormulaDesk > Copy as image' or 'FormulaDesk > Save as image' on the pop-up menu.

#### Document your formulas by adding live (auto-updating) equations to the worksheet

Add the equation displayed in the Math Equation viewer to the current worksheet. 'Live' equations will auto-update when the cell's formula changes.

To add a 'Live' equation, in the Math Equation viewer

- Click the 'Add to sheet (live)' ...or 'Add to sheet (static)' to add a non-auto-updating equation.
- For 'Live' equations, Click 'FormulaDesk > Go to cell' on the right-click menu to go to the source cell which has the formula on which the Live equation is based.

#### Quickly add Greek letters, subscripts and superscripts

- To enter Greek letters into cells, type their name (eg: pi, alpha, beta), or if the cell contains more text in addition to the Greek letter, then prepend the Greek letter name with '\' (eg: \pi, \alpha, \beta).
- To enter subscripts, use an underscore (eg: d_x).
- To enter superscripts, use a hat (eg: d^x).
- Select the cells.
- Right-click the selected cells. In the pop-up menu, click 'FormulaDesk > Convert text to Greek letters and subscripts'
- A form will open, select your options then apply.
- The selected cells will be transformed!

#### Save (or copy) shape and equations as images (or copy as an equation object)

- Select the shape or equation.
- Right-click the selected cells. In the pop-up menu, click 'FormulaDesk > Copy as image' (or 'Save as image').

#### Math symbols

The Math Viewer shows these symbols:

- * displays as ×
- <> displays as ≠
- <= displays as ≤
- >= displays as ≥
- SUM() displays as ∑
- SQRT() displays as √
- SQRTPI() displays as √𝜋
- PRODUCT() displays as ∏
- ABS() displays as ||
- FLOOR.MATH() displays as ⌊ ⌋
- CEILING.MATH() displays as ⌈ ⌉
- FACT() displays as X!
- FACTDOUBLE() displays as X!!
- EXP() displays in e notation
- POWER() displays in power notation
- LOG() displays in 𝑙𝑜𝑔 notation
- LOG10() displays in 𝑙𝑜𝑔 notation
- Intersections display as ∩

### Visualize and understand complex formulas

#### Formula Explorer

The Formula Explorer makes auditing and understanding formulas quick and easy. See what's going on with complex formulas - trace how the result is calculated by inspecting the calculation steps. Instantly answer questions such as:

- Why is that formula's value different from what you expect?
- What values are the nested formulas returning?
- Is the IF statement returning TRUE or FALSE?

#### Inspect Calculation Steps

See intermediate values in a single glance. A staggered view enables quicker understanding ie: no need to continually click the 'Evaluate formula' button multiple times to determine how a value is calculated anymore.

- Hover over parts of a formula to get further information.
- Write your formulas in the single line above the staggered view.

### 'Live' precedents & dependents

See the precedents and dependents of the current cell updating as your move to different cells. Plus, hover over the arrow to view far-away cells (off-screen and even off-sheet) - no need to navigate to them then navigate back.

When 'Show Precedents' and/or 'Show Descendents' is turned on, as you click on different cells, their precedents/descendents are shown in realtime.

- Live preview: hover over an arrow to display the precedent cell and its surrounding cells. This also works for precedents on other sheets (or scrolled off the screen) - no need to switch to the sheet to inspect the value.
- Choose to view direct precedents only, or all levels at once.
- Display arrows and/or highlight the precedents a different color.
- Display precedents/descendents across all sheets, or limit to just the current sheet.
- Pause the 'Live View' mode to stop automatically displaying precedents when other cells are clicked.

### Quickly fix formula errors

#### Formula Explorer

FormulaDesk pinpoints exactly where the error in a formula is. Normally, you have no idea exactly where or what the error is.

- The 'deepest' error is usually the root-cause of 'upstream' errors.
- Hovering over an error displays more information about the problem.

### Combine multiple formulas into a single mega-formula

When you have a formula that refers to other cells which also have formulas, have you ever wished that you could assemble it all into a single formula? Well now you can! Build a formula easily up by splitting it into smaller formulas, then assemble them into a single formula which you can copy and use elsewhere.

Now you can easily create a formula by first splitting it between many cells, then combine them into a single mega-formula.

This makes it possible to compose formulas from small understandable sub-formulas.

When you're finished, copy the formula text from top line of the editor to use it elsewhere.

### Break formulas into smaller steps

One-click to break complex formulas into small steps, which are then written to a new sheet, allowing you to work through the individual steps and gain a thorough understanding of the formula.

### Refactor formulas (rewrite and improve)

#### ...then update all instances of that formula in the entire workbook!

Now you can easily find formulas that contain a specific pattern. For example, say you want to find all formulas that contain the SUM function with three arguments anywhere in the formula *Example: SUM(1, 2, 3)*

It will find 'patterns' even when they are nested deeep inside a formula, no matter how complex the formula is.

The process of 'improving' or 'cleaning up' your formulas is technically known as Refactoring, and is usually applied to source-code in software programs. Now, you can refactor your Excel spreadsheets!

You can define the 'pattern' with placeholders for the variable content eg: **SUM({a}, {b}, {c})**

This pattern will be found in the following formulas:

**=SUM(11, 22, 33)**

One pattern found. Variables:- {a}: 11
- {b}: 22
- {c}: 33

**=A3 + G6 - SUM(H7, F1, 777) + 888**

One pattern found. Variables:- {a}: H7
- {b}: F1
- {c}: 777

**=7 + SUM(11, IF(1 > 2, SUM(A1, B3, C1), 22), 33)**

Two patterns found:**First**pattern found. Variables:- {a}: 11
- {b}: IF(1 > 2, SUM(A1, B3, C1), 22)
- {c}: 33

**Second**pattern found. Variables:- {a}: A1
- {b}: B3
- {c}: C1

Once cells with matching formulas are found, then you can choose to replace the pattern with another pattern. Replacement template text can use the variables defined in the 'find pattern' Eg: Using the example pattern above **SUM({a}, {b}, {c}**, we can write the replacement template text like this: **({a} + {b} + {c})**. If we run this replacement on the formulas above we get the following outputs:

**=SUM(11, 22, 33)**

gets transformed into:**=(11 + 22 + 33)****=A3 + G6 - SUM(H7, F1, 777) + 888**

gets transformed into:**=A3 + G6 - (H7 + F1 + 777) + 888****=7 + SUM(11, IF(1 > 2, SUM(A1, B3, C1), 22), 33)**

gets transformed into:**=7 + (11 + IF(1 > 2, (A1 + B3 + C1), 22) + 33)**

### Replace references in formulas

This enables you to quickly and easily replace references (cell addresses) in formulas.

Specify the reference (use wildcards if you need to ('*' for multiple characters and '?' for single characters)

Eg:

- Sheet3!B45 (finds all formulas with this exact reference)
- Sheet3!B* (finds all formulas with references to any cell in column B of Sheet3)
- Sheet3!* (finds all formulas with references to any cell on Sheet3)

Specify the replacement as exact text, or using the following variables (note the use of curly braces!):

**{Ref.Sheet}**This is the name of the sheet in the discovered reference (if one was specified in the formula) including the '!'. If the discovered reference didn't include a sheet name, then this variable will be blank.**{Ref.Address}**This is the address part of the discovered reference Eg: A5, B3

**Examples:**

- To replace all references to any cells on 'Sheet2' with the same cell address on the 'Totals' sheet:
- Find pattern: Sheet2!*
- Replacement pattern: Totals!{Ref.Address}

- To replace all references to all sheets whose name starts with Sheet (eg: Sheet1, Sheet2, Sheet3) with NewSheet1, NewSheet2, NewSheet3 etc:
- Find pattern: Sheet*!*
- Replacement pattern: New{Ref.Sheet}{Ref.Address}

### Add power to the Formula Bar

Right-click the Excel formula bar to see the context-menu with new features:

**Format formula:**This formats the formula across multiple lines and applies indents to create a more readable display of the formula.**Unformat formula:**This removes any formatting (multiple and indents) from teh displayed formula to display it on a single-line.**Auto-size Formula Bar:**This will increase the height of the formula bar to display the current formula in it's entirety, if possible (there is a maximum height).**Select All (CTRL+A):**Selects the entire formula with a single click. Even better, use the**CTRL+A**shortcut to select all.**Formula Bar Settings:**View the settings form to set whether F2 toggles between the in-cell editor and the formula-bar editor and whether CTRL+A works in the editors. Also set the indent size for formatted formulas.

Shortcuts:

**F2:**Quickly toggle between the in-cell editor and the formula-bar editor.**CTRL+A:**Selects the entire formula with a single click. Even better, use the CTRL+A shortcut to select all.