How can I pretty-print a formula in Google Sheets?

Question

Without proper line breaks and indentation, even moderately complex formulas are visually not well navigable in Google Sheets.

How can I pretty-print them? I know I can create a line break with Ctrl + Return and indent with Space but these changes are lost when leaving the cell.

Answer 1

This excel formula pretty-printer worked pretty good on my google spreadsheet:

http://excelformulabeautifier.com/

Before:

=ifs(OR($I111="Elixir",$I111="Collectible",$I111="Lootbox"),,$AS111<='Items Statistics'!$H$22,1,$AS111<='Items Statistics'!$H$33,12,$AS111>'Items Statistics'!$H$33,12)

After:

=ifs(
    OR(
        $I111 = "Elixir",
        $I111 = "Collectible",
        $I111 = "Lootbox"
    ),
    ,
    $AS111 <= 'Items Statistics'!$H$22,
    1,
    $AS111 <= 'Items Statistics'!$H$33,
    12,
    $AS111 > 'Items Statistics'!$H$33,
    12
)

I guess syntax is compatible for both spreadsheet and excel.

Answer 2

If you entered a formula let's say:

=sum(A1,C1)

and then you changed your mind on its looks and want to look like:

=sum(A1, 
     C1)

all you need to do is change at least one lowercase of fx to uppercase (or vice versa):

=Sum(A1,
     C1)

=sUM(A1,
     C1)

also, you could just add an insignificant lock $ to any cell reference

=sum(A1,
    $C1)

=sum(A$1,
     C1)

---

CTRL + ENTER - for line break

Answer 3

Indeed, you can pretty-print your Google Sheets formulas with Ctrl + Return and Space. But there is a software bug in the current (2019-04) Google Sheets implementation that causes your whitespace changes to not be saved. A workaround is provided below.

Bug description

The bug is triggered like this:

1. Google Sheets keeps an internal history about what formulas have been stored in a cell so far.

2. When you change a formula, it is compared against this history, and whitespace differences are ignored.

3. When it is found in the cell history, the old version of the formula (including its own whitespace formatting!) is used. When it is not found in the cell history, the formula you just entered is used.

In effect, whitespace-only changes are discarded when editing a formula.

Workaround

For your whitespace changes to always be saved, your formula has to be always unique in its history (with whitespace differences ignored). You can achieve this by appending a do-nothing-operation to the formula and changing that with every change of your formula. Below are examples. You would choose a new line from the following list on each edit and use it at the end of your formula:

• For number formulas, use the next line from this list for every new edit you make. It just adds zero, but always in a new way.

  + 0 * 1
  + 0 * 2
  + 0 * 3
  ...
  

• For text formulas, use the next like from this list for every new edit you make. It appends the empty string always one more time than the last time.

  & REPT("",1)
  & REPT("",2)
  & REPT("",3)
  ...