Help, examples, and information on Excel formulas
Formulas are what help make spreadsheets so popular. Using formulas help perform 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.
- 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, like addition, subtraction, and multiplication, the math formula is surrounded in parentheses. Math functions or calculations can use an operator, including plus (+), minus (-), multiply (*), divide (/), greater than (>), and less than (<).
- Using the colon (:) lets you 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 becomes 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 click the formula button in Excel to automatically create the formula. Next, we show how you can manually enter a formula, and then using a mouse, get the cell values (you can also highlight multiple cells to create a range). Finally, we manually enter a times ( * ) formula using the sum function to find the value of 5 * 100.
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.
The examples below are listed in alphabetical order. To start with the most common formula, we suggest starting with the =SUM formula.
An = (equals) creates a cell equal to another. For example, if you were to enter =A1 in B1, whatever value was in A1 would automatically be placed in B1. You could also create a formula that would make one cell equal to more than one value. For example, if cell A1 had a first name and cell B1 had a last name, you could enter =A1&" "&B1, which combines A1 with B1, with a space between each value. You can also use a concatenate formula to combine cell values.
To multiply or divide cell A1 by cell B1, you could enter =A1*B1 to multiply the two cells (A1 times B1) or =A1/B1 to divide the two cells (A1 divided by B1).
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 the number of cells in a range containing only numbers. For example, you could find how many cells between A1 and A15 contain a numeric value using the =COUNT(A1:A15). If only cell A1 and A5 contained numbers, the cell containing this function would display "2" as its value.
Count the number of cells in a range containing any text (text and numbers, not only numbers) and are not empty. For example, you could count the number of cells containing text in cells A1 through A20 using =COUNTA(A1:A20). If seven cells were empty, the formula would return the number "13" (20-7=13).
Count the cells with a certain value. For example, if you have =COUNTIF(A1:A10,"TEST") in cell A11, then any cell between A1 through A10 with the word "test" is counted as one. So, if you have five cells in that range containing the word "test," the value "5" is shown in cell A11 (10-5=5).
Find the starting position of a specific character, word, or number in a cell value, where X is the object you are trying to locate. If you are searching for letters or words, they must be enclosed in quotes, whereas numeric values do not. Y is the cell containing the value to search. Z is the position where to start the search and is optional. Here are a few examples:
Using the formula above, if cell A3 contains the words "Computer Hope is awesome," the formula returns a value of 10, and the starting position of the word Hope in cell A3. Notice that Hope is enclosed in quotations marks in the formula; it's a word (composed of letters), and therefore requires quotes around it.
This formula example searches for 3.5 in cell A6. If A6 contained the text "My computer has a 3.5 GHz processor," the formula would return a value of 19 and the starting position of the number 3.5. Notice that 3.5 is not enclosed in quotation marks in the formula; it is a numeric value and not letters, and therefore does not require quotes.
=IF(CELL="VALUE","PRINT OR DO THIS","ELSE PRINT OR DO THIS")
Create a conditional statement in a spreadsheet, the above example is written using pseudo code to help illustrate each part of the formula. For example, the formula =IF(A1="","BLANK","NOT BLANK") makes any cell besides A1 display the text "BLANK" if A1 has nothing in it. If A1 is not empty, the cells display the text "NOT BLANK". The IF statement has more complex uses, but can generally be reduced to the structure above.
Using IF can also be useful when you may want to calculate values in a cell, but only if those cells contain values. For example, you may be dividing the values between two cells. However, if there is nothing in the cells, you would get the #DIV/0! error. Using the IF statement, you can only calculate a cell if it contains a value. For example, if you only wanted to perform a divide function if A1 contains a value, type =IF(A1="","",SUM(B1/A1)), which only divides cell B1 by A1 if A1 contains a value. Otherwise, the cell is left blank.
Returns a reference specified by a text string. In the example above, the formula would return the value contained in cell A2.
Returns the value of a random cell between A1 and A10 using the indirect and randbetween functions.
Find the median of the values in cells A1 through A7. For example, four is the median for 1, 2, 3, 4, 5, 6, 7.
The above formula looks at the A2 cell value and extracts 8 characters from its value, starting at position 1, which is the first character. If the value in cell A2 is "Computer Hope," the formula extracts the characters "Computer" from that value.
This next formula looks at the B2 cell value and extracts 6 characters, starting at position 5, which is the fifth character from the left. If the value in cell A2 is "Computer Hope," the formula extracts the characters "uter H" from that value.
MIN AND MAX
Min and Max represent the minimum or maximum value in the cells. For example, to get the minimum value between cells A1 and A30, type =MIN(A1:A30), or =MAX(A1:A30) to get the maximum value.
Multiplies two or more cells together. For example, =PRODUCT(A1:A30) would multiply cells from A1 to A30 together (i.e., A1 * A2 * A3, etc).
Use the SUM function to multiply two cells.
Generates a random number greater than zero, but less than one. For example, "0.681359187" could be a randomly generated number placed in the formula's cell.
Generate a random number between two values. In the example above, the formula would create a random whole number between 1 and 100.
Extract characters from the far right end of a cell value. X is the cell containing the number or word where you want to extract characters. Y is how many characters you want to extract from the far right end of the number or word. Below are some examples:
If cell A2 contains the word HOPE, the formula above displays a value of E in the cell with that formula.
If cell A3 contains the number 402849601742, the formula above generates a value of 742.
Round a number to a specific number of decimal places. X is the cell containing the number to be rounded. Y is the number of decimal places to round. Below are examples.
Rounds the number in cell A2 to one decimal place. If the number is 4.7369, the example above rounds that number to 4.74. If the number is 4.7614, it rounds to 4.76.
Rounds the number in cell A2 to zero decimal places, or the nearest whole number. If the number is 4.736, the example above would round that number to 5. If the number is 4.367, it would round to 4.
The most commonly used function to add, subtract, multiple, or divide values in cells. Below are different examples of this function.
Add the cells A1 and A2.
Add cells A1 through A5.
Adds cells A1, A2, and A5.
Subtracts cell A1 from A2.
Multiplies cells A1 and A2.
Divides cell A1 by A2.
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 entered somewhere between A1:A6. So if you entered "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.
Prints the current date in the cell containing the formula. The value changes when you open your spreadsheet to reflect the current date and time. To enter a date that doesn't change, press Ctrl and ; (semicolon) to enter the date.
The trim formula lets you remove any unnecessary spaces at the beginning or end of a value in a cell.
The lookup, hlookup, or vlookup formula lets you search and find related values for returned results. See our lookup definition for more information about this formula.