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

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

share|improve this question
"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
up vote 4 down vote accepted

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)
share|improve this answer
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.

share|improve this answer

replace with the cell reference,


=LOG(A3, 10)


=LOG(A1:A5, 10)

for a range

share|improve this answer
But I want every cell to contain the formula for itself – Andreas Grech Feb 25 '10 at 12:50

You must log in to answer this question.

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