Copying OpenOffice formulas to a whole column

In my entire working life the single biggest increase to my productivity came the day that I found out that double clicking on the little box at the bottom right of a cell containing a formula would copy that formula to the entire column.  Prior to that, on a bad day, I could spend half an hour just watching a cursor scroll down a very large spreadsheet over and over again.

These days I use OpenOffice for most of my work and this little trick doesn’t work in Calc.  Losing this one thing was enough to make me go back to Excel for my spreadsheet work.

However I’ve now found an equivalent function in Calc which has made me a whole load happier with OpenOffice.

The trick is as follows:

  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.

OK, so it’s not quite as quick as just double clicking the cell handle, but it’s pretty close and works well enough to allow me to keep using OpenOffice.


Published:January 29, 2010


Bookmark the permalink

Both comments and trackbacks are currently closed.