Formulas are what helped make spreadsheets so popular. By creating formulas, you can have quick calculations even if the information changes in the cells relating to the formula. For example, you could have a total cell that adds all values in a column.
The basics
All spreadsheet formulas begin with an equal sign (=) symbol.
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.
Formulas are created using relative cell reference by default and if you add a dollar sign ($) in front of the column or row it will become an absolute cell reference.
Entering a spreadsheet formula
Below is an animated visual example of how an excel formula can be inserted into a spreadsheet. In our first formula entered into the cell "D1," we manually enter a =sum formula to add 1+2 (in cells A1 and B2) to get the total of "3." With the next example, we use the mouse to highlight cells A2 to D2 and then instead of typing the formula use the formula button in Excel to automatically create the formula. Next, we show how you can manually enter a formula and then with the help of a mouse get the cell values (you can also highlight multiple cells to create a range). Finally, we manually enter a times ( * ) formula using sum function to find the value of 5 * 100.
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.
Tip: All of the examples below are listed in alphabetical order, if you want to start with the most common formula, we suggest starting with the =SUM formula.
- =
- AVERAGE
- COUNT
- COUNTA
- COUNTIF
- IF
- INDIRECT
- MEDIAN
- MIN AND MAX
- PRODUCT
- RAND
- RANDBETWEEN
- SUM
- SUMIF
- TODAY
- TREND
- VLOOKUP
=
An = (equals) creates a cell equal to another. For example, if you were to put =A1 in B1 whatever 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. You can also use a concatenate formula to combine cell values.
AVERAGE
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).
COUNT
COUNTA
Count the number of cells in a range that contain any text (text and numbers, not just numbers) and are not empty. For example, you could count the number of cells containing text in cells A1 through A20 by using the =COUNTA(A1:A20). If seven cells were empty the number "13" would be returned.
COUNTIF
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 one. So, if you have five 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"). For example, the formula =IF(A1="","BLANK","NOT BLANK") makes any cell besides A1 say "BLANK" if a1 had nothing within it. If A1 is not empty, the other cells will read "NOT BLANK". The if statement has more complex uses, but can generally be reduced to the above structure.
INDIRECT
Returns a reference specified by a text string. In the above example, the formula would return the value of the cell contained in A2.
Returns the value of a random cell between A1 and A2 using the indirect and randbetween (explained below) functions.
MEDIAN
MIN AND MAX
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 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
Multiples multiple cells together. For example, =Product(A1:A30) would multiple all cells together, so A1 * A2 * A3, etc.
RAND
Generates a random number greater than zero but less than one, For example, "0.681359187" could be a randomly generated number placed into the cell of the formula.
RANDBETWEEN
Generate a random number between two values. In the above example, the formula would create a random whole number between 1 and 100.
SUM
The most commonly used function to add, subtract, multiple, or divide values in cells. Below are some examples.
Add the cells A1 and A2.
Add cells A1 through A5.
Adds cells A1, A2, and A5.
Subtract cell A1 from A2.
Multiply cells A1 and A2.
Divide cell A1 by A2.
SUMIF
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.
See our SUMIF definition for additional information.
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 ; (semicolon) to enter the date.
TREND
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
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.
Related Articles:)
Preventing a spreadsheet formula from changing when copying.
See the Excel and formula definition for further information and related links.
Microsoft Excel help and support.
Posts
