60 Advanced Excel Formulas – A Must Know For All Professionals

Ever found yourself staring at a massive Excel spreadsheet, wondering how to make sense of all the numbers? That’s where Excel formulas come to the rescue! Whether you're managing financial reports, analyzing sales data, or planning a project, formulas in Excel simplify complex calculations, save time, and enhance accuracy.

From basic functions like SUM and AVERAGE to advanced Excel formulas like VLOOKUP and INDEX-MATCH, these tools are designed to boost productivity and streamline workflows. Learning these formulas isn't just about crunching numbers—it's about making smarter decisions faster.

In this guide, we’ll explore the power of Excel formulas, highlight their importance in data analysis, and provide a list of advanced Excel formulas every professional should know. 

Let’s unlock the full potential of Excel together!

List of Top 60 Advanced Excel Formulas Everyone Should Know in 2025

Excel formulas are essential tools for simplifying complex calculations, analyzing data efficiently, and boosting productivity. Learn how to use Excel effectively with this comprehensive tutorial below is a comprehensive list of 60 advanced Excel formulas that are categorized to help you understand and utilize them effectively:

Mathematical Formulas

These formulas help you perform basic and complex calculations, such as addition, multiplication, and rounding values.

Explore more about Excel worksheets and formulas in this tutorial.

1. SUM()

SUM() adds all the numbers in a specified range or set of values. It works only on cells with numerical data.

Explanation: =SUM(D1:D3) will add the values in cells D1 through D3, providing a total. 

2. AVERAGE()

AVERAGE() calculates the mean of a range of numbers by dividing their total by the count of the numbers.

Explanation: =AVERAGE(C3:C10) computes the average of values in cells C3 to C10, a core formula for data analysis.

3. COUNT()

COUNT() counts the number of numeric values in a specified range.

Explanation: =COUNT(D1:D10) counts how many numeric entries exist in cells D1 to D10. It’s useful for large datasets.

4. COUNTA()

COUNTA() counts all non-empty cells, including text and numbers.

Explanation: =COUNTA(C1:C15) counts all non-blank cells in the range C1 to C15.

5. COUNTIF()

COUNTIF() counts the number of cells in a range that meet a specific condition.

Explanation: =COUNTIF(A2:A9, "Printer")  Here, we want the COUNTIF function to count the number of items, in the range A2:A9 

6. SUMIF()

SUMIF() adds numbers in a range that meet a specific condition.

Explanation: 

