My previous article, "See how our sample spreadsheet uses absolute cell references and drop-down lists," was met with some confusion about the use of absolute cell references. Several TechRepublic members said they don't understand absolute cell references and how they can be used. This article will discuss in more detail configuring and using absolute references. To illustrate my instructions, I created a sample general ledger you can download.
Click here to download the sample general ledger that follows along with this article. To download it, you will need Microsoft Excel 2000 and a file unzip utility such as PKZIP or WinZip.
Absolute references defined
By default, Excel cell referencing is "relative." This means that if in cell D5 you entered =C4, Excel actually sees this formula as =R[-1]C[-1]. (The “R” stands for row, and the “C” stands for column.) In plain English, this formula says, I want the value of D5 to equal the value in the cell one row less than the current row and one column less than the current column (moving left one column and up one row).
Normally, Excel does not display data using this type of row and column reference. You can, however, achieve R1C1 cell referencing (as it is called) by clicking Tools | Options, selecting the General tab, and selecting R1C1 Reference Style (see Figure A). While this view is useful for demonstrating absolute cell references, it can be confusing, and you will want to turn it off for the rest of this article.
Creating absolute references for columns and rows
To create an absolute reference to a column, enter a formula like this: =$E5. If you were to copy and paste this formula from cells E6 to G15, this formula would only see the values in column E and not be concerned with the row factor. The same effect occurs when making absolute references to a row. Suppose you are in cell G77, want to reference a value in cell C4, and are going to copy this formula to other cells. Using the formula =$C$4 will absolutely reference cell "C4."
Using our sample ledger
The sample ledger sheet illustrates how absolute column and row referencing can be used. Assume the user is entering the first ledger entry. The user would go to cell A7 and enter the date of the entry. He or she would then enter the invoice number that this entry pertains to in cell B7. Next, in cell C7, the user would enter the amount paid on the invoice. In cell D7, the user would select which column header would apply to this invoice. Let’s say the user selects the Income column. The user enters 100 in the cell D7, and the amount of the transaction is placed in cell E7. “Oops, I wanted to put it in the ’General’ account,” says the user. The user returns to cell D7 and enters 200. The formula automatically moves the amount to cell F7.
Absolute references make the sample ledger work
Now let me tell you how our sample ledger works. The formula in cell E7 is =IF($D7=E$5,$C7,""). This formula tells Excel that if the value in column D that is in this row matches the value in row 5 of this column, place the value in this cell. Otherwise, enter a blank value.
To demonstrate this with the sample spreadsheet, go to cell D7. Enter 100, 200, 300, or 400 in that cell. You will see that the amount paid for the invoice is put in the proper column relating to the "GL#". The user does not have to reenter the amount under the desired category (see Figure B).
Keep in mind that if you are going to copy and paste this formula, first copy the formula from the original cell and paste it the desired number of rows down the column. Then, copy that selection of cells to other columns where the formula is needed.
What do you think of Bob’s explanation of absolute cell references? If you downloaded the sample ledger, did you find it useful? What other Excel features would you like TechRepublic to cover? Post a comment or write to Bob Johnson and share your thoughts.