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

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 2

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 3

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

In newer version of Excel (2016 or newer) the CONCAT function can be used for arrays and longer series of variables.

CONCAT(1:1)

Answer 4

Boolean Logic Shortcuts

Universal Set of Operators

0 => FALSE and any other number => TRUE, so we can exploit this fact with some arithmetic operators

Standard	Hacky	# Saved
NOT(A1)	A1=0	3, 1 if parenthesized
XOR(A1,B1)	A1<>B1	4
XOR(A1,B1,...)	A1=B1=...=0	3
NOT(XOR(A1,B1,...))	A1=B1=...	6 (XNOR)
OR(A1,B1,...)	A1+B1+...	4 or 2 (see note)
AND(A1,B1,...)	A1*B1*...	5

Note: This XOR only works if all argument values are the same nonzero number. You may have to "cast" a result (such as with OR) to a Boolean using ...>0

Operator precedence

You may have to add parentheses, depending on precedence.

1. Reference (:/ /,)
2. Negation (Prefix -)
3. Percent (%)
4. Exponentiation (^)
5. Multiplication/Division (*//) (AND)
6. Addition/Subtraction (+/-) (OR)
7. Concatenation (&)
8. Comparison (=, <, >, <=, >=, <>) (XOR/NOT)

Other Things

• A blank cell evaluates to FALSE.

Answer 5

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 6

Boolean Shorthand:

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

Answer 7

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 8

Converting Types

To Number

• Use a -- prefix.
  • --FALSE = 0, --TRUE = 1
• Sometimes an arithmetic operator can be used to save steps later.
  • "2"-2 = 0, which can be used as an equality check.

To Boolean

• Since numbers are implicitly Boolean-like, you may be able to avoid this.
  • 0 => FALSE, Any other number => TRUE
  • Blank cell => FALSE
• If you need it, you can use comparison operators.
  • A1<>0 => Non-zero to TRUE, A1>0 => Positive to TRUE.

To String

• Concatenate either a blank cell or "" using the & operator.

Using BASE() and DECIMAL()

BASE takes a decimal number and converts it to another base. For example, =BASE(6873049,25) becomes "HELLO". You can do the reverse of this with =DECIMAL("HELLO",25). This is sometimes useful for restricted source problems, or can be used to compress a big number in a smaller base using a decimal number.

Answer 9

LET Function

LET allows you to define names within a function. For example, =LET(x,1,y,2,x+y) returns 3. This allow you to define longer expressions with one character and reuse them within a formula. For example, LET(q,ROW(1:19),if(q>10,19-q,q)) lists the numbers from 1 to 10 and then back down to 1.

This sounds like a simple function but it has allowed golfing answers that were not possible before this was added to Excel.

There are functions like OFFSET and SUMIF that do not work entirely correctly on names that are not tied directly to cell references.

More info on LET

Answer 10

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="".