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

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 ?.

Wahid Bitar

Posted 2009-11-02T12:52:59.420

Reputation: 1 735

In Excel 2007 this can be done automatically if you convert the range to a list (Ctrl + L). If you enter a formula in the first row (beneath the header) in an otherwise empty column, the formula will be copied to all rows in the list. – Mike Fitzpatrick – 2010-01-27T05:26:32.590

Excel keyboard shortcut to copy/fill down for all cells with non-blank adjacent cells? – phuclv – 2016-08-18T04:27:22.520

You may not need to fill the whole column with formulas but only fill the formular for the whole table and then when new data is added below Excel will fill the formulas for you How to make Excel 2010 auto-fill new row with formulas?

– phuclv – 2016-08-18T04:36:37.367

Answers

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.

PileOfMush

Posted 2009-11-02T12:52:59.420

Reputation: 123

A couple notes: 1) Since he's using Excel 2007, the last row would actually be A1048576. And 2) If you have data in the column but you still want it to go down to row 1048576, after you do the last step here, just press End, hold Shift and press Down Arrow one more time. – Travis – 2009-11-05T17:44:44.383

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?

AdamV

Posted 2009-11-02T12:52:59.420

Reputation: 5 011

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)

Siamak

Posted 2009-11-02T12:52:59.420

Reputation: 11