Applying a formula to a whole column

English
, ,
#1

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?

1 Like
#3

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.

2 Likes
#4

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.

1 Like
#6

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

#7

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

What do you mean by sequential programs?

#8

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
[Edit 2025-04-26]
The above link is broken. Things have changed since august of 2021. There are new functions, SORTBY() one of them, e.g, and the old commented example may no longer be of value. It also is to big to attach it here directly. (?? 1.2 MiB > 4 MiB ??)
[/Edit]
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.

1 Like
#2

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.

2 Likes
#5

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.

#10

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.

#9

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.

#11

For those finding this comment later: Array formulas have the disadvantage, that they severely limit the ability to edit the spreadsheet later. For example,

  • Alt+Drag&Drop to move rows/columns doesn’t work if the row/column contains an array formula. You have to start manually using “insert cells” / “delete cells” and copy/paste of the contents.
  • “Insert rows above/below” does not work anymore.
  • The behavior of “insert cells” with “shift cells up/down” etc changes subtly.
  • Editing array formulas is unintuitive; If you try to edit the cell e.g. with F2 or Ctrl+F2, you get the error “You cannot change only part of an array” instead of the whole array being selected automatically.
#12

Since you seem to be concerned with the disadvantages of array formulas with a locked output range, you might want to take the time to study the attached example.
It is based on a template containing the extended BSM-package and a few additional helpers. That’s user code by “Lupp”.
nextSortByExample.ods (94.5 KB)
(Needs LibO V24.8 or higher.)

I am interested in your comments.

#13

The cells of a result matrix are automatically protected from changes. However, you can edit, delete, or copy the matrix formula by selecting the entire matrix cell range completely.

#14

by selecting the entire matrix cell range completely.

Is there some command for that? Personally, I’d still expect the matrix cell range to be selected automatically when trying to edit.

#15

[Ctrl /] to select the array range when in one of their cells.

1 Like
#16

[Ctrl /] to select the array range when in one of their cells.

Thanks for the hint. The command isn’t in “Edit > Select”, and the hotkey is one that doesn’t work on a German keyboard.

<offtopic type=“rant”>

Not something I will blame LibreOffice for though. Google has been using “QUERTY only” keyboard shortcuts for as long as I remember across all their webapps, and keeps ignoring the feedback. (Not just mine of course, also others on google forums etc).

Probably a hard problem to solve. Using only letter keys and numbers for key combination would work at least across latin alphabet keyboard loadouts, but still leads to edge cases like some games being unplayable, due to ZXC not being next to each other. And that ignores that there are keyboard layouts that don’t even contain latin characters.

In JavaScript, one solution would be to use keyEvent.code. Google’s apps use .keyCode, which depends both on the keyboard layout and the browser, leading to Alt + \ being available as Alt + # on a German QUERTZ layout in Chrome, but not in Firefox. By contrast .code is a string, that represents the position of the key, and gives a string, corresponding to the behavior of the key when using a US QUERTY layout. E.g. the QUERZ # key would return a keyEvent with .code === "Backslash".

For testing, in the javascript console:

document.addEventListener("keydown", (e) => {
    console.log(e.code, e.keyCode);
})
</offtopic>
#17

If it doesn’t work in a German layout, maybe because it is a shift key, it happens in a lot of languages.
But it is really simple to change in Menu>Tools>Customize>Keyboard.

image
image902×143 13.1 KB

#18

I know. But sadly, this comes with a few downsides of its own.

  • Libeoffice stores not user-defined customizations, but a whole new menu/hotkey/etc profile. When a new version introduces new functionality, customizing anything will prevent these new features from showing up. Compare that to LyX, which stores user customizations to key bindings as \unbind and \bind commands relative to the LyX-dfined profiles.
  • I know of no way to synchronize these customizations across devices.
  • Compared to other software, changing keybindings by selecting hotkeys from a long list is unituitive. Compare again to LyX, where you select the command you want to change (searchable list, also ability to enter custom compound commands like command-sequence buffer-save; buffer-export pdf2) and then you enter a key or key sequence after clicking a button. Some software allows assigning hotkeys in the menubar.
#19

On the custom shortcut dialog, there are 2 buttons to load and save the shortcuts.

#20

You can also write a macro that sets your favorite hotkey combinations for an application (LO), module (Writer, Calc, …), or a separate document.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.