Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Basically I am facing some problem due to excel auto generate formula that is wrong in such situation. The formula at B6 is =A6*B1. Then the rest of the rows are using cursor bottom right drag down. I want B6 onward to all have *B1.

But, when I use the cursor drag down, the formula is applied as:


I want all of the cells to be multiplied by B1 and not have it increment.

What can I accomplich this?

here's the image

share|improve this question

Change the formula in B6 to =A6*$B$1 (note the $'s) This makes the reference to B1 absolute.

Now when you drag it down, the reference to B1 does not change.

For more details, google excel absolute relative reference

share|improve this answer
+1 This is by far the best response. Only thing that could make it better might be an active link to something like About Cell and Range References. – Jeeped Nov 6 '14 at 17:40

Follow these steps
enter image description here
enter image description here
enter image description here
enter image description here

In the paste special menu select Multiply and you are done!

share|improve this answer

By dragging down to the bottom excel autofill's B1 as in numbering to make it B2, B3, B4 What you can do is for every calculation you can do it manually select B1 e.g =A7*B1 ... this is what you do type = then select A7 type * then select B1 ... then press enter

share|improve this answer
no other faster way? I got hundred of rows.. – i need help May 25 '12 at 5:52
oh, I just found the solution, btw, thanks for your reply too.… – i need help May 25 '12 at 6:09
@Dima - Would you even consider using this for a worksheet with a dozen columns of 100K rows that require formulas? – Jeeped Nov 6 '14 at 17:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .