7 Most Underrated Excel Functions You Must Try

Microsoft Excel is such a complex program but still it is used worldwide to perform various tasks such as analyzing, auditing, and calculating data. Even though MS Excel provides varieties of functions to perform countless tasks easily. However, some of the Excel functions are used by many users regularly. So, in this post, we will discuss the most underrated Excel functions in detail so that should be used rarely.

So, let’s get started…

What Are the Most Underrated Excel Functions/Obscure Excel Functions?

Well, some of the Excel functions are so specific that only a number of statistical, financial, or the engineering specialists, understand and use them. And most of them used to use some functions on a regular basis that should not be used so often.

So, let’s proceed to mention the most underused Excel functions or obscure Excel functions so that they are least used by the users.

1: VLOOKUP

VLOOKUP is one of the Excel’s highly useful functions, but this is also one of the least understood functions. Here know why?

Underused:

The VLOOKUP function has a certain limitation in it and this is the reason it is least used by the users. The hardest part of using this function is understanding exactly what it is for.

The main reason behind its limited use is its design flaw. This by default presumes the results are OK with an approximate match, however, they are probably NOT. And this can cause results that look completely normal even though they are totally incorrect.

Another biggest limitation of VLOOKUP is that it only looks right to retrieve data. This means, this only obtains data from columns to the right of the first column in the table.

Well, these are a few limitations due to which this is least used by the users, but still, as I said above it is highly useful. Here know HOW???

Why Is It Useful?

VLOOKUP is designed to recover data from a particular column in a table. This Excel function helps to find out a value in an Excel list or table. This is relatively easy to use, only you need to know how to use it and what exactly it is.

VLOOKUP looks at a value in one column and finds out its corresponding value on the same row in another column. This is very useful for the business person as well as the retailers. One can search for an item name and in return get the number or price with the VLOOKUP function.

One can format it for a table to calculate price or discount on an item in a lot easier way.

Also Read: HLOOKUP Vs VLOOKUP: Clear Contrast Between Excel Lookup Functions

2: HLOOKUP

Now comes the HLOOKUP function, which is very much similar to the VLOOKUP function.

And this is also the least used by the users. Here check out the complete information why it is underused and why it is useful.

Underused:

Just like the VLOOKUP function, this is also having some limitations and this makes it an underused function of the Excel.

The HLOOKUP function only looks for the value in the first row and returns another value from the same column in that table. The main reason that clearly identifies why it is underused is this is not very consistent.

And as result, you need to do other modifications.

Why Is It Useful?

This is useful because this can find an exact match in the lookup row or the closest match. Apart from that it can be used as a worksheet function (WS) in Excel.

This is highly useful if you need to find out the sales of the total in a particular region and as find the interest rate in effect on a particular date.

3: IF- Underrated Excel Function

Underused:

IF is another function in Excel that is highly popular but least used by the Excel users. The main reason is this works in a similar manner and as well there is a limit to an amount of arguments an IF function can have.

Also, the multiple IF functions require multiple opening and closing of parentheses (), that can be relatively difficult to manage.

However, despite its limitation, this is also very useful to the Excel user. If you are wondering how to automate tasks in Excel then read our guide.

Why Is It Useful?

This is highly useful because, with the help of it, users can make logical comparisons between a value and what you expect.

The IF function carries out a logical test and returns the first value for a TRUE, and the second for a FALSE result. This is easy to use and makes the comparison lot easy by testing for a specific condition.

The main reason behind using the IF function is to trap errors, handle them easily, and better user experience. This is the simple to use and handles errors in an elegant way.

4: INDEX

Underused:

The main reason behind its limited use is its popularity and it is quite a complex function. This function provides the result but needs the position of rows and columns. And this is often used with the MATCH function, where MATCH traces and provides a position to INDEX.

INDEX Excel function is a bit difficult to understand by the users and due to this is not so popular among the users.

Why Is It Useful?

The INDEX function makes the work easier by returning either the value or the reference to a value from a table or range. This can be used to recover individual values or entire rows and columns.

The user can use this as a worksheet function in Excel. And as a worksheet function, the INDEX function can be entered as a part of a cell of a worksheet and this makes the work lot easier.

The INDEX function is easily created and provides the exact result in a couple of minutes.

Also Read: Everything You Need to Know About Excel Matrix Functions

5: INDIRECT

The INDIRECT function is the one that is known by the many Excel users but still used by the least Excel users. Her know Why???

Underused:

As the name suggests, the Excl INDIRECT function is used to indirectly reference cells, ranges, other sheets or workbooks.

This is function is quite tricky and as well consistent. As the indirect references won’t change when new rows or columns are inserted in the worksheet or when the existing rows/columns are deleted.

But despite these flaws, this is still useful here know How???

Why Is It Useful?

This function is useful when the user wants to return a value, based on a text string.

Apart from that with the INDIRECT function, a particular cell in a formula can be locked. And if rows or columns are inserted or deleted above or to the left of that cell, the reference does not change.

One can also create the formula with the INDIRECT function, by making use of the references to a sheet name and cell name.

The INDIRECT reference is not limited to building “Dynamic” cell references but also for others tasks as well.

Well, these are some of the uses of the INDIRECT function, only you need to know is how to correctly use it.

6: TRANSPOSE

The TRANSPOSE function in Excel is a bit tricky and needs complete knowledge to use it.

Underused:

The main reason behind the limited use of the TRANSPOSE function is this is able to transpose limited data when the user enters a large amount of data it starts displaying errors.

Another reason is not being used carefully this can create duplicates. And as a result, if the original cells change, the copies will not get updated.

Its certain limitation makes it an underused function.

Why Is It Useful?

Apart from its limitation this still has certain uses that make the work easy for Excel users.

This function can be used to transpose a vertical cell range where the data runs down the rows of adjacent columns to one where the data runs across the columns of adjacent rows and vice versa.

The user can make use of the TRANSPOSE function if they don’t want to type the whole formula. This makes the work easy for the users.

This is a worksheet function and applies best to all the earlier Excel versions.

7: SUMIF

This is the last but not the least Excel underused function in our list. Here follow the information about it.

Underused:

The SUMIF function’s biggest reason behind its underused is this look for certain criteria and if this finds it then it will Sum up the related cell. Otherwise, leaves it.

And the biggest disadvantage is that the SUMIF family is not used as an array formula.

This is highly popular but due to its certain limitation, we mention it in our list of underused Excel functions.

Why Is It Useful?

This is a highly popular and commonly used function in Excel.

This is useful when we need to sum against given criteria we use this function.

The SUMIF function makes the work easy for the users, as this can be entered as a part of a formula in a cell of a worksheet.

So, for adding numbers in a range based on multiple criteria, using the SUNIFs function is worth it.

This logical Excel function is great and makes the work easy for the users. And the best is SUMIF () formulas are faster than most of the custom-produced array formulas.

Also Read: How To Use Excel ISERROR Function?

Final Thoughts:

Well, I tried my best to put together some of the underrated excel functions. I guess after reading this article, you will definitely use these Excel functions very rarely.

We love to hear from you.

So, please let us know which underused Excel functions you like the best. Also, if you have any queries, or other MS Excel skills that I haven’t mentioned in this article then feel free to reach out on our social accounts Facebook and Twitter.

That’s it…