How to set Function "formula" for whole column in Excel 2007?

28
2014-03
  • Wahid Bitar

    I want to set "Formula" to all cells in certain column whatever the number of rows was.

    But i don't want to set this function to first cell "Header" of this column.

    How do i make it ?.

    • Answers
  • PileOfMush

    If your header is in cell A1 and your formulas need to start in A2 and go to the end, start by selecting A2, press (and release) "End" then press "Shift+Down Arrow". Now everything from A2 to the end is selected and you can paste your formula. If you have no data in A2-A65535, you will select the entire range. If you have data, this will only select to the end of the list of data.

  • AdamV

    I think you may have to reword this a little for people to understand exactly what you are after. Are you trying to reference the range of all cells in a column except for the header? Most likely you can do this using an OFFSET with a COUNTA function inside, for example to get all of column A without the header you could use:

    =OFFSET($A$1,1,0,COUNTA(A:A),1)

    This assumes all the other cells in A have something in up to the end of the data range, no blanks in between.

    What function are you trying to point at this range?

  • Siamak

    for example if you want to apply SIN(x) on a set of data in a row, in a vacant cell type: =SIN("click on the first cell of the row") then press "enter" then grab the handle of this cell and draw it up to the last cell. now you have a set of data in a row each cell of which is has the value of the SIN(the original cell)


    • Related Question

    How to update all functions in Excel 2007?
  • Jonno_FTW

    Is there a way to quickly fill down a column of functions. For example, if I change my function from

    =3*B2

    And then fill down so it does it for a lot of rows.

    But then change this column to:

    =10*B2

    Is there a way to quickly update all my functions in the column?


    • Related Answers
  • Craig

    Update your formula in the first cell. Position you mouse over the bottom-right hand corner of the cell, the cursor which change from a white-cross to a black-cross. Double-click and your range will auto-fill with your new formula.

  • Lance Roberts

    Drag and Fill Again.

    If this is a common occurrence you can used Named Constants for the constant value, so you can change it easy.

  • David Basarab

    You can do a copy and replace.

    So highlight the cells you want to change, and do a Ctrl-F and replace the value according.

    However this might have side effects you might not desire.

    For example lets say you want to change 3 to 10, then you might actually change cell B3 to B10, B30 to B100 etc. . .

    Your best bet is to make a constant by having all the values represented in another cell.

    For exmaple A1 then your formula will look like 

    $A$1 * B1
$A$1 * B2
$A$1 * B3

    etc.. .

    Then you just change A1 and all your forumlas update accordingly.

    ($ means don't change the cell either column or row after when I do a drag or fill. In this example A and 1 do not change.)