CoverAcknowledgementsExcel OverviewTime SaverAuto Fill and Fill SeriesCell ReferencingAbsolute and Relative ReferencingNavigation ShortcutsManaging CellsSorting and FilteringHiding and GroupingPaste SpecialNamed RangesRankingRoundingSum and CountManaging WorksheetsLookup Functions Data Validation IF Statements Formula Errors Text FunctionsText to ColumnsLogic Functions 2Date and TimeConditional Formatting 1Standards of Professionalism Spreadsheet Formatting and Design Basic ChartsDashboardsConditional Formatting 2Spreadsheet Protection Basic Pivot TablesAdvanced Pivot Tables and Slicers Logic Functions 3Advanced Lookup FunctionsLoan Amortization Schedule Advanced ChartsMacros 1Macros 2Macros 3How to Use Search Engines

Absolute and Relative Referencing

In this chapter, we’re going to learn about formula anchoring. This is also referred to as absolute or relative cell referencing. The terms anchoring and absolute referencing can be used interchangeably.

Practice Spreadsheet

Use this workbook for the chapter.

Note: This is the same workbook used in the Cell Referencing chapter.

Anchors

Ships drop anchors to prevent drifting when there are waves. I’d like you to think about the following scriptures and how an anchor for a ship relates to our personal lives.

But now, behold, the [wicked] are led about by Satan, even … as a vessel is tossed about upon the waves, without … anchor, … and even as she is, so are they” (Mormon 5:18).

Wherefore, … hope cometh of faith [and] maketh an anchor to the souls of men, which would make them sure and steadfast, always abounding in good works, being led to glorify God” (Ether 12:4).

This concept has some connection in Excel as well. 

Relative Referencing

When formula anchoring in a spreadsheet, a cell reference is anchored and this prevents the formula from referring to other cells when it is dragged or copied down. This will be best described through examples:

This example includes seven or so different individuals, their annual salary, the percent of their salary that they have deductions to go to benefits (health care insurance deductions, dental insurance deductions, 401K deductions), and what percent of their salary they would like to have deducted for their personal savings as well. This table also includes tithing and tax. 

