Sophisticated formulas in Excel can be challenging to write and inconvenient to edit in the formula bar or a cell. Formula Editor converts formulas to a tree view so that you can comfortably write, change, or copy them.
Before you start
All changes you make using Formula Editor refer to a cell whose address you can see on the tool pane.
- Windows 10 ver. >= 1903 (build 18362 AND later)
- Excel 2019 (build 11629 AND later)
If your Microsoft Windows and Office versions meet the above requirements but you're getting a message saying "Sorry, the Formula Editor is supposed starting from Excel 2019 build 11629 and Windows 10 1903 build 18362", make sure that you have WebView2 installed. Check this by going to Control Panel → Programs → Programs and Features
You can install WebView2 from the Microsoft website using a download link that is provided under "Evergreen Bootstrapper" at the very bottom of this page: https://developer.microsoft.com/en-us/microsoft-edge/webview2/.
How to edit formulas
Start Formula Editor
To start working with the tool, select the cell with the formula you want to edit and click Formula Editor in the Formulas group on the Ablebits Tools tab.
Get familiar with the Formula Editor pane
The tool pane with your formula in a tree view will show up:
- Here is the Formula Editor toolbar.
By default, the first icon on the toolbar is disabled because the Auto-refresh when selecting another cell
checkbox below the formula field is selected.
You can choose any cell on your worksheet and see the cell contents and address on the Formula Editor pane immediately.
- When this icon is enabled, you can move between cells on a worksheet without displaying cell contents on the Formula Editor pane. If you want to start editing a formula, select the cell you need and then click the Get the formula from the currently selected cell icon. The cell contents are shown in a tree view on the pane.
Note. The first icon gets enabled when the Auto-refresh when selecting another cell checkbox is cleared.
- Click the Format the formula icon to convert the formula you're writing to a tree view.
On the left side of the Formula Editor pane, hover the cursor over the formula field to see arrows showing nested levels of your formula.
Click the arrows to collapse or expand nested levels of the formula.
- If you need to copy the formula you're working with, just click the Copy your formula to clipboard icon:
The formula is copied in its standard view (like it is shown in the formula bar).
- When writing or editing your formula, click the Cell address capture mode icon, and now you can select a cell or range on your worksheet to have the cell or range address automatically inserted into the formula.
Note. When you work in the Cell address capture mode, all changes you make to cells on a worksheet refer to the formula you're currently editing.
- Click this icon and choose how you want your formula to be shown on the pane.
To see every closing bracket in a separate line, choose Cosy style.
Or, select the Compact style option.
- Here is the formula field where you can write or edit a formula.
- If you have the Auto-refresh when selecting another cell checkbox selected, the contents of a cell will be displayed on the pane whenever you select a cell on a worksheet.
Clear this checkbox if you want to move between cells on your worksheet without displaying cell contents on the Formula Editor pane.
- After editing the formula, click Update cell.
If you don’t click Update cell
after changing your formula and select a different cell for editing, a message will appear asking whether you want to save changes made to the formula.
- Click this button to undo the latest change you’ve made to the cell contents.
Edit your formula
- Start writing or editing a formula in the formula field on the pane, and when you type the beginning letters of a function, the Excel Formula AutoComplete shows the Excel functions list to assist you:
- If you want to use a named function or range in your formula or just can't remember the name of an Excel function, type the name of the category to which the function belongs (with a dot (.) after the name of the category, for example, math.) and select what you need from the list.
- Edit multiple parts of your formula simultaneously by adding as many cursors as you need.
For this, hold down the Alt key and select places where you want to have additional cursors.
Or, press Ctrl+F2 and the cursor will be added to all occurrences of the selected part of your formula or of the part where the cursor currently stands.
- When editing your formula, open the context menu with a right click:
- Select Change All Occurrences to find and highlight occurrences of the selected part or of the part where the cursor is to edit all the occurrences as one.
- Opt for Format Document to convert your formula to a tree view.
- Use the standard Cut, Copy, or Paste options.
- To see the list of the available commands, select Command Palette.
Here are the keyboard shortcuts that come in handy when you edit a formula:
Shows the list of all functions. If the list is already displayed, the shortcut shows or hides the selected function syntax and description.
Sets an additional cursor in the formula you're editing. Hold the Alt key and by clicking parts of your formula add as many cursors as you need.
In the formula, adds cursors to all occurrences of the selected part of your formula or of the part where the cursor currently stands.
Converts your formula to a tree view.
Shows a hint on the current function parameter. When the hint is displayed, use arrow keys on your keyboard to move inside the formula and see hints on other parameters.
Alt+Up arrow key
Swaps the current line in the formula tree with the line above.
Alt+Down arrow key
Swaps the current line in the formula tree with the line below.
Shift+Alt+Up arrow key
Copies the current line and pastes it above.
Shift+Alt+Down arrow key
Copies the current line and pastes it below.