Formula Structure

Ordinarily, when you write a mathematical formula, you write the values and the operators, followed by an equal sign, such as 2 + 2=. In Excel, formula structure works a bit differently. All Excel formulas begin with an equal sign (=), such as =2 + 2. The equal sign tells Excel to recognize any subsequent characters you enter as a formula rather than as a regular cell entry.

Reference a Cell

Every cell in a worksheet has a unique address, composed of the cell’s column letter and row number, and that address appears in the Name box to the left of the Formula bar. Cell B3, for example, identifies the third cell down in column B. Although you can enter specific values in your Excel formulas, you can make your formulas more versatile if you include — that is, reference — a cell address rather than the value in that cell. Then, if the data in the cell changes but the formula remains the same, Excel automatically updates the result of the formula.

Cell Ranges

A group of related cells in a worksheet is called a range. You specify a range using the cells in the upper-left and lower-right corners of the range, separated by a colon. For example, range A1:B3 includes cells A1, A2, A3, B1, B2, and B3. You can also assign names to ranges to make it easier to identify their contents. Range names must start with a letter, underscore, or backslash, and can include uppercase and lowercase letters. Spaces are not allowed.

Mathematical Operators

You use mathematical operators in Excel to build formulas. Basic operators include the following:

Operator Precedence

Excel performs operations in a formula from left to right, but gives some operators precedence over others, following the rules you learned in high school math:

When you are creating equations, the order of operations determines the results. For example, suppose you want to determine the average of values in cells A2, B2, and C2. If you enter the equation =A2 + B2 + C2/3, Excel first divides the value in cell C2 by 3 and then adds that result to A2 + B2 — producing the wrong answer. The correct way to write the formula is =(A2 + B2 + C2)/3. By enclosing the values in parentheses, you are telling Excel to perform the addition operations in the parentheses before dividing the sum by 3.

Reference Operators

You can use Excel’s reference operators to control how a formula groups cells and ranges to perform calculations. For example, if your formula needs to include the cell range D2:D10 and cell E10, you can instruct Excel to evaluate all the data contained in these cells using a reference operator. Your formula might look like this: =SUM(D2:D10,E10).

Using Functions

Functions are distinct in that each one has a unique name. For example, the function that adds values is called SUM, and the function for averaging values is called AVERAGE. You use functions as part of your worksheet formulas. You can insert a function by typing the function name and arguments directly into your formula; alternatively, you can use the Insert Function dialog box to select and apply functions to your data.

Construct an Argument

Functions use arguments to indicate which values you want to calculate. Arguments can be numbers, cell or range references, range names, or even other functions. Arguments are enclosed in parentheses after the function name. For functions that require multiple arguments, you use a comma to separate the values, as in =AVERAGE(A5,C5,F5). If your range has a name, you can insert the name, as in =AVERAGE(Sales).

Types of Functions

Excel groups functions into 13 categories, not including functions installed with Excel add-in programs:

Common Functions

The following table lists some of the more popular Excel functions that you might use with your own spreadsheet work.