Step 1. Each formula starts with an equal sign (=)
This flag tells Excel that the string of characters you entered into a cell is a mathematical formula. If you forget the equals sign, Excel will treat the entry as a character string.
Step 2. Use the coordinate reference for the cell that contains the values in the formula
While you can include numeric constants in formulas, in most cases you will use values entered in other cells (or results of other formulas displayed in those cells) into the formula. You refer to those cells by reference to the coordinates of the row and column of cells. There are several formats used:
- The most common reference for coordinates is to use the letter or letters that represent the column, followed by the number of rows of cells in it: A1 refers to cell Column A, Row 1. If you add a row above the reference cell or a column above the reference cell, the cell reference will change to reflect the new position. Adding a row above cell A1 and the column to the left of it will change its reference to B2 in the formula the cell refers to.
- A variation of this reference is to make absolute references to rows or columns by using the dollar sign ($). Whereas the reference name for cell A1 will change if a row is added above it or a column is added in front of it, cell $A$1 will always refer to the cell in the upper-left corner of the worksheet. Thus, cell $A$1 in the formula can have different values or even be invalid if the row or column is entered in the worksheet. (You can make absolute references to row or column cells only.)
- Another way to refer to cells is numerically, in RxCy format, "R" denotes "rows", "C" denotes "columns", and "x" and "y" are row and column numbers. Cell R5C4 in this format is the same as cell $D$5 in column reference format, absolute row. Entering a number after the letter "R" or "C" will make it a reference relative to the top-left corner of the worksheet page.
- If you only use the equals sign and single cell references in the formula, you will copy the values from the other cells into the new cells. Entering the formula "=A2" in cell B3 will copy the value entered in cell A2 into cell B3. To copy values from cells on one worksheet page to cells on a different page, enter the page name followed by an exclamation point (!). Entering "=Sheet1!B6" in cell F7 on worksheet Sheet2 will display the value of cell B6 on Sheet1 in Cell F7 on Sheet2.
Step 3. Use arithmetic operators for basic calculations
Microsoft Excel can perform all the basic arithmetic operations - addition, subtraction, multiplication, and division - as well as exponentiation. Some operations use symbols that are different from the symbols when writing equations by hand. A list of operators is available below, in the order in which Excel arithmetic operations proceed:
- Negation: Minus sign (-). This operation returns the inverse of the additive number represented by the numeric constant or cell reference after the minus sign. (An inverse additive is a value added to a number to produce a value of zero, or the equivalent of multiplying a number by -1.)
- Percentage: Percent sign (%). This operation returns the decimal equivalent of the percentage constant in front of the number.
- Exponent: Caret (^). This operation multiplies the number represented by the cell reference or constant in front of the caret as much as the number after the caret.
- Multiplication: Asterisk (*). An asterisk is used for multiplication so as not to be confused with the letter "x."
- Division: Slash (/). Multiplication and division have the same prefix and are performed from left to right.
- Addition: The plus sign (+).
- Subtraction: Minus sign (-). Addition and subtraction have the same prefix and are performed from left to right.
Step 4. Use comparison operators to compare values in cells
You will use the comparison operator most often used in formulas, namely the IF function. Add a cell reference, numeric constant, or function that returns numeric values on both sides of the comparison operator. The comparison operators are listed below:
- Equals: The equal sign (=).
- Not equal to ().
- Less than (<).
- Less than or equal to (<=).
- Greater than (>).
- Greater than or equal to (>=).
Step 5. Use an ampersand (&) to concatenate text strings
Concatenating text strings into a single string is called concatenation, and the ampersand is known as a text operator when it is used to concatenate strings in Excel formulas. You can use it with text strings, cell references or both. If "BAT" is entered in cell A1 and "MAN" is entered in cell B2, Entering "=A1&B2" in cell C3 will return "BATMAN".
Step 6. Use reference operators when working with ranges of cells
You will often use a range of cells in Excel functions, such as SUM to find the sum of a range of cells. Excel uses 3 reference operators:
- Range operator: colon (:). The range operator refers to all cells in the range starting with the reference cell in front of the colon and ending with the reference cell after the colon. All cells are usually in the same row or column. "=SUM(B6:B12)" returns the result of adding a column of cells from B6 to B12, while "=AVERAGE(B6:F6)" returns the average of the numbers in the row of cells, from B6 to F6.
- Concatenation operator: comma (,). The union operator includes the cells or ranges of cells before and after the comma. "=SUM(B6:B12, C6:C12)" will add cells from B6 to B12 and C6 to C12.
- Junction operator: space(). The junction operator recognizes cells generally for 2 or more ranges. The list of cell ranges "=B5:D5 C4:C6" returns the value in cell C5, which is common to both ranges.
Step 7. Use brackets to identify the function arguments and override the order of operations
Parentheses serve 2 functions in Excel, to identify function arguments and to specify a different order of operations from the usual order.
- Functions are predefined formulas. Some functions such as SIN, COS, or TAN take one argument, while others such as IF, SUM, or AVERAGE can take multiple arguments. Some arguments in the function are separated by commas, for example "=IF (A4 >=0, "POSITIVE," "NEGATIVE")" using the IF function. A function can be nested within another function, up to 64 levels.
- In mathematical operations formulas, the operations inside the brackets are performed first than outside the brackets; so "=A4+B4*C4" will multiply B4 by C4 and then add the result to A4. However "=(A4+B4)*C4" will add A4 and B4 first, then multiply the result by C4. Parentheses in operations can be nested inside other parentheses; operations inside the innermost brackets will be processed first.
- Whether nested parenthesis in mathematical operations or in nested functions, always make sure the number of closing brackets is the same as the number of opening brackets, or an error message will appear.
Method 2 of 2: Entering the Formula
Step 1. Select a cell to enter the formula
Step 2. Type an equal sign in the cell or in the formula bar
The formula bar is located above the rows and columns of cells and below the menu bar or ribbon.
Step 3. Type the opening brackets if needed
Depending on the structure of the formula, you may need to type in some opening brackets.
Create a cell template. You can do this in several ways: Manually typing a cell reference. Selecting a cell or range of cells on a worksheet page. Selecting a cell or range of cells on another worksheet page. Selects a cell or range of cells on different worksheet pages
Step 4. Enter a mathematical operator, comparison, text, or reference if needed
Step 5. Repeat the previous 3 steps as needed to create the formula
Step 6. Type a closing parenthesis for each opening parenthesis in the formula
Step 7. Press "Enter" when the formula is finished
Tips
- When you're first working on a complex formula, it might help to write the formula down on paper before entering it into Excel. If a formula looks too complex to fit into a single cell, you can break it up into sections and enter the formula sections into multiple cells, and use simple formulas in other cells to combine the results from each section of the formula together.
- Microsoft Excel provides help typing formulas with Formula AutoComplete, which is a dynamic list of functions, arguments, or other possibilities that appear after you type an equal sign and the first few characters of a formula. Press the "Tab" key or double-click an item in the dynamic list to enter it into the formula; if the item is a function, you will be prompted for arguments. You can enable or disable this feature by selecting "Formulas" in the "Excel Options" dialog and checking or unchecking the "Formula AutoComplete" box. (You can access this dialog by selecting "Options" from the "Tools" menu in Excel 2003, from the "Excel Options" button on the "File" button menu in Excel 2007, and by selecting "Options" on the "File" tab menu in Excel. 2010.)
- When renaming sheets in a worksheet that has multiple pages, make it a habit not to use spaces in the new sheet names. Excel will not recognize spaces in sheet names in formula references. (You can use underscores to replace spaces in sheet names for easy use in formulas.)
