Spreadsheet series: CELL REFERENCING
Introduction
Also called Cell Address, Cell Referencing is just as literal in meaning as its name; a cell whose address is referring to another cell. While scanning through a workbook of any spreadsheet, you happen to look into a cell, for example “C2” and in its formula box, it says: “=A1”. This means the value in cell C2 is referring to whatever you have in cell A1.
Advantages
- Easy to read and understand
- Simplifies complex calculations
- Enables dynamic data analysis coupled with automatic update upon removal or removal of data
- Enhances spreadsheet flexibility
- Improves accuracy and reduces errors
- Essential for data visualization and reporting
Learning Objectives:
- Understand relative, absolute, and mixed cell references
- Apply cell referencing techniques in real-world scenarios
- Tips / Best Practices
Types
There are different ways of referencing cells: Relative, Absolute, Mixed, Named Ranges, Circular, Indirect and 3D References. However, I’d keep things within the scope of the learning objective and expatiate on the 3 most common types you can’t escape in real world practice.
Relative Cell Referencing
This is the default way cells are referenced in spreadsheets. When a cell with formula is copied to another cell, the formula changes with it.
Example: In the images below, F2 references A2. By the logic of Relative referencing, if F2 is copied to F3 or G2 with its formula, the resulting value will be the same as the corresponding relative cells to A2. In other words, F3 = A3, G2 = B2
Absolute Cell Referencing
In this type of referencing, the referenced cells are fixed. Similar to how you would freeze the first row or column of your tab, Absolute referencing ensures that the address is constant, regardless of how much copy and paste is done.
Example: You have a cell A2 that holds a value (think aggregate value), and there is cell H2 that references A2, but you’d like other cells (vertically or horizontally) to make reference to A2 when you copy the formula in H2 across without change, the formula is written by adding “$” sign before the column and cell number concurrently.
Pay attention to the formula box of the selected cell.
Mixed Cell Referencing
This type of referencing is a combination of both relative and absolute referencing. Meaning, while referencing, you can choose to fix the column while the row changes as you move down the cells or fix the row while the column position remains relative.
Example:
$A1 : Column A is static, while the row number changes
A$1:Row number will always be 1, regardless of what column you are in.
More often than not, you will use cell referencing a lot while performing analysis in any spreadsheet software, especially Mixed Cell Referencing.
Bonus: Named ranges
This type of referencing is basically renaming a cell for better description (from B18 to total_sales) just like you would a tab (sheet5 to database)
Practical use case of cell Referencing
Think of LOOKUP FUNCTIONS; the lookup array and lookup value, in most cases would be referenced using Mixed Referencing style while the return array could be Relative or Mixed. Learn More
Tips / Best Practices:
1. Use absolute references for constants or fixed values.
2. Use relative references for formulas that need to adjust.
3. Use named ranges for clarity and readability.