I have 150000 observations, which I am trying to work with in LibreOffice (new name for OpenOffice.org). The main thing I want to do is to have some formula calculated for all of these observations. Different parameters of the observations are in the first 4 columns, so I wrote a function in the following column in row 2 that uses the previous columns. Now I need to copy the formula all the way down to row 150000. I tried using the mouse (as usual), but this is just way to slow. Is there a faster way to mark everything all the way to the bottom?
Yes - you can define the range of selected cells manually, and after that, "fill" the formula into all selected cells. This may take some time if the formula is applied to 150,000 data sets, so maybe you should proceed stepwise (applying for rows 1 to 50,000, 51.000 to 100,000 and so on - or use a database as proposed by m0skit0).
The cell that's currently selected is displayed in the
You can modify the selection by simply clicking into the
"Name Box" and change its value:
After you've set the selection's range this way, you can copy the the formula from cell
B1 down into all selected cells using
or using the shortcut CTRL+D: this operation will only affect the selected cells; in my example:
As result, the formula is pasted only inside the cell range defined in the
Try double-clicking on the bottom right hand corner of the cell (ie on the box that you would otherwise drag).
Copied verbatim from this stackoverflow answer.
This is a keyboard-only solution, which I've used for many years. It's extremely fast, and comes naturally when you've used it a few times:
Go to an adjacent column that has no blank rows in it, use CTRL + down arrow to go to the bottom row of the dataset.
Use right (or left) arrow to move to the bottom of the blank column that has your formula in the top row, then type a period (or any other character) in that last row.
Use Up Arrow, then Ctrl + Up Arrow to go to up the cell with your formula in it.
Hold down Ctrl + Shift, and Down Arrow to select the whole column down to, and including, the period you typed at the bottom.
Type Ctrl + d to fill the formula down the column (this make take a few seconds in a very large dataset).
Type Ctrl + s to save your changes, of course.