Every cell in an Excel worksheet has its own label, or address. For example, if you go to the third column (column C) and work your way down to the fourth row, that cell’s label, or address, is C4 (column C, row 4). To quickly determine a cell reference, click in the cell, and look in the Name Box at the left of the Formula bar.

A selected range of cells is listed from the top-left cell to the bottom-right cell, so if you select cells from the aforementioned C4 to the sixth column (column F) and the ninth row, the selection goes from cell C4 to cell F9, and, in Excel’s cell notation method, this selected range is designated as C4:F9. Excel also uses this method of notation to designate when whole rows or columns are selected. Select row 12, and Excel calls it 12:12; select columns G and H, and their designation is G:H.

Before we go further in this discussion about ranges, there are three special situations you need to be aware of. Occasionally, to make working in Excel more user friendly, someone will give a cell or a range of cells a name, and will use the name instead of the cell reference. Excel also automatically names some components, such as tables. So, if you click in a cell and see a name in the Name Box instead of a cell reference, you’ll know why. However, you can ignore the name and use the cell reference if you prefer—just pretend the name doesn’t exist.

The second situation you might encounter is a style of cell referencing that you’re not familiar with. If you notice that the columns in a workbook use numbers instead of letters as their labels, or headers, the workbook has been set up to use the R1C1 reference style. This is an old style of cell referencing that some people prefer, but it isn’t Excel’s default style. In this style, what was cell C4 is referred to as cell R4C3 (row 4 column C). If you want to switch back to Excel’s default reference style, choose Excel Options from the Office menu, and, in the Formulas category, clear the R1C1 Reference Style check box; then click OK.

The third situation is simple and logical. Because your workbook typically contains more than one worksheet, cell references often include the name of the worksheet as well as the cell references in that worksheet. When your references are exclusively within one worksheet, you can simply omit the worksheet reference. However, if you’re including data from more than one worksheet, you’ll need to reference each worksheet so that Excel can understand which cells you’re referencing. For example, the cell in reference Sheet1!C4 is a different cell from the one in reference Sheet2!C4. Fortunately, in most cases, Excel adds the sheet reference for you when necessary.

You’ll use cell references frequently in Excel—when you designate the area of a worksheet you want to print, for example. One of the most important uses of cell references, however, is in formulas. A formula is what you use to put Excel to work by making calculations for you. A formula can be just a little arithmetic (=4.201*12.8), or it can use values already in the worksheet (=C4/B3). Note that all formulas begin with an equal sign (=). The asterisk (*) symbol indicates multiplication; the forward slash (/) symbol indicates division. The complexity of your formulas is limited only by your mathematical and logical abilities and by the way you need to manipulate the data.

Another component of a formula can be a function. A function is a bit of computer programming code that does the math for you. What you have to do is insert the correct function and provide the necessary data for the function. For example, if you want to know the total for cells C4 through C20, instead of creating a formula of =C4+C5+C6, and so on, all the way up to +C20, you can simply use the Sum function and create the formula =Sum(C4:C20).

Of course, now that you’ve got this all figured out, Excel makes it a bit more complicated! There are actually a couple of additional ways to reference cells. You probably won’t need to deal with them too often, and, when you do, they’ll make more sense to you and can often solve some problems. The cell reference we just described is called a relative reference. The other method is called an absolute reference. An absolute reference always references a particular cell, and it’s useful when you always want to reference the same constant value in that cell. A relative reference is useful when you’re working on a series and want to reference a relative position. So, for example, when you reference cell C4 from cell D6, and you’re using the absolute reference, you’re actually saying, "I don’t care what you copy or move or how you fill other cells with this reference; this reference is always to the value in cell C4." When you use the relative reference, you’re saying, "I want the value from the cell that’s up two rows and over one column; so, from cell D6, I’m referencing cell C4." However, if you copy this relative reference or use it to fill other cells, the cell you’ll be referencing is not cell C4 but the cell that’s two rows up and one column over. Got it?

Take a look at the two views of the worksheet below; it uses both relative and absolute references. The first view shows the first three rows of a standard invoice, with the sales-tax rate listed in cell B1, making it easy to modify the numbers if the tax rate goes up or (however unlikely!) down. The second view shows the formulas in this part of the invoice. Note that all the references are relative except the one in cell E3.

If we use Excel’s AutoFill feature to fill row 4 with formulas, as shown in the worksheet below, the formulas relate to items in row 4 and not to those in row 3, except for the absolute reference. That’s because when you create the series, AutoFill sees the formula in column D as being the value of the cell two cells to the left plus the value of the cell one cell to the left. If all the references were absolute, each row would have the same values in columns D, E, and F. However, if cell E3 used a relative reference for the tax, the formula in cell E4 would be D4*B2—a meaningless value.

Now that the formulas have the correct references and the cells are filled with the correct formulas, Excel will generate a correct invoice. For information about calculating totals, see "Summing the Data". For more information about creating formulas with AutoFill, and about using absolute references, see "Creating a Series of Calculations". For information about displaying formulas, see "Troubleshooting Formulas".