Overview
This lecture covers cell references in Excel, focusing on the differences and uses of relative, absolute, and mixed references with practical examples.
Relative References
- Relative references adjust automatically when formulas are copied to other cells.
- Example: Adding "w + x + y" in row 1, then copying the formula to other rows updates cell positions automatically.
- Useful for performing the same calculation across multiple rows or columns.
Absolute References
- Absolute references keep the cell fixed when the formula is copied, using the dollar sign ($).
- Format: $Column$Row (e.g., $F$12) keeps both column and row fixed.
- Example: Multiplying sales by a fixed profit percentage stored in cell F12.
- Press F4 to make a reference absolute in a formula.
- Essential for referencing constants like rates or parameters in multiple calculations.
Mixed References
- Mixed references fix only the column or only the row using the dollar sign.
- $ColumnRow (e.g., $E9): fixes column only.
- Column$Row (e.g., E$9): fixes row only.
- Useful for partial fixation when copying formulas across rows or columns.
- Example: To keep the discount row fixed while summing across rows, use E$9.
Practical Examples
- To copy "w + x + discount" down a column, fix only the discount's row.
- To multiply "z * profit" across rows, fix only the profit's row.
- When calculating profits by product and month, fix the row of the percentage so it moves horizontally but not vertically.
Key Terms & Definitions
- Relative Reference — A cell reference that updates its position automatically when copied.
- Absolute Reference — A cell reference that remains constant using $ before both column and row.
- Mixed Reference — A cell reference that fixes either the row or the column but not both.
Action Items / Next Steps
- Practice using relative, absolute, and mixed references in Excel exercises.
- Review the use of the F4 key to toggle reference types in formulas.