Top 5 Advanced MS Excel Functions That Will Make You an Expert User
MS Excel is one of the most popular data processing tools out there and there is a reason for this – this program can save you so much time once you learn how to use it properly. Check this article to see which are the most important advanced tips and tricks in Excel and why it is so important to learn them. If you are a beginner Excel user, I will advise you to read this article first: 20 Excel Formulas You Should Start Using Now.
Index - Match
This is a gorgeous MS Excel function that is similar to vlookup, however, it has much more functionalities. In fact, the only advantage of vlookup is that it is easier to learn. Once you step your foot into Index-Match, you will forget about vlookup. Now, let’s make a quick revision what vlookup can do. This is an Excel formula that lets you compare data from different tables, different sheets and even different workbooks. You can use this function to take data from one table and add it to another. Let’s have a look at the following example:
We have a table with employees, their position, the companies that they work for and their monthly salaries. We want to display on a separate cell where does any of the employees work.
Let’s say we want to check this info for cell A7 (Ralf Grutzen). The formula will return Soprano LTD, the company where the employee from cell A7 works for:
We can accomplish the same result with index match. This formula will also return Soprano LTD:
What does the Index-match function do? As we can see, it has a different logic than vlookup. It actually works the other way around. It does not start with the determinant (the value we are using to get the result). Instead, it starts from the column with the results (C1:C8) and looks for a match in the determinant’s column. This property is one of the function’s biggest advantages over vlookup. We will find out shortly why.
Now, let’s try to use both formulas to answer the following question: Who is the employee of Soprano LTD? Try to do it with vlookup first. It doesn’t work, does it? The reason for this is that vlookup has very limited usage. You cannot get data from the left side of the determinant. When the determinant is in Column C (cell C7 in our case), you can only get data from columns D,E,F, etc. However, you cannot reach the data from the left side of the column. Now, let’s check if we can get the result with index-match.
The function returns Ralf Grutzen and this is the correct answer.
Conclusion: We have just found out that we cannot use vlookup to get values from the left-side of the look-up array. Instead, we should use index-match.
Index-match has a lot more advantages than vlookup and it is the preferred function for the Excel experts. Other reasons for that include:
- The table can easily be expanded by adding new columns without breaking the formula. A new column can be added in the middle of the table, at the beginning or at the very end. The function should continue to work properly.
- There is a lesser error rate when using index-match. The reason for this is that only two columns are used and there is no need to count the columns manually in order to construct the formula. This is especially important when workng with large files with a lot of columns
- Unlike vlookup, you can use Index-match to make a horizontal lookup (hlookup). In vlookup, you can only look up vertically.
- Last but not least, Index-match occupies significantly less computing power on the computer, so it is the mandatory way to search in larger tables with more than a thousand rows, as it works several times faster than vlookup.
The array functions are advanced Excel functions, because you need to use data from more than one cell in order to return a result. Moreover, these functions are not entered as the other Excel functions. Normally, excel functions work when you press “enter” on your keyboard. However, array formulas do not work in this way. Instead, you should press “ctrl+shift+enter” to make them work.
The arrays are extremely useful when you want to multiply matrices or when you want to sum numbers from even/odd rows only. Let’s have a look at this example: we want to multiply the numbers from cells C and D. After that, we want to calculate the sum of their multiplied numbers.
We can use two ordinary functons to do this or we can use the following array formula:
One big advantage of that method is when we have a very large workbook with thousands of rows. Moreover, the risk of making mistakes is also lower, when using array functions. This is why I highly recommend you to start using them now.
Sumifs / Countifs
Sumifs and Countifs are another great functions that let you sum or count data based on multiple criteria. This can be very useful in many cases. Let’s have a look at the following example. We have a column with products, suppliers, quantity and delivery date. We want to know the total quantity of a specific product delivered by a specific supplier. To do that, we need to use the following function:
We can see that George has supplied 3 tons of apples in total.
Goal seek is an amazing financial instrument. It is an extremely helpful tool when working with financial data. It lets you determine how much you need to increase x, so you can reach a certain level of y.
Let’s have a look at this example. We have a table with sales for a certain amount of time. We want to find out how much sales we need to do for the next month, so we can reach a certain revenue threshold for the next quarter.
Our total revenues are 76,945 €. Let’s say we want to reach 100,000 € by the end of the next month. In order to estimate how much sales we need to generate in the next month, we should use the goal seek tool in Excel. To do that, go to data tab -> data tools group -> what if analysis -> go seek and set the parameters:
The goal seek function tells us we need to do 512 sales in the next month to reach that threshold.
XNPV and XIRR
If you are working in the corporate finance field, these functions are exactly what you need. They let you estimate the discounted cash flows and the net present value of a project. The XNPV formula lets you estimate the NPV by using the following parameters: the case flows, the dates to which the cash flows are discounted and the discount rate. You can check this tutorial for a detailed explanation how to use these functions: Why use the XNPV function in Excel?
This article aims to show the most important advanced Excel functions and tools that you can use to significantly improve your Excel skills. You can use these techniques to improve your user experience with the software, make your life easier, become more efficient and even to get a promotion.
© 2017 Atanas Yonkov