20
\$\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\$
  • 4
    \$\begingroup\$ apparently cellular-automata isn't something that is related to excel... :( \$\endgroup\$ – user56309 Sep 20 '16 at 18:46
  • \$\begingroup\$ I was wondering if is it valid to create an UDF with VBA? \$\endgroup\$ – danieltakeshi Oct 16 '17 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\$ – Taylor Scott Dec 18 '17 at 20:29
11
\$\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
    \$\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 '16 at 19:13
9
\$\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

\$\endgroup\$
9
\$\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\$
  • 1
    \$\begingroup\$ This process is usually called "vectorization" if my memory serves me well. \$\endgroup\$ – Conor O'Brien Sep 20 '16 at 18:56
  • \$\begingroup\$ @ConorO'Brien Thanks! I changed my answer! \$\endgroup\$ – user56309 Sep 22 '16 at 18:07
2
\$\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\$
1
\$\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\$
1
\$\begingroup\$

Boolean Shorthand:

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

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

Shorten sheet names

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

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy