This page collects my tips and tricks for Excel, mainly formulas but also customisation and other things.

Note: Microsoft has different versions of Excel, most of these tips and tricks assume you are on the latest version Excel for Microsoft 365. You can check requirement for each function by going to the Microsoft documentation linked.

• Concatenation
  • Text
  • Arrays
• Dynamic array formulas
  • Filtering data
  • Sorting
  • Remove Duplicates
• Generating numbers
  • Random numbers
  • Sequential numbers
• Formula Variables

Concatenation

Text

You can join text strings together using the CONCAT function.

If you want to join an array of text strings together, with a defined delimiter between each of the elements without having to define the delimiter each time, you can use TEXTJOIN instead.

=CONCAT(text1, [text2],…)
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Arrays

You can also join (dynamic) arrays together using the VSTACK or HSTACK functions. VSTACK concatenates the arrays together row wise, whereas HSTACK concatenates column wise.

=VSTACK(array1,[array2],…)
=HSTACK(array1,[array2],…)

Note: You can also add values to these functions if you want to concatenate a value to the beginning or end of an array)

=VSTACK(Value1, array1, …)
=HSTACK(Value1, array1, …)

Dynamic array formulas

There are some functions that were introduced a couple of years ago that can create arrays dynamically, depending on what the source data is. This can be useful to filter and transform data.

See also Microsoft Documentation for more detailed information.

Filtering data

The FILTER function is a powerful function, but it can be a bit difficult to understand, specially the include argument.

=FILTER(array,include,[if_empty])

Example data (Headers are not part out outputs):

Filtering can be on the same set of data. Let us filter out all rows where the Type is Car and then let us filter out all rows where the Brand is Volvo:

=FILTER(Table1,Table1[Type]="Car")
=FILTER(Table1,Table1[Brand]="Volvo")

You can also add multiple filters with Boolean expressions. Let us filter out all rows where the Type is Car and where the Brand is Volvo. This uses an AND operator, which is defined by a multiplication, *.

=FILTER(Table1,(Table1[Type]="Car")*(Table1[Brand]="Volvo"))

We can also change this to an OR operator, which is defined by an addition, +.

=FILTER(Table1,(Table1[Type]="Truck")+(Table1[Brand]="Toyota"))

Filtering can be on a separate set of data, assuming the data has the same form. Let us filter out all models which are Aeroplanes:

=FILTER(Table1[Model],Table1[Type]="Aeroplane")

Sometimes you want to filter and sort many rows of data, which does not always fall into nice tables, then you might be using a whole column (e.g. =B:B) as the source array. Often this ends up including a 0 in the list, as Excel most often considers an empty cell to have the value 0 when processing data. We can use the FILTER function together with the LET function to create a function to remove 0 values, without repeating calculation the source data multiple times.

=UNIQUE(B:B)
=FILTER(UNIQUE(B:B),UNIQUE(B:B)<>0)
=LET(src,UNIQUE(B:B),FILTER(src,src<>0))

Sorting

If you need to sort your arrays, the two functions SORT and SORTBY will help you. SORT will sort the array based on the contents of the array being sorted, whereas SORTBY will sort the array based on another array.

=SORT(array,[sort_index],[sort_order],[by_col])
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

Remove Duplicates

If you need to remove duplicates from an array the UNIQUE will do that for you.

=UNIQUE(array,[by_col],[exactly_once])

Generating numbers

Random numbers

If you need a random number, the two functions RAND and RANDARRAY can help you. Both functions generate new numbers whenever you re-calculate your worksheet.

The RAND function gives you a random number between [0, 1).

The RANDARRAY function also gives you a random number between [0, 1), but if you provide arguments you can specify if you want an array of number instead of a single number, and your minimum and maximum values, as well as if you want a decimal number or whole number.

=RAND()
=RANDARRAY([rows],[columns],[min],[max],[whole_number])

Sequential numbers

If you need to generate sequential numbers, the SEQUENCE function is the function to use. You can generate the sequence row wise (default) or column wise. If you have both row wise and column wise, the function will fill each row (column wise), before continuing (spilling over) to the next row.

=SEQUENCE(rows,[columns],[start],[step])

Formula Variables

Sometimes in a formula you might need to perform a complicated function multiple times e.g. combining a filter with an IF. This can make the formula long and difficult to read, as well as make the Excel sheet slow. The LET function exists for this purpose; you assign it a name¹, define the function, and then you can re-use the value of the function multiple times in the formula.

=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3…])