In the 36 years (that’s right, 36 years) that Microsoft Excel has been with us, it has grown into a truly advanced tool for collating, analysing and visualising data.

Over time a whole host of new functions have been added, some of which may have passed you by. So here are five that may not be quite familiar just yet, but are really worth learning about and trying out.

1. UNIQUE

Most large datasets come with duplicate entries. Rather than going through and manually pulling out single entries, however, you can use UNIQUE to quickly declutter your data.

For this feature you use the syntax: UNIQUE(array, [by_col], [exactly_once]), where ‘array’ is the data range, ‘by_col’ is a logical value (requiring TRUE to compare columns or FALSE for rows), and ‘exactly_once’ returns values that haven’t been duplicated if set to TRUE.

2. TEXT SPLIT

If your data is a series of keywords, for example, but you want to separate them out rather than have whole sentences in one field, use TEXT SPLIT.

To do this use the syntax: TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]).

Here, ‘text’ refers to the content to be split, whilst ‘col_delimiter’ is the character used to separate text into columns. Then, ‘row_delimiter’ separates text into rows and ignore_empty can skip blank cells (when set to ‘TRUE’). Finally, ‘match_mode’ can be used to specify case sensitivity and ‘pad_width’ identifies what will fill a cell if the source content cannot be split evenly.

3. SUBSTITUTE

To fix typos or standardise spellings, give SUBSTITUTE a try instead of the more usual Find & Replace.

The syntax is: SUBSTITUTE(text, old_text, new_text, [instance_num]). As you’d expect, ‘old_text’ refers to the original content; ‘new_text’ is its replacement. ‘Instance_num’ specifies which occurrence of old text to replace.

4. NETWORKDAYS.INTL

Want to determine the number of working days between two points? If so, try out: NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]).

Whilst start and end date are self-explanatory, ‘weekend’ allows you to state the days that aren’t counted as typical work (meaning it doesn’t have to be Saturday and Sunday, but can be customised), and ‘holidays’ allows you to do similar but for longer spells. An intuitive way of doing this is to use a seven-character string (for example, 1100000) where each digit represents a day of the week, with the ones as weekends.

5. TRIM

Irregular spacing in text strings not only look unsightly but can also impact data cleanliness. Thankfully, the solution is incredibly simple. Just TRIM(text) is enough syntax to tidy up errant spacing – thereby resolving issues that could otherwise impact your MATCH or VLOOKUP functions.