View formulas as Math equations

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

  1. Click the 'Add to sheet (live)' ...or 'Add to sheet (static)' to add a non-auto-updating equation.
  2. 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

  1. 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).
  2. To enter subscripts, use an underscore (eg: d_x).
  3. To enter superscripts, use a hat (eg: d^x).
  4. Select the cells.
  5. Right-click the selected cells. In the pop-up menu, click 'FormulaDesk > Convert text to Greek letters and subscripts'
  6. A form will open, select your options then apply.
  7. The selected cells will be transformed!


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

  1. Select the shape or equation.
  2. 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.



View Formula Tree

The Formula Tree Viewer displays formulas in a tree-view. Each line displays the portion of the formula together with further information such as value and address if it refers to a range. Further information such as descriptions is displayed for functions and their parameters.


'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

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 the 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.


Find slow formulas (Excel Profiler)

Find slow formulas

FormulaDesk has a profiler which times the calculation speeds of the cells in your file. This is a performance tools that shows you exactly where your spreadsheet is slow and gives you the information you need to make your spreadsheet faster.


It gives you the information you need to make your spreadsheet faster:

  • Calculation times.
  • Volatile cells - get a full list of all volatile cells and their calculation times.
  • Mini bar-charts make comparison easy.
  • Hovering over an address displays the underlying formula.


Find all volatile cells

The profiler can identify all volatile cells. These are cells that contain formulas that re-calculate every single time any cell is edited anywhere, and cause a rippling effect of re-calculations due to other cells referencing them, which in turn recalculate as well. Volatile cells can make your spreadsheets very slow because every little edit you make triggers possibly thousands or millions of re-calculations.


Examples of volatile formulas are: NOW()


Trim bloated files

Fix big Excel files

Excel can get bigger and bigger, for no apparent reason. This is often due to deleting many cells that previously had values. The problem is that their formatting remains, which takes up space and makes the file bigger. It often occurs when copy-pasting large numbers of cells from another file. The ‘Trim Bloated Workbook’ feature will fix this for you and trim down your Excel files, making them smaller.


The 'Trim Bloated Workbook' feature can find these blank rows and columns for you, and gives you the option to delete them too.


Fix the 'Too many different cell formats' error

If you receive a Too many different cell formats error in Excel, the 'Trim Bloated Workbook' features can tell you which custom styles in your spreadsheet are unused and can be deleted.


Keep selected ranges & charts in view


Periscope: Heads-up-display


See any part of your spreadsheet in it's own floating window. It will always show a live view, displaying updates as you edit another part of the spreadsheet.

You can create periscopes for ranges of cells and for charts.


Note: The periscope is view-only. You can't interact with or edit the contents of the periscope window.


  • Right-click a selected range of cells or chart.
  • Click 'FormulaDesk' > 'Create Periscope' on the pop-up menu.

Freeze any columns and rows

Excel only allows you to freeze columns and rows on the left. With FormulaDesk you can now freeze ANY combination of columns and rows and make them appear on the right-hand side of the worksheet. Their values update in real-time as you make changes to your spreadsheet.


Steps:

  1. Select the column(s) or row(s) to freeze by clicking the column or row headers (hold down the CTRL button to select non-contiguous columns or rows).
  2. Right-click the selected column.
  3. Click 'Freeze columns' on the pop-up menu.
  4. Add more columns at any time by following the same procedure above - they will be added to the frozen collection.
  5. To close the frozen column, hover over the frozen columns and a close button will appear above them, at the top right.


Excel subscript & superscript

With keyboard shortcuts too

Subscript and superscript buttons on Excel menu


Finally, subscript and superscript buttons for Excel.


You can even add these buttons to your QAT toolbar for easy access.


Keyboard shortcuts:

  • Subscript: CTRL+=
  • Superscript: CTRL+?


Remove common annoyances

Disable nuisance keys

If you keep pressing special keys by mistake, you can now disable them (just inside Excel) so that they never bother you again while you're working in Excel.


You can disable and ignore the following keys:


  • F1
  • Insert
  • Num-Lock
  • Scroll-Lock
  • Caps-Lock


Easy Select

Select & deselect multiple cells

Easily select and deselect multiple cells and ranges by clicking the 'Easy Select' button to turn 'Easy Selection mode' on, then start making your selections. When you're finished making selections, click the 'Easy Select' button again to turn 'Easy Selection mode' off again.


No need to hold down the CTRL key while selecting - just click and drag to select/deselect what you want.


Get alerted about manual calculation mode

Manual calculation mode assistant


