31
\$\begingroup\$

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.

\$\endgroup\$
5
  • 6
    \$\begingroup\$ apparently cellular-automata isn't something that is related to excel... :( \$\endgroup\$
    – user56309
    Sep 20, 2016 at 18:46
  • \$\begingroup\$ I was wondering if is it valid to create an UDF with VBA? \$\endgroup\$ Oct 16, 2017 at 13:42
  • 1
    \$\begingroup\$ @danieltakeshi - no; well, not as an Excel answer - if you instead use Excel VBA and then call it from the immediate window, the activesheet or a subroutine, that it generally regarded as valid \$\endgroup\$ Dec 18, 2017 at 20:29
  • \$\begingroup\$ If I want to make a Google Sheets tip, should I put it here or make a new question? \$\endgroup\$ Sep 21, 2020 at 14:47
  • \$\begingroup\$ @Calculuswhiz - I would say it is probably best to add a new question for tips that are Google Sheets Specific, and include a link to this question in that response. Otherwise keeping functions that only available on one of the two platforms - like let, lambda, arrayformula and regexreplace - may get really confusing. \$\endgroup\$ Jan 19, 2022 at 18:27

10 Answers 10

12
\$\begingroup\$

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}))

\$\endgroup\$
3
  • 1
    \$\begingroup\$ This process is usually called "vectorization" if my memory serves me well. \$\endgroup\$ Sep 20, 2016 at 18:56
  • \$\begingroup\$ @ConorO'Brien Thanks! I changed my answer! \$\endgroup\$
    – user56309
    Sep 22, 2016 at 18:07
  • \$\begingroup\$ My current version of Excel seems to only allow constants inside of {}. Cell references are not allowed. \$\endgroup\$
    – Axuary
    May 30, 2021 at 13:28
12
\$\begingroup\$

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))

\$\endgroup\$
1
  • 1
    \$\begingroup\$ I know this isn't a great tip, but it's specific enough to excel that I thought it should be included. \$\endgroup\$
    – user56309
    Sep 20, 2016 at 19:13
11
\$\begingroup\$

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)
\$\endgroup\$
1
  • 2
    \$\begingroup\$ Note, in newer Excel versions, CONCATENATE() was replaced by CONCAT(). \$\endgroup\$ Jul 24, 2020 at 13:27
5
\$\begingroup\$

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.
\$\endgroup\$
3
\$\begingroup\$

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.
\$\endgroup\$
3
\$\begingroup\$

Boolean Shorthand:

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

\$\endgroup\$
2
\$\begingroup\$

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:

example sheet

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

Without Vectorization, one might use the two formulas like so: enter image description here

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:
enter image description here

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

\$\endgroup\$
2
\$\begingroup\$

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.

\$\endgroup\$
2
\$\begingroup\$

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

\$\endgroup\$
1
\$\begingroup\$

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

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.