Formula Editor Help Guide
The Excel Formula Editor can be used the same way as the default cell input built into excel, but better.
- Begin typing a formula into the editor. As you type the name of an excel function or table/column name, an autocomplete menu will appear with matching completion options.
- When writing, clicking, or arrowing through a function, help text will appear to show you the function parameters.
- Selecting a cell reference or a reference to a range of cells in the editor will highlight the associated cell(s) in the worksheet.
- As opposed to traditional excel formula entry, there is no need to click out of the editor to select a cell or range for submission. The editor will either submit to the currently highlighted cell, or you can specify a cell reference anywhere in the sheet. (More details below.)
- You can navigate through excel sheet to get to your target cell from directly within the editor, by hitting Alt + arrow key (up, down, right, left) and the excel selection will move in the appropriate direction and by the amount of times you hit the key combination. You can also select these move commands from the command palette. (use option key for mac.)
- Unlike the basic excel editor, you can add the cell reference to the formula and it will populate that cell, e.g.
B1=SUM(1, 2, 3). You are no longer restricted to entering a formula only in the highlighted cell.
- You can enter multiple formulas at the same time. Just use a ';' to end one function and to begin your next. When submitting multiple functions, you need to specify the cells reference for each formula in your list, e.g.
- You can apply a formula to a range of cells, e.g.
- Unlike the built in excel input, you can use spaces and indent your code to make it more readable and easier to follow. The editor will also automatically indent and adjust spacing as your function grows.
A1 = IF( SUM(B1:B5), "Positive Result", "Negative Result )
- Formula editor offers an auto formatter that will automatically format your excel function into digestible chunks. You can either use the command palette or use hot keys Ctrl + Shift + F. (cmd for mac)
- Find previously submitted formulas for review or reuse by hitting Ctrl + Alt + Up/Down to cycle through previous/next formulas, respectively. These commands are also accesible via the command palette.
Using the Menu
- Get Range - Will copy the selected range in excel to the editor as a range reference.
- Get Selected Formulas - Will get all formulas in currently selected cells in excel, and paste the formulas and their associated cell references into the editor.
- Get All Formulas - Similar to Get Selected Formulas, except it will get all formulas in the entire worksheet.
- Submit - Will submit the formulas within the editor to excel. If a cell reference is provided, it will place the formula in that cell. If the cell referene is omitted, it will put the formula in the currently selected cell.
Using the Command Palette
- Command palette is a shortcut for all custom functionality provided by the Formula Editor. You can run a specific command by clicking on it in the palette, hitting the hot keys, or by typing in the desired command and hitting enter in the palette input.
- If there are keyboard shortcuts available, they will be shown next to the function in the command palette. Mac users, replace alt with option and ctrl with command keys.
Addtional Editor commands
The commands below are built into the ace editor and are available by default, as well.
|Alt-Shift-Down||Command-Option-Down||Copy lines down|
|Alt-Shift-Up||Command-Option-Up||Copy lines up|
|Alt-Down||Option-Down||Move lines down|
|Alt-Up||Option-Up||Move lines up|
|Alt-Delete||Ctrl-K||Remove to line end|
|Alt-Backspace||Command-Backspace||Remove to linestart|
|Ctrl-Backspace||Option-Backspace, Ctrl-Option-Backspace||Remove word left|
|Ctrl-Delete||Option-Delete||Remove word right|
|Ctrl-Shift-Left||Option-Shift-Left||Select word left|
|Ctrl-Shift-Right||Option-Shift-Right||Select word right|
|Shift-Home||Shift-Home||Select line start|
|Shift-End||Shift-End||Select line end|
|Alt-Shift-Right||Command-Shift-Right||Select to line end|
|Alt-Shift-Left||Command-Shift-Left||Select to line start|
|Shift-PageUp||Shift-PageUp||Select page up|
|Shift-PageDown||Shift-PageDown||Select page down|
|Ctrl-Shift-Home||Command-Shift-Up||Select to start|
|Ctrl-Shift-End||Command-Shift-Down||Select to end|
|Ctrl-Shift-P||---||Select to matching bracket|
|Ctrl-Alt-Up||Ctrl-Option-Up||Add multi-cursor above|
|Ctrl-Alt-Down||Ctrl-Option-Down||Add multi-cursor below|
|Ctrl-Alt-Right||Ctrl-Option-Right||Add next occurrence to multi-selection|
|Ctrl-Alt-Left||Ctrl-Option-Left||Add previous occurrence to multi-selection|
|Ctrl-Alt-Shift-Up||Ctrl-Option-Shift-Up||Move multicursor from current line to the line above|
|Ctrl-Alt-Shift-Down||Ctrl-Option-Shift-Down||Move multicursor from current line to the line below|
|Ctrl-Alt-Shift-Right||Ctrl-Option-Shift-Right||Remove current occurrence from multi-selection and move to next|
|Ctrl-Alt-Shift-Left||Ctrl-Option-Shift-Left||Remove current occurrence from multi-selection and move to previous|
|Ctrl-Shift-L||Ctrl-Shift-L||Select all from multi-selection|
|Left||Left, Ctrl-B||Go to left|
|Right||Right, Ctrl-F||Go to right|
|Ctrl-Left||Option-Left||Go to word left|
|Ctrl-Right||Option-Right||Go to word right|
|Up||Up, Ctrl-P||Go line up|
|Down||Down, Ctrl-N||Go line down|
|Alt-Left, Home||Command-Left, Home, Ctrl-A||Go to line start|
|Alt-Right, End||Command-Right, End, Ctrl-E||Go to line end|
|PageUp||Option-PageUp||Go to page up|
|PageDown||Option-PageDown, Ctrl-V||Go to page down|
|Ctrl-Home||Command-Home, Command-Up||Go to start|
|Ctrl-End||Command-End, Command-Down||Go to end|
|Ctrl-L||Command-L||Go to line|
|Ctrl-Down||Command-Down||Scroll line down|
|Ctrl-Up||---||Scroll line up|
|Ctrl-P||---||Go to matching bracket|
|---||Option-PageDown||Scroll page down|
|---||Option-PageUp||Scroll page up|
|Ctrl-Shift-Z, Ctrl-Y||Command-Shift-Z, Command-Y||Redo|
|Ctrl-,||Command-,||Show the settings menu|
|Ctrl-Enter||Command-Enter||Enter full screen|
|Ctrl-Shift-U||Ctrl-Shift-U||Change to lower case|
|Ctrl-U||Ctrl-U||Change to upper case|
For any issues, ideas or feedback please contact us at: email@example.com