Thứ Tư, Tháng Hai 12, 2025
spot_img
HomeFinance8 Excel functions that few people know can help save a lot...

8 Excel functions that few people know can help save a lot of effort

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 smart functions. Here are some little-known Excel functions that can save you a lot of time and effort.

1. UNIQUE function

Extract a list of unique names from a column in Excel.
Extract a list of unique names from a column in Excel.

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

The syntax for this function is as follows:

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

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

2. TEXTSPLIT function

Split different types of text from a specific cell into multiple cells in Microsoft Excel.
Split different types of text from a particular cell into multiple cells in Microsoft Excel.

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 items, that you want to split into individual parts, 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. text is the string you want to split, col_delimiter is the character used to separate text into columns and row_delimiter separates text into rows. ignore_empty determines whether to ignore empty cells (set to TRUE to ignore them).

Xem thêm  How to easily free up OneDrive storage space

Likewise, match_mode Specifies case sensitivity for text matching and pad_with Determines what content should fill the cell when the text is not evenly divided.

3. CHOOSECOLS / CHOOSEROWS function

Extract specific columns from a dataset range in Microsoft Excel.
Extract specific columns from a dataset range in Microsoft Excel.

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 pieces of 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 is the index 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

Replace specific text in a cell using the SUBSTITUTE function.
Replace specific text in a cell using the SUBSTITUTE function.

Sometimes we need to correct 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, etc.

The syntax of SUBSTITUTE is:

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

In there text. text is a cell or string containing text, old_text is the content you want to replace, new_text is the replacement content and instance_num Specifies which occurrences of the old text will be replaced.

5. XMATCH function

Find the location of a specific number in a Microsoft Excel spreadsheet.
Find the location of a specific number in a Microsoft Excel spreadsheet.

You will spend a lot of time manually locating a specific value in a range or array of data until you find XMATCH. Unlike Find and Replace, the XMATCH function allows you to efficiently search for a value within a specific range or array, whether it's an exact match or the next smaller or larger value. You can even do a reverse search.

Xem thêm  How to share YouTube Music over time

The XMATCH function follows the syntax below:

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

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

match_mode Specifies how the function finds the value. You need to use 0 for exact match, -1 for exact match or next smaller value, 1 for exact match or next larger value, and 2 for wildcard match. Search_mode Specifies the search direction: 1 for standard search from beginning to end and -1 for reverse search from last to beginning.

6. Function NETWORKDAYS.INTL

Calculate the number of days between April 1 and April 15 in Microsoft Excel.
Calculate the number of days between April 1 and April 15 in Microsoft Excel.

NETWORKDAYS.INTL is one of the most valuable functions you can find in Excel. Using this function, you can calculate the number of business days 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 represents the period of time you are working. Weekend specify which days of the week are considered weekends and holidays is the date range you would consider non-working.

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 7-digit string (for example, 0000011) where each digit represents a day of the week and 1 represents a non-business day.

7. SEQUENCE function

Create series of numbers in Microsoft Excel.
Create series of numbers in Microsoft Excel.

Whenever people want to try 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 series.

Xem thêm  Top 5 websites to create free flyers

The syntax of the SEQUENCE function is:

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

In there rows. rows specify the number of rows you want in the series, columns. columns set the number of columns, the start defines the starting number, and the step defines the increment between each number.

8. TRIM function

Output of the TRIM function in Excel.
Output of the TRIM function in Excel.

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

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

TRIM(text)

In there text. text refers to the text string or cell reference from which you want to remove extra spacing. This also resolves issues when using functions like VLOOKUP or MATCH, where extra spacing can cause problems.

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

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments