One thing you are probably using in Excel is multiplying formulas, you know, that thing that happens when you drag from the botton-right part of a cell. In this article we will try to understant the “mistery” behind formula multiplication.
We can take the image above as an example, we have 2 columns and want to apply the following formulas:
- A+B
- A+B2
- A2+B
- A2+B2
A+B
This is a “hard one”, we will write in the first column an extremly complex formula: =A2+B2. After that we just drag it down and we will see that the numbers increment with each row. Adding 2 columns in not the most difficult thing in the world.
A+B2
If we want to add B2 to the column A, we cannot do the same like in the previous case. Somehow we want to keep B2 fixed as we multiply the formula. How do we do that?
Great question, the answer lies in the F4 key, let’s take the formula bellow:
=A2+B2
If we want to make B2 fixed, after we write the formula above, we will click on B2 (in the formula bar) and press F4, this will change the formula that will end up looking like this:
=A2+$B$2
The dollar sign will make B2 fixed as we multiply the formula.
A2+B
Pretty similar with the example above, this time we will click on A2 in the formula and then F4. See the formula bellow:
=$A$2+B2
A2+B2
In this last example we want both A2 and B2 fixed, no problem, we will select both and then click F4, here is how the formula will look like:
=$A$2+$B$2
What’s with the dollar sign
I showed you one way to work with the dollar sign, there are actually 4 ways:
- A2 – no dollar sign: when we multiply the formula down the numeric value will increase (A2, A3, …), when we multiply to the right the letter will increment (B2, C2, …)
- $A2: when we multiply the formula down the numeric value will increase (A2, A3, …), when we multiply to the right the formula stays the same because of the $
- A$2: when we multiply the formula down the formula stays the same because of the $, when we multiply to the right the letter will increment (B2, C2, …)
- $A$2: Weather we multiply the formula down or to the right, because of the $ sign it will stay the same.
Wishing you lots of $ in and out of Excel 🙂 .