What it does
Uses picker prompt to expedite performing many formula actions (toggling calculation, displaying the formulas, etc.), including some additional actions, such as setting and searching for favorite formulas to enter.

When to use it
When updating and troubleshooting formulas.

Why to use it
This helps you quickly and easily update and troubleshoot formulas by bringing together several formula-related actions in one place.  It also lets you save your favorite formulas you can quickly enter in one step for formulas you commonly enter or search for complex formulas from other files.

Default shortcut
None

Other Details

• Category: Formulas / General
• Difficult: 2/5
• Usage/frequency: 3/5
• Automation factor: 3/5 (estimated 5 seconds saved each time used)
• Type: Shortcut
• Date added: 2/26/2018
• Tags: Formulas, picker, toggle, custom

Related Macros
[T] Toggle Formula Calculations
[I] Wrap If Error
[R] Wrap Round
[D] Remove Indirect

Note: most of the related filter macros above will be covered in the instructions below.  The picker option code is displayed in the [brackets].

Other Articles
None

None

Prerequisites
Most options: select the cell containing formulas you’d like to update.

Instructions
With cell(s) selected per the prerequisites above (if applicable), run the Formula Picker macro.  It will present several helpful actions to run for updating and troubleshooting formulas.  For the action you run most often, you can set your default so you don’t have to enter it (just launch the picker and press Enter).  Note that the default is highlighted when the picker is launched, so to change the action code, there is no need to press the backspace first.  Further instructions for the individual action codes are described below.

• [T] This will toggle the formula recalculation mode between automatic and manual.
• [H] This will toggle displaying resulting formula values and the formulas themselves for the entire sheet.
• [V] Toggle formula/value calculations (to recalculate more quickly).
• [A] When in manual recalculation mode, this will recalculate the formulas in all open files.
• [S] When in manual recalculation mode, this will recalculate the formulas in the current sheet only.
• [Z] For all selected cells, this will wrap the entire formula contents in an IFERROR() function, and allows you to enter the value to use when there is an error.
• [R] For all selected cells, this will wrap the entire formula contents in a ROUND() function, and allows you to enter the number of decimals to round to.
• [D] For all selected cells, this will replace any INDIRECT function references with the resulting direct references.
• [I] For the active (top-left selected) cell, this will insert a formula using the insert formula dialog box.
• [E] For the active (top-left selected) cell, this will enter formula editing mode (mimicking the F2 key).
• [F] Search for or set a favorite formula (see more below).
• [Q] For the active (top-left selected) cell, this will show the formula evaluation dialog box.
• [C] Use to define a custom formula you can reuse over and over (see more below).
• [1-5] For the active (top-left selected) cell, this will insert the custom set formula based on the number entered.

To set the custom formulas for options 1-5, enter C, then the formula to save (copying and pasting an existing formula from the clipboard is usually best), then a space, then the word DEFAULT, then a #, with the # being 1-5.  For example, entering “C=MID(A1,5,8) DEFAULT1” will set custom option 1 to “=MID(A1,5,8)” and you can then enter that entire formula by pressing 1 then Enter.

To set a favorite formula, enter F and Enter, then click the Save Formula button.  This will save the entire formula for the active cell, the cell address, and the active file name, so that it can be used again later.  You can use the same search form to find and insert a favorite formula.  Enter a few characters and it will show you results as you type.  Select one of the options and press Enter to insert it.  You can optionally shift the relative references within formula based on the active selected cell versus the cell that contained the formula when it was saved.

Like most of the picker macros, you can set the default option to apply so you don’t even have to enter the letter/number – just the Enter key.  Just enter your preferred default letter or number, then click the default checkbox, and those letter(s) becomes the new default.

TBD…