Last week I sent out (via email to club members) the promised 100MB step-by-step screenshot tutorial demonstration video showing how to create a dynamic formula that copies across ALL cells in a table of any size, using a mixture of absolute and relative cell references.Next week, I will do a step-by-step analysis of the various parts of that formula, explaining the components of the syntax, so you can fully understand how it works.
Before then, this week, I have compiled previews and links to 3 websites offering useful explanatory information about relative and absolute cell addressing as used in different Excel versions (see below).
I do this to ensure you have access to other useful online resources that you can learn from – outside of me.
I strongly recommend you read each of the articles, to boost your understanding of these powerful concepts.
If you need help making sense of any of it, do not hesitate to contact me.
1. Excel 2013:Relative and Absolute Cell References
Introduction
Video: Cell References
There are two types of cell references: relative and absolute. Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant, no matter where they are copied.
2. USING ABSOLUTE AND RELATIVE REFERENCES IN EXCEL 2010 FORMULAS
You can use three types of cell references in Excel 2010 formulas: relative, absolute, and mixed. Using the correct type of cell reference in formulas ensures that they work as expected when you copy them to another location in the worksheet. Formulas and functions are at their most useful when using references, so you need to understand them.
3. Relative vs. Absolute Cell References in Spreadsheets
In working with spreadsheets, you need to know about relative vs. absolute cell references.
Here is the issue: when you COPY A FORMULA that contains cell references, what happens to the cell references?
Usually the CELL REFERENCES will CHANGE! If you copy a formula 2 rows to the right, then the cell references in the formula will shift 2 cells to the right. If you copy a formula 3 rows down and 1 row left, then the cell references in the formula will shift 3 rows down and 1 row left. These are called “relative” cell references, since they change relative to where you copy the formula.