Excel Functions
This article describes the most popular Excel functions and gives several examples of each. Below are links that will take you directly to your area of interest.
For an introduction to Excel Math Basics, see the separate article Excel Math Formulas. For an introduction to Microsoft Excel, see our tutorial Excel Made Easy.
Microsoft Excel has many built-in functions to perform calculations. Here are the most popular functions. An Excel function always begins with an equal = sign.
Note: When you have a function in a cell, it is a good idea to lock the cell to prevent accidently typing over it. If you need to change the function, just unlock the spreadsheet. Instructions for locking and unlocking cells can be found in our Excel Made Easy tutorial.
=SUM
If you want to total a column of numbers, i.e. cells G3 through G28, you could use a simple math formula like =G3+G4+G5 … all the way to G28! But there is a better way. In Microsoft Excel, the continuous block of cells G3 through G28 are referenced G3:G28. And to add these cell contents, use the Excel SUM function =SUM(G3:G28). Here are some other examples of =SUM:
- =SUM(B6:M6,Q6) This Excel function adds the contents of B6 through M6, plus Q6
- =SUM(A2:A23)/150 This Excel function adds the contents in A2 through A23, and then divides the result by 150.
- =10*SUM(A1:D1) This Excel formula adds the contents in A1 through D1, and then multiplies the result by 10.
- =SUM(A2:A14,C4:C29) This Excel function adds the contents in A2 through A14, and then adds that to the sum of the contents of C4 through C29.
=PRODUCT
This Excel function multiplies the contents of the referenced cells
- =PRODUCT(B4,B5) This Excel function multiplies the contents of B4 and B5.
- =PRODUCT(A1:A4) This Excel function multiplies the contents of A1 through A4. This would be the same as A1*A2*A3*A4
- =PRODUCT(D4,E4,3) This Excel function multiplies the contents of D4 and E4, and then multiplies the result by the number 2
- =PRODUCT(A1:A4,2) This Excel function multiplies the contents of A1 through A4, and then multiplies by the number 2
Note: There is a bug in Microsoft Excel (explained in KB61892) such that the PRODUCT function treats a blank cell as if it has the value of 1. In example #1 above, if cell B4 contains the number 9 and B5 is empty, Excel will compute the product as 9, even though the answer should be 0. Microsoft's answer to this dilema is to write the formula in example #1 as follows: =PRODUCT(B4*1,B5*1). There is no solution if you are multiplying a range of cells, as in example #2 above. In that case you must ensure that no cell referenced in a PRODUCT function is allowed to be left blank.
=ROUND
This Excel function rounds the value in a cell following normal rounding rules: round down if the decimal is <.5 and round up if the decimal is ≥ .5. Inside the parenthese you specify what you wanted rounded, and after the comma, the number of decimal points. Below are some examples and a discussion of why this function is important!
- =ROUND(A3,2) This Excel function rounds the value in A3 to the nearest hundredth (2 decimal places). If the value in A3 is 7.239, the result is 7.24.
- =ROUND(C9,0) This Excel function takes the value in C9 and rounds it to the nearest integer (zero decimal places). So if the value in C9 is 22.43, the formula produces a value of 22.
When you format a cell in Microsoft Excel, you specify how Excel should display the cell contents. However, the actual value Excel is storing inside the cell may include additional decimal points. Consider this example, where the cells are formatted for currency:
- In cell B3 we have the formula: =.25*A3. The value in B5 is $12.85, so .25*$12.85=$3.2125, and Excel displays $3.21 as shown.
- Likewise, in cell B4 we have the formula: =.25*A4. The value in A4 is $17.45, so .25*$17.45=$4.3625, and Excel displays $4.36.
- So, we then add $3.21 and $4.36 and Excel displays the answer - $7.58 in cell B5. Is this the correct answer? No!
The actual numbers value in these cells that Excel is rounding are shown in column H. Excel is behaving properly, but your spreadsheet appears to have an error!
We eliminate this potential problem by using the Excel ROUND function to calculate the percentages; see columns D and E. In cell E3 we write =ROUND(.25*D3,2) and in E4 we write =ROUND(.25*D4,2). Now our sum in E5 is correct because Excel uses the rounded numbers to calculate the sum.
=SQRT
To find the square root of the contents of a cell, use the Excel function =SQRT. You can put formulas inside the parentheses.
- =SQRT(M5) This Excel function finds the square root of the contents of cell M5.
- =SQRT(B4+B5) This Excel function find the square root of the sum of B4 and B5.
=AVERAGE
When you average numbers, you take their sum and divide by the numbers of numbers. To have Microsoft Excel find an average, use the =AVERAGE function. Specify the cell or a formula inside the parentheses.
- =AVERAGE(D2,D4,D6,D8) This Excel function displays the average the contents of those four cells.
- =AVERAGE(C2:C9,C15:C19) This Excel function displays the average of the contents of the cells in these two ranges.
=MAX
The Excel MAX function will indicate which cell in a specified range or group of cells has the greatest value. Example:
- =MAX(G3:G23) This Excel function displays the highest number found in cells G3 through G23.
- =MAX(A2,A4,A6,A10:A20) This Excel function displays the highest number found in the cells referenced.
=MIN
The Excel MIN function is similar to the MAX function. If we used the examples above but used =MIN instead, Excel would display the minimum value.
=TODAY()
Whatever cell you place this Excel function will display the current day’s date. After you enter the formula, click in an empty cell and see the result. If you want to format the date differently than displayed, right-click in the cell and click Format. Click the first tab, click Date, and choose your format.
=IF
One of the most useful functions in Excel is the IF function. You use this function to test for a particular condition, then tell Excel what to do if the condition is met, and what to do if the condition is not met. The format is =IF(A,X,Y). The first value (A) is the condition which you are testing. The second value (X) is what Microsoft Excel should do if the condition is true, and the last value (Y) is what Excel will do if the condition is false. Let’s look at some examples:
- =IF(D1 >26,33,44) This Excel function checks to see if the value in D1 is greater than 26. If so, it displays 33. If not, it displays 44.
- =IF(D1<100,"OK","Over Budget") This Excel formula checks to see if the value in D1 is less than 100. If so, it displays the text OK. If not, it displays the text Over Budget. (If text if to be displayed, it must be in quotes.)
- =IF(SUM(A1:A3)=50,"Slow","Fast") This Excel formula checks to see if the sum of the contents of cells A1 through A3 equals 50. If so, it displays Slow. If not, it displays Fast.
- = IF (A2>1000,985,B2+245) This Excel formula checks to see if the value in A2 is greater than 1000. If so, it displays 985. If not, it displays the sum of B2 and 245.
NESTED IFs
You may have =IF statements inside of =IF statements. These are called nested IFs. Here is a simple example::
- =IF(B9≥18,"Adult",IF(B9>12,"Teen","Child")) This Excel formula evaluates the value in cell B9. If ≥18, it displays the word Adult, and stops reading the formula. If the value is <18, the condition has not been met, so Excel executes the IF statement that follows. Excel now evaluates the value in B2 to see if it is >12. If so, it displays Teen. Otherwise, it displays Child.
We see examples on the Internet where complicated AND functions are included in nested IFs in formulas like this. For example, instead of the second IF statement above just testing if the value in B9 is >12, they test to see if the value in cell B9 is >12 AND <18. This is not necessary. We already KNOW the value is <18 or Microsoft Excel would not have proceeded to the second IF statement!
Here is an example of a more complicated formula with a nested IF function. In this example, we award a Gold medal for a score ≥12, and a Silver medal for a score >7, but <12. Hint: since we are working with integers in this formula, for the Gold medal score of ≥ 12, we could have used >11 instead.
=OR
Use the Excel OR function to test for multiple conditions. If any of the the conditions are met, Excel displays TRUE. Otherwise, it displays FALSE. Here is an example:
- =OR(G4="John",G4="Bob",G4="Judy")
- =OR(B1≥400,B1<600)
=AND
Use the Excel AND function to test for multiple conditions. If all the conditions are met, Excel displays TRUE. Otherwise, it displays FALSE. Here is an example:
- =AND(G4<100,G4>49)
- =AND(D6≥"200MB",E6="External")
Using AND and IF together
We saw an example above of the Excel SUM function inside an IF function. Here is an example of the AND function inside an IF function. In this example, we are testing whether or not students pass a course based on two different test scores:
- =IF((AND(H3≥500,H4≥800)),"Pass","Fail")
=COUNTIF
This Excel function allows you to display the number of cells in a specified range whose value meets your criteria. The format is: =COUNTIF(range,"criteria"). Look at the following examples:
- =COUNTIF(A1:A18,“=100") This Excel formula will display the number of cells in A1 through G1 that contain the value 100.
- =COUNTIF(B2:B12," < 8") This Excel formula will display the number of cells in B2 through B12 whose value is less than 8.
- =COUNTIF(F3:Q3,"=Smith") This Excel formula will display the number of cells in F3 through Q3 whose contents equals Smith.
► Related tutorial: Microsoft Excel: How to Link Worksheets.
► Featured tutorial: Your Credit Score! Learn what a credit score is, why it so important, and how you can know your credit score.
