6 ms·

The thing that's always baffled me about Excel is why you must always work in "minified" mode when composing formulas. It seems like just adding the ability to

The thing that's always baffled me about Excel is why you must always work in "minified" mode when composing formulas. It seems like just adding the ability to spread a calculation out over multiple lines and add some indentation would make the bugs everyone complains about go down by... a lot.

skipnup 5y ago
But you can just drag the input field larger and add line breaks?
- FriedrichN 5y ago
  You could, but when you finally press enter it's all compressed back to one line.
  - HPsquared 5y ago
    It doesn't get compressed back, I often break complex formulas into multiple lines with indentation. The only disadvantage is if someone else isn't expecting the formulas to be like this, then gets confused when they can only see the first line.
    - catherd 5y ago
      Huh. Well, LibreOffice still can't preserve the formatting. Not sure if the last version of real-Excel I tried could. Having the compose box fit itself to the formula size or give other indication that there is more to see is still a head-scratcher why they didn't do it.
      - CRConrad 5y ago
        > real-Excel ??? :-(
FriedrichN 5y ago
I'll give a free cookie to whoever gets this done. Sometimes I'll get these spreadsheets with byzantine formulas that I have to copy it to a text editor and format myself to make sense of all the parenthesis.
chrispsn 5y ago
A nice shortcut is Ctrl-Shift-U to toggle single- or multi-line view.
quietbritishjim 5y ago
You can just spread the formula over multiple cells. Instead of A3: IF(<boolean>, <result if true>, <result if false>) where each of the three parameters are complex formulae, you can do: A3: IF(B3, C3, D3) B3: <boolean> C3: <X> D3: <Y> Not only is the formula now broken down into simpler chunks, you also get to inspect the component results (like watches in a breakpoint! sorta...). Then you can just hide the relevant columns if you like (B,C,D in this case). You can even use a separate sheet and hide the whole sheet if you wish.
- DataGata 5y ago
  You say this but it still feels gross to do.
akho 5y ago
No, it wouldn’t. Case in point: Excel has ability to spread a calculation over multiple lines, but the bugs are not down. In fact, people who are aware of Alt-enter produce buggier code: they end up writing longer formulas, with fewer intermediate results displayed, and have less visibility of the functioning of their spreadsheets. Write simpler formulas.
- mkl 5y ago
  > Write simpler formulas. Excel's formula language seems deliberately designed to prevent that.
  - quietbritishjim 5y ago
    They mean simpler formula per cell, by splitting the formula across multiple cells (as I mentioned in my sibling comment).
reportgunner 5y ago
It's called "VBA Macros"
- pjmlp 5y ago
  Extended with Lambda, PowerQuery and AddOns.

skipnup 5y ago

But you can just drag the input field larger and add line breaks?

FriedrichN 5y ago
You could, but when you finally press enter it's all compressed back to one line.
- HPsquared 5y ago
  It doesn't get compressed back, I often break complex formulas into multiple lines with indentation. The only disadvantage is if someone else isn't expecting the formulas to be like this, then gets confused when they can only see the first line.
  - catherd 5y ago
    Huh. Well, LibreOffice still can't preserve the formatting. Not sure if the last version of real-Excel I tried could. Having the compose box fit itself to the formula size or give other indication that there is more to see is still a head-scratcher why they didn't do it.
    - CRConrad 5y ago
      > real-Excel ??? :-(

FriedrichN 5y ago

I'll give a free cookie to whoever gets this done. Sometimes I'll get these spreadsheets with byzantine formulas that I have to copy it to a text editor and format myself to make sense of all the parenthesis.

chrispsn 5y ago

A nice shortcut is Ctrl-Shift-U to toggle single- or multi-line view.

quietbritishjim 5y ago

You can just spread the formula over multiple cells. Instead of A3: IF(<boolean>, <result if true>, <result if false>) where each of the three parameters are complex formulae, you can do: A3: IF(B3, C3, D3) B3: <boolean> C3: <X> D3: <Y> Not only is the formula now broken down into simpler chunks, you also get to inspect the component results (like watches in a breakpoint! sorta...). Then you can just hide the relevant columns if you like (B,C,D in this case). You can even use a separate sheet and hide the whole sheet if you wish.

DataGata 5y ago
You say this but it still feels gross to do.

akho 5y ago

No, it wouldn’t. Case in point: Excel has ability to spread a calculation over multiple lines, but the bugs are not down. In fact, people who are aware of Alt-enter produce buggier code: they end up writing longer formulas, with fewer intermediate results displayed, and have less visibility of the functioning of their spreadsheets. Write simpler formulas.

mkl 5y ago
> Write simpler formulas. Excel's formula language seems deliberately designed to prevent that.
- quietbritishjim 5y ago
  They mean simpler formula per cell, by splitting the formula across multiple cells (as I mentioned in my sibling comment).

reportgunner 5y ago

It's called "VBA Macros"

pjmlp 5y ago
Extended with Lambda, PowerQuery and AddOns.