PDF download Download Article PDF download Download Article

Microsoft Excel's power is in its ability to calculate and display results from data entered into its cells. To calculate anything in Excel, you need to enter formulas into its cells. Formulas can be simple arithmetical formulas or complicated formulas involving conditional statements and nested functions. All Excel formulas use a basic syntax, which is described in the steps below.

Method 1
Method 1 of 2:

Excel Formula Syntax

PDF download Download Article
  1. The equal sign tells Excel that the string of characters you're entering into a cell is a mathematical formula. If you forget the equal sign, Excel will treat the entry as a character string.
  2. While you can include numeric constants in your formulas, in most cases you'll use values entered in other cells (or the results of other formulas displayed in those cells) in your formulas. You refer to those cells with a coordinate reference of the row and column the cell is in. There are several formats:
    • The most common coordinate reference is to use the letter or letters representing the column followed by the number of the row the cell is in: A1 refers to the cell in Column A, Row 1. If you add rows above the referenced cell or columns above the referenced cell, the cell's reference will change to reflect its new position; adding a row above Cell A1 and a column to its left will change its reference to B2 in any formula the cell is referenced in.
    • A variation of this reference is to make row or column references absolute by preceding them with a dollar sign ($). While the reference name for Cell A1 will change if a row is added above 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 spreadsheet; thus, in a formula, Cell $A$1, could have a different, or even invalid, value in the formula if rows or columns are inserted in the spreadsheet. (You can make only the row or column cell reference absolute, if you wish.)
    • Another way to reference cells is numerically, in the format RxCy, where "R" indicates "row," "C" indicates "column," and "x" and "y" are the row and column numbers. Cell R5C4 in this format would be the same as Cell $D$5 in absolute column, row reference format. Putting either number after the "R" or the "C" makes that reference relative to the upper left corner of the spreadsheet page.
    • If you use only an equal sign and a single cell reference in your formula, you copy the value from the other cell into your new cell. Entering the formula "=A2" in Cell B3 will copy the value entered into Cell A2 into Cell B3. To copy the value from a cell in one spreadsheet page to a cell on a different page, include the page name, followed by an exclamation point (!). Entering "=Sheet1!B6" in Cell F7 on Sheet2 of the spreadsheet displays the value of Cell B6 on Sheet1 in Cell F7 on Sheet2.
    Advertisement
  3. Microsoft Excel can perform all of the basic arithmetic operations �- addition, subtraction, multiplication, and division -� as well as exponentiation. Some operations use different symbols than are used when writing equations by hand. A list of operators is given below, in the order in which Excel processes arithmetic operations:
    • Negation: A minus sign (-). This operation returns the additive inverse of the number represented by the numeric constant or cell reference following the minus sign. (The additive inverse is the value added to a number to produce a value of zero; it's the same as multiplying the number by -1.)
    • Percentage: The percent sign (%). This operation returns the decimal equivalent of the percentage of the numeric constant in front of the number.
    • Exponentiation: A caret (^). This operation raises the number represented by the cell reference or constant in front of the caret to the power of the number after the caret.
    • Multiplication: An asterisk (*). An asterisk is used for multiplication to avoid confusion with the letter "x."
    • Division: A forward slash (/). Multiplication and division have equal precedence and are performed from left to right.
    • Addition: A plus sign (+).
    • Subtraction: A minus sign (-). Addition and subtraction have equal precedence and are performed from left to right.
  4. You'll use comparison operators most often in formulas with the IF function. You place a cell reference, numeric constant, or function that returns a numeric value on either side of the comparison operator. The comparison operators are listed below:
    • Equals: An equal sign (=).
    • Is not equal to (<>).
    • Less than (<).
    • Less than or equal to (<=).
    • Greater than (>).
    • Greater than or equal to (>=).
  5. The joining of text strings into a single string is called concatenation, and the ampersand is known as a text operator when used to join strings together in Excel formulas. You can use it with text strings or cell references or both; entering "=A1&B2" in Cell C3 will yield "BATMAN" when "BAT" is entered in Cell A1 and "MAN" is entered in Cell B2.
  6. You'll use ranges of cells most often with Excel functions such as SUM, which finds the sum of a range of cells. Excel uses 3 reference operators:
    • Range operator: a colon (:). The range operator refers to all cells in a range beginning with the referenced cell in front of the colon and ending with the referenced cell after the colon. All the cells are usually in the same row or column; "=SUM(B6:B12)" displays the result of adding the column of cells from B6 through B12, while "=AVERAGE(B6:F6)" displays the average of the numbers in the row of cells from B6 through F6.
    • Union operator: a comma (,). The union operator includes both the cells or ranges of cells named before the comma and those after it; "=SUM(B6:B12, C6:C12)" adds together the cells from B6 through B12 and C6 through C12.
    • Intersection operator: a space ( ). The intersection operator identifies cells common to 2 or more ranges; listing the cell ranges "=B5:D5 C4:C6" yields the value in the cell C5, which is common to both ranges.
  7. Parentheses serve 2 functions in Excel, to identify the arguments of functions and to specify a different order of operations than the normal order.
    • Functions are pre-defined formulas. Some, such as SIN, COS, or TAN, take a single argument, while other functions, such as IF, SUM, or AVERAGE, may take multiple arguments. Multiple arguments within a function are separated by commas, as in "=IF (A4 >=0, "POSITIVE," "NEGATIVE")" for the IF function. Functions may be nested within other functions, up to 64 levels deep.
    • In mathematical operation formulas, operations within parentheses are performed before those outside it; in "=A4+B4*C4," B4 is multiplied by C4 before A4 is added to the result, but in "=(A4+B4)*C4," A4 and B4 are added together first, then the result is multiplied by C4. Parentheses in operations may be nested inside each other; the operation in the innermost set of parentheses will be performed first.
    • Whether nesting parentheses in mathematical operations or in nested functions, always be sure to have as many close parentheses in your formula as you do open parentheses, or you'll receive an error message.
  8. Advertisement
Method 2
Method 2 of 2:

Entering Formulas

PDF download Download Article
  1. The formula bar is located above the rows and columns of cells and beneath the menu bar or ribbon.
  2. Depending on the structure of your formula, you may need to type several open parentheses.
  3. You can do this in 1 of several ways: Type the cell reference manually.Select a cell or range of cells in the current page of the spreadsheet.Select a cell or range of cells in another page of the spreadsheet.Select a cell or range of cells on a page of a different spreadsheet.
  4. For most formulas, you'll use a mathematical operator or 1 of the reference operators.
  5. Advertisement


Community Q&A

Search
Add New Question
  • Question
    What is the name of ";"?
    Community Answer
    Community Answer
    It is called a semicolon, which can be used in programming languages to separate the statements or print variables by using one output statement.
  • Question
    How do I subtract one cell from another in Excel?
    Community Answer
    Community Answer
    If you want to subtract cell A1 from cell B1, in cell C1 you would type "=A1-B1" and hit enter. You can also use the autosum feature in the far right of the header.
  • Question
    How do I type the symbols that are used in Excel?
    Community Answer
    Community Answer
    You click Shift and the number/letter you see the symbol on (for example, if you want the multiplication symbol * you hit Shift+8) when using a keyboard. On a phone you have to switch the keyboard to the special characters one and find the symbol you want.
See more answers
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement

Tips

  • When you first start working with complex formulas, it may be helpful to write the formula out on paper before entering it into Excel. If the formula looks too complex to enter into a single cell, you can break it down into several parts and enter the parts into several cells, and use a simpler formula in another cell to combine the results of the individual formula parts together.
  • Microsoft Excel offers assistance in typing formulas with Formula AutoComplete, a dynamic list of functions, arguments, or other possibilities that appears after you type the equal sign and the first few characters of your formula. Press your "Tab" key or double-click an item in the dynamic list to insert it in your formula; if the item is a function, you will then be prompted to enter its arguments. You can turn this feature on or off by selecting "Formulas" on the "Excel Options" dialog and checking or unchecking the "Formula AutoComplete" box. (You 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 the sheets in a multi-page spreadsheet, make it a practice not to use any spaces in the new sheet name. Excel won't recognize naked spaces in sheet names in formula references. (You can also get around this problem by substituting an underscore for the space in the sheet name when using it in a formula.)
Submit a Tip
All tip submissions are carefully reviewed before being published
Name
Please provide your name and last initial
Thanks for submitting a tip for review!
Advertisement

Warnings

  • Don't include formatting such as commas or dollar signs in numbers when entering them in formulas because Excel recognizes commas as argument separators and union operators and dollar signs as absolute reference indicators.


Advertisement

You Might Also Like

Print Cell Formulas Used on an Excel Spreadsheet Print Cell Formulas Used on an Excel Spreadsheet: 3 Ways
Merge Cells in Excel Merge Cells in Microsoft Excel: A Quick Guide
Unprotect an Excel SheetUnprotect an Excel Sheet
Unhide All Rows and Columns in Microsoft Excel
Use If‐Else in Excel Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
Insert Pictures in Excel That Automatically Size to Fit Cells Insert Pictures in Excel That Automatically Size to Fit Cells
Create a Mortgage Calculator With Microsoft ExcelCreate a Mortgage Calculator With Microsoft Excel
Create a Graph in Excel Create a Graph in Excel
Unhide Columns in Excel Show Hidden Columns in Microsoft Excel & Unhide Column A
Ethernet Doesn't Have a Valid IP Configuration Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
Freeze Cells in Excel3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
Change from Lowercase to Uppercase in Excel Change from Lowercase to Uppercase in Microsoft Excel
Make a Spreadsheet in ExcelMake a Spreadsheet in Excel
Use ExcelNew to Excel? Here's Super Easy Tricks to Get You Started
Advertisement

References

  1. http://office.microsoft.com/en-us/excel-help/create-a-formula-HP010081867.aspx
  2. http://office.microsoft.com/en-us/excel-help/overview-of-formulas-HP010081865.aspx
  3. �Use Formula AutoComplete," Excel 2007 help file, Microsoft Corporation.
  4. Rodney Ruff, Omaha, NE; Microsoft Excel user (Excel 2007 on laptop, Excel 2010 on desktop)

About This Article

wikiHow is a “wiki,” similar to Wikipedia, which means that many of our articles are co-written by multiple authors. To create this article, volunteer authors worked to edit and improve it over time. This article has been viewed 313,012 times.
How helpful is this?
Co-authors: 8
Updated: June 9, 2020
Views: 313,012
Categories: Microsoft Excel
Thanks to all authors for creating a page that has been read 313,012 times.

Is this article up to date?

Advertisement