The power of Microsoft Excel lies in being able to calculate and display the results of the data entered in its cells. Whatever calculation you make, you need to enter formulas in Excel. These can be simple arithmetic expressions or complicated functions involving conditional statements and nested formulas. All Excel formulas use basic syntax, as described in the following procedure.
Steps
Part 1 of 2: Excel Formula Syntax
Step 1. Start each formula with an equal sign (=)
Indicates that the character string you are entering into a cell is a mathematical formula. If you forget the equal sign, Excel will treat the entry as a string of characters.
Step 2. Use the references to those cells which contain the values used in the formula
Although you can include numeric constants in your formula, in most cases you will use the values entered in other cells or the results of other formulas that are displayed in them. Reference those cells with a row and column reference. There are several formats:
- The most common coordinate reference is the one that uses the letter or letters representing the column followed by the row number of the cell: A1 refers to the cell of column A, row 1. If you add rows or columns respectively above and to the left of the cell reference, the cell address will be modified to fit the new location; for example, by adding a row above cell A1 and a column to its left, its reference will become B2.
- A variation of this reference is to make row and / or column references absolute with a dollar sign ($). While the reference name of cell A1 will change by adding a row above or a column in front of it, $ A $ 1 will always refer to the cell in the upper left corner of the spreadsheet; thus, in a formula, $ A $ 1 could take on a different or even invalid value in the formula if rows or columns were inserted into the spreadsheet. You can also make only the row reference absolute or just the column reference if you want.
- Another way to indicate a cell is numerically, in the RxCy format, where "R" stands for "row", "C" stands for "column" and "x" and "y" are row and column numbers. R5C4 would correspond to cell $ D $ 5 with absolute reference. By putting the number 1 after the "R" and "C", the reference becomes relative to the upper left corner of the spreadsheet.
- If you only use an equal sign and a single cell reference, copy that cell's value into the new one. Entering the formula "= A2" in cell B3 will copy the value entered in A2. To copy the value from one cell on a given spreadsheet page to another, include the page name, followed by an exclamation point (!). By inserting "= Sheet1! B6" in cell F7 of sheet 2 of that file, the value of cell B6 of the sheet named "Sheet1" will be displayed there.
Step 3. Use arithmetic operators for basic calculations
Microsoft Excel can perform all basic arithmetic operations: addition, subtraction, multiplication and division, as well as exponentiation. Some operations use symbols other than those used when writing by hand. A list of operators is shown below, in the order in which Excel processes the arithmetic operations:
- Negation: a minus sign (-). This operation returns the opposite of the constant numeric value or the contents of the cell whose reference is preceded by the minus sign. The additive inverse, also called opposite, is that value which, added to a number, returns zero; is equivalent to multiplying it by -1.
- Percentage: The percent symbol (%). This operation returns the decimal equivalent of the percentage of the numeric constant.
- Exponentiation: a circumflex accent (^). This operation raises the number represented by the cell reference or constant to the exponent following the sign.
- Multiplication: an asterisk (*). It is used for multiplication to avoid confusion with the letter "x".
- Division: a slash (/). Multiplication and division have equal precedence and are performed from left to right.
- Addition: a plus sign (+).
- Subtraction: a minus sign (-). Additions and subtractions have equal precedence and are performed from left to right.
Step 4. Use comparison operators to compare cell values
You will often use comparison operators with the IF function. You must enter a cell reference, a numeric constant, or a function that returns a numeric value on both sides of the comparison operator. The comparison operators are listed below:
- Equal: An equal sign (=).
- Different ().
- Minor (<).
- Less than or equal (<=).
- Major (>).
- Greater than or equal (> =).
Step 5. Use an ampersand (&) to join the text strings
The union of the text strings into a single string is called concatenation and ampersand is recognized as a text operator when used to join strings in Excel formulas. You can use it with text strings or cell references or both: entering "= A1 & B2" in cell C3, it returns "BATMAN" if "BAT" is contained in cell A1 and "MAN" in B2.
Step 6. When working with cell ranges, use reference operators
You'll use cell ranges very often with Excel functions, such as SUM, which adds up the values contained in a range of cells. Excel uses three reference operators:
- Interval operator: colon (:). The range operator refers to all cells that are between the cell in front of the colon and the cell written after it. All cells are usually in the same row or column; "= SUM (B6: B12)" displays the result of adding the numbers in column B from the sixth to the twelfth row, while "= AVERAGE (B6: F6)" calculates the average of the numbers contained in the sixth row from column B to F included.
- Union operator: a comma (,) in the English version, but a semicolon in the Italian version. The union operator combines cells or ranges written before the comma (semicolon) and those written after: "= SUM (B6: B12, C6: C12)" - in Italian “= SUM (B6: B12; C6: C12)”- adds the values of cells from B6 to B12 with those from C6 to C12.
- Intersection operator: a space (). The intersection operator identifies cells common to two or more ranges; inserting the cell ranges "= B5: D5 C4: C6" displays the value present in cell C5, which is common to both ranges.
Step 7. Use parentheses to identify function arguments and to ignore the order of operations
Two brackets are needed in Excel to identify the arguments of the functions and to specify a different order of operations than the normal one.
- Functions are predefined formulas. Some, such as SIN, COS, TAN, take only one argument, while other functions, such as IF, SUM, or AVERAGE, may require more than one. The arguments are separated by commas, as in "= IF (A4> = 0;" Positive ";" Negative ")" for the IF function. Functions can be nested within others, up to 64 levels deep.
- In mathematical formulas, the operations inside the brackets are performed before the outside ones: in "= A4 + B4 * C4", first B4 is multiplied by C4 and then A4 is added to the result. In "= (A4 + B4) * C4", on the other hand, A4 and B4 are added together first And then the result is multiplied by C4. Operations may have several orders of nested parentheses: first the series of operations contained in the innermost part will be performed.
- With nesting always be careful to close any parentheses that have been opened or you will receive an error message.
Part 2 of 2: Entering Formulas
Step 1. Select the cell where you want to enter the formula
Step 2. Type an equal sign in the cell or formula bar, which is above the rows and columns of the cells and below the menu bar or ribbon
Step 3. If necessary, type an opening parenthesis
Depending on the structure of your formula, you may need to insert several opening brackets.
Step 4. Create a reference cell
You can do this in several ways: by entering the reference manually; selecting a cell or a range of cells on the current page of the spreadsheet; selecting a cell or a range of cells on another page of the spreadsheet; selecting a cell or a range of cells on a page of a different worksheet.
Step 5. Enter a math, comparison, text, or reference operator, if needed
For most formulas, you will use a mathematical or reference operator.
Step 6. Repeat the previous three steps as needed to build your formula
Step 7. In the formula, close the parenthesis each time you have opened one
Step 8. Press "Enter" when the formula is what you want
Advice
- When renaming different sheets of a multi-page spreadsheet file, do not use spaces in the new sheet name. Excel will not recognize spaces in sheet names in formula references. You can work around this problem by replacing the space with an underscore when using it in a formula.
- When you start working with complex formulas, it can be helpful to write the formula on paper before entering it into Excel. If the formula seems too complex to fit into a single cell, you can break it down into multiple parts to insert into different cells and recompose the results in another.
- Microsoft Excel offers assistance in writing formulas with Formula Completion, a dynamic list of functions, arguments, or other possibilities that appear after typing the equal sign and the first few characters of the formula. Press the "Tab" key or double-click an item in the dynamic list to insert it into the formula; if the element is a function, you will be prompted for arguments. You can turn this feature on or off by selecting "Formulas" in the "Excel Options" dialog box and checking or unchecking the "Formula AutoComplete" box. You can access this dialog box by selecting "Options" from the "Tools" menu in Excel 2003, the "Excel Options" button on the "File" menu in Excel 2007, and choosing "Options" from the "File" tab in Excel 2010.
