Top 10 Most Important Excel Functions (Cheat Sheet)

Last Updated: October 29, 2023
puneet-gogia-excel-champs

- Written by Puneet

1. IF Function

IF Function returns a value if the condition you specify is TRUE, else some other value. In simple words, the IF function can test a condition first and returns a value based on the result of that condition.

Syntax

IF(logical_test,value_if_true,value_if_false)

Arguments

  • logical_test: The condition which you want to evaluate.
  • value_if_true: The value which you want to get if that condition is TRUE.
  • value_if_false: The value which you want to get if that condition is FALSE.

Notes

  • The maximum number of nested conditions you can perform is 64.
  • You can use comparison operators to evaluate a condition.

Example

In the below example, we have used a comparison operator to evaluate different conditions.

excel-if-function-example-1
  1. We have used a specific text to get in the result if the condition met or not.
  2. You can also use TRUE and FALSE to get in result.
  3. If you skip specifying a value to get the result if the condition is TRUE, it will return zero.
  4. And if you skip specifying a value to get the result if the condition is FALSE, it will return zero.

In the below example, we have used the IF function to create a nesting formula.

excel-if-function-example-2

We have specified a condition and if that condition is false then we have used another IF to evaluate another condition and perform a task and if that condition is FALSE we have used another IF.

In this way, we have used IF five times to create a nesting formula. You can use the same for 64 times for a nesting formula.

2. IFERROR Function

IFERROR function returns a specific value if an error occurs. In simple words, it can test value and if that value is an error, it returns the value you have specified.

Syntax

IFERROR(value, value_if_error)

Arguments

  • value: The value you want to test for the error.
  • value_if_error: The value which you want to get in return when an error occurs.

Notes

  • IFERROR function is concerned with the occurrence of an error, not with the type of the error.
  • If you skip specifying value or value_if_error, it will return 0 in the result.
  • It can test #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.
  • If you are evaluating an array it will return an array of results for each item specified.

Example

In the below example, we have used the IFERROR function to replace the #DIV/0! with some meaningful text.

excel-iferror-function-example-1

IFERROR is only compatible with 2007 and earlier versions. To deal with this problem, you can use ISERROR.

3. TRUNC Function

TRUNC Function returns an integer after truncating the original number. In simple words, it removes the decimals from a number to a specific precision and then returns the integer part of the result.

Syntax

TRUNC(number, [num_digits])

Arguments

  • number: The number you want to truncate.
  • [num_digits]: A number to specify precision to truncate a number.

Notes

  • If you skip specifying multiple it will return with an error.
  • It rounds away from zero.
  • If you have two multiples on the same distance it will return the multiple which is higher than the number, you are rounding.

Example

In the below example, we have used TRUNC to truncate data for removing time from the dates.

excel-trunc-function-example-1

4. SUMIF Function

SUMIF Function returns the sum of the numbers which meet the condition you specify. In simple words, it only considers and calculates the sum of values that fulfill the condition.

Syntax

SUMIF(range, criteria, [sum_range])

Arguments

  • range: A range of cells from which you want to check for criteria.
  • criteria: A criteria which can be a number, text, expression, cell reference or a function.
  • [sum_range]: A cell range that has the values you want to sum.

Notes

  • If the sum_range is omitted, the cells in the range will be summed.
  • Make sure to use double quotation marks to specify Text criteria or criteria that include math symbols, which must be enclosed in double quotation marks.
  • The size of the criteria range and sum range should be of the same size.

Example

In the below example, we have specified A1:A9 as the criteria range and B1:B9 as the sum range and after that, we have specified the criteria in A12 which has the value C.

excel-sumif-function-example-1

You can also insert criteria directly into the function. In the below example, we have used an asterisk wildcard to specify a criterion that has an alphabet “S”.

excel-sumif-function-example-2

And, if you skip specifying the sum range it will give you the sum of the criteria range. But, that will be only possible if the criteria range has numeric values.

excel-sumif-function-example-3

5. INDEX Function

The INDEX function returns a value from a list of values based on its index number. In simple words, INDEX returns a value from a list of values, and you need to specify that value’s position.

Syntax

INDEX has two different syntaxes. In the first, you can use an array form of an index to simply get a value from a list using its position.

INDEX(array, row_num, [column_num]) 

In the second, you can use a referral form that is less used in real life but you can use it if you have more than one range to get value from.

INDEX(reference, row_num, [column_num], [area_num])

