How-to

10 Excel formulas for advanced users

by XLTools team
July 15, 2022
10 min read
no comments

Excel is a powerful tool that you can use to perform a variety of tasks. However, many people never tap into its true potential because they only know the basic formulas. In this article, we’re going to show you 10 Excel formulas that are essential for any advanced user.

These formulas will come in handy when you need to perform complex calculations or when you need to work with large data sets. So without further ado, let’s get started!

1. VLOOKUP

The VLOOKUP function is one of the most popular formulas in Excel. It allows you to look up a value in a table based on the value of another column. For example, let’s say you have a list of employees and their corresponding department codes.

If you want to look up an employee’s department name, you can use the VLOOKUP function. All you need to do is specify the employee’s name and the formula will return the department name. This is how the syntax looks like:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

You can combine the function with Excel checkboxes to create an interactive checklist if that's what your preferences and needs are.

2. INDEX/MATCH

The INDEX/MATCH function is similar to the VLOOKUP function. However, it’s more flexible because it allows you to look up a value in a table based on multiple criteria. For example, let’s say you have a list of employees and their corresponding department codes and job titles.

If you want to look up an employee’s department name and job title, you can use the INDEX/MATCH function. This is how the syntax looks like:

=INDEX(return_range,MATCH(lookup_value,lookup_range,0))

3. SUMIF

The SUMIF function allows you to sum a range of cells based on a criteria. For example, let’s say you have a list of expenses and you want to sum only the food expenses.

You can use the SUMIF function to do that. This is how the syntax looks like:

=SUMIF(range,criteria,sum_range)

4. COUNTIF

The COUNTIF function allows you to count the number of cells that meet a certain criteria. For example, let’s say you have a list of items and you want to count how many are red.

You can use the COUNTIF function to do that. This is how the syntax looks like:

=COUNTIF(range,criteria)

5. IFERROR

The IFERROR function allows you to handle errors in your formulas. For example, let’s say you have a formula that returns an error when it can’t find a match.

If you want to hide the error and return a blank cell instead, you can use the IFERROR function. This is how the syntax looks like:

=IFERROR(formula,value_if_error)

6. CONCATENATE

The CONCATENATE function allows you to join two or more values together. For example, let’s say you have a first name and a last name in two different cells.

If you want to join them together in one cell, you can use the CONCATENATE function. This is how the syntax looks like:

=CONCATENATE(value1,value2,...)

7. TEXTJOIN

The TEXTJOIN function is similar to the CONCATENATE function. However, it’s more flexible because it allows you to specify a delimiter. For example, let’s say you have a list of items that you want to join together with a comma as the delimiter.

You can use the TEXTJOIN function to do that. This is how the syntax looks like:

=TEXTJOIN(delimiter,ignore_empty,text1,text2,...)

If you need even more flexibility, try the XL.Concatenate function that is available in XLTools Add-in. It helps сoncatenate all values in a range into a single string, optionally with any delimiter and qualifiers.

8. TRANSPOSE

The TRANSPOSE function allows you to transpose a range of cells. For example, let’s say you have a row of data that you want to convert into a column.

You can use the TRANSPOSE function to do that. This is how the syntax looks like:

=TRANSPOSE(array)

9. OFFSET

The OFFSET function allows you to reference a cell or a range of cells relative to another cell. For example, let’s say you have a list of data and you want to reference the third cell in the list.

You can use the OFFSET function to do that. This is how the syntax looks like:

=OFFSET(reference,rows,cols)

10. CHOOSE

The CHOOSE function allows you to return a value from a list of values based on an index number. For example, let’s say you have a list of colors and you want to return the color at index 2.

You can use the CHOOSE function to do that. This is how the syntax looks like:

=CHOOSE(index_num,value1,value2,...)

These are just some of the many formulas that you can use in Excel. And if Excel does not have what you need, take a look at the functions within XLTools Add-in. Stay safe and have fun!

Tags:
DataPrep ExcelFunctions
Any questions or comments?