formulasgoogle sheetskeyboard shortcuts
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.
With the following custom number format, you are able to decimal-align numbers in Google Sheets, even by using fonts that use kerning.
???.?????
custom number format
result
I've added this solution in an example file: Decimal-Aligned Numbers
You can achieve the effect of CLAMP(value, min, max) with
=IFERROR(max-SQRT(max-IFERROR(SQRT(value-min)^2+min,min))^2,max)
which is compatible with ARRAYFORMULA and uses value only once. Explanation:
SQRT(value-min)^2+min returns value if value>=min, and gives an error otherwise.IFERROR returns the first argument, unless an error occurred in it, which case it returns the secondIFERROR(SQRT(value-min)^2+min,min) clamps the value from below by min. The above formula can be simplified, reducing the references to max and min from three to two of each:
=max-IFERROR(SQRT(max-min-IFERROR(SQRT(value-min)^2,0))^2,0))
Best Answer
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:
Google Sheets keeps an internal history about what formulas have been stored in a cell so far.
When you change a formula, it is compared against this history, and whitespace differences are ignored.
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:
For text formulas: append the empty string a number of times