Arguments

  • array: A range of cells or an array constant.
  • reference: A range of cells or multiple ranges.
  • row_number: The number of the row from which you want to get the value.
  • [col_number]: The number of the column from which you want to get the value.
  • [area_number]: If you are referring to more than one range of cells (using reference syntax), specify a number to refer to a range from all those.

Notes

  • When both the row_num and column_num arguments are specified, it will return the value in the cell at the intersection of both.
  • If you specify row_num or column_num as 0 (zero), it will return the array of values for the entire column or row, respectively.
  • When row_num and column_num are out the range, it will return an error #REF!.
  • If area_number is greater than the number ranges you have specified then it will return #REF!.

Example 1 – Using ARRAY to Get Value from a List

In the below example, we have used the INDEX function to get the quantity of June month. In the list, Jun is in 6th position (6th row) that’s why I have specified 6 in row_number. INDEX has returned the value 1904 in the result.

excel-index-function-example-1

And if you referring to a range with more than one column you have to specify the column number.

Example 2 – Using REFERENCE to Get Value from Multiple Lists

In the below example, instead of selecting all the range in one go, I have selected it as three different ranges. In the last argument, we have specified 2 in area_number which will define the range to use from these three different ranges.

excel-index-function-example-2

Now in the second range, we are referring to the 5th row and 1st column. INDEX has returned the value 172 which in the 5th row in the 2nd range.

6. VLOOKUP Function

The VLOOKUP function lookups for a value in the first column of a table and returns the value from the same row of the matched value using the index number. In simple words, it performs a vertical lookup.

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Arguments

  • lookup_value: A value that you want to search in a column. You can refer to a cell that has the lookup value or you can directly enter that value into the function.
  • table_array: A range of cells, a named range from which you want to look up the value.
  • col_index_num: A number represents the column number from which you want to retrieve the value.
  • range_lookup: Use false or 0 to make an exact match and true or 1 for an appropriate match. The default is True.

Notes

  • If VLOOKUP cannot find the value you are looking for, it will return an #N/A.
  • VLOOKUP is only able to give you the value which is on the right side of the lookup value. If you want to look upon the right side, you can use INDEX and MATCH for that.
  • If you are using an exact match then it will only match the value which is first in the column.
  • You can also use wildcard characters with VLOOKUP.
  • You can use TRUE or 1 if you want an appropriate match and FALSE or 0 for an exact match.
  • If you are using an appropriate match (True): It will return the next smallest value from the list if there is no exact match.
  • If the value which you are looking for is smaller than the smallest value in the list, VLOOKUP will return #N/A.
  • If there is an exact value exists which you are looking for, it will give you that exact value.
  • Make sure you have sorted the list in ascending order.

Example

1. Using VLOOKUP for Categories

In the below example, we have a list of students with marks they have scored, and in the remarks column, we want to a grade according to their marks.

excel-vlookup-function-example-1

In the above marks list, we want to add remarks as per the below category range.

excel-vlookup-function-example-2

In this, we have two options to use.

FIRST is to create a nesting formula with IF which is a little bit time-consuming, and the SECOND option is to create a formula with VLOOKUP with an appropriate match. And, the formula will be:

=VLOOKUP(B2,$E$2:$G$5,3,TRUE)

excel-vlookup-function-example-3

How it works

I am using the “MIN MARKS” column to match the lookup value and I am getting value in return from the “Remarks” column.

I have already mentioned that when you use TRUE and there is no exact match lookup value then it will return the next smallest value from the lookup value. For example, when we are looking for a value 77 from the category table, 65 is the next smallest value after 77.

That is why we got “Good” in remarks.

2. Handling Errors in VLOOKUP Function

One of the most common problems which come when you are using VLOOKUP is that you’ll get #N/A whenever there is no match is found by it. But the solution to this problem is simple and easy. Let me show with an easy example.

In the below example, we have a list of names and their age and in cell E6, we are using the VLOOKUP function to look up a name from the list. Whenever I type a name that is not on the list I am getting #N/A.

excel-vlookup-function-example-4

But what I want here is to show a meaningful message instead of the error. The formula will be: =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),”Not Found”)

excel-vlookup-function-example-5

How it works: IFNA can test a value for #N/A and if there is an error you can specify a value instead of the error.

7. IFNA Function

IFNA function returns a specific value if an #N/A error occurs. Unlike IFERROR, it only evaluates the #N/A error and returns the value you specified.

Syntax

IFNA(value, value_if_na)

Arguments

  • value: The value you want to test for #N/A error.
  • value_if_na: The value you want to return if an error occurred.

Notes

  • If you skip specifying any argument, IFNA will treat it as an empty string (“”).
  • If a value is an array then it will return the result as an array.
  • It will ignore all other errors #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.

