Quickest way to apply a formula to an entire column?

Question

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?

possible duplicate of [Applying a formula to all cells in a column, not just one at a time?](http://stackoverflow.com/questions/5372727/applying-a-formula-to-all-cells-in-a-column-not-just-one-at-a-time) — tohuwawohu, Nov 11 '14 at 19:23
The best answer on that question applied to excel, LibreOffice is different though. Ended up hand copy/pasting and altering the formula. — Eamonn M.R., Nov 11 '14 at 21:59
Copy/Paste a formula is straight forward, but how about the references? Just note that you can prefix for fixed positions with $, e.g. $A$1, or $A1, or A$1 if the copied formula should NOT automatically change one or both of the cell coordinates. See: https://ask.libreoffice.org/en/question/73273/copying-formulae-down-while-only-changing-certain-aspects/ — Roland, Feb 03 '20 at 10:26

score 147 · Answer 1 · edited Aug 08 '19 at 13:44

147

You can follow these steps:

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

Source at Zyxware

edited Aug 08 '19 at 13:44

Gaurang Tandon

6,504
11
47
84

answered Jul 14 '16 at 13:55

Victor Gazotti

1,773
1
13
9

6

I need to press Shift+Enter additionally in step 2, to go back to the first cell. (LibreOffice) For Mac, use Command instead of CTRL. – Nianliang Jan 24 '17 at 04:27
2

This is the way. I have had 30000 rows and couldn't select by mouse. It lasted too long. Thanks! – Hrvoje T Feb 20 '17 at 13:00
8

You can use Ctrl+Shift+Downarrow to only select cells in the **column** – Nero Vanbiervliet Jan 07 '20 at 09:54
2

@NeroVanbiervliet Correct. This answer should be edited. – SurpriseDog Mar 10 '20 at 18:30
1

@SurpriseDog > Click Ctrl+Shift+End. This will select the **last right cell used on the worksheet.** – Victor Gazotti Mar 12 '20 at 17:01
2

The question asks for selecting a column, not for selecting the "last right cell" – SurpriseDog Mar 12 '20 at 17:04
@SurpriseDog as you may see by the up votes in the answer, people understood – Victor Gazotti Mar 13 '20 at 19:53
Looks like this only works if you have no filters applied. – claudiu.nicola Nov 29 '21 at 12:04
Ctrl+Shift+Downarrow also works in Google Sheets – AAAfarmclub Jan 12 '22 at 07:31
1

The answer by Bastiaan Wakkie actually answers the question as posed: more easily filling just the column, just to the end of the data. As noted by SupriseDoc, this answer does something different, if there are columns to the right. – nealmcb Aug 22 '22 at 14:19
Just tried this and it doesn't fill the formula as of the latest LibreOffice version. Ctrl+Shift+End will select remaining cells in the column, but Ctrl+D does absolutely nothing. – Shajirr Dec 23 '22 at 07:44
Oh, really? Didn't see in the latest libreOffice version, I'll check i out – Victor Gazotti Dec 28 '22 at 18:27

Rama · Answer 2 · 2016-06-17T07:09:44.920

This worked for me,

Write the formula in the top cell of a column.
Copy this cell
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
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.

Oleh Holovko · Answer 3 · 2017-07-27T10:34:24.797

25

A slightly modified version of what Victor Gazotti proposed:

Write the formula in the cell.
Click on the cell with the formula.
Scroll to the last element in the column.
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.
Click CTRL+D and the formula will be spread for the whole selection.

edited Jul 27 '17 at 10:34

answered Jul 27 '17 at 10:29

Oleh Holovko

449
5
7

2

This is a better answer than Victor's. Simpler (no 3-button combos) and works in more cases (doesn't assume pressing Enter leaves you on the current cell). – otocan Mar 09 '18 at 12:03

Bastiaan Wakkie · Answer 4 · 2023-07-07T08:57:04.460

11

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

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

This will copy your formula down in row B till the last filled cell in row A

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

edited Jul 07 '23 at 08:57

answered Jun 10 '20 at 12:24

Bastiaan Wakkie

303
3
9

2

In 6.3.4.2, you have to double-click the fill handle. – Violets Jul 20 '20 at 23:05
2

in 3 + right click didn't work for me, it had to left double-click – Laurent Sep 01 '20 at 11:24

Prashant Bharadwaj · Answer 5 · 2023-05-15T19:11:13.967

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

As for why you would want to use array formulas vs regular formulas has some points here -

array formulas can be a bit faster, but a bit more difficult for editing.. you can't easily overwrite array formula ranges (which is good for safety).

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)

Quickest way to apply a formula to an entire column?

5 Answers5