8 little-known Excel functions that can save you a lot of work

Most of us use common Excel functions like SUM and VLOOKUP, only scratching the surface of what Excel can actually do. As a result, even seasoned Excel users often find themselves stuck performing tasks manually that could be automated with a few clever functions. Here are some lesser-known Excel functions that can save you a lot of time and effort.

1. UNIQUE function

8 little-known Excel functions that can save you a lot of work Picture 1

Often, Excel spreadsheets contain duplicate entries, which can clutter the data and make analysis difficult. However, we can easily remove duplicate entries and extract unique values ​​using the UNIQUE function. If you remove duplicate entries manually, this function simplifies the workflow and saves you time.

The syntax for this function is as follows:

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

Where array refers to the range of data from which you want to extract unique values. The by_col argument is a logical value - set to TRUE to compare columns or FALSE to compare rows. The exactly_once argument returns values ​​that appear only once in the range if set to TRUE .

2. TEXTSPLIT function

8 little-known Excel functions that can save you a lot of work Picture 2

The TEXTSPLIT function allows you to split text into multiple cells based on a specific delimiter. If you have a string, such as a name, address, or other data item, that you want to split into individual pieces, you can use the TEXTSPLIT function to automate the process. This is especially useful when working with unstructured data.

The syntax of TEXTSPLIT is:

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) 

Here, text is the string you want to split, col_delimiter is the character used to separate the text into columns, and row_delimiter separates the text into rows. Ignore_empty determines whether to ignore empty cells (set to TRUE to ignore them).

Similarly, match_mode specifies case sensitivity for text matching, and pad_with determines what to fill in the cell when the text is not evenly spaced.

3. CHOOSECOLS / CHOOSEROWS function

8 little-known Excel functions that can save you a lot of work Picture 3

The CHOOSECOLS and CHOOSEROWS functions allow you to extract specific columns or rows from a data set. These functions are useful when you want to focus on certain parts of your data for analysis without changing the original data set. Instead of manually copying and pasting, you can easily isolate the most relevant information.

The syntax of CHOOSECOLS is:

CHOOSECOLS(array, column_num1, [column_num2], .)

Where array is the range of data you want to extract columns from, column_num1 and column_num2 are the indices of the columns you want to select.

The syntax of CHOOSEROWS is similar to the following:

CHOOSEROWS(array, row_num1, [row_num2], .)

4. SUBSTITUTE function

8 little-known Excel functions that can save you a lot of work Picture 4

Sometimes we need to fix typos, update names, or normalize items in a data set. While Excel's Find and Replace feature can handle this, the SUBSTITUTE function makes the process quicker and more efficient. It allows you to replace a specific part of a text string with another text in a cell, replace a specific occurrence of text in a data set, and more.

The syntax of SUBSTITUTE is:

SUBSTITUTE(text, old_text, new_text, [instance_num])

Where text is the cell or string containing the text, old_text is the text you want to replace, new_text is the replacement text, and instance_num specifies which occurrences of the old text will be replaced.

5. XMATCH function

8 little-known Excel functions that can save you a lot of work Picture 5

You would spend a lot of time manually locating a specific value in a range or array of data until you found XMATCH. Unlike Find and Replace, the XMATCH function allows you to efficiently search for a value in a specific range or array, whether it is an exact match or the next smaller or larger value. You can even perform a reverse lookup.

The XMATCH function follows the syntax below:

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Where lookup_value is the value you are looking for and lookup_array is the range or array you want to search.

Match_mode determines how the function searches for values. You should use 0 for an exact match, -1 for an exact match or the next smaller value, 1 for an exact match or the next larger value, and 2 for a wildcard match. Search_mode determines the direction of the search: 1 for a standard end-to-end search and -1 for a back-to-end search.

6. NETWORKDAYS.INTL function

8 little-known Excel functions that can save you a lot of work Picture 6

NETWORKDAYS.INTL is one of the most valuable functions you can find in Excel. Using this function, you can calculate the number of workdays between two dates and determine which days count as weekends. People often use this function when planning projects or scheduling tasks.

The syntax is as follows:

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Start_date and end_date represent the time period you are working. Weekend specifies which days of the week are considered weekends, and holidays is the range of days you will consider non-working.

You can customize the weekends with parameters like 1 for Saturday and Sunday, 2 for Sunday and Monday, 11 for Sunday only, or 12 for Monday only. You can also use a 7-character string (for example, 0000011) where each digit represents a weekday and 1 represents a non-working day.

7. SEQUENCE function

8 little-known Excel functions that can save you a lot of work Picture 7

Whenever people want to try out a new Excel feature, they often download practice spreadsheets, which is time-consuming. To simplify this, use the SEQUENCE function to quickly create sequential arrays of numbers. This allows you to easily create rows, columns, or even grids of numbers, as well as create date sequences.

The syntax of the SEQUENCE function is:

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

Where rows specifies the number of rows you want in the series, columns sets the number of columns, start defines the starting number, and step defines the increment between each number.

8. TRIM function

8 little-known Excel functions that can save you a lot of work Picture 8

When importing data from online sources, you have to deal with uneven spacing between words, which can be time-consuming to clean up. The TRIM function in Excel removes extra spaces from text strings, leaving only single spaces between words.

This is an easy to use formula. The syntax of the TRIM function is:

TRIM(text)

Where text refers to the text string or cell reference from which you want to remove extra spaces. This also solves problems when using functions like VLOOKUP or MATCH, where extra spaces can cause problems.

These lesser-known Excel functions are just a glimpse of what you might be missing. If you're new to Excel, give them a try and see how they streamline your workflow. Excel is packed with more powerful tools, so make a habit of exploring its features regularly - you never know what time-saving trick you'll discover next.

Samuel Daniel

Update 20 December 2024