Quickest way to apply a formula to an entire column?

I want each row in column B to be f( column A), so that b[n]=f(a[n]). I could go row by row and apply the formula, but is there an easy way to just drag the formula down and have it increment the cells it's referencing automatically?

Solution 1:

You can follow these steps:

1. Write the formula in the first cell.
2. Click Enter.
3. Click Ctrl+Shift+End. This will select the last right cell used on the worksheet.
4. Ctrl+D. This will fill the value in the remaining cells.

Source at Zyxware

Solution 2:

This worked for me,

1. Write the formula in the top cell of a column.
2. Copy this cell
3. Select all of the rest of cells in the column. You can do this by clicking on the top cell and shift clicking on the bottom cell so the scrolling is really quick
4. With all of the cells selected select Paste. The formula will be copied to all of the currently selected cells, but will be adjusted according to its position in the sheet.

Solution 3:

A slightly modified version of what Victor Gazotti proposed:

1. Write the formula in the cell.
2. Click on the cell with the formula.
3. Scroll to the last element in the column.
4. Click on the last row in this column while holding Shift button, so that the column becomes highlighted, while the first row in the selection contains the formula.
5. Click CTRL+D and the formula will be spread for the whole selection.

Solution 4:

There is an other easier way! (at least in libreoffice 6.4.3.2)

1. Create your formula next to the column with values (say A values, B formula)
2. Select the cell with the new Formula
3. [ctrl+shift] + double left-click the fill handle of the selected cell

This will copy your formula down till the last filled cell

(EDIT: Adjusted the solution to double left-click in point 3)

Solution 5:

Quickest way to manipulate whole rows or columns is using an array formula

Check my answer to the similar question - https://stackoverflow.com/a/52119910/9049673

--Copied verbatim here for ease--

From your question it seems that you are trying to apply the same formula on whole row (or column) of cells and show the result on another row (or column).

The best way to do this is to use something called array formulas or array functions (in libre office). This link has very good explanation - https://help.libreoffice.org/Calc/Array_Functions

The way you would implement this in your case is

Type this formula in cell D1

=C1:C30 *48

and press ctrl + shift + enter

The formula now looks like this with the flower braces {..}

={C1:C30 *48}

and the formula gets applied all the way from D1 to D30.

You can even extrapolate the same concept to a whole matrix (for example: A1:C10)

(The way to tell excel/open office/ libre office that you wrote an array formula is to press ctrl + shift + enter. Never press enter as that will break the array function and convert it to a regular function)