• Range: B2:B10 - The range where the condition (e.g., "North") is checked.
• Criteria: "North" - The condition to filter the range or a cell reference like F1 containing the condition.
• Sum_range: C2:C10 - The range of sales amounts to the sum where the condition is met.
• Formula: =SUMIF(B2:B10, "North", C2:C10) - Adds values from C2:C10 where B2:B10 equals "North."
• Dynamic Option: Replace "North" with F1 for flexibility: =SUMIF(B2:B10, F1, C2:C10

7. SUMIFS()

SUMIFS() adds numbers in a range based on multiple conditions.

Explanation: =SUMIFS(K6:K10, L6:L10, ">50",M6:M10, "<100") adds values in K1:K10 where L1:L10 is greater than 50 and M1:M10 is less than 100.

Statistical Formulas

Statistical formulas help find averages, minimums, maximums, and more useful for analyzing data. Learn about the differences between Google Sheets and Excel for data analysis.

8. MIN()

MIN() returns the smallest value in a range.

Explanation: = MIN(number1, [number2], …)’. If it’s a range, then the formula will look something like, ‘=MIN(C2:C9)’

9. MAX()

MAX() returns the largest value in a range.

Explanation: =MAX(B2:B8) gives the highest number in the range B2 to B8.

1. Range: B2:B8 is the range of values.
2. Formula: =MAX(B2:B8) calculates the largest value in this range.

Result:

• The highest number in the range B2:B8 is 92. This value will be displayed wherever the formula is applied.

Get more insights about the MAX() function in this detailed tutorial.

10. PRODUCT()

PRODUCT() multiplies all numbers in a range.

Explanation: =PRODUCT(X2:X6) multiplies the numbers in cells X2 through X6.

• Result: The result of multiplying 2 * 3 * 5 * 4 * 6 is 720. This value will appear in the cell containing the formula.

11. POWER()

POWER()  raises a number to the power of an exponent.

Explanation: =POWER(3, 2) returns 9, which is 3 squared.

1. Base: B2 contains the base number (3).
2. Exponent: C2 contains the power to which the base is raised (2).
3. Formula: =POWER(B2, C2) calculates 3 raised to the power of 2.

Result:

• The result is 9 since 32=93^2 = 932=9. This value appears in the corresponding cell.

12. SQRT()

SQRT() calculates the square root of a number.

• Explanation: =SQRT(16) returns 4.
• Value: A2 contains the number (16) for which you want to find the square root.
• Result: The square root of 16 is 4, which appears in the cell containing the formula.

Formatting Formulas

These formulas allow you to format data, such as rounding numbers or converting them into specific formats. Discover how to enhance data presentation with Excel formatting techniques.

13. ROUND()

ROUND() rounds a number to a specified number of digits.

• Explanation: =ROUND(123.456, 2) returns 123.46.
• Value: A2 contains the number (123.456) that needs rounding.
• Result: The rounded value of 123.456 is 123.46, which appears in the formula cell.

14. ROUNDUP()

ROUNDUP() rounds a number up to the nearest specified decimal place.

• Explanation: =ROUNDUP(123.456, 1) returns 123.5.
• Value: A2 contains the number (123.456) that you want to round up.
• Result: The value 123.456 is rounded up to 123.5, regardless of the remaining decimals. This result will appear in the cell containing the formula.

15. ROUNDDOWN()

ROUNDDOWN() rounds a number down to the nearest specified decimal place.

• Explanation: =ROUNDDOWN(123.456, 1) returns 123.4.
• Value: A2 contains the number (123.456) that you want to round down.
• Result: The value 123.456 is rounded down to 123.4, and this appears in the formula cell.

16. CEILING()

CEILING() rounds a number up to the nearest multiple of a specified significance.

• Explanation: =CEILING(23.25, 0.5) returns 23.5.
• Value: A2 contains the number (23.25) that you want to round up to the nearest multiple.
• Result: The value of 23.25 is rounded up to 23.5, the nearest multiple of 0.5. This result appears in the cell with the formula.

17. FLOOR()

FLOOR() rounds a number down to the nearest multiple of a specified significance.

• Explanation: =FLOOR(23.25, 0.5) returns 23.
• Value: A2 contains the number (23.25) that you want to round down.
• Result: The value of 23.25 is rounded down to 23, the nearest lower multiple of 0.5. This is particularly helpful for rounding values to specific intervals.

Logical Formulas

Logical formulas evaluate data based on specific conditions, returning TRUE or FALSE results.

18. IF()

IF() returns one value if a condition is TRUE and another if it is FALSE.

Explanation: =IF(A4>50, "Pass", "Fail") checks if A4 is greater than 50 and returns "Pass" or "Fail."

1. Condition: Check if the value in column A is greater than 50.
2. Formula:
   1. If the condition is TRUE, it returns "Pass".
   2. If the condition is FALSE, it returns "Fail".
3. Explanation Results:
   1. For A4=75, it returns "Pass" since 75 > 50.
   2. For A5=45, it returns "Fail" since 45 is not greater than 50.
   3. For A6=55, it returns "Pass" since 55 > 50.

19. AND()

AND() checks multiple conditions and returns TRUE if all conditions are met.

Explanation: =AND(A4>0, B4<100) returns TRUE if both conditions are met.

1. Condition 1: Checks if the value in column A is greater than 0.
2. Condition 2: Check if the value in column B is less than 100.
3. Formula:
   1. If both conditions are TRUE, the formula returns TRUE.
   2. If either or both conditions are FALSE, it returns FALSE.
4. Explanation Results:
   1. For A4=50 and B4=80, both conditions are met, so the result is TRUE.
   2. For A5=-10 and B5=90, the first condition is FALSE, so the result is FALSE.
   3. For A6=30 and B6=120, the second condition is FALSE, so the result is FALSE.

20. OR()

OR() checks multiple conditions and returns TRUE if at least one condition is met.

Explanation: =OR(A2>50, B2<20) returns TRUE if any condition is met.

1. Condition 1: Checks if the value in column A is greater than 50.
2. Condition 2: Check if the value in column B is less than 20.
3. Formula:
   1. If either condition is TRUE, the formula returns TRUE.
   2. If both conditions are FALSE, it returns FALSE.

Result

1. Row 1: A2=60 and B2=15.
   1. Condition 1: TRUE (60 > 50).
   2. Condition 2: TRUE (15 < 20).
   3. Result: TRUE (either condition is TRUE).
2. Row 2: A3=40 and B3=25.
   1. Condition 1: FALSE (40 is not > 50).
   2. Condition 2: FALSE (25 is not < 20).
   3. Result: FALSE (both conditions are FALSE).
3. Row 3: A4=30 and B4=10.
   1. Condition 1: FALSE (30 is not > 50).
   2. Condition 2: TRUE (10 < 20).
   3. Result: TRUE (either condition is TRUE).

21. NOT()

NOT() reverses the logical value of a condition, returning TRUE for FALSE and vice versa.

Explanation: =NOT(A2>50) returns TRUE if A2 is not greater than 50.

1. Condition: Checks if the value in column A is not greater than 50.
2. Formula:
   1. NOT() inverts the logical value of the condition:
      1. If A2 > 50, the formula returns FALSE.
      2. If A2 <= 50, the formula returns TRUE.

Explanation Results:

1. Row 1: A2=40.
   1. Condition: 40 > 50 is FALSE.
   2. NOT(FALSE) returns TRUE.
2. Row 2: A3=60.
   1. Condition: 60 > 50 is TRUE.
   2. NOT(TRUE) returns FALSE.
3. Row 3: A4=50.
   1. Condition: 50 > 50 is FALSE.
   2. NOT(FALSE) returns TRUE.

22. IFERROR()

IFERROR() returns a specified value if a formula results in an error, otherwise it returns the formula’s result.

Explanation: =IFERROR(A2/B2, "Error") returns "Error" if B2 is 0 or blank.

1. Condition: The formula divides the value in column A by the value in column B.
2. Error Handling:
   1. If the division operation results in an error (e.g., dividing by 0 or blank cells), the formula returns "Error".
   2. Otherwise, it returns the result of the division.

Result: 

1. Row 1: A2=10, B2=2.
   1. Division: 10 / 2 = 5.
   2. Result: 5.
2. Row 2: A3=15, B3=0.
   1. Division: 15 / 0 results in an error (#DIV/0!).
   2. IFERROR replaces the error with "Error".
3. Row 3: A4=20, B4=(Blank).
   1. Division: 20 / Blank results in an error.
   2. IFERROR replaces the error with "Error".

Lookup and Reference Formulas

These are powerful for searching data within large datasets, like VLOOKUP or INDEX-MATCH.

23. VLOOKUP()

VLOOKUP() searches for a value in the first column of a range and returns a value in the same row from another column.

Explanation: =VLOOKUP(101, A2:C5, 2, FALSE) finds 101 in column A and returns the value in column B from the same row.

1. Search Value: The formula searches for 101 in Column A (ID).
2. Range: The data range is A2:C5 (all columns containing the data).
3. Column Index: 2 specifies that the value to return is from Column B (Name).
4. Match Type: FALSE ensures an exact match for the value 101.
5. Result: The formula finds 101 in Column A and retrieves the corresponding value "Vikram" from Column B. 

24. HLOOKUP()

HLOOKUP() searches for a value in the first row of a range and returns a value in the same column from a specified row.

Explanation: =HLOOKUP("Region", A1:C3, 3, FALSE) finds "Region" in row 1 and returns the value in row 3 from the same column.

1. Lookup Value: "Region" is the value to be found in Row A (headers).
2. Table Range: A1:C3 specifies the range where the search will occur, spanning rows A through C and columns 1 through 3.
3. Row Index: 3 specifies that the value to return is from Row C (States).
4. Match Type: FALSE ensures an exact match for the lookup value "Region"
5. Result: The formula finds "Region" in Row A and retrieves the corresponding value "South" from Row C.

This example is ideal for data organized horizontally with region-specific information.

25. INDEX()

INDEX() retrieves the value of a cell within a range based on specified row and column numbers.

Explanation: =INDEX(A3:C5, 2, 3) returns the value in the second row and third column.

1. Range: A3:C5 defines the area of the table you want to search.
   1. It includes rows 3 to 5 (Mango to Peach) and columns A to C.
2. Row Number: 2 specifies that the formula will retrieve data from the second row of the range (Row 4 in the sheet).
   1. The second row in the range (A3:C5) corresponds to Row 4 in the original sheet.
3. Column Number: 3 specifies that the formula will retrieve data from the third column of the range (Column C in the sheet).
4. Result: The value in the second row and third column of the range A3:C5 is Grapes.

26. MATCH()

MATCH() returns the position of a value in a range.

Explanation: =MATCH(50, A1:A10, 0) finds 50 in the range A1:A10 and returns its position.

Explanation:

1. Range: A1:A10 specifies the range of cells in Column A where the formula will search for the value 50.
2. Lookup Value: 50 is the value being searched within the specified range.
3. Match Type: 0 specifies an exact match.
4. Result: The value 50 is found in the eighth position within the range A1:A10.The formula returns to position 8, indicating that 50 is the fifth item in the specified range.

27. OFFSET()

OFFSET() returns a cell or range of cells that is a specific number of rows and columns away from a starting point.

Explanation: =OFFSET(A2, 3, 2) returns the value two rows below and one column to the right of A2

1. Starting Reference (A2):
   1. The formula starts at cell A2 (value: "Apple").
2. Row Offset (3):
   1. The formula moves 3 rows down from A2.
   2. Starting from Row 2, it lands on Row 5 (row with "Grape").
3. Column Offset (2):
   1. From the starting column (Column A), it moves 2 columns to the right.
   2. Moving from Column A → Column C, the formula lands on the cell in Row 5, Column C.

Result:

1. The value at the cell Row 5, Column C is "Blueberry", which is the result of the formula.

28. CHOOSE()

CHOOSE() selects a value or action to perform from a list based on a given index number.

Explanation: =CHOOSE(D1, A2, A3, A4, A5) dynamically selects a value from the list of names based on the number in D1. Since D1 = 3, the formula returns "Shikha", which is the 3rd value in the range A2:A5

Understanding the Formula: Formula in D2: =CHOOSE(D1, A2, A3, A4, A5)

This formula dynamically selects a value from the list of names in A2:A5 based on the number provided in D1.

Breakdown of Components:

1. Input Values:
   1. D1 (Random P): The value in D1 is 3, which acts as the index_num.
   2. A2:A5 (List of Names): The list includes:
      1. A2: Nandini
      2. A3: Khushi
      3. A4: Shikha
      4. A5: Neha
2. Formula Logic:
   1. The CHOOSE function uses the value in D1 (3) to pick the 3rd value from the list A2:A5.
   2. Since the 3rd value is "Shikha" (in A4), the formula returns "Shikha" in D2.
3. Output:: The formula evaluates the list and returns Shikha as the winner because D1 = 3.

29. INDIRECT()

INDIRECT() returns a cell reference specified by a text string.

Explanation: =INDIRECT("C2") returns the value in cell D2.

Steps to Recreate in Excel:

1. Input the Data:
   1. Enter values 10, 18, 29, 21, 35, and 27 into cells A2:A7.
   2. Enter the text A6 into cell C2.
2. Apply the Formula:
   1. In cell D2 (or any empty cell), type the formula:
      =INDIRECT(C2)
3. Formula Logic:
   1. C2 contains the text "A6".
   2. The formula =INDIRECT(C2) interprets "A6" as a cell reference and retrieves the value stored in A7, which is 27.

• Result: The value 35  (stored in A6) is displayed as the result in D2 or the cell where you input the formula.

30. TRANSPOSE()

TRANSPOSE() changes the orientation of a range of cells from rows to columns or vice versa.

1. Explanation: =TRANSPOSE(A1:A5) converts the column range A1:B4 into a row.

2. Data is originally presented in two columns:

1. Column A contains months (Jan, Feb, Mar, Apr).
2. Column B contains associated numbers (100, 200, 150, 300).

Steps in the Explanation:

1. Input Data:
   1. The range A1:B4 contains the source data.
2. Select Output Range:
   1. Select the output area (in this case, A6:D7) where the transposed data will appear.
   2. This selection must match the number of rows and columns after transposition:
      1. Original range: 4 rows × 2 columns.
      2. Transposed range: 2 rows × 4 columns.
3. Enter the Formula:
   1. In the output area starting at A6, enter the formula:
      {=TRANSPOSE(A1:B4)}
4. Press Ctrl + Shift + Enter:
   1. Because TRANSPOSE is an array formula, pressing Ctrl + Shift + Enter ensures it calculates correctly.

31. CONCATENATE()

CONCATENATE() joins two or more text strings into one.

Explanation: =CONCATENATE(B2, " ", A2) joins A2 and B2 with a space in between.

This example demonstrates the use of the CONCATENATE function in Excel to combine text from two columns into a single column.

Explanation:

1. Columns:
   1. Column A: Last Name.
   2. Column B: First Name.
   3. Column C: Age (not used in the formula).
   4. Column D: Full Name (output).
2. Formula in D2:
   1. =CONCATENATE(B2, " ", A2) combines:
      1. B2 (First Name),
      2. A space " " to separate the names,
      3. A2 (Last Name).
   2. Explanation: For Row 2, "Alex" and "Jones" are combined as "Alex Jones".
3. Result:
   1. The combined full names are displayed in Column D, e.g.,:
      1. "Alex Jones" (Row 2),
      2. "Betty Smith" (Row 3), and so on.
4. Drag to Apply:
   1. The formula is dragged down the column to apply to other rows automatically.

32. TEXTJOIN()

TEXTJOIN() combines text strings using a delimiter, ignoring empty cells.

• Explanation: In an empty cell, type the formula:
  =TEXTJOIN(", ", TRUE, A2:A5)
• Result: The formula will return:
  Apple, Banana, Cherry, Date

33. LEFT()

LEFT() extracts a specified number of characters from the start of a text string.

• Explanation: In the above example the result comes “excel” which means the first five characters of A2 by applying the following formula,
• =LEFT(A2, 5) returns the first 5 characters of A2.
• Result: Excel 

34. RIGHT()

RIGHT() extracts a specified number of characters from the end of a text string.

• Explanation:  In the above example the result comes “las” which means the last three characters of A2 by applying the following formula,
• =RIGHT(A2, 3) returns the last 3 characters of A2.
• .Result: Excel 
• Also Read: Full Tutorial on Excel Right Function by upGrad 

35. MID()

MID() extracts a substring from a text string starting at a specified position.

• Explanation: The formula begins at the 2nd character of A2 (starting from "x" in "Excel Formulas").
• It extracts 4 characters, resulting in xcel.
• Result:=MID(A2, 2, 4) returns xcel, which is a substring starting from the 2nd character and spanning 4 characters in total.
• =MID(A2, 2, 4) extracts 4 characters starting from the second character of A2.

36. LEN()

LEN() calculates the number of characters in a text string, including spaces.

• Explanation: =LEN(A1) returns the total number of characters in A2.
• Result: 14 
• Our learners also read: Learn Excel for free!

37. TRIM()

TRIM() removes all extra spaces from a text string, leaving only single spaces between words.

Explanation: =TRIM(A1) cleans up text in A2.

38. UPPER()

UPPER() converts text to uppercase.

• Explanation: =UPPER(A2) changes text in A2 to all caps.
• Result: EXCEL FORMULAS

39. LOWER()

LOWER() converts text to lowercase.

• Explanation: =LOWER(A2) changes the text in A2 to all lowercase.
• Result: excel formulas

40. PROPER()

PROPER() capitalizes the first letter of each word in a text string.

• Explanation: =PROPER(A2) changes "excel formulas" to "Excel Formulas".
• Result: Excel Formulas 

41. REPT()

REPT() repeats text a specified number of times.

The value to be repeated (e.g., *, Hello, ! in Column A).

• Number of Times: The number of repetitions (e.g., 5, 3, 10).
• Explanation: =REPT("*", 5) returns "*****".

42. SUBSTITUTE()

SUBSTITUTE() replaces specific text in a string with new text.

Explanation: In the above image we substitute “apples” with “oranges” by applying the formula =SUBSTITUTE(A3, "apples", "oranges") replace "apples" with "oranges" in A3.

43. TEXT()

TEXT() formats a number and converts it to text.

• Explanation: =TEXT(A2, "0.00%") formats A2 as a percentage.
• Result: For =TEXT(A2, "0.00%"):
• If A2 = 0.12345, the formula formats it as a percentage with two decimal places:
  • Result: 12.35%.

44. VALUE()

VALUE() converts text into a number.

• Explanation: The text string containing a number to convert (e.g., "123", "456.78").
• =VALUE(A2) returns 123 as a number.

Date and Time Formulas

These formulas simplify calculations related to dates and times, such as finding the difference between two dates. For more information, you can learn how to split cells in Excel with this tutorial!

45. DATE()

DATE() creates a date from year, month, and day values.

• Explanation: =DATE(2025, 12, 25) returns December 25, 2025.
• =DATE(A2, B2, C2) combines the values in A2 (year), B2 (month), and C2 (day) into a valid date.

46. TODAY()

TODAY() returns the current date.

• Explanation: Simply type =TODAY() into a cell to display the current date.
• Result: 26th November 2024, the current date

47. NOW()

NOW() returns the current date and time.

• Explanation: Simply type =NOW() into a cell to display the current date and time.
• Result: 26th November 2024 displays the current date and 11:12 displays the time.

48. DATEDIF()

DATEDIF() calculates the difference between two dates.

Explanation: In the above example C2 describes the difference between A2 and B2 which is a difference in the number of days from 1st Jan 2024 to 25th November 2024 by applying the following formula  =DATEDIF(A2, B2, "D") i.e, 329 days 

Learn more about How to Use the DATEDIF Formula with upGrad 

49. NETWORKDAYS()

NETWORKDAYS() calculates the number of working days between two dates.

• Explanation: In the above example, The NETWORKDAYS() function calculates the number of working days (Monday to Friday) between two dates, excluding weekends and specified holidays
• Formula: =NETWORKDAYS(A2, B2) excludes weekends and holidays where, 
• Start Date: 01-Nov-2024.
• End Date: 30-Nov-2024.
• Holiday: 11-Nov-2024.
• Result: 20 working days.

50. WORKDAY()

WORKDAY() returns a date after a specified number of working days.

Explanation: =WORKDAY(A2, B2,$G$2:$G$5) adds 5 working days to the date in A1.

51. EOMONTH()

EOMONTH() returns the last day of the month after adding a specified number of months.

Explanation:

1. =EOMONTH(A2, B2):
   • Start Date: January 15, 2016
   • Months to Add: 1
   • Result: The last day of the following month (January 31, 2016), which is returned as a serial date (42400) and can be formatted as "Jan 31, 2016."
2. =EOMONTH(A3, B3):
   • Start Date: February 10, 2016
   • Months to Add: 2
   • Result: The last day of March 2016 (March 31, 2016), returned as serial date (42460), formatted as "Mar 31, 2016."
3. =EOMONTH(A4, B4):
   • Start Date: March 15, 2016
   • Months to Add: 3
   • Result: The last day of June 2016 (June 30, 2016), returned as serial date (42551), formatted as "Jun 30, 2016."
4. =EOMONTH(A5, B5):
   • Start Date: December 10, 2015
   • Months to Add: 1
   • Result: The last day of December 2015 (December 31, 2015), returned as serial date (42369), formatted as "Dec 31, 2015."
5. =EOMONTH("1/27/2016", 3):
   • Start Date: January 27, 2016
   • Months to Add: 3
   • Result: The last day of April 2016 (April 30, 2016), returned as serial date (42490), formatted as "Apr 30, 2016."

52. DAY()

DAY() extracts the year from a date.

• Explanation: =DAY(A1) returns the day of the date in A1.

53. MONTH()

MONTH() extracts the month from a date.

• Explanation: =MONTH(A1) returns the month of the date in A1.

54. YEAR()

YEAR() extracts the day from a date.

• Explanation: The YEAR() function extracts the year from a given date.
• Parameters:
  • A2: Contains the date from which the year is to be extracted.
• Formula Logic:
  • For =YEAR(A2):
    • The function reads the date in A2 and returns the year component.

More Advanced Excel Formulas

These include the latest and most versatile formulas like XLOOKUP, FILTER, and SEQUENCE for dynamic data analysis. If you want to be a pro in Excel Advanced Formula then learn how to insert Excel Average Equation in Excel 

55.  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:

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

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

58. IRR()

The IRR() function calculates the Internal Rate of Return (IRR) for a series of cash flows that occur at regular intervals. IRR represents the discount rate at which the Net Present Value (NPV) of the cash flows becomes zero. Essentially, it indicates the annualized rate of return an investment is expected to generate over time.

• Explanation: In cell B2, the initial investment is represented by -$1,000, indicating an outflow. From B3 to B5, we have positive cash flows that reflect returns or profits from the investment.
• IRR Calculation:
  The IRR formula determines the discount rate that makes the Net Present Value (NPV) of these cash flows equal to zero. In this case, the IRR is 8.90%, which means the investment is projected to generate an annual return of 8.90%.

59. QUARTILE

This function returns the quartile (each of four equal groups) in a given set of data and can return the minimum value, first quartile, and 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.

60. SUBTOTAL()

The SUBTOTAL() function returns a subtotal in a list or database. It can perform various calculations, such as SUM, AVERAGE, COUNT, MAX, MIN, etc., on a range of data.

The main advantage of SUBTOTAL() is that it can ignore hidden rows, filtered data, or other SUBTOTAL() results within the range.

Also Read: Data Analysis course with certification for beginners to become a pro in Excel 

How to Use Formulas in Excel

Excel formulas are the backbone of efficient data analysis and management. Applying and editing them effectively can save you time and enhance your productivity. Below are step-by-step instructions on how to use Excel formulas and advanced Excel formulas with examples:

Step 1: Start with the Equals Sign

Every formula in Excel begins with the = symbol. This tells Excel that you're entering a formula.

• Explanation: Type =SUM(A1:A5) to calculate the total of values in cells A1 to A5.

Step 2: Use Cell References

Instead of hardcoding values, use cell references to make formulas dynamic and easy to update.

• Explanation: Enter =A1+B1 to add the values in cells A1 and B1. If these values change, the formula automatically updates.

Step 3: Leverage Built-In Functions

Excel offers a range of built-in functions for calculations, from basic arithmetic to complex data analysis.

• Explanation: Use advanced Excel formulas like =VLOOKUP(101, A1:D10, 2, FALSE) to search for a value in a dataset.

Step 4: Combine Multiple Functions

Combine formulas to perform complex calculations. This is especially useful for nested formulas.

• Explanation: =IF(SUM(A1:A5)>50, "Pass", "Fail") combines the SUM() and IF() functions to evaluate a condition.

Step 5: Edit and Debug Formulas

Click on a cell with a formula to edit it. Use the Formula Bar or press F2 to make changes.

• Tip: Use Ctrl + (backtick) to display all formulas in a sheet for debugging.

Step 6: Use Absolute and Relative References

Understanding cell references is key to writing flexible formulas.

• Relative Reference: =A1+B1 changes when copied to another cell.
• Absolute Reference: Use $ (e.g., =$A$1+$B$1) to lock cell references during copying.

Step 7: Apply Formulas Across Ranges

Drag the fill handle (a small square at the bottom-right corner of a selected cell) to copy formulas across multiple cells.

• Explanation: Enter =SUM(A1:A5) in one cell and drag it down to apply the same formula to other ranges.

Step 8: Handle Errors with IFERROR()

Errors like #DIV/0! can be handled gracefully using IFERROR().

• Explanation: =IFERROR(A1/B1, "Error") returns "Error" if B1 is 0 or empty.

Step 9: Use Advanced Excel Formulas for Dynamic Analysis

Excel offers advanced functions like XLOOKUP() and FILTER() to handle complex tasks.

• Explanation: =FILTER(A1:A10, B1:B10>50) filters values in A1:A10 where B1:B10 is greater than 50.

Step 10: Practice and Experiment

Practice using formulas with real-world data to build confidence and explore the versatility of Excel advanced formulas.

Learn how to move columns in Excel by our tutorial

Parts of an Excel Formula

An Excel formula is made up of various components that work together to perform calculations, manipulate data, and generate results. Understanding these components is essential for creating and using Excel formulas and advanced Excel formulas effectively. Below is a breakdown of the key parts:

1. Functions

Functions are predefined operations in Excel that perform specific tasks like calculations, data manipulation, or logical tests.

• Explanation: =SUM(A1:A5) uses the SUM() function to calculate the total of values in the range A1 to A5.

2. Arguments

Arguments are the inputs provided to a function. They can be numbers, text, cell references, or ranges, depending on the formula’s requirements.

• Explanation: In =AVERAGE(A1:A10), A1:A10 is the argument, specifying the range of cells to calculate the average.

3. Operators

Operators are symbols used in formulas to perform arithmetic, comparison, or logical operations.

• Arithmetic Operators: +, -, *, /, ^ (e.g., =A1+A2).
• Comparison Operators: >, <, >=, <=, =, <> (e.g., =A1>B1 returns TRUE if A1 is greater than B1).
• Logical Operators: AND, OR, NOT (e.g., =AND(A1>10, B1<5) checks multiple conditions).

4. Cell References

Cell references specify the cells to be used in a formula. They can be:

• Relative References: Adjust when a formula is copied (e.g., =A1+B1).
• Absolute References: Fixed with $ (e.g., =$A$1+$B$1).
• Mixed References: Partially fixed (e.g., =$A1+B$1).

5. Constants

Constants are fixed values directly included in a formula instead of cell references.

• Explanation: In =A1+10, 10 is a constant added to the value in cell A1.

6. Parentheses

Parentheses determine the order of operations in complex formulas, ensuring certain calculations are performed first.

• Explanation: =(A1+A2)*B1 calculates the sum of A1 and A2 before multiplying by B1.

7. Text Strings

Text strings are enclosed in double quotes (") and used in formulas for operations involving text.

• Explanation: =CONCATENATE("Hello ", A1) joins "Hello " with the value in A1.

8. Error Handling

Error-handling components manage potential issues in a formula, ensuring cleaner outputs.

• Explanation: =IFERROR(A1/B1, "Error") prevents division errors by providing "Error" if B1 is 0.

Get more insights on Count Colored Cells in Excel

How upGrad Provides You Excel Courses to Boost Your Skills

Excel is a powerful tool for professionals, and mastering its formulas and functions can significantly enhance your productivity and analytical capabilities. upGrad offers a variety of resources to help you learn both basic and advanced Excel formulas, making it easier to manage data and improve decision-making.

With comprehensive tutorials and courses, upGrad ensures you can leverage Excel for tasks ranging from data analysis to financial modeling. Below are some of the top courses and resources offered by upGrad:

UpGrad’s Top Excel Resources by UpGrad

These resources cater to professionals of all levels, offering step-by-step guidance on mastering both simple and advanced Excel formulas. With upGrad, you can unlock the full potential of Excel and elevate your career!

Read: What is goal seek in Excel? 

Conclusion

Now, you too can harness the power of advanced Excel formulas like a pro! From simplifying calculations with SUM and AVERAGE to tackling complex data with XLOOKUP and FILTER, these 60 essential formulas equip you to handle any challenge with confidence. Whether you're managing data, analyzing trends, or automating tasks, you’ve got the tools to work smarter and faster.

Why stop here? Take your skills to the next level with upGrad’s Free Excel Courses and unlock the full potential of Excel in your professional journey!

References:

https://static.makeuseof.com/wp-content/uploads/2015/11/excel-formula25.jpg

https://static.makeuseof.com/wp-content/uploads/2015/11/excel-formula21.jpg

https://clickup.com/blog/excel-formulas/

https://exceljet.net/sites/default/files/styles/function_screen/public/images/formulas/nth%20smallest%20value.png?itok=SXfATBY8

https://www.ablebits.com/office-addins-blog/irr-function-excel-internal-rate-return/

https://careerfoundry.com/en/blog/data-analytics/subtotal-function-excel/#:~:text=A%20SUBTOTAL%20formula%20is%20easily,starting%20with%20ref2%2C%20are%20optional.

Master the essentials of Microsoft Excel with our step-by-step tutorial, designed to boost your productivity and data management skills.