Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

=A7*B2
=A8*B3
=A9*B4

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
add comment

3 Answers 3

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
add comment

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. superuser.com/questions/113292/… –  i need help May 25 '12 at 6:09
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.