Apply a formula to particular cells in Microsoft Excel

macrosmicrosoft excelspreadsheet

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

Best Answer

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)

Related Solutions

Excel – Apply one formula to all selected cells

If the cells are selected already then just press CTRL + ENTER. You can also drag the square in the bottom right of the cell after you press ENTER if you forget.

Also, when specifying a cell. If you put a $ in front of either the column, the row, or both the column or row will remain the same for all items. If not then that row or column will be relative to the value in the current cell. For example, if you have A1:C4 selected and you enter =D1 into the formula for A1 and press CTRL + ENTER then the values in D1:F4 will be used in the corresponding cells. If you use =$D$1 then all cells will use the value in D1.

UPDATE

The only way that I'm aware of to do what you are wanting without using intermediate values is to use VBA. The basic function you want is simple. It will look something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("A1:C100")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
End Sub

Just replace the line Target = UCase(Target) with whatever it is that you want to do.

Best Answer

Related Solutions

Excel – Apply one formula to all selected cells

Related Question