Reference: Formula Editor

The formula editor is an interactive editor that enables you to quickly build, evaluate, and debug complex nested formulas. Example: You can change a function argument and then see the effect it has on the results.

Open the editor by clicking the

Formula Editor

icon at the right side of the formula bar.

Note:

The editor icon doesn't display when there is an active connection to the Data Wizard. (You can't use the formula editor for live data formulas.)

The formula editor doesn't support constrained array formulas.

This table summarizes the key features of the editor:

Feature	Notes
Formatting	The row you're actively editing displays in a different color. Syntax highlighting colors are: Functions: green Quoted strings: purple Cell references: blue Operators: brown Error text: red Default: black Indentation of lines and creating new lines is automatic, based on rules such as whether a formula contains nested formulas. When an argument extends beyond the width of the panel, Worksheets wraps the line, and indents lines after the first line by 1 space.
Evaluations	When you click Evaluate , the editor recalculates values and reformats the lines in the formula as needed, such as to redo indentations. The default is to evaluate a formula as the type you initially opened in the editor; you can evaluate a formula as a scalar (non-array) or as an unconstrained formula by selecting the appropriate option from the drop-down menu next to the Evaluate button. The calculated results of the main formula and nested formulas display in a green indicator at the top of the formula. The workbook remains available for editing, such as for dependent cells, while you are editing a formula. The active formula remains in the panel. Example: If you're editing a formula in cell A1, and the results depend on a value in B1, you can click into the B1 cell and change the value; the formula editor continues to display the formula in A1 in the panel. Click Evaluate to update the results based on the change you made in B1.
Error assistance and prevention	To help prevent errors, Worksheets automatically inserts matching grouping characters such as parentheses, braces, and quotes. If you place the cursor to the left or right of one of these characters, the matching character is highlighted. A green indicator at the top of the panel shows the active cell and its calculated value. If an error exists, the indicator becomes red and displays an error message. To view help text about the error, hover over the message. If a syntax error exists in a line of the formula, an exclamation mark icon displays to the left of that line, and the line is highlighted.
Submit the formula and close the panel	Click Save & Close . Notes : When you open the formula editor for a formula, the editor detects if the formula is a standard scalar (single value, non-array) formula or an unconstrained array formula, and submits it appropriately when you click Save & Close . The editor prevents you from losing unsaved changes if you select to close the panel; you can choose to save the changes or discard them. If you select Save & Close without using Evaluate first, the Evaluate action runs before closing the panel. When you close the editor panel, the formula displays again in the formula bar as a single line.