Quick Links
Most of us stick to the popular Excel functions like SUM and VLOOKUP, barely scratching the surface of what Excel can really do. As a result, even seasoned Excel users often find themselves stuck doing tasks manually that could be automated with a few clever functions. Below, I’ve outlined some lesser-known Excel functions that can save you tons of time and effort.
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 caneasily remove duplicatesand extract unique values using the UNIQUE function. If you manually remove duplicates, this function will simplify your workflow and save you time.
The syntax for this function is below:
Wherearrayrefers to the data range from which you want to extract unique values. Theby_colargument is a logical value—set it to TRUE to compare columns or FALSE to compare rows. Theexactly_onceargument returns values that appear only once in the range if set to TRUE.
2TEXTSPLIT
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 data entries, that you want to break into individual parts, you canuse the TEXTSPLIT functionto automate the process. This is especially helpful when working with unstructured data.
The syntax for TEXTSPLIT is:
Here,textis the string you want to split,col_delimiteris the character used to separate text into columns, androw_delimiterseparates text into rows.Ignore_emptydefines whether to skip empty cells (set to TRUE to ignore them).
Likewise,match_modespecifies case sensitivity for text matching, andpad_withdetermines what fills cells when the text doesn’t split evenly.
3CHOOSECOLS / 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 your data for analysis without altering the original dataset. Instead of manually copying and pasting, you can easily isolate the most relevant information.
The syntax for CHOOSECOLS is:
Wherearrayis the range of data you want to extract columns from, andcolumn_num1andcolumn_num2are the indices of the columns you wish to select.
The syntax for CHOOSEROWS is similar:
4SUBSTITUTE
Sometimes, we need to fix typos, update names, or standardize entries in a dataset. WhileExcel’s Find and Replace featurecan handle this, theSUBSTITUTE functionmakes the process quicker and more efficient. It allows you to replace a specific part of a text string with another text within a cell, replace a particular occurrence of the text in a dataset, and more.
The syntax for SUBSTITUTE is:
Wheretextis the cell or string containing the text,old_textis what you want to replace,new_textis the replacement, andinstance_numspecifies which occurrence of the old text should be replaced.
I used to spend a lot of time manually locating a specific value within a range or array of data until I found about XMATCH. Unlike Find and Replace, the XMATCH function allows you to efficiently search for a value in a specific range or array, whether it’s an exact match or the next smaller or larger value. You can even perform reverse searches.
The XMATCH function follows the syntax below:
wherelookup_valueis the value you’re searching for, andlookup_arrayis the range or array where you want to search.
Match_modedefines how the function finds the value. You need to use 0 for an exact match, -1 for an exact match or next smaller value, 1 for an exact match or next larger value, and 2 for a wildcard match.Search_modedetermines the search direction: 1 for a standard first-to-last search and -1 for a reverse search from last to first.
6NETWORKDAYS.INTL
NETWORKDAYS.INTL is one of the most valuable functions I’ve come across in Excel. Using this function, I cancalculate the number of working days between two dates, and I can define which days count as weekends. I frequently use this function when planning projects or scheduling tasks.
The syntax is as below:
Start_dateandend_daterepresent the period you’re working with.Weekendspecifies which days of the week are considered weekends, andholidaysis a range of dates you’re going to treat as non-working days.
You can customize weekends using 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-character string (e.g., 0000011) where each digit represents a day of the week, and 1 indicates a non-working day.
Whenever I wanted to try out a new Excel feature, I used to download practice spreadsheets, which was time-consuming. To streamline this, I began using the SEQUENCE function to quickly generate 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 of the SEQUENCE function is:
whererowsspecifies how many rows you want in the sequence,columnssets the number of columns,startdefines the starting number, andstepdetermines the increment between each number.
When importing data from online sources, I had to deal with irregular spacing between words, which was tedious to clean up. Then, I discovered the TRIM function in Excel, which helpsremove extra spaces from a text string, leaving only single spaces between words.
It’s a straightforward formula to use. The syntax for the TRIM function is:
where text refers to the text string or cell reference from which you want to eliminate extra spaces. This also resolves issues 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 they’re new to you, give them a try and watch how they streamline 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.
