Applying a formula to a whole column

[jeshkhol]

I assume the answer to my question is pretty simple but I fail to find it.

In a sheet there are 3 “main” columns (A to C) with the data I enter and 3 “helper” columns (D to F) consisting of formulas (REGEX functions) modifying the contents of the main columns (necessary for the desired sorting).

Now I usually enter the formula in D2, E2 and F2 (the first row is a column label), and once they prove to work correctly, I copy and drag these cells down the sheet a bit. Often 1,000 cells down a column are enough, but sometimes I need up to 10,000 cells.

Is there an easy way to automatically fill the cells with the formula down to D1048576, E1048576, and F1048576?

And, if I later I need to change the formula in D2, how can I easily update the formula in all the cells down to D1048576?

[LeroyG]

If C column has data in all cells, select D2:F2, and double click in the fill square (the black box at the lower right corner of the selection).

Tested with LibreOffice 7.0.6.2 (x86); OS: Windows 6.1.

[Lupp]

It’s easy to trigger the filling down affter a Ctrl+Downarrow by Ctrl+D, but filling a substantial formula into more than 3E6 cells (3 columns) will be very inefficient (if not killing the app) concerning the needed time and storage. Tasks of such a size should be done by sequential programs. Sheets need to keep and save a formula for every row representing what’s done in one step by always the same code in an efficient program.

[jeshkhol]

Hmm, I fail to make this work here – nothing happens if I double click the fill square.

Version: 7.0.6.2
Mac OS X 10.14.6

[jeshkhol]

Thank you for pointing me to the Fill Down command which I hadn’t been aware of.

What do you mean by sequential programs?

[Lupp]

Concerning the term “sequential” and also topics of efficiency (time, RAM usage, file size) I made a commented example.
http://psilosoph.de/AskLO/compareSortBySortToSortByFormulas_1000.ods

If you (somebody) wants to study it, please regard: The example discussing the potential inefficiency of workiing with formulas filled-down to many rows is limited to only 1000 rows, and therefore not remarkably inefficient.
To actually see what I mean, you should expand it to 10000 or even more rows using additional data from the second sheet and filling down the formulas as far as needed.

The lesson from the example in short:
The tool >Data>Sort can sort 100000 words needing less than a second, and no extra space.
The formulas doing the same task in the other sheet would need about an hour for the full data set - and lots of additional file size and…
Look for efficient tools before you fill down formulas for thousands of rows.

[Wanderer]

You already know: You can copy the formula and paste it into other cells. So you need a quick way to select the column to the end.

First suggestion: use keys Shift-Ctrl-End to select to the End of the table, use Shift-LeftArrow back to your column and Paste.

Second: Look at the box in the upper left corner of the table (left of A). This shows the current selection, but you can also edit it. Write A2:A40 in the box, hit enter and this area is selected.

[jeshkhol]

Thanks! So with your second suggestion, I select D2, enter D2:D1048576 in the Name Box, click enter to select the column, and I can paste the formula of D2 into the whole column.

I now realise it’s even possible to do this with several columns in one go (e.g., select D2 to F2 and enter D2:F1048576 in the Name Box, then paste). It lasts a moment so it may be better to only apply this to a few thousand rows rather than to the whole column.

[LeroyG]

@jeshkhol

enter D2:D1048576 in the Name Box

For me it is easier to click in the column D heading (to select all the column), then Ctrl+click in D1 to unselect it.

[mariosv]

You can use a matrix formula (introduce it with [Shift+Ctrl+Enter] instead only [Enter]

d1=A:A*2 [Shift+Ctrl+Enter]-> {=A:A*2}
d1=A1:A1048576*2 [Shift+Ctrl+Enter]-> {=A:A*2}
d1=A1:A5000*2 [Shift+Ctrl+Enter]-> {=A1:A5000*2}

so you don’t need to copy-down, with this is even easier to do for your desired range, the whole column in any case it’s not a good idea for the performance.