7 Underrated Excel Functions That I Wish Knew Before

We all know how complex is Excel program. But behind of its complexity this is still used worldwide to perform various tasks such as analyze, audit and calculate data.

Therefore due to this Excel provides varieties of functions to perform various tasks easily.

However, some of the Excel functions are used by many Excel users regularly. Whereas some of the Excel functions are so specific that only a number of statistical, financial, or the engineering specialists, understand and use them.

Well, these underused Excel functions are also useful but due to certain reasons least used by the users.

Today, here in this tutorial, we are going to explore some of the useful underused Excel functions. Learn about some interesting Excel function that you need to use to become more productive.

Also why it is useful and as well why it is least used by the users.


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


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. Means, this only obtain data from columns to the right of the first column in the table.

Well, these are 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 businesses person as well as the retailers. One can search 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 lot easier way.

Additional Reading: 

  1. 11 Vital Tips for Optimizing Excel Spreadsheets and Speed-up your Excel
  2. 11 Simple Yet Powerful Excel Troubleshooting Tricks for Analyzing Data
  3. 30 Tips & Tricks to Master Microsoft Excel
  4. 16 Advanced Excel Skills To Succeed at Office


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

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


Just like the VLOOKUP function this is the 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.

For Example: While copying and pasting formula to another cell the HLOOKUP function delivers #N/A error, if not find the match.

And as result, you need to do other modification.

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 is 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 the particular region and as find the interest rate in effect on a particular date.

3: IF


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 in 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.

Why Is It Useful?

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

The IF function carry out a logical test and return first value for a TRUE, and 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 handle errors in an elegant way.



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 column. 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.


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???


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

This is a 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.


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


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 used carefully this can creates duplicates. And as a result, if the original cells changes, the copies will not get updated.

Its certain limitation makes it’s the underused function.

Why Is It Useful?

Apart from its limitation this still has certain uses that make the work easy for the 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 with all the earlier Excel versions.


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


The SUMIF function 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 highly popular and commonly used functions in Excel.

This is useful when we need sum against a 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.

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.

Final Thoughts:

Well, I tried my best to put together some of the underused Excel functions.

I guess after reading this article, you must make use of these Excel function in future.

However, after writing the article, it is clear to me behind of its certain limitation they are useful also in certain places.

So I am going to use it in future to carry certain things in Excel and make the work easy for me.

What about you??? Learn them out completely and use them in future to become more productive and work easily.

We love to hear from you. So, please let us know which underused Excel functions you like the best and also if you have any query, other MS Excel skills that I haven’t mention in this article than do let me know, you can visit our comment section box below or visit our Repair MS Excel Ask Question.

That’s it…

7 Underrated Excel Functions That I Wish Knew Before
Article Name
7 Underrated Excel Functions That I Wish Knew Before
Learn the underused Excel functions as well as its uses to carry out certain tasks easily and quickly by learning them completely...
Publisher Name
Repair MS Excel Blog
Publisher Logo

Margret Arthur is an entrepreneur & content marketing expert. She writes tech blogs and expertise on MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in easy to understand language is very impressive. When not writing, she loves unplanned travels.