Most of us stick to popular Excel functions like Sum and Vlookup, which barely scratches the surface of what the application can really do. In Excel, there are many functions that not everyone may know, but they may be the ideal solution to save time and increase productivity. Whether you're analyzing data or preparing reports, these hidden functions will help you get things done more quickly and accurately. Even the most advanced Excel users often find themselves stuck manually doing tasks that could be automated with some clever function. Below, I've identified some lesser-known Excel functions that can save you a lot of time and effort and how they can be used to achieve better results in less time. Check out Learn to perform basic calculations in Excel easily and conveniently.
1. UNIQUE
More often than not, Excel spreadsheets contain duplicate entries, which can clutter your data and make analysis difficult. It took me some time to know that we can easily remove duplicates and extract unique values using the unique function. If you often remove duplicates manually, this function will simplify your workflow and save you time.
The syntax for this function is as follows:
UNIQUE(array, (by_col), (exactly_once))
Where you point Array To the dataset from which you want to extract unique values. Intermediate BY_COL is a logical value — change it to TRUE To compare columns or FALSE To compare rows. Returns an argument while_once 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 custom delimiter. If you have a string, such as names, addresses, or other data entries, for which you want to get the individual parts, you can use the TextSplit function to automate the process. 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, mark Text is the string you want to split, col_delimiter Separator is the character used to separate text into columns ROW_DELIMITER Text into rows. Defines the tag ignore_empty Whether to skip empty cells (set to TRUE to ignore it).
Likewise, it is determined Match_Mode Case sensitivity for text matching is specified PAD_WITH What fills cells when text is not evenly divided.
3. CHOOSECOLS / CHOOSEROWS
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 specific 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 for choosecols is:
CHOOSECOLS(array, column_num1, (column_num2), ...)
Where you are array she The range of data you want to extract the columns from, and Column_num1 and Column_num2 The indices of the columns you want to specify.
The syntax for chooserows is similar:
CHOOSEROWS(array, row_num1, (row_num2), ...)
4. SUBSTITUTE
Sometimes, we need to fix typos, update names, or standardize entries in a data set. Although Excel's built-in Find and Replace feature can handle this, the SUBSTITUTE function makes the process faster and more efficient. It allows you to replace a specific part of a text string with other text within a cell, replace the occurrence of specific text in a data set, and more.
The syntax for SUBSTITUTE is:
SUBSTITUTE(text, old_text, new_text, (instance_num))
Where there is a sign text is the cell or string that contains the text, while Old_text is what you want to replace, New_Text It is the replacement value, and is determined instance_num Any appearance of old text must be replaced. Check out how AI saves time working with complex functions in Excel.
5. XMATCH
I used to spend a lot of time manually locating a specific value within a range or set of data until I found the XMatch function. Unlike Find and Replace, the XMatch function lets you efficiently search for a value in a given range or array, whether it's an exact match or the next smallest or largest value. You can even perform reverse searches.
The XMatch function follows the syntax below:
XMATCH(lookup_value, lookup_array, (match_mode), (search_mode))
While you are Lookup_Value is the value you are looking for, and Lookup_array The range or array you want to search.
Determine Match_Mode How does the function find the value? need to use 0 For exact matching, -1 For exact matching or the next smaller value, 1 For exact matching or the next largest value, and 2 To match the allowance. Determine Search_Mode Search direction: 1 To search from first to last and -1 To reverse search from last to first.
6. NETWORKDAYS.INTL
NetworkDays.Intl is one of the most valuable functions I have come across in Excel. Using it, I can count the number of working days between two dates, and I can determine which days are considered weekends. I use this function frequently when planning projects or scheduling tasks.
The syntax is as follows:
NETWORKDAYS.INTL(start_date, end_date, (weekend), (holidays))
start_date and end_date Represents the period you are working with. Determine weekend Which days of the week are considered weekends, and holidays It is a set of dates that you will treat as non-working days.
You can customize your weekends using criteria such as 1 For Saturday and Sunday, 2 For Sunday and Monday, 11 For Sunday only, or 12 For Monday only. You can also use a string of seven characters (for example, 0000011) where each number represents a day of the week, and 1 indicates a non-working day. Check out a simple guide to using VLOOKUP in Excel: to search and match data.
7. SEQUENCE
Whenever I wanted to try a new Excel feature, I used to download training spreadsheets, which was time-consuming. To simplify this, I started using the SEQUENCE function to quickly create arrays of sequential numbers. This allows me to easily create rows, columns, or even grids of numbers, as well as generate sequences of dates.
The syntax for the SEQUENCE function is:
SEQUENCE(rows, (columns), (start), (step))
Where specified rows The number of rows you want in the sequence, you choose columns By setting the number of columns, it is determined START The starting number is specified step The increment between each number.
8. TRIM
When importing data from online sources, I always had to deal with irregular spacing between words, which was tedious to clean up. Then, I discovered Excel's trim function, which helps remove extra spaces from a text string, leaving only single spaces between words.
It is a straightforward formula to use. The syntax for the trim function is:
TRIM(text)
Where the sign indicates text 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 are lesser-known Excel functions that are just a glimpse of what you might be missing. If it's new to you, give it a try and see how it simplifies your workflow. Excel is full of more powerful tools, so make it a habit to explore its features regularly — you never know what time-saving trick you'll discover next time. Now learn how to use ChatGPT with Excel and conquer your spreadsheet fears.
Get IPTV Free Trial Now