My best Excel trick, which reveals how little I know, and yet Early [0] doesn't use it (or maybe doesn't need it, but that's hard to believe):

1. You can drag down the bottom of the formula bar/field and make it multi-line

2. You can insert arbitrary[*] newlines in an Excel formula

Combining those, you can turn the absurd default format of single-line-of-code functions into something readable and manageable. Here's a simple one from a spreadsheet I have open:

  =INDEX(
  $C$17:$S$24,
  MATCH(A6,$A$17:$A$24,0),
  MATCH(C6,$C$15:$S$15,0)
  )

And just think of highly nested functions. Once you know it, writing single-line functions of any complexity is absurd, as absurd as writing 'real' code that way.

[0] Early shows how it was done: https://news.ycombinator.com/item?id=46340638

[*] I think you can do it anywhere but I haven't tested anything crazy; mostly I just use them between expressions.

▲ Terr_ a day ago | parent | next [-]

> You can drag down the bottom of the formula bar/field and make it multi-line

For folks on LibreOffice (currently v24.2):

* There's an downward-pointing "expand" triangle to the far-right of the formula input line.

* That button toggles the formula input area between 1-line vs 6-lines with scrolling.

* Newlines can inserted by shift-enter.

* If there are additional formula lines lines outside the viewable line(s), then a dashed line on the relevant border will be shown. (Plus the regular scrollbar, in expanded mode.)

▲ simonh 20 hours ago | parent | prev | next [-]

It could be that in a competitive context fussing with formatting would cost precious seconds. Great general tip for us mortals though.

	▲	mmooss 13 hours ago \| parent [-]
		Imagine a coding competition: Would you forgo newlines and write everything on one long line? Would that save time? Perfect style guide format does consume time, but pressing Alt+Enter a few times would seem to reduce errors at essentially no cost.

▲ mmooss a day ago | parent | prev | next [-]

Terr_'s comment reminds me and I'm too late to edit the parent: In Excel's formula bar/field, insert newlines by pressing Alt+Enter.

▲ stevesimmons 12 hours ago | parent | prev | next [-]

You can also use the =LET(...) formula to define named variables:

    =LET(
        filterCriteria, "Fred",
        filteredRange, FILTER(A2:D8,A2:A8=filterCriteria),
        IF(ISBLANK(filteredRange),"-", filteredRange)
    )

There must be an odd number 2D + 1 of arguments. The first 2D are D name-expression pairs and the final one is the expression whose value is returned.

The end result - as you see - is quite readable.

▲ Dumblydorr 17 hours ago | parent | prev | next [-]

No need to drag the bottom of the cell to expand function down. Just double click the bottom of the function cell, it’ll expand down automatically.

	▲	orev 15 hours ago \| parent [-]
		They’re referring to the formula bar at the top of the screen (under the ribbon), not the cell itself (which are located within the grid).

▲ 303uru 11 hours ago | parent | prev [-]

Oh yes indeed. For example, here's something I was just working on:

=LET(

  h, ROWS(A2#),

  names, A2#,

  vals, K2:INDEX(K:K, h+1),

  denoms, J2:INDEX(J:J, h+1),

  k, 20,

  groupAvg, SUMPRODUCT(vals, denoms) / SUM(denoms),

  adj, (denoms/(denoms + k))*vals + (k/(denoms +

k))groupAvg,
inc, (names <> "") \* ISNUMBER(vals), namesF, FILTER(names, inc), valsF, FILTER(vals, inc), denomsF, FILTER(denoms, inc), adjF, FILTER(adj, inc), r, ROWS(namesF), nShow, MIN(10, r), sorted, SORTBY(HSTACK(namesF, valsF, denomsF), adjF, -1), TAKE(sorted, nShow)
)

	▲	mmooss 9 hours ago \| parent [-]
		fyi: if you indent a couple of spaces, you get a <code> block.