Excel Advanced Formulas and Functions for Advanced Excel Training

If you’re ready to take your advanced Excel skills to the next level, here is a list of advanced formulas and functions to give you the headstart you need. Tailored for beginners and experts alike, this collection simplifies complex Excel features, helping you seamlessly transition from basic spreadsheets to advanced data manipulation. 

Whether you’re a student, professional, or data enthusiast, this MS Excel formulas list is perfectly designed to make learning easy. Once you learn advanced Excel spreadsheet formulas, you can be well on your path to becoming an expert in it.

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.

2. AVERAGE

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.

3. SUMIF

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:

=SUM(B4:OFFSET(B4,0,E2-1))

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.

Anybody who has devoted much time doing different types of financial models understands how complex nested IF formulas can be. Combining IF with the AND or the OR function can help keep the formulas easier to review and understand. So, this function is one of the easy-to-use advanced excel formulas.

For example, if you want to mark the employees with salaries above 50K and employee ID greater than 3, then the formula will be:

IF(AND(E4>3,F4>50000)1,0)

Since there are no real cases in the above data, the formula would return the value 0.

6. CONCATENATE

Concatenate is not a function on its own, but it’s only a creative way of combining information from various cells, thus making the worksheets more dynamic.  It is an efficient tool for financial analysts performing financial modeling. So, financial analysts consider Concatenate function as one of the most useful advanced excel formulas.

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:

=CONCATENATE(B3, C3)

7. PMT

The reason to name the function PMT is that it calculates the payment amount.

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
• Compulsory Parameters in the PMT formula:RateNperPvOptional Parameters in the PMT formula:[Fv][Type]

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:

=PMT(C2/12.B2.A2)

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!

8. TRIM

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:

=TRIM(C6)

This would return you with the value of Chandan Kale without any extra spaces attached to the front or the end.

9. LEN

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:

=LEN(B2)

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.

10. CHOOSE

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:

=CHOOSE(C7,D3,D4,D5)

How is the ‘CHOOSE’ Function useful in Excel?

When searching for a value equivalent to the index number or implementing the Vlookup function, you will realize that you have to pick the data from the left side because the Vlookup function works on the lookup value’s right side. Such problems can be solved through the CHOOSE function in excel. Therefore, it is one of the important functions in the advanced excel formulas list.

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!

XNPV and XIRR help you to include specific dates for each cash flow that is being discounted.  Excel’s basic NPV and IRR formulas assume that the periods between cash flow are the same.  Analysts may encounter situations where cash flows are not scheduled evenly. In that case, the XNPV and XIRR formulas can solve the problem; therefore, they are considered in the advanced excel formulas list.

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 ( )

This Microsoft Excel function counts the number of cells (whose values are not empty) and the number of value arguments given.  Note that a value argument is a parameter, not a cell or range of cells.

It is a built-in Excel function characterized as a Statistical Function. Moreover, it can be used as a worksheet function (WS). When working as a WS function, it can be applied as part of formula within a worksheet cell.

 Note:

• If the argument is a cell and it is not empty, its value is considered ‘1’.
• If the argument is a value, not a cell or range, its value is considered ‘1’.
• If the argument is a range, the value of each cell in the range which is not empty is considered ‘1’.

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:

=COUNTA(A1: A10)

14. FV

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:

=FV(A2/12,B2,C2,D2)

The results will be:

15. RANDBETWEEN

RANDBETWEEN is a volatile function, which will alter the values created every time you newly define the formula on the same range. It generates a single random number at a single iteration between a specified range of numbers.

For example, for random numbers between 1 and 6, this function generates only integer random numbers by default.

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)

In this syntax,

Starting point: A required argument that defines the smallest value that the function can return

It’s the lower limit of the range from which the random number will be generated.

This argument is mandatory, and you need to provide a numerical value that represents the minimum value of the range.

 ending point: A required argument that defines the largest value that the function can return

It’s the upper limit of the range within which the random number will be generated.

Similar to the starting point, this argument is also mandatory, and you need to provide a numerical value representing the maximum value of the range.

16. SMALL

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

=SMALL (times,range)

For example,

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

Important Points to Remember about SMALL function:

• It ignores text values and only considers numerical values.          
• It returns an error if no numerical values are available in the list.
• In the case of duplicates, the first value is considered the smaller one.
• K must be numeric; else, it returns the error as #VALUE!
• The given range must not be empty.
• When finding only the least value, you can use the MIN Function. It only finds the first smallest value.
• Although SMALL ignores text values, if any errors exist, it returns the result as #DIV/0!
• The SMALL function can be used with several other functions to find the Nth values
• When used with other functions, it works as an array formula

17. QUARTILE

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)

Uses of QUARTILE function:

This built-in function in Excel falls under the Statistical functions category. It is alternatively known as a Worksheet function in excel. Working as a worksheet function can be used as a part of the formula within a cell of a worksheet.

18. XLOOKUP

It is is one of the print functions. This function is available in the Excel 2021 and Excel for Microsoft 365. This is one of the advance excel formulas is the refined version of VLOOKUP. This formula can be used to scan the list, find the position value,  and return the value from the corresponding column in the same row.

XLOOKUP is one of the most dynamic and versatile Excel advanced formulas that has become a game-changer for Excel users. Unlike its predecessor, VLOOKUP, XLOOKUP allows users to search for a value in a range, find its position, and retrieve a corresponding value from the same row. 

What sets XLOOKUP apart is its flexibility—it doesn’t require the data to be sorted in any particular order, making it more user-friendly. This function is bidirectional, enabling searches from left to right or right to left, offering a comprehensive solution for various lookup scenarios. Its enhanced features, such as the ability to handle errors more effectively and work seamlessly with arrays, make XLOOKUP an indispensable tool for professionals engaged in intricate data analysis and lookup tasks. 

