Absolute Reference in Excel Worksheets

Enter Data

Autosum

Insert Row

Calculate Percent

Absolute Reference

View Formulas

Format $ & %

Textbook Budget

This tutorial illustrates the use of autosum, absolute reference and the display formulas option. Formatting numbers for currency and % are used. The textbook budget assignment from CAT is used as an example.

The textbook budget assignment asks for the total cost of the student's textbooks for the semester and the % of the total that was spent for each text. In the formula

  • %book1 = $book1/total$ *100%

The denominator total$ is a constant in that it is used for each book while the cost for each book changes as the formula if filled down the column. The behavior of the individual book cost in the formula is the ordinary (default) behavior in spreadsheet formulas. This is called relative reference, that is the meaning of cell names is relative to the cell that contains the formula. The method used for constants is called absolute reference indicated by $A$1 which can be inserted with the F4 key. top of page

Using the Images

The images below are shown at a reduced scale. Click on the image to jump to a full size picture. Then use the Back and Forward arrows on your browser to jump back and forth between this page and the image as often as needed.

Words in RED refer to menu or dialog box items, or words to enter.

Enter Data

Enter headings for four columns, Courses, Text, Price and Percent. Enter the names of courses and textbooks (these may be abbreviated). Enter the price for each text, do not enter $-signs. Type Total in the row below the final text title. top of page

Use Autosum to Calculate the Total Cost

Place the cell pointer where the answer will go, cell C5 in the example at the right.

Click Autosum on the standard toolbar. The formula =SUM(C2:C4) is pasted into cell C5 and into the formula bar. A dotted box is also drawn around the range Excel thinks we want to add. If this is not what we really want the box can be dragged to enclose the correct range.

The range is correct for our problem so we click the green check mark to confirm and the total 158.43 appears in cell C5 while the formula bar continues to display =SUM(C2:C4).

Insert Additional Textbook

We just noticed that we forgot to include the Prep for Chem textbook. That is easily fixed. Click on a row number such as 3, then click Insert, click Rows and new row appears above the selected row. Enter the data for the missing text. Note that when the cost of 45.25 is entered the new total of 203.68 appears.

Click on the formula for sum that is now located in cell C6 and observe that is has changed to =SUM(C2:C5) including the new row automatically. top of page

.

Calculate Percents

Column D will hold the results of the percent calculated for each book using the formula:

  • %book1 = $book1/total$ *100% (actually we will not need to do the *100% part because the Excel % format converts fractions to percents)

The graphic at the right shows the result of entering the formula =C2/C6 in cell D2 and filling down the rest of the column. Cells D3:D6 show a division by zero error because relative referencing changes the denomination D6 to D7 when the formula is copied to cell D3, etc. Cell D7 is blank. top of page

.

Use Absolute Reference

The error found in the step above can be corrected by using absolute reference for the denominator C6 that should be held constant as the formula is filled down the column.

Enter the formula by placing the cell pointer in D2, clicking =, clicking C2, entering /, clicking C6. Give C6 absolute reference by pressing the F4 function key that changes C6 to $C$6. Now click the green check mark. top of page

Fill Down to Copy Formula

Drag the fill handle (small square on lower right border) down through cell D6 to copy the formula to the remaining cells. The value 1 in cell D6 serves as a check on the validity of the formula. top of page

View Formulas to See What's Happening

Click the Tools menu, click Options at the bottom of the list, click the View tab. In the Window Options section, check Formulas.

Now we can see that $C$6 is the denominator of each of the formulas. top of page

Format Currency and Percent

Select column C, the prices, by clicking on the column letter C. Click on the toolbar to format the numbers as currency. This adds the $ and displays 2 decimals.

Select column D, the prices, by clicking on the column letter D. Click on the toolbar to format the numbers as percents. This adds the %-sign and multiplies by 100.

These operations and other formatting can be done by clicking Format, then clicking Cells and selecting the desired items on the Number tab.

The finished worksheet is shown at the right. top of page

© Treva Pamer 1-14-2001