We’ll start without really looking at the full table. Instead, start by going to G5 in reference to Anne’s salary. (See Figure 4.1

BUS115_image_4.1.png
Figure 4.1
 
  1. Refer to that cell by typing =C5, and then pushing the Enter key. (See Figure 4.1)
    1. It will give you the same result as cell C5: $45,000
  2. Select the cell with the dotted line and drag it over the two cells to the right (D5 and E5).
    1. When you hit the Enter key, the Benefits and Savings cells will show as zeros in cells H5 and I5 because they are not formatted as percentages. 
  3. Select Format Painter, then select cells C5, D5, and E5. Once they are selected, simply select G5 and it will format from the original information. (See Figure 4.2)
BUS115_image_4.2.png
Figure 4.2
 

Because it referred from C5, the formula drifted as it was dragged over. It went from C5, and then when dragged over it went to D5 and then to E5. This is because the formula is not anchored. Similarly, if you grab the whole row and drag it down, it will repeat everything in the original table because none of the formulas are anchored. (See Figure 4.3)

BUS115_image_4.3.png
Figure 4.3
 

You’ll notice that each cell references the corresponding relative cell, relative to the first top-left cell in our new grid. 

Absolute Referencing

Absolute referencing is also called anchoring. Learn to anchor by following the following steps.

  1. Select columns G through J.
  2. Right-click on the highlighted columns and select Insert. (See Figure 4.4)
    BUS115_image_4.4.png
    Figure 4.4
     
  3. Start in G5 and reference the C5 cell by typing =C5.
  4. Press the F4 key or type in an America dollar sign in front of the column and row indicators. (See Figure 4.5)
  5. Press Enter.
    1. This will bring in the $45,000 again.
    2. However, when you drag the G5 cell over, all the cells stay at 45,000. This is because it maintains the same reference as cell C5.
  6. Label the first grid Absolute, and the second Relative.

 

Referencing the Row or the Column

Let’s try again, this time referencing either only a row or only a column.

  1. Insert four new columns again.
  2. In G5, reference cell C5.
  3. Press F4.
    1. This will create an absolute reference for both the column and row.
  4. Press F4 a second time.
    1. This will create an absolute reference for only the row.
    2. The column will change as you drag it across to the right, but the row will stay anchored to the original row, 5.
  5. Drag cell G5 to the right and use the format painter to change the second and third values to percentages.
  6. Drag these three cells down, and you’ll notice that all the rows now match row 5. (See Figure 4.6)
    BUS115_image_4.6.png
    Figure 4.6
     
  1. Insert four new columns again.
  2. In G5, reference cell C5.
  3. Press F4.
    1. This will create an absolute reference for both the column and row.
  4. Press F4 a second time.
    1. This will create an absolute reference for only the row.
  5. Press F4 a third time.
    1. This will create an absolute reference for only the column.
    2. The row will change as you drag down, but the columns will stay anchored to the original column, C.
  6. Drag cell G5 to the right and note that the column stays the same. 
  7. Drag these three cells down, and you’ll notice that all the columns now match column C. 
  8. Label the new grids “Row” and “Column” according to which part of the formula was absolutely referenced.

Calculating Take-Home Pay

Let’s suppose that we ultimately want to know their take-home pay. To get to that, we are going to want to know their benefits in dollars, and also we want to know their savings in dollars. Let’s start by calculating. Make sure you widen your cells up a little bit so that you can see them.

  1. Insert four new columns once more. We want to understand take-home pay after benefits, savings, tithing, and tax.
  2. Label the columns Benefits, Savings, Tithing, and Tax.
  3. Use the format painter to paint the format from the salary column into the new four columns.
  4. Right-click to insert a fifth column between Tithing and Tax.
    1. It will inherit the currency formatting you just painted in.
  5. Name this new column Tax, and rename the fifth column from Tax to Take-Home.

Benefits and Savings

  1. In F5, reference C5 with the anchor on the column. (=$C5).
  2. Use the asterisk symbol (*) to multiply $C5 by D5. (See Figure 4.7)
    1. It is not necessary to anchor D5 because as it is dragged down, the benefits percentage will change. As dragged over, it will also bring in the savings percentages and calculate them automatically into dollars.
      BUS115_image_4.7.png
       Figure 4.7
       
  3. Push Enter. The dollar amount for 25% of $45,000 will automatically be calculated.
  4. Drag it across to the savings column. Note that the formula references the next column over for the percent, but it remains anchored to the salary column.
  5. Take those two formulas, and drag them all the way down. Note that whichever cell you select, it now references the appropriate salary and benefits or savings. (See Figure 4.8)
    BUS115_image_4.8.png
    Figure 4.8
 

Tithing and Tax

  1. To calculate tithing, start by referencing the salary, anchoring only the column. (=$C5)
  2. Multiply that by the cell that represents the percentage of tithing, C1. Anchor both the row and column of C1 by pressing F4.
    1. This ensures that the tithing percentage will not move when dragged.
  3. Press Enter and drag the column down for the rest of the information.
  4. To calculate tax, create the same formula, but instead of multiplying by C1 and anchoring it, multiply it by C2 and anchor both the row and column. 
  5. Press Enter and drag the column down for the rest of the information. (See Figure 4.9)
BUS115_image_4.9.png
Figure 4.9
 

Take-Home Pay

To calculate the take-home pay or the money left from the salary after deductions from benefits, savings, tithing, and tax have been factored in, we will need to subtract those things from the salary.

  1. In the Take Home column, start by referencing C5. 
    1. Leave it as an unanchored, relative reference, as the reference salary will change as it is pulled down.
  2. Subtract the sum of the calculated benefits, savings, tithing, and tax, and close the parenthesis. (See Figure 4.10)
  3. Press Enter and drag down to calculate the take-home pay for the rest of the employees.
BUS115_image_4.10.jpg
Figure 4.10

Anchoring takes a lot of practice and you’re encouraged to do so whenever possible, as anchoring will be critical in any career using Excel. It is vital when creating workbooks or worksheets that are fast and intuitive for you and the user. It may be challenging at first, but as you practice, anchoring will become second nature!

Supplemental Resource

End-of-Chapter Survey

: How would you rate the overall quality of this chapter?
  1. Very Low Quality
  2. Low Quality
  3. Moderate Quality
  4. High Quality
  5. Very High Quality
Comments will be automatically submitted when you navigate away from the page.
Like this? Endorse it!