Example

In VLOOKUP function, #N/A occurs when the lookup value is not in the lookup range and for that we have specified a meaningful message using IFNA.

excel-ifna-function-example-1

Note: IFNA is introduced in Excel 2013 so it is not available in the previous versions.

8. RAND Function

The RAND function returns a random number ranging from 0 to 1. In simple words, you can generate a random number between 0 to 1 (it updates its value every time you make a change in the worksheet).

Syntax

RAND()

Arguments

  • There is no argument to specify in RAND functions

Notes

  • If you put zero in multiple it will return zero in the result.
  • If you skip specifying multiple it will return with an error.
  • It rounds away from zero.
  • If you have two multiples on the same distance it will return the multiple which is higher than the number you are rounding.

Example

Apart from having numbers between 0 and 1, you can also use RAND for random numbers between two specific numbers. In the below example, I have used it to create a formula that generates a random number between 50 and 100.

excel-rand-function-example-1

When you enter this formula in a cell it returns a number between 100 and 50 by multiplying the values returned by the RAND with the equation we have used. To understand this formula we need to split it into three parts:

  1. First of all, when it detects the lowest number from the highest number you get the difference between both of them.
  2. Then secondly it multiplies that difference with the random number returned after the subtraction.
  3. And third, add that number with the lowest number left in the third part of the equation.

Related: How to Quickly Generate Random Letters in Excel

9. SUM Function

