Formula Editor

Enable Editing

Introduction

A modern code editor for Excel functions and formulas.

Do you want to master Excel functions and formulas? Do you want to quickly understand and edit long and complex formulas? Do you want to write correct, succinct, efficient and powerful formulas? Do you want to build error-free and fast spreadsheets?

Functions and formulas are elementary components of spreadsheets and spreadsheet language; they are at the core of Excel editing and programming. However, the current formula editing is constrained by the narrow formula bar or single cell boxes. Formula Editor provides features that existing tools don't have, and helps you to better understand, optimize, and write functions and formulas. Therefore, it assists you to build safe, neat and powerful spreadsheets.

At the moment, Formula Editor has two major functionalities, more features are coming soon:

Formula Optimization:

  • Formula Editor automatically identifies in your formula the part that can be improved in terms of correctness, readability or efficiency.
  • It suggests better ways to rewrite your formula, and explains to you why they are better, by texts and by videos!
  • You could accept its suggestion of optimization by just clicking on Quick Fix.

Formula Formatting:

  • Formula Editor automatically formats a long and complex formula in several lines with appropriate with whitespace indentation and proper nesting.
  • Therefore, you could understand better the logic of the formula.

Screenshots

Enable Editing

Instructions

  1. Open your Excel file or our sample file.
  2. Load the Formula Editor add-in, click on the Formula Editor button in the ribbon under the Formula Editor tab.
  3. Turn the toggle On.
  4. Select a cell that contains a long and complex formula (e.g., containing IF or VLOOKUP function), then you will see how it is formatted in the editor. If our algorithm considers unnecessary to change the format of the formula, the formula will be unchanged.
  5. If Formula Editor detects a part of the formula could be optimized, that part will be underlined. Hover over the underlined part to see the suggested optimizations. Click on the hyperlinks of webpages or videos in the hints to learn more about the reason of the optimization. Click on Quick Fix to accept a suggested optimization.
  6. Navigate through cells in the workbook, the editor always tries to format the formula of the selected cell, and to suggest hints.
  7. You could edit the formula in the editor, that will not impact the cell content immediately.
  8. Anytime, Format (resp., Unformat) button tries to format (resp., unformat) whatever in the editor.
  9. Manually adjusting Width Limit gives you different formats of a formula.
  10. Finally, the Unformat & Write to cell button tries to unformat the formula in the editor and write it back to the selected cell.
  11. Turn the toggle Off when you want to disassociate the editor to your selection on the workbook.

What's special?

For the time being, Formula Editor is the only one editor of Excel functions and formulas in the Microsoft add-in store. Formula Editor is built by fancy technologies to enable its creative functionalities:

  1. It is our analyzer of spreadsheet files and formulas that powers Formula Editor behind the screen. We wrap the analyzer and make it a language server of Excel language that conforms to Microsoft Language Server Protocol. The advantage of this is to ensure the implementation of our language server standardized and independent of the editor.

  2. Then, we chose Monaco Editor to build the interface of Formula Editor. Monaco Editor is the code editor that powers the largely-used VS Code. It is very web-friendly, and has many advanced features that we leverage and will leverage for formula editing, such that coloring, IntelliSence, auto-complete, etc.

Video