Tips for golfing in Excel?

Question

It's strange that I haven't seen this, as Excel seems to be a valid language for code golfing (despite its 'compiler' not being free).

Excel is somewhat of a wildcard in golfing, being good at golfing challenges of medium complexity, and sometimes simpler challenges as well. More often than not, Excel is good at challenges with string manipulation and mixed string-number manipulation.

What general tips do you have for golfing in Excel? I'm looking for ideas that can be applied to code golf problems in general that are at least somewhat specific to Excel (NOT VBA). Please, one tip per answer.

Answer 1

Reference shorthand:

If your program is required to take multiple inputs, you may want to be able to scoop them all up at once. to read multiple cells as an array, one could do such as the example:

Example:
=len(A1)+Len(B1)+LEN(C1)
could be
=SUM(LEN(A1:C1))

=SUM(LEN(A1:C1 A2:C2 A3:C3))
could be
=SUM(LEN(A1:C3))

=SUM(LEN(A1:A1024))
could be
=SUM(LEN(A:A))

=SUM(LEN(A:A B:B C:C))
could be
=SUM(LEN(A:C))

Answer 2

Concatenate shorthand:

The CONCATENATE function can be replaced with the & symbol 100% of the time, so long as the first argument is a string, or cell.

example:
=CONCATENATE(A1,B1)
could be shortened to
=A1&B1

Answer 3

Vectorization with Arrays

Whenever a function takes an array as an argument instead of a singleton, That function will also output an array with the result value to the corresponding indecies.

Example:
=LEN(A1)+LEN(B2)+LEN(C3)
could be replaced with
=SUM(LEN({A1,B2,C3}))

Answer 4

Converting a Number to Text:

This is a very simple tip, but it may be useful to some nonetheless...

• If you need to convert a number to text from within a formula, use the concatenation operator to join two parts of the number as a string (i.e. 1&23).
• If you need to convert a number to text for use by cell reference (i.e. A1), change the Number Format of the cell to Text to eliminate the need for extra bytes.
• See the chart below for a comparison of number-to-text methods.

Quick Reference Chart:

+-------------------------------------------------------------------------------------+
|   | A               | B        | C         | D                | E                   |
|-------------------------------------------------------------------------------------|
| 1 | Formula         | Bytes    | Result    | ISTEXT(cell)¹    | ISTEXT(formula)²    |
|-------------------------------------------------------------------------------------|
| 2 | =TEXT(123,0)    | 12       | 123       | TRUE             | TRUE                |
| 3 | ="123"          | 6        | 123       | TRUE             | TRUE                |
| 4 | =1&23           | 5        | 123       | TRUE             | TRUE                |
| 5 | '123            | 4        | 123       | TRUE             | NOT VALID           |
| 6 | 123             | 3        | 123       | TRUE             | FALSE               |
| 7 | 123             | 3        | 123       | FALSE            | FALSE               |
+-------------------------------------------------------------------------------------+

Note: The result for cell C6 has been formatted as text, whereas the result for C7 has not.

¹ Denotes =ISTEXT(C2), =ISTEXT(C3), =ISTEXT(C4), etc.
² Denotes =ISTEXT(TEXT(123,0)), =ISTEXT("123"), =ISTEXT(1&23), etc.

Answer 5

Boolean Shorthand:

Instead of using the =TRUE() and =FALSE() functions, use =1=1 and =1=2.

Answer 6

Vectorization of arrays of cells:

The tip Vectorization with arrays Shows how you can golf down a function with an array by using specific formatting within the array. It is possible to do the same thing with cells, and will save you many many bytes in the long run. Say you have the following sheet:

And we want to find the highest shelf life of a fruit.

Without Vectorization, one might use the two formulas like so:

And this does give a correct answer, but the Score for this golf is Unconventional, and will probably not be accepted as widely. On top of that, this uses a drag down function (Ew), which makes for a confusing answer.

Instead, We can place the function in column D right on in with the formula in E2. To do this, you replace (in this case B2 and C2) your variables with arrays for the range you want to test. Thus, your formula becomes:

This saves you a few bytes as well as making your entry properly score-able.

Answer 7

ISBLANK() Shorthand:

Instead of using =ISBLANK(A1), use =A1=0 to determine if a cell (i.e. A1) is empty.

Note: This shortcut will not work if cell A1 contains 0. In that case, you will need to use =A1="".

Answer 8

Shorten sheet names

If you rename Sheet2 to S then references to Sheet2!a0 become S!a0.