Say I have the following sheet:

  |  A   |  B  |  C  |  
1 |  5   |  1  |  5  |    
2 |  8   |  8  |  99 |
3 |  77  |  3  |  3  |
4 |  2   |  7  |  4  |
5 |      |     |     |
6 | Base:|  2  |     |

Now I want to apply such a 'formula' to the cells mentioned about : =LOG(<cell>, B6), where <cell> is obviously the cell value of the above cells. Obviously, the B6 should remain as it is.

How can I do this without the copy and pasting?

Update: The problem with putting the formula in the first cell and dragging down is that the B6 gets incremented to B7, B8 etc, but I want it to stay as B6

  • "cell number of the above cells"? Do you mean cell values? – o.k.w Feb 25 '10 at 12:25
  • Yup, cell values. I updated the question – Andreas Grech Feb 25 '10 at 12:51

Enter the formula into the first cell, and then click and drag the black square on the bottom right of the cell to cover all the cells you want to contain the formula. Excel is clever enough to update the cell references appropriately as it copies the formula.

Edit You want to add dollar symbols before both parts of the static cell:

LOG(<cell>, $B$6)
  • Updated the question. The problem is that the B6 value is changed when u drag downwards – Andreas Grech Feb 25 '10 at 12:49
  • Ah, I see, answer updated. – Mark Pim Feb 25 '10 at 13:13

If you want B6 to stay the same in all the cells after you drag, write $B$6 instead.

The dollar sign preceding row or column references prevents them from being updated when drag-copying the formula.


replace with the cell reference,


=LOG(A3, 10)


=LOG(A1:A5, 10)

for a range

  • But I want every cell to contain the formula for itself – Andreas Grech Feb 25 '10 at 12:50

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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