0

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.

1

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:

    + 0 * 1
    + 0 * 2
    + 0 * 3
    ...
    
  • For text formulas: append the empty string a number of times

    & REPT("",1)
    & REPT("",2)
    & REPT("",3)
    ...
    
| improve this answer | |
1

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

| improve this answer | |
  • Case change is a nice hack for the "whitespace changes are ignored" bug. It's still sad we have to do these workarounds, given Google's reputation of just not caring about bug reports … – tanius Apr 23 '19 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.