With its introduction in Excel 2021 and Excel for Microsoft 365, XLOOKUP represents a significant advancement in Excel’s functionality, streamlining data retrieval processes and contributing to a more efficient workflow.

19. VLOOKUP

Another one of the advance excel formulas is VLOOKUP, which has been a significant function since forever. It can also be used to scan the list in a column and return the matching value from any column in the same row. This advance excel formula is can make a really big difference if you know VLOOKUP.

As one of the most important Excel advanced formulas and functions, VLOOKUP is particularly valuable in scenarios where large datasets need to be navigated, providing a quick and systematic approach to data retrieval.

VLOOKUP operates unidirectionally, meaning it searches for values from left to right in a specified table array. It requires the data to be sorted in ascending order for accurate results, and the function can be sensitive to changes in the structure of the data. Despite these considerations, mastering VLOOKUP can significantly enhance data analysis capabilities, offering a reliable method for finding and referencing information within a spreadsheet. 

Whether you’re a financial analyst, data scientist, or business professional, understanding and using VLOOKUP is a fundamental skill that can contribute to increased efficiency and accuracy in Excel-based tasks.

20. FILTER

This is one of the most requested function which is used to filter a dataset in Excel and extract the useful information. This is one of the advanced excel formulas which is used to quickly filter the records to find any specific information.

As one of the most valuable advanced Excel formulas, this one is highly used for managing large datasets, allowing users to focus on relevant information and disregard unnecessary data.

One notable feature of the FILTER function is its versatility. Users can apply multiple criteria simultaneously, creating more refined filters to extract precisely the data they need. This makes FILTER an efficient tool for complex data analysis tasks, providing a streamlined and intuitive method for data extraction. Whether you’re dealing with financial data, sales records, or any other dataset, mastering the FILTER function empowers you to efficiently sift through information and uncover valuable insights within your Excel spreadsheets.

21. SORT and SORTBY

With the help of the excel formulas SORT and SORTBY the users can easily sort the data based on the desired column and row. It gives a good flexibility to choose any order based on preference. Moreover, the SORTBY formulas allows the users to sort the data based on multiple columns.

The SORT function allows users to sort data in ascending or descending order based on a single column or row. This feature is valuable when you need to organize information in a specific sequence to better analyze or present your data.

On the other hand, SORTBY is a more sophisticated formula that enables users to sort data based on multiple columns. This functionality adds a layer of complexity to the sorting process, allowing for a more nuanced arrangement of data. Sorting based on multiple columns is particularly useful when dealing with datasets that require a hierarchical or multi-factor sorting approach.

22. UNIQUE 

In the excel formulas list the function of UNIQUE has to be of mention. The UNIQUE function helps in giving the list of unique names. Before this function, there was a lot of labour involved in removing the duplicate functionality and using the advanced filter functionality.

With the UNIQUE function, users can now effortlessly extract a list of distinct values from a given range, streamlining data cleaning and analysis processes. This function is particularly beneficial when dealing with datasets that might contain redundant information, offering a quick and efficient solution to identify and work with unique values.

The UNIQUE function enhances the overall data management capabilities of Excel, providing users with a straightforward method to handle and analyze datasets with diverse information. Whether you’re preparing reports, conducting data analysis, or simply organizing information, the UNIQUE function proves to be an invaluable asset, significantly reducing the time and effort previously required for handling duplicate values in Excel spreadsheets.

23. TEXTJOIN

The advanced excel of TEXTJOIN is one of the relatively new function which has come into picture recently in Excel 2019. As the name suggests, the formula can be used to quickly combine the content of a selected range of cells without the need of craetion for the concatenate formula.

This function is particularly handy when you need to concatenate text values with a specified delimiter, such as a comma or space. TEXTJOIN not only simplifies the process but also enhances flexibility by enabling users to skip empty cells or include a delimiter only between non-empty cells.

With its introduction, TEXTJOIN has become a time-saving feature for professionals dealing with text manipulation in Excel. Whether you’re working on reports, generating summaries, or combining data from different cells, the TEXTJOIN function provides a more streamlined and intuitive approach to text concatenation, contributing to increased efficiency in spreadsheet tasks.

24. FIND/ SEARCH

This is one of the excel advanced formulas which are used to search for a given a text in a cell and helps to return to the starting position of a string which the users searched for.  With the use of LEFT/RIGHT/MID, one can do some serious manipulation and make the most out of the function.

The FIND function is case-sensitive, meaning it distinguishes between uppercase and lowercase letters. It returns the starting position of the first occurrence of the searched text, allowing users to pinpoint the exact location.

On the other hand, the SEARCH function is case-insensitive, making it more versatile when the case of the text is unknown or irrelevant. Like FIND, SEARCH returns the starting position of the first occurrence of the specified text in a cell.

When combined with other functions like LEFT, RIGHT, or MID, FIND and SEARCH become powerful tools for text manipulation. Users can extract substrings, manipulate text based on its position, or perform various operations to derive meaningful insights from the data.

25. REPLACE/SUBSTITUTE 

As the name suggests, these functions clearly help to find some text in a text free space and replace or substitute with the desired data. In the advanced excel formulas list, this function had to be there. REPLACE function can help to identify the starting position and the total characters one wants to replace with the specified text string.

This function provides more control over the replacement process compared to SUBSTITUTE, as it allows you to specify the position and length of the substring to be replaced.

Both functions are powerful in scenarios where text manipulation is required, enabling users to update, edit, or modify textual data within Excel cells. Whether you’re cleaning up data, making corrections, or adapting information to a specific format, the REPLACE and SUBSTITUTE functions offer precise control and flexibility in managing text in your spreadsheets.