How to edit formulas in Excel

Requirements:

• Windows 10 ver. >= 1903 (build 18362 AND later)
• Excel 2019 (build 11629 AND later)

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:

1. Here is the Formula Editor toolbar.
   Note. 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.
     
     Tip. 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.
2. 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.
   

3. Here is the formula field where you can write or edit a formula.
4. 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.

5. After editing the formula, click Update cell.
   Note. 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.
   
6. 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:
  
  1. 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.
  2. Opt for Format Document to convert your formula to a tree view.
  3. Use the standard Cut, Copy, or Paste options.
  4. To see the list of the available commands, select Command Palette.