One of the simplest tools in Excel is the dollar sign. When you are using a lot of tables, or repeating a lot of formulas, the dollar sign comes in real handy.
The dollar sign in the cell name as above tells Excel not to shift when the cell moves. The below table shows the four different ways of writing this. Each one has different functionality.
So if we copy the formulas down the left hand column of each table:
Notice that the cells with the dollar sign in front of the one keep a value of Q. That happens because the dollar sign is telling Excel not to advance down past row one.
In the same way, the dollar sign in front of the A tells Excel not to advance across past column A:
So you can see that, depending on where the dollar sign is, Excel will “lock” the row/column when you copy the formula down/across.
The completed table above will look like this:
The most useful of these formulas is the double dollar sign: =$A$1. If you wanted to multiply an array of values by a single number in one cell, the double dollar formula will be the solution.
Another Excel tip done!
Thanks to Business Insider for the great lesson!