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

Most of us stick to Excel’s favorite functions like SUM and SEARCH and barely scratch the surface of what Excel can really do. As a result, even advanced Excel users often get stuck manually performing tasks that can be automated with a few smart functions. Below I’ve outlined some lesser-known Excel features that can save you a lot of time and effort.

1 UNIQUE

Excel spreadsheets are more likely to contain duplicate entries that can clutter your data and make analysis difficult. It took me a while to realize that we can easily remove duplicates and extract unique values ​​using the UNIQUE function. If you manually remove duplicates, this feature simplifies your workflow and saves you time.

The syntax of this function is below:

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

Where field refers to the data range from which you want to extract unique values. The by_col argument is a boolean value – set it to TRUE for column comparisons or FALSE for row comparisons. The exactly_once argument returns values ​​that appear only once in the range if set to TRUE.

2 TEXTSPLIT

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

The syntax for 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 a character used to split text into columns and line_separator splits the text into lines. Ignore_empty defines whether to skip empty cells (set to TRUE to ignore them).

Also, match_mode specifies case sensitivity for matching text and pad_s determines what fills the cells when the text is not evenly spaced.

3 CHOOSECOLS / CHOOSEROWS

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

The syntax for CHOOSECOLS is:

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

Where field is the range of data from which you want to extract columns, a column_num1 and column_num2 are the indexes of the columns you want to select.

The syntax for CHOOSEROWS is similar to:

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

4 REPLACE

Sometimes we need to fix typos, update names, or standardize items in a dataset. While Excel’s Find and Replace function handles this, the SUBSTITUTE function makes the process faster and more efficient. It allows you to replace a certain part of a text string with other text in a cell, replace a specific occurrence of text in a dataset, and more.

The syntax of SUBSTITUTE is:

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

Where text is a cell or string containing text, old_text is what you want to replace new_text is a substitute for a instance_num specifies which occurrence of old text to replace.

5 XMATCH

I spent a lot of time manually searching for a specific value in a range or array of data until I found about XMATCH. Unlike Find and Replace, XMATCH allows you to efficiently search for a value in a range or array, whether it’s an exact match or the next smaller or larger value. You can even do a reverse search.

The XMATCH function follows the following syntax:

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

where lookup_value is the value you are looking for, a search_field is the range or field where you want to search.

match_mode defines how the function finds the value. You must use 0 for an exact match, -1 for an exact match or the nearest smaller value, 1 for an exact match or the next larger value, and 2 for a wildcard match. Search_mode specifies the search direction: 1 for standard first-to-last search and -1 for last-to-first backward search.

6 NETWORKDAYS.INTL

NETWORKDAYS.INTL is one of the most valuable functions I have come across in Excel. Using this function I can calculate the number of working days between two dates and I can define which days count as weekends. I often use this feature 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 period you are working with. Weekend determines which days of the week are considered weekends and holiday is the range of dates that you will consider as non-working days.

You can customize 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 seven-digit string (eg 0000011) where each digit represents a day of the week and 1 indicates a non-working day.

7 SEQUENCE

Whenever I wanted to try out a new Excel feature, I downloaded practice spreadsheets, which was time-consuming. To make it more efficient, I started using the SEQUENCE function to quickly generate arrays of sequence numbers. This allows me to easily create rows, columns or even grids of numbers as well as generate sequences of data.

The syntax of the SEQUENCE function is:

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

where lines specifies how many rows you want in the sequence, columns sets the number of columns, start defines the starting number and step specifies the increment between each number.

8 TRIM

When importing data from online sources, I had to deal with irregular spaces between words that were tedious to clean up. Then I discovered the TRIM function in Excel, which helps to remove excess spaces from a text string, leaving only single spaces between words.

It is a straightforward formula to use. The syntax of the TRIM function is:

TRIM(text) 

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

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