Microsoft Office Tutorials and References

In Depth Information

**Chapter 11: Working with Formulas and Functions**

Understanding Formulas

You can use formulas, which you build using mathematical operators, values, and cell references,

to perform all kinds of calculations on your Excel data. For example, you can add the contents of

a column of monthly sales totals to determine the cumulative sales total.

If you are new to writing formulas, this section explains the basics of building your own formulas in

Excel. You learn about the correct way to structure formulas in Excel, how to reference cell data in

your formulas, which mathematical operators are available for your use, and more.

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 data 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

instead of 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
. Excel identifies a range

by 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.

194