How to use different reference types in an Excel formula. - Tutorial

How to use cell and range references in a formula.

A cell reference identifies a cell's position on a worksheet. A range reference identifies a group of cells and their position on a worksheet. These references tell an Excel formula > and other Microsoft Excel features where to obtain a value or group of values. They also tell Excel whether they change when copied and pasted which is referred to as absolute or relative reference notation. Some common cell and range references used in a formula are presented in the table below:

Refers To	Reference
A2	The cell in column A and row 2, a cell reference
A1:A10	The cells between row 1 and row 10 column A, a range reference
B1:C10	The cells between row 1 and 10 columns B and C, a range reference
D:D	All the cells in column D, a range reference
2:2	All the cells in Row 2, a range reference
D:E	All the cells in columns D through E, a range reference

By default, Excel uses what is called A1 reference style >, which refers to columns with letters and refers to rows with numbers. These letters and numbers are called row and column headings. That is what is being used on this page.

If you see a formula and you start to see =R1C1+1 in it, you are in what is called R1C1 reference style. Only an advanced Excel user uses this notation and very rarely is it seen. To make the matter short, get out of this mode, refer to Excel help by typing in A1 Reference style to see how to toggle it back to the A1 style most commonly used.

Absolute or Relative References: What do the dollar signs mean in a formula?

When you place dollar signs around a cell  or range reference, you are preventing the reference from changing when you copy or paste a cell with a formula in it or drag it. Cell and range references can move relative to your copy and paste, that is what they are designed to do by nature.

For example, if you had a cell with a formula in it, say cell B2 = A1+1 and you copied cell B2 to B3 then the formula would shift to be cell B3 =A2+1 because it moved relative to your copy. If you wrapped dollar signs around A1 like =$A$1+1 then when you copied the formula it would stay fixed on cell A1 and not change.

You can easily put dollar signs around a cell reference or range reference by highlighting the reference in the formula and pressing the F4 function key. The F4 key will cycle you through all the dollar sign configurations as you keep pressing it. Here are what the dollar signs mean:

Refers To	Reference
A1	When cell copied and pasted or dragged, cell reference will shift relative to new cell position
$A$1	When cell copied and pasted or dragged, cell reference will not move
$A1	When cell copied and pasted or dragged cell reference will move row position if new position is off current row but will not shift column number
A$1	When cell copied and pasted or dragged, cell reference will move column notation if new position is off current column but will not shift row position

How to create a cell or range reference for another worksheet in a formula.

To refer to another Excel worksheet, place the worksheet name in front of the reference followed by an explanation point !. For example, to refer to Sheet2 cell A1 the syntax (grammar) for a formula would be:

=Sheet2!A1+1

If the sheet name has non alpha-numeric characters or spaces in it, use apostrophes to enclose the name, for example for a worksheet named Data Sheet the syntax would be:

='Data Sheet'!A1+1

Note that if you build your formula by clicking and highlighting cells with the mouse, Excel will build the basic cell references including sheet names automatically.

To build a formula that refers to cells on another worksheet with the mouse, first type an = sign in a cell. Next click on the tab that has the cells or range to use and select them with the mouse. Watch the formula bar above the column headers and you will see the formula being built.

When you are on another worksheet, you can click in the formula and add operators and values. You build the formula the exact way as discussed on the previous page.

When you are done building the formula just press Enter. You do not click back on the original worksheet tab as this may skew the formula if the last entry is a cell or range reference