Another fun pre-formula lesson on confusing/interesting things in Excel©! This time, stick with me, because we're talking about formula things without formulas...so here we go!
Absolutes are formula-language for "stay". Basically what you're telling Excel© to do is keep the reference cell where you told it the first time.
When you type a formula, most, if not all the time, it will automatically input the cell as a "relationship" instead of a "cell". (Those aren't the technical terms). Basically what it does is it will display in the formula "A1" but what Excel© thinks is "the cell two to the left of the formula cell". So, when you move the formula to row B, directly under the original, it will change the reference to "B1" because that is the cell two to the left of the formula cell. And if you move it to G5, it displays "G3". Ok?
So this is fairly useful for most formulas when you need to copy paste one formula down and have it apply to a whole column of info, but it gets annoying if, say, you're calculating a percent and you don't want the "total" of something to change every time. The answer to this dilemma is to tell Excel© to "stay". This is signified by a $ before the variable you want to keep the same. This $ must appear before every variable you want the same.
Example:
$B1 when copied to cell S5 will become $B5, because you told it that you want it to keep referencing column B, but the row is changing.
B$1 when copied to cell S5 will become S$1, because you told it that the column is changing, but the row is staying the same.
$B$1 when copied to any other cell will stay $B$1, because you told it you wanted it to stay where you put it.
(Here comes an analogy, because I am an analogy person)
It's like making a rabbit run. If you want the rabbit to stay in the left side of the pen, you fence off that side of the pen. If you want the rabbit to stay in the front of the pen, you fence off the front. If you want the rabbit to stay in the top-left corner, you put fence around that corner. When you move the food bowl, the rabbit will get as close to the food bowl as it can, within the fencing parameters you set.
Now, instead of having to retype the automatic "freelance" input Excel© does with dollar signs, there is a simpler way. Right after you select a cell, hit F4. This cycles through all the different absolute possibilities. Hit F4 until the $ signs are in the correct combination. If you forget to do it as you type, like I forget all the time, all you have to do is edit the formula, put the cursor either before or in the middle of the cell reference and hit F4 until your absolutes are displayed.
Note: If you've already copied the formula with the un-absoluted reference, you will have to re-copy it. Excel© does not automatically change all the formulas. Once a formula is copy/pasted from the original, Excel© treats it as it's own formula, and won't associate it with any other parent or child formula you copy/paste.
That's basically it. Absolutes are most valuable in mathematical formulas, such as percents, where one variable is constant, and in things like vlookups, where the range stays constant (We'll see these later).
Note: (I like notes) Some formulas will automatically input the reference as an absolute. Example: if in a vlookup, you select the whole spreadsheet instead of a range of cells, it will automatically input that as a absolute. A general rule of thumb is to assume Excel© won't make it absolute (like a dog won't naturally stay) so you'll just have to check each time and decide whether or not you need the absolute.
No comments:
Post a Comment