SUM function returns the sum of the values supplied. In simple words, with the SUM function, you can calculate the sum of a list of values (you can directly input value into the function or refer to a range of cells.

Syntax

SUM(number1,[number2],…)

Arguments

  • number1: A number, a range of cells that contain numbers, or a single cell that contains a number.
  • [number2]: A number, a range of cells that contain numbers, or a single cell that contains a number.

Notes

  • It ignores text values.

Example

In the below example, you can insert numbers directly into the function by using commas between them.

excel-sum-function-example-1

You can also simply refer to a range for calculating the sum of the numbers and if there is a text, logical value, or empty cell it will ignore them.

excel-sum-function-example-2

If there is an error value in a cell that you are referring to, it will return #N/A in the result.

excel-sum-function-example-3

If you have numeric values that are formatted as the text it will ignore them. It’s recommended to convert them into numbers before using SUM.

excel-sum-function-example-4

10. OR Function

OR Function returns a Boolean value (TRUE or FALSE) after testing conditions you specify. In simple words, you can test multiple conditions with AND function and it returns TRUE if any of those (or all) conditions is TRUE and returns FALSE only if all those conditions are FALSE.

Syntax

OR(logical1, [logical2], …)

Arguments

  • logical1: Condition which you want to verify.
  • [logical2]: Additional Conditions you want to verify.

Notes

  • Values will be ignored if the reference cell or array contained an empty cell or text.
  • The result of conditions should be in logical value (TRUE or FALSE).
  • It will return an error if there is no logical value is returned.

Example

In the below example, we have created a condition using IF function that if a student score 60 above marks in one of the both of the subjects the formula returns TRUE.

excel-or-function-example-1

Now in the below example, we have used a number to get logical values in a formula. You can also perform the above condition in reverse order.

You can use TRUE and FALSE instead of numbers. OR function treats these logical values as numbers.

109 thoughts on “Top 10 Most Important Excel Functions (Cheat Sheet)”

  1. I fancy EXCEL & where ever possible I try to programe it. PLEASE help me with all the functions & their examples. Have done it for Numerology, Family genealogy, now Family trust fund am stuck with the “AND” function of using it. Please help me.83 years age. NO Website, wish I can develop one.

  2. This is very useful, but I often don’t have access to the internet when I am working in Excel. Could you make this information in a PDF file, so that it would be accessible offline?

  3. Please help. I have a column with a title.
    If it has been checked how to I copy the title to another cell?

  4. =search(“9″,”99 9000”) = result is 1
    How about another “9” position? What is formula?

  5. Sir, 2nd Sept.2019.
    Really you have shown very clearly and deeply all points.
    I must thank you.
    Hoping to receive more articles in future too.

    Kanhaiyalal Newaskar.

  6. Thank you for working hard to help others. Your generous effort will be put into good use.

  7. This is very useful, but I often don’t have access to the internet when I am working in Excel. Could you make this information in a PDF file, so that it would be accessible offline?

  8. Keep on sharing. This help us a lot. Its a time save for us and no need to search each functions from net cause its already here.

  9. Hi, I find your Excel tips very useful. It helps me a lot. Tips likes this give me a lesser time to search for the uses and functions of each formulas. Thanks a lot.

  10. Sir, very useful information,notes and formula you have given.User like me would appreciate the work.
    Once again thanks.

  11. Thank you very much Mr. Punit Sir. I am computer faculty and recently I have asked to teach Excel functions and I find the complete list here. Could you possibly provide us the PDF format of all the functions with examples to use. Thank you.

  12. Thanks a lot for sharing your knowledge with us with clear explanation. Your blog is my guru. All your excel tips are being very useful for my work. Thanks again.

  13. First of All.. Thank you for sharing the above info.. I fully appreciate this gesture..

  14. A honest effort with intention of educating…. Really Super …………..

  15. Hello Punit,

    I am very thankful to you as you have start this learning portal for all. And thanks for giving ur stuff from this web portal i will be able to use excel smartly and efficiently. I learn so far with this really Excellent work ur doing
    Thanks a lot again.

  16. Dear Puneet,
    Excellent work. Many files are not working or the link is wrong.

    Thanks Rashid

  17. Dear Puneet,
    Excellent work. Many files are working or the link is wrong.

    Thanks Rashid

  18. Hi Puneet,

    I want to learn macro VBA step by step please help me.. you are doing really great job

  19. Thank you so much Puneet Gogia and nice to see the whole functions list in online,it is helpful to me.I’m really appreciate to you… i want to learn VBA pls need you guideline (confusion in coding)

  20. Hi, Dear Punit,

    I am very thankful to you as you have start this learning portal for all. In my office people are jealous they don’t give me chance to learn excel and no one guide me how to use excel smartly even though many of them are working smartly in excel. But from your this web portal i will be able to use excel smartly and efficiently. Thanks a lot again.

  21. (Maraming salamat po sayo Puneet..) Thank you so much Puneet. It’s a great help for me to learn more from you

  22. It will be better if you put more information about Excel macros. Thank you for your contributions in order to make people’s working lives easier. Keep up the good work.

  23. my self deepak,
    thanks for this, good job
    kindly provide advance vba/excle programes

    • Sir it was done manually just to let you explain my actual need, please suggest function, if you can help in this regard.

  24. Thanks a lot for providing a helpful resources. You did a excellent job. Thanks again…

  25. Are these available in a PDF or Word File? This is a good reference, but it’s hard to remember to come back here for the information. Having them as something you can download would be really helpful.

  26. Thank you for the valuable information…I would like to know one specific thing.
    Once we use the transpose, only values are copied. If i want to retain the values with formula in transpose function, is it possible. This is very important to keep scientific way to prove that the transpose operation done is correct. In data analytics big excel sheets are handled and a small mistake can lead to big problems .. Please advise me ..

  27. with transpose function can we reverse the data set; like AAA, BBB, CCC transpose to CCC,BBB,AAA. Thanks

  28. Hi Puneet I think it is great if you combine these formulas in one store in PDF it is really awesome

  29. HI, is there any way to work out how long there is between 2 times? ie. 09:42 and 10:01 (that is, I want it to show 19 minutes)

  30. SUBTOTAL – which I find is a powerful function, but often overlooked – particularly with the different function numbers that formula uses.

  31. Punnet thanks, May I suggest this, an average subscriber fairly uses excel and functions but in isolation. Every one knows match, offset etc what most need is the combination of these functions to generate more potent practical problem solving forces is the next step I will like you to do after these functions in isolation.

  32. I would love others to learn these functions “Indirect, Offset, Match, Index, Vlookup, Mid, Find, Replace, Substitute, Sumif, Sumifs, SumProduct mainly and of course array functions as well.

  33. I would really like to have a better understanding of the Indirect with Row and/or Columns function. Could we include these please.

  34. Hi Puneet, I saw someone at work using both the INDEX and MATCH functions. I see that someone has already requested MATCH so if possible, I would like to see INDEX added to the list. Thank you!

  35. Hello again, Puneet (and congratulations on your 1st 1,000 subscribers)! I would like to see the IFERROR function covered. I am just learning about it, so all lessons are greatly appreciated. Thank you for asking!

  36. i would like to Get a good description of the match function and/or the choose function. Thanks Torstein (sejohnse@yahoo.no)

    • Hey Torstein,

      You will get the both.

      Thanks for words.

      You’ll get your E-book on 30 Apr

    • Hello Eanna,

      Already Added.

      Thanks for your comment. You’ll get your E-book on 30 Apr

  37. Nice to see the whole functions list..would appreciate if you let us know the complete descriptions of all functions.

Comments are closed.