In B.E Aerospace project, we have to deal with lots of complicated SmartView Reports (SmartView is a plugin on Excel for Hyperion which is a BI suite of Oracle). And it would be a great advantage if you are familiar with the various Excel functions and how to make good use of them. Now I am just going to share a small tip on this: relative and absolute references in formulas. It will save you a lot of time and make you do “copy and paste” wisely, if you can master the method which I will introduce you bellow.
Let’s start with bringing in the concept of “Cell Reference”. Well, you might have already known that: we are able to reference other cells in Excel formulas, E.g. in the screenshot below, cell A3 is to sum A1 and A2 and we use the formula “=A1 + A2”. Here the “A1” and “A2” are both examples of cell reference.
So what are the relative and absolute references? Absolute references have a “$” character before the row portion of the reference or/and the column portion of the reference while relative references do not use that. In the simple example above, both of the “A1” and “A2” are relative references. The “$” character indicates that it should not increment the row or/and column references as you copy a formula from one cell to another.
Well, word descriptions might be dull and hard to understand. Let’s dive into some live examples:
Let’s continue the simple example above but this time, we also fill B1 and B2 with values and then we copy the formula “=A1+A2” from A3 to B3. You must have figured out that B3 will be filled with the formula “=B1+B2” as here we use relative references and thus the column reference gets incremented, as shown in the screenshot below:
What will happen if we modified the formula in A3 to “=$A1+$A2” and then again copy it to B3? Since now we are using the absolute references to both A1 and A2, B3 will still hold the value of 300 which is the addition of A1 and A2 but not B1 and B2. The screenshot below will make this clearer:
You can also try modifying A3 to “=$A1+$A2” or “=A$1+A$2” to experience the difference. Well, that’s all for this sharing. Anyway, have fun!
