Referring to Cells and Ranges in Formulas and Functions
To make your formulas and functions work correctly, you need to refer to the cells and ranges you want. This section makes sure you know how to refer to cells and ranges, both when they're on the same worksheet as the formula and when they're on a different worksheet. You can even refer to cells and ranges in another workbook—as long as it will remain in the same place in your Mac's file system.
Referring to a Cell
To refer to a cell on the same worksheet, simply use its column lettering and its row number. For example, use =A10 to refer to cell A10.
To refer to a cell on a different worksheet, enter the worksheet's name followed by an exclamation point and the cell reference. For example, use =Supplies!A10 to refer to cell A10 on the worksheet named Supplies. The easiest way to set up such a reference is like this:
- Start creating the formula. For example, type = in the cell.
- Click the worksheet tab for the worksheet that contains the cell you want to refer to.
- Click the cell.
- Click the Enter button on the formula bar to enter the reference. Excel returns you to the worksheet on which you're creating the formula.
NOTE: Instead of clicking the Enter button on the formula bar in step 4, you can press Return to enter the cell reference. When you do this, Excel moves the active cell to the cell below the formula on the worksheet. You then need to press the up arrow key before taking Step 5.
- Press Ctrl+U to resume editing the cell. Alternatively, click in the formula bar, and continue editing there.
- Finish creating the formula as usual.
NOTE: If the worksheet's name contains any spaces, you must put the name inside single quotes; for example, ='Sales Results'!A10 rather than =Sales Results!A10. You can also use the single quotes on worksheet names that don't have spaces if you find it easier to be consistent. If you omit the single quotes when they're needed, Excel displays an error message.
To refer to a cell in a different workbook, enter the workbook's path, then the file name in brackets, then the worksheet's name, and then the cell reference. For example, the reference ='Shared:Spreads:[Results.xlsx]Sales!'AB12 refers to cell AB12 on the worksheet named Sales in the workbook Results.xlsx in the Shared:Spreads folder.
Unless you happen to know the path, file name, worksheet name, and cell, it's usually easiest to set up the reference using the mouse. Follow these steps:
- Open the workbook you want to refer to.
- In the workbook that will contain the reference, start creating the formula. For example, type = in the cell.
- Switch to the other workbook.
- If you can see the other workbook, click it.
- If you can't see the other workbook, open the Window menu on the menu bar, and then click the appropriate window.
- Navigate to the worksheet that contains the cell, and then click the cell.
- Switch back to the workbook in which you're creating the reference, and then complete the formula.
When you create a reference to a cell in another workbook, Excel keeps the value of the referring cell updated. If you move or delete the other workbook, Excel displays a dialog box warning you that it can't find the workbook and asking if you want to locate it manually.
Referring to a Range
To refer to a range that consists of a block of cells, give the addresses of the first cell and the last cell, separating them with a colon. For example, to refer to the range from cell P10 to cell Q12, use =P10:Q12.
To refer to a range that consists of individual cells, give the address of each cell, separating the addresses with commas. For example, to refer to cell J14, cell K18, and cell Z20, use =J14,K18,Z20.
To refer to a range on a different worksheet or in a different workbook, use the techniques explained in the previous section. For example, if you need to refer to the range P10 to Q12 on the worksheet named Stock Listing, use ='Stock Listing'!P10:Q12.
Making One Row or Column Refer to Another Row or Column
Sometimes you may need to make the contents of one row or column refer to another row or column. For example, say you need to make each cell in row 25 refer to the corresponding cell in row 4, so that cell A25 refers to cell A4, cell B25 to cell B4, and so on.
To do this, click the row heading for row 25, selecting the row. Then type =4:4 to create the reference to row 4, and press Cmd+Return to enter it in all the cells of the selection. Similarly, you can refer to a whole column by entering its letter designation, a colon, and the letter designation again; for example, E:E.
UNDERSTANDING ABSOLUTE REFERENCES, RELATIVE REFERENCES, AND MIXED REFERENCES
Using cell addresses or range addresses is straightforward enough, but when you start using formulas, there's a complication. When you copy a formula and paste it, you need to tell Excel whether the pasted formula should refer to the cells it originally referred to, or the cells in the same relative positions to the cell where the formula now is, or a mixture of the two. (If you move a formula to another cell by using drag and drop or Cut and Paste, Excel keeps the formula as it is.)
To make references clear, Excel uses three types of references:
- Absolute reference. A reference that always refers to the same cell, no matter where you copy it. Excel uses a dollar sign ($) to indicate that each part of the reference is absolute. For example, $B$3 is an absolute reference to cell B3.
- Relative reference. A reference that refers to the cell by its position relative to the cell that holds the reference. For example, if you select cell A3 and enter =B5 in it, the reference means “the cell one column to the right and two rows down.” So if you copy the formula to cell C4, Excel changes the cell reference to cell D6, which is one column to the right and two rows down from cell C4. To indicate a relative reference, Excel uses a plain reference without any dollar signs; for example, B5.
- Mixed reference. A reference that is absolute for either the column or the row and relative for the other. For example, $B4 is absolute for the column (B) and relative for the row (4), while B$4 is relative for the column and absolute for the row. When you copy and paste a mixed reference, the absolute part stays the same, but the relative part changes to reflect the new location.
If you're typing a reference, you can type the $ signs into the reference to make it absolute or mixed. If you're entering references by selecting cells, click in the reference in cell you're editing or in the Formula bar, and then choose Formulas 
Function Switch Reference from the Ribbon. Click the Switch Reference button repeatedly to cycle a reference through its absolute, relative, column-absolute, and row-absolute versions. If a formula refers to multiple cells, you need to alter the reference for each cell separately—you can't change the whole lot in one fell swoop.
Referring to Named Cells and Ranges
To make your references easier to enter and recognize, you can assign a name to any cell or range, as discussed in the section “Identifying Parts with Named Ranges” in Chapter 3. You can then refer to the cell or range by the name, even if it's on another worksheet whose name you've forgotten.