Help, examples, and information on Excel formulas

Formulas are what helped make spreadsheets so popular. By creating formulas, you can have instantaneous calculations even if the information changes in the cells relating to the formula.

The basics

• All spreadsheet formulas begin with and equal sign (=).
• After the equal symbol either a cell or formula function is entered. The function tells the spreadsheet the type of formula.
• If a math function is being performed the math formula is surrounded in parentheses.
• Using the colon (:) allows you to get a range of cells for a formula. For example, A1:A10 is cells A1 through A10.

Formula examples

Note: The functions listed below may not be the same in all languages of Microsoft Excel. All these examples are done in the English version of Microsoft Excel.

=

= will create a cell equal to another. For example, if you were to put =A1 in B1 what ever was in A1 would automatically be put in B1. You could also create a formula that would make one cell equal to more than one value. For example, if you have a first name in cell A1 and a last name in cell B1, you could put in cell A2 =A1&" "&B1 which would put cell A1 in with B1 with a space between.

=AVERAGE(X:X)

Display the average amount between cells. For example, if you wanted to get the average for cells A1 to A30, you would type: =AVERAGE(A1:A30).

=COUNTIF(X:X,"*")

Count the cells that have a certain value. For example, if you have =COUNTIF(A1:A10,"TEST") in cell A11, then any cell between A1 through A10 that has the word test will be counted as 1. So if you have 5 cells in that range that contain the word test, A11 would say 5.

=IF(*)

The syntax of the IF statement are =IF(CELL="VALUE" ,"PRINT OR DO THIS","ELSE PRINT OR DO THIS"). So a good example of the syntax would be =IF(A1="","BLANK","NOT BLANK"), this would make any cell besides cell A1 say "BLANK" if a1 had nothing within it, and "NOT BLANK" if any information was within it. The if statement can, of course, become a lot more complicated but can be reduced if following the above structure.

=MEDIAN(A1:A7)

Find the median of the values of cells A1 through A7. For example, four is the median for 1, 2, 3, 4, 5, 6, 7.

=MIN/MAX(X:X)

Min and Max represent the minimum or maximum amount in the cells. For example, if you wanted to get the minimum value between cells A1 and A30 you would put =MIN(A1:A30) or if you wanted to get the Maximum about =MAX(A1:A30).

=Product(X:X)

Multiples multiple cells together. For example =Product(A1:A30) would multiple all cells together, so A1 * A2 * A3, etc.

=SUM(X:X)

The most commonly used function to add, subtract, multiple, or divide values in cells. Below are some examples.

=SUM(A1+A2)

Add the cells A1 and A2.

=SUM(A1:A5)

Add cells A1 through A5.

=SUM(A1,A2,A5)

Adds cells A1, A2, and A5.

=SUM(A2-A1)

Subtract cell A2 from A1.

=SUM(A1*A2)

Multiply cells A1 and A2.

=SUM(A1/A2)

Divide cells A1 and A2.

=SUMIF(X:X,"*"X:X)

Perform the SUM function only if there is a specified value in the first selected cells. An example of this would be =SUMIF(A1:A6,"TEST",B1:B6) which only adds the values B1:B6 if the word "test" was put somewhere in between A1:A6. So if you put TEST (not case sensitive) in A1, but had numbers in B1 through B6, it would only add the value in B1 because TEST is in A1.

=TODAY()

Would print out the current date in the cell entered. This value will change to reflect the current date each time you open your spreadsheet. If you want to enter a date that doesn't change hold down CTRL and ; to enter the date.

=TREND(X:X)

To find the common value of cell. For example, if cells A1 through A6 had 2,4,6,8,10,12 and you entered formula =TREND(A1:A6) in a different cell, you would get the value of 2 because each number is going up by 2.

=VLOOKUP(X,X:X,X,X)

The lookup, hlookup, or vlookup formula allows you to search and find related values for returned results. See our lookup definition for a complete definition and full details on this formula.