4 Tools to Pretty-Print / Beautify a Spreadsheet Formula (+3 extra)

Contents




Why Bother Beautifying Complex Spreadsheet Formulas?

The one thing harder, more time consuming and emotionally draining than writing a long formula with lots of functions and parenthesis is reading and understanding an existing one, or even worse, troubleshooting a broken one. Having lots of cell references, numbers, operators and formula names jam packed on one line can easily lead to headaches and mistakes. The pain of dealing with long formulas is probably one of the few things that fans of Excel and Google Sheets would agree on.

Placing the formula on multiple lines, adding white space and indentation to accentuate the different parts of the formula can help a lot when understanding and modifying formulas on spreadsheets. The tools to do this and the process itself can be called different things:

  • Beautification / formula beautifier.
  • Prettification / prettyfier / prettifier. Despite it being popular on the web, Webster does not like 'prettYfier' but rather prefers ⧉ 'prettIfier'.
  • Pretty printing / formula pretty-printer. That would be a term familiar to those with coding background as it is often used to describe source code formatting tools.
  • Formula Formatting / formatter.

Those terms should not be confused with conditional formatting using a formula. A distinction should be made between beautifying a formula and styling/formatting/beautifying the spreadsheet as a whole through the use of colors, fonts, formatting, alignment, white space, etc.

The reverse process where the formula is compressed into one line and all the white space is removed is often called uglification (performed by an uglifier).




Pretty Formula - Excel Only

Pretty Formula on Microsoft App Source ⧉

Pretty Formula is a powerful pretty-print and formula beautification tool for Excel. Available through Microsoft's App Source, Pretty Formula allows spreadsheet users and VBA developers to build larger and more sophisticated formulas. Their information page features the following capabilities:

  • Smart, automatic formatting where the formula is spread into multiple lines with appropriate whitespace indentation and nesting.
  • Tunable "Width Limit" to produce different formattings of the same formula.
  • When turned on it provides automatic preview of the formula for the selected cell with pretty-printed format.
  • Parenthesis matching.
  • Syntax highlighting.
  • Multi-language support.
  • Support for R1C1 reference style, lambda functions, table references.

The following video is a quick demonstration of having Pretty Formula turned on inside of Excel and clicking on the various formulas, seeing their 'pretty' versions generated on the fly. Quick and efficient!




Excel Beautifier - Online Only

Excel Formula Beautifier's main website ⧉

Built using the Excel Formula Utilities for JavaScript ⧉ this free online formula beautifier is very easy to use - paste your formula in the box and instantly review the pretty-printed formula. A button for instant copying of the results to the clipboard is provided. The tool can also work in 'reverse' with a "minify" option, and can also format the formula in the following programming languages:

  • Python
  • C#
  • JavaScript

There is an option to use ";" as a separator. The number of indentation spaces for every level is also specified. Despite its name, the Excel Formula Beautifier can handle many Google Sheets formulas as well.




Formula Boost - Online, Excel

Formula Boost's Parse ⧉

Formula Boost's online Excel Beautifier can beautify/pretty print the formula, but it also provides a formula tree. The internal formulas with higher indentation can be hidden in order to focus on the particular parts of the formula. This Excel formula beautifier support a variety of options, among them:

  • Choose a fixed (2, 4, 8) number of spaces for indentation or align to the opening parenthesis of a formula
  • Indicate whether to place spaces between operators, brackets, function brackets, etc.
  • When to start a new line - for functions, arguments, both, etc.
  • Various options for the placement of opening and closing brackets.

The variety of formatting options, the formula tree capability and the ability to test in Excel make this a powerful tool for understanding and troubleshooting spreadsheet formulas.




ExcelFormulaBeautifier - Online, Intranet.

ExcelFormulaBeautifier's GitHub Page ⧉ - includes source control.

ExcelFormulaBeautifier's Demo ⧉ - fully functional demo.

The interface of the aptly named Excel formula beautifier is straightforward - there is an input field for the formula and maximum indentation level for the analysis. The function is pretty printed in a way where every function parameter is on a separate line. Additional information is presented indicating the name of the particular function parameter. However, when the result is copied with the Copy result button, the clipboard will contain a version without the additional comments, which is fully operational. On the right side there is a panel with a list of used functions with short descriptions of how they work. These features make ExcelFormulaBeautifier a particularly good choice for beginners who are unfamiliar with the functions present in the formulas.

Using ExcelFormulaBeautifier

This tool's distribution is a bit different from the other online tools on this list. One of the main features is that the source code can be deployed locally - on a shared folder in Windows, local web server, etc. For this reason the online version is listed as a 'demo'. However, since it is fully functional, we can consider it an online tool as well.




Manual Formatting - no Tools, no Problem

While tools can do the job easily and consistently, the most obvious way of making the formulas look good is to format them by hand. Whenever a formula gets too long, simply adding new lines can improve its readability. Instead of using Enter like in a text editor, when editing a formula in Excel's (or Google Sheets') formula bar, one must use Alt+Enter (for PC) or Option+Enter (for Mac). Simply breaking one long line of formula gives the majority of the benefits of more sophisticated beautification techniques. Adding some leading white space to create indentation is also fairly straightforward. The advantage of manual formatting is that it can be precisely tailored to one's tastes or needs. The disadvantage - it has to be done manually.




Use of Custom VBA Scripts

Not shying away from using VBA and being a fan of string operations? Not satisfied by all the existing Excel formula beautifier tools? Maybe writing a custom script would be appropriate for your needs. Here is an example from jtolle on Stack Overflow ⧉ with a basic, yet powerful script for formula beautification. While it would be hard to match all the features of a specialized tool in a custom script, the advantage is that one can tailor it to do exactly what they need and want. Definitely an approach to be considered if you are implementing some kind of automated process.




Visual Studio Code SQL Formatter

Using tools created for other languages could also be a valid approach. In The Fool's post on Stack Overflow ⧉ you can see an example of using an extension built for SQL to format Excel formulas. It works, but we suspect that the results could be unpredictable for larger, more complex formulas.