If you find yourself often flexing your hands and wasting hours to do things manually in Excel, you are missing out on how powerful Excel can be if you know how to use it the right way. Microsoft Excel is known for its formulas that can work efficiently over large numbers of data in the cell, without much manual involvement needed in the process.
We have compiled 17 advanced excel formulas that have an uncanny ability to transform lengthy mundane, manual tasks into a few seconds of work. These are formulas that every professional should have on their fingertips to save their own precious time, or to impress their boss at a critical moment.
Go through the list and do tell us your favourite!
1. INDEX MATCH
Formula = INDEX(C3:E9,MATCH(B13, C3:C9,0),MATCH(B14,C3:E3,0))
An excellent alternative to the VLOOKUP or HLOOKUP formula on Excel that has some drawbacks for performing lookup tasks. The INDEX MATCH is a combination formula of 2 separate functions:
INDEX: This formula returns the value of a cell in a table based on the column and the row number.
MATCH: This formula returns the position of a cell in a row or column.
An example of the INDEX and MATCH formulas combine is: When you are looking up and return a person’s height based on their name, you can use this formula to change both the variables (in this case, name and height) using the INDEX MATCH formula.
Let us break it down step-by-step:
How to Combine INDEX and MATCH
- Type INDEX
- Select Area you want to INDEX
- Lock the area with F4
- Find the Row you want to look for the data from
- Type MATCH with the series of information and lock the area with F4
- Type 0 for the exact match, close the brackets
- Hit Enter
Now you have a fully dynamic and functional set of columns and rows in the sheet where all combing through the cells and rows for the right data is done automatically.
To perform the average formula to find the mean of any range of numbers, here are the steps you need to follow:
Enter the values, cells or scale of cells that you’re calculating in the format.
The formula shall begin with =AVERAGE(number1, number 2, etc.)
If you want to perform the average of a range of cells in the sheet, here are the steps you need to follow:
- Enter the values, cells or scale of cells that you’re calculating in the format, just like you did above
- The formula shall be like =AVERAGE(Start Value: End Value).
If you are wondering how to enter the values for the region of cells, you can select the area on your sheet with your mouse and lock it in with F4. This shall make Excel do the rest of the work for you without doing any sum and division of the figure with the number of items in the group.
This formula in excel is denoted as SUMIF(range, criteria, [sum range]). This would result in the sum of the values within the desired Range of cells that would meet the requirements set by you. For example, =SUMIF(C3: C12, “>70,000) would return the sum of values between the cells of C3 and C12 from only the cells that have the value more than 70,000.
In the case of finances, salary, or even cost calculations, you need the value of leads who are associated with specific employees determined by the condition set by you. Doing this manually is very time consuming and slows down things.
The formula for the above condition can be =SUMIF(range, criteria, [sum_range]), where Range can be defined as the Range of the sheet that you need to choose the values from.[sum_range] is defined as the additional or optional Range that you’re going to add up in addition to the first Range entered.
Here’s an example:
4. OFFSET COMBINED WITH SUM
Alone, the OFFSET function may not be handy, but when combined with other services, you can get a complex formula with faster results if you want to create a dynamic role which can sum any variable number of cells, the regular SUM formula, which is static needs to be combined with the OFFSET function.
So for finding out the sum of values in variable cells, the formula should be:
Here the ending reference of the SUM Function gets replaced with the OFFSET function. The SUM formula starting in B4 ends with a variable and is an OFFSET formula starting at B4, continuing by the value in E2 (“3”) minus one. This helps the method to move over two cells and summing three years of data. In the above reference, you can see that the cell F7 holds the sum of the cells B4: D4 = 15.
5. IF AND
This formula comes handy in situations when you need to create certain conditions to comb through a heap of data. The IF statement helps in using the advanced excel IF function for creating a new field based on these conditions on an already existing track.
For example, if you want to mark the employees with salaries above 50K and employee ID greater than 3, then the formula will be:
Since there are no real cases in the above data, the formula would return the value 0.
If you have a lot of text strings in your datasheet and would like to make the data appear in one line, this formula is your go-to. For example, if you want to represent the employee ID and Employee name in a single column, the method should be:
This function will help you figure out the future payments due for a loan, given a specific interest rate, principal amount, and duration of the loan. Here is what you need to begin:
- The term of the loan
- The starting principal (money) of the loan
- The future value
- The type of loan
Now, if you want to find the monthly payment for a principal amount, the formula for the same will be:
= PMT (rate, per, PV, [fv], [type])
Let us understand this with an example:
And the best part is, you can drag the lower right corner of the PMT cells across the fields to automatically calculate the monthly payment amount for all the fields!
This is one of the most used formulas in excel that cleans any unwanted space in the fields. For example: If you need to remove the spaces at the beginning of some name, you can do that by using the TRIM function:
This would return you with the value of Chandan Kale without any extra spaces attached to the front or the end.
This is a function which tells you the number of characters in a string of text. One of the most exciting ways to use this is, for example, if you want to highlight the donors who donated over $1,000 by counting the number of digits in the donating column, the formula will be:
And if you want to highlight all the cells in the Donation column, then you need to:
- Select the Home tab in the menu
- Click on Conditional Formatting (in the toolbar)
- Select Use a Formula to determine which cells to format
Here, if you make the condition “>3”, then anything over $1,000 would receive the unique formatting, that you can choose by clicking the Format option.
This is a great function for scenario analysis in financial modelling. It allows you to pick between a specific number of options and return the “choice” which you’ve selected. For example, if you have three different values for revenue growth next year based on assumptions, using the CHOOSE function, you can return the amount according to your requirement.
The formula is:
11. CELL, LEFT, MID, and RIGHT
All the above functions can be combined in many ways to get some advanced formulas.
- The CELL function is used to return different types of information about the contents of the cell
- The LEFT service is used to replace text from the beginning of the cell.
- The MID function can return text from any of the starting points of the cell.
- The RIGHT function only returns text from the end of the cell.
12. XNPV and XIRR
For all the analysts who come across investment banking, equity research, or any other area of corporate finance that requires discounting cash flows, then these formulas are sure to save you a lot of time and grumbling!
For example, if you want to calculate the Present Net Value (NPV) for any investment using a specified rate of discounting and cash flows occurring irregular intervals, use the following function.
=XNPV(discount rate, cash flows, dates)
On the other hand, the XIRR function tells you the internal rate of return (where outflow = inflow) for a series of cash flows occurring at irregular intervals, such as:
13. COUNTA ( )
Analysts are often found on their toes struggling to find the number of cells with values (numbers, errors, text, logical values) and those that are empty. The COUNTA( ) function can help you find out the name of non-empty cells in a selected range. For example, the formula for counting values for a data sheet having columns A1-A10 is:
This formula comes in handy if you are looking to invest money in something and to know its worth. The requirements of the FV formula are:
- The interest rate of the loan
- Number of Payments
- The Payment for each period
- Current Starting Balance
- Type of Loan
For example, if you want to compare several blank CDs and you have a $20,000 inheritance to invest in a CD. The interest rates are represented in the decimal format; payments are zero. The formula for the scenario will be:
The results will be:
This function helps to select a number within a predefined range of numbers randomly. Once you put the lowest and the highest numbers in the formula, Excel can choose the right data from the fields to which the names in the Range are attached and randomly pick from them. The method for the scenario is:
=RANDBETWEEN(starting point, ending point)
The SMALL function in Excel returns numeric values based on the position of the value in a list which is ranked by importance. This function helps to retrieve the “nth smallest values” from an array or Range of cells like the smallest value, 2nd lowest value, 3rd lowest value, etc.
The syntax for the formula is
As the SMALL function is automatic, you need to supply a range and an integer for ‘nth’ to specify the ranked value. The official names for these arguments are ‘array’ and ‘k’.
This function returns the quartile (each of four equal groups) in a given set of data and can return minimum value, first quartile, second quartile maximum value. This function brings the quartile amount of the fields in an array. The function returns a numerical value according to the requested percentile.
Syntax: =QUARTILE (array, quart)
Microsoft Excel is quite inevitable when it comes to the 21st-century workplace, but the trick is that it doesn’t have to be so daunting. Make it your friend, and watch your productivity shoot up!
Latest posts by upGrad (see all)
- Top 5 Applications of Machine Learning Algorithms Using Cloud - November 12, 2019
- Top Data Science / Machine Learning Languages & Tools to Master in 2019 - November 12, 2019
- 4 Significant Ways an MBA Helps Your IT Career - November 6, 2019