# Excel - Tips and Tricks

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

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

Type | Brand | Model |
---|---|---|

Aeroplane | Airbus | A320 |

Aeroplane | Boeing | B787 |

Car | Toyota | Corolla |

Car | Volvo | V60 |

Car | Volvo | V70 |

Truck | Volvo | FH16 |

Truck | Volvo | FMX |

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

Type | Brand | Model |
---|---|---|

Car | Toyota | Corolla |

Car | Volvo | V60 |

Car | Volvo | V70 |

Type | Brand | Model |
---|---|---|

Car | Volvo | V60 |

Car | Volvo | V70 |

Truck | Volvo | FH16 |

Truck | Volvo | FMX |

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

Type | Brand | Model |
---|---|---|

Car | Volvo | V60 |

Car | Volvo | V70 |

We can also change this to an `OR`

operator, which is defined by an addition, `+`

.

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

Type | Brand | Model |
---|---|---|

Car | Toyota | Corolla |

Truck | Volvo | FH16 |

Truck | Volvo | FMX |

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

Model |
---|

A320 |

B787 |

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^{1}, 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…])`

- If you need to assign name to multiple functions, you do not need to nest
`LET`

functions.^{[return]}