Excel 2007 Formula Drag

Question

My excel drag is NOT working. I have ensured that in excel options the "Enable fill handle and cell drag-and-drop" has been selected, as well as that the calculations are "Automatic."

In my current scenario, I am trying to drag all cells to be multiplied by 2%. Since I will need to see multiple scenarios other than 2%, I have placed 2% in a cell above the actual calculations. For instance, cell A4 is 200k. Cell A1 is 2%. I'm using the formula A4*A1 to calculate. Now cell A5 is 250k, when I drag the formula down to multiple A5*A1, instead it does A5*A2. I've checked the fill options and it doesn't even give me the "fill series" option.

Any thoughts?

Answer 1

You need to tell Excel that part of the formula will be fixed, while the other will be adjusted when you drag it.

The part of the formula that moves stays like you wrote it, A4

The fixed part of the formula must be fixed with dollar sign $, like $A$1

Your new first formula will look like:

=A4 * $A$1

And when you drag this, it should have the correct look on every cell.