How to Use Absolute and Relative References in Excel

Absolute and Relative References in Excel

1. Start with a basic formula using relative references

• Build a simple invoice calculation using parts cost, labor hours, and labor rate
• Copy the formula down the column
• Notice that every cell reference updates automatically as the formula moves down
• This is relative referencing, where Excel adjusts the references based on the new row or column position

2. See what happens when the formula is copied in the wrong direction

• Drag the same formula across instead of down
• Notice that Excel shifts the references to neighboring columns
• The formula no longer points to the intended inputs
• This shows why you need to control whether a reference should move or stay fixed

3. Use absolute references to lock a value in place

• In the commission example, multiply each salesperson’s revenue by a single commission rate stored in one cell
• Copy the formula down
• Without locking the commission-rate cell, the reference shifts and the results break
• Add dollar signs to lock that cell so it always points to the same commission rate
• Copy the formula again and confirm that every row now calculates correctly

4. Test why cell locking is useful

• Change the commission rate in the source cell
• Watch all the commission calculations update automatically
• This shows why linking to a locked reference is better than hardcoding a number directly into every formula

5. Move into a mixed reference example

• Use a pricing table with base prices down one side and seasonal adjustment percentages across the top
• Start with one calculation that combines a base price and a seasonal multiplier
• Copy it across and down
• Notice that one part of the formula should move by row, while the other should move by column

6. Lock only the row when needed

• For the seasonal percentages across the top, keep the formula anchored to the correct header row
• Let the column change as the formula moves left to right
• This is a mixed reference where the row is locked but the column is allowed to move

7. Lock only the column when needed

• For the base prices listed down the left side, keep the formula anchored to the correct price column
• Let the row change as the formula moves downward
• This is another mixed reference where the column is locked but the row is allowed to move

8. Copy the mixed-reference formula across the full table

• Paste the formula across all seasons and down all job types
• Confirm that each result uses the correct base price and the correct seasonal multiplier
• Notice that the formula now works across the entire matrix without being rewritten manually

9. Compare this to manual formula entry

• Look at how much extra work is required if you do not use absolute or mixed references
• Without locking cells, you would need to recreate formulas one by one
• With proper referencing, you write the formula once and reuse it correctly everywhere

10. Use F4 to cycle through reference types

• Click into the formula bar or edit a cell reference
• Press F4 to rotate through the different reference styles
• Use it to switch between:
  • fully relative references
  • fully absolute references
  • row-locked mixed references
  • column-locked mixed references

11. Practice the three reference behaviors shown in the video

• Relative references when the formula should move naturally
• Absolute references when the formula should always point to one fixed cell
• Mixed references when only the row or only the column should stay fixed

12. Rebuild the examples yourself

• Clear the completed formulas
• Recreate the invoice example
• Recreate the commission example
• Recreate the pricing table example
• This is the fastest way to understand when to let a reference move and when to lock it