Page 1 of 6
Office 2016– Excel Basics 16
Video/Class Project #28
Excel Basics 16: Mixed Cell References in Formulas & Functions to Save Time
Goal in video # 16: Learn how to use Mixed Cell References in Excel Formulas.
Topics Covered in Video:
1) Example of Cell Reference: A1
i. Column reference = A
ii. Row reference = 1
2) Copying formulas with Cell References:
i. When we copy a formula that contains cell references, we need to consider whether we need: Relative,
Absolute, Mixed with the Column Locked or Mixed with the Row Locked.
ii. If you will not copy the formula, there is no need to consider what type of cell reference it will be.
3) Four Basic Types of Cell References (Relative, Absolute, Mixed Column Locked, Mixed Row Locked):
i. Relative Cell References – Example: A1
• No dollar signs
• Moves relatively throughout the copy action.
• Relatively means that if the formula is looking at a cell reference that is three cells to the left,
when you copy the formula to any other cell, the cell reference will still be looking three cells to
the left.
ii. Absolute Cell References – Example: $A$1
• Dollar signs before both:
i. Column reference = A
ii. Row reference = 1
• Absolute means that if the formula is looking at a particular cell reference, when you copy the
formula to any other cell, the cell reference will still be looking at that particular cell reference. If
the absolute cell reference is $A$1, the formula will always look at cell A1. It is as if the formula
is locked on the cell A1 throughout copy action.
iii. Mixed Cell References with Row Locked – Example: A$1
• Dollar sign before row reference only.
• Remains absolute or locked when copying across the rows, vertically (up and down).
• Moves relatively when copying across the columns, horizontally (side to side).
iv. Mixed Cell References with Column Locked – Example: $A1
• Dollar sign before column reference only.
• Remains absolute or locked when copying across the columns, horizontally (side to side).
• Moves relatively when copying across the rows, vertically (up and down).
4) Keyboard to Toggle Cell References = F4 Key.
i. F4 key = If cursor is touching a cell reference in a formula while in edit mode, F4 toggles between the
four basic types of cell references.
5) Why do we use Mixed Cell References:
i. BECAUSE THEY SPEED UP FORMULA CREATION TIME!!!
ii. For a 12 month budget, using Mixed Cell References will allow you to create your formula 12 times
faster than someone who only used Relative and Absolute Cell References.