If your workbook calculation mode is set to manual then that setting gets saved when you save the file. This means that when the file is opened by you or anyone else it will be in manual calculation mode. Sometimes that's what you want, but ususally it isn't. Also, when you open a file from someone else, do you remember to check the calculation mode every time, in case the other person saved it in manual mode?


FormulaDesk can alert you when you try to save a file with manual calculation mode on, or when you open a file that has manual calculation mode on.


It also displays an persistent notification in the Excel title-bar when in manual calculation mode. This can be dismissed. Calculation mode can also be changed via this easy-to-access notification area.


Resize & right-size array formulas


Easily resize an array formula by selecting the range you want to resize it to, then right-click the selection and click the 'Resize array' item on the context (pop-up) menu, or the 'Resize array' on the top menu.


Easily right-size an array formula to exactly match it's dimensions by selecting a cell containing the array formula, then right-click the selection and click the 'Right-size array' item on the context (pop-up) menu, or the 'Right-size array' on the top menu.

Find problems in your spreadsheet

Workbook Detective


Use Workbook Detective searches all of your sheets, even the hidden ones, to find and list all:

  • Comments
  • Formula Errors
  • Usages of data connections
  • Invalid data (fails validation rules)
  • Links to external workbooks, where they are used in:
    • Cell formulas
    • Charts
    • Names

Unit Formatting for Cells

Such as:   m²   °C   °F   etc.


Easily add unit number formats to cells. Add more by clicking 'Add/edit unit formats...' on the menu.

Functions

FormulaDesk adds the following functions to Excel:


  • FD.Utility.RemoveVolatility
  • FD.Utility.RunMacroWhenValueChanges
  • FD.Text.ToStringFormat
  • FD.Text.Concatenate
  • FD.Text.Split
  • FD.Text.NumberToOrdinal
  • FD.Text.NumberToWords
  • FD.Text.NumberToOrdinalWords
  • FD.Text.NumberToRoman
  • FD.Text.RomanToNumber



FD.Utility.RemoveVolatility


=FD.Utility.RemoveVolatility(volatileCell, targetAddress)

  • volatileCell: The volatile cell - a proper reference. Can only be a single cell, not a range of multiple cells.
  • targetAddress: The address (text addres, not an actual reference ie: 'A2' not A2) of the cell to update with the value of the volatileCell (volatile cell).

Description: Update the target address only when the volatileCell value changes, not on every recalculation of the volatile cell. This essentially removes the volatility of all dependent cells, as long as they refer to the new target cell instead of the volatile cell. Note: Tracing precedents will stop at the target cell because Excel has no way of knowing that the target cell refers to the volatile cell. In the same way, tracing dependents of the volatile cell won't identify the target cell as a dependent. This function returns the value of the volatile cell.

Example: =FD.Utility.RemoveVolatility(A1, 'A2') where cell A1 has a value of 'Hello'
Output: 'Hello', and A2's value will also be set to 'Hello'





FD.Utility.RunMacroWhenValueChanges


=FD.Utility.RunMacroWhenValueChanges(cellToWatch, macroName, param1, param2, param3)

  • cellToWatch: The cell to watch for when it's value changes.
  • macroName: The name of the macro to run.
  • param1: Macro's first argument [optional - only use if macro needs this parameter].
  • param2: Macro's second argument [optional - only use if macro needs this parameter].
  • param3: Macro's third argument [optional - only use if macro needs this parameter].

Description: This function will run the specified macro with the specified parameters when the value of the target cell changes. ie: changing the value of the target cell causes the macro to run.

Example 1: =RunMacroWhenValueChanges(A1, 'MyMacro1', 111, 'Red', D5) where cell D5 has a value of 'Hello', and MyMacro1 has three parameters.
Output: The macro will be called like this: MyMacro1(111, 'Red', 'Hello')

Example 2: =RunMacroWhenValueChanges(A1, 'MyMacro2') where MyMacro2 has no parameters.
Output: The macro will be called like this: MyMacro2(111, 'Red', 'Hello')




FD.Text.ToStringFormat


=FD.Text.ToStringFormat(format, p0, p1, p2, p3, p4, p5, p6, p7, p8, p9)

format The format string which is the text output you want with placeholders for p0 to p9.
p0: The value to be inserted into the format string.
p1 to p9: [optional] The values to be inserted into the format string.

Description: Replaces the format items in the specified string with the string representation of the specified arguments.

Example: =FD.Text.ToStringFormat('Hello {0}. How is the weather in {1}?', 'Bob', 'New York')
Output: 'Hello Bob. How is the weather in New York?'




FD.Text.Concatenate


