Absolute and Relative References in Excel

Recommended Posts

Absolute and Relative References in Excel

The key here lies in understanding absolute and relative cell references. Here's your opportunity to learn by doing. Launch Excel and follow along as we illustrate the concept with an example. In a new workbook, enter the column headings North, South, East, West, and Total, starting in cell B1. Enter the row headings Spring, Summer, Fall, Winter, Total, and Proportion, starting in cell A2. Enter some random numbers in the rectangle B2:E5.

In cell B6, enter the formula =SUM(B2:B5) or just click in that cell and press Alt-<Equal sign> to insert the formula automatically. Highlight the cells from B6 to E6 and choose Edit | Fill | Right. Note that Excel did not copy the formula exactly; it modified the cell references relative to the column. For example, the formula in cell E6 is =SUM(E2:E5). The same thing happens when you copy and paste a formula or copy it by clicking in the cell and dragging that cell's fill handle.

Now we'll add row totals. Click in cell F2 and press Alt- to insert the formula =SUM(B2:E2) automatically. Highlight the range from F2 to F6 and choose Edit | Fill | Down from the menu. You now have row totals, with a grand total in cell F6.

The next step is to determine what proportion each row total is from the grand total. Click on cell B7 and enter the simple formula =B6/F6. Now, as before, use Edit | Fill | Right to copy that formula into the other columns. This time, it doesn't work! All the other columns display a #DIV/0! error message. When you click on the formula for one of those columns, you'll see why. With each move to the right, Excel adjusts both cell references, so the formula in column C is =C6/G6. But G6 and the cells to the right of it are empty, so dividing by them naturally causes an error.

To fix the problem, go back to cell B7 and change the formula to =B6/\$F6. The dollar sign here tells Excel that the column in the reference F6 is absolute, meaning it should not be changed when the formula is copied. Fill the remaining columns with the modified formula and they'll all correctly display the proportions of the grand total they represent. Use the \$ character in front of the column letter or the row number or both to make that portion of any reference absolute.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.