=FD.Text.Concatenate(arg, separator, [includeEmptyValues])

  • arg: Array of text values.
  • separator: The separator (text) to join them with.
  • includeEmptyValues (Optional) Specifies whether empty values should be included. Default value is FALSE.

Description: Concatenates the values of a range where the cells have a non-whitespace value.

Example 1: =FD.Text.Concatenate(A1:A3, ', ') where the cell values are: A1: 'Red', A2: 'Blue', A3: 'Green'
Output: 'Red, Blue, Green'

Example 2: =FD.Text.Concatenate({11, 22, 33}, ' - ')
Output: '11 - 22 - 33'

Example 3: =FD.Text.Concatenate({'aa', 'bb', 'cc'}, '*')
Output: 'aa*bb*cc'

Example 4: =FD.Text.Concatenate({'aa', '', 'cc'}, '*', FALSE)
Output: 'aa*cc'

Example 5: =FD.Text.Concatenate({'aa', '', 'cc'}, '*', TRUE)
Output: 'aa**cc'

Example 6: (add new lines)=FD.Text.Concatenate({'Line1', 'Line2', 'Line3'}, CHAR(13) & CHAR(10))
Output: 'Line1
Line2
Line3'




FD.Text.Split


=FD.Text.Split(text, delimiter, indexToReturn)

  • text: Text to split.
  • delimiter: Splits the text into parts, based on the delimiter. Returns the part at position specified by the 'part' parameter. Part is 1 based.
  • indexToReturn: [optional] The index to return. Must be between 1 and the total number of items resulting from the split. If no value is supplied, then an array of the split values is returned.

Description: Splits the text into parts, based on the delimiter. If a value is supplied for the 'indexToReturn' parameter, then it returns the part at position specified by the 'indexToReturn' parameter. If no value is supplied for the 'indexToReturn' parameter, then an array is returned.

Example 1: =FD.Text.Split('a,b,c', ',', 2)
Output: 'b'

Example 2: =FD.Text.Split('a,b,c', ',')
Output: {a, b, c} [ARRAY]




FD.Text.NumberToOrdinal


=FD.Text.NumberToOrdinal(number, genderChar)

  • number: Number to convert. Must be an integer.
  • genderChar (optional): For languages that support gender, 'M' denotes masculine, 'F' denotes feminine, 'N' denotes neutral.

Description: Converts a number into an ordinal string eg: 1 > 1st, 2 > 2nd etc. Note: the current language of the computer will be used.

Example 1: =FD.Text.NumberToOrdinal(2)
Output: '2nd'

Example 2: =FD.Text.NumberToOrdinal(23)
Output: '23rd'




FD.Text.NumberToWords


=FD.Text.NumberToWords(number, culture, genderChar)

  • number: Number to convert. Must be an integer.
  • culture: Language to use eg: 'es' for Spanish, 'fr' for French. See a full list of culture codes: here.
  • genderChar (optional): For languages that support gender, 'M' denotes masculine, 'F' denotes feminine, 'N' denotes neutral.


Description: Converts a number into words eg: 1 > one, 53 > fifty three

Example 1: =FD.Text.NumberToWords(2)
Output: 'two'

Example 2: =FD.Text.NumberToWords(53)
Output: 'fifty three'

Example 2: =FD.Text.NumberToWords(2, 'es')
Output: 'dos'




FD.Text.NumberToOrdinalWords


=FD.Text.NumberToOrdinalWords(number, culture, genderChar)

  • number: Number to convert. Must be an integer.
  • culture: Language to use eg: 'es' for Spanish, 'fr' for French. See a full list of culture codes: here.
  • genderChar (optional): For languages that support gender, 'M' denotes masculine, 'F' denotes feminine, 'N' denotes neutral.

Description: Converts a number into ordinal words.

Example 1: =FD.Text.NumberToOrdinalWords(2)
Output: 'second'

Example 2: =FD.Text.NumberToOrdinalWords(53)
Output: 'fifty third'





FD.Text.NumberToRoman


=FD.Text.NumberToRoman(number)

  • number: Number to convert. Must be an integer.

Description: Converts a number into Roman numerals.

Example 1: =FD.Text.NumberToRoman(5)
Output: 'V'

Example 2: =FD.Text.NumberToRoman(12)
Output: 'XII'




FD.Text.RomanToNumber


=FD.Text.RomanToNumber(roman)

  • roman: Roman numeral to convert eg: VI, XIII.

Description: Converts a Roman numeral into a number.

Example 1: =FD.Text.RomanToNumber('V')
Output: '5'

Example 2: =FD.Text.RomanToNumber('XII')
Output: 12