Move, copy, and link cells using drag and drop - OpenOffice.org Ninja

Move, copy, and link cells using drag and drop

Posted by Andrew Z at Monday, March 3, 2008 | Permalink

OpenOffice.org 2.4 (due soon) introduces quick ways to move, copy, and link cells in Calc using drag and drop. To move a column (for example), it is no longer necessary to insert a column, cut the old column, paste the column into its new place, and delete the old column. This article covers the old drag and drop mode, the three new drag and drop modes, and finish up with a demo video.

Overwrite mode

The overwrite mode available since before version 2.4 is still available.

  1. Highlight some cells.
  2. Drag the black selected area. Notice the dragged box has a thick black border which means overwrite mode.
  3. Drop using the mouse.

The source location overwrites the target. No cells are shifted.

Insertion mode

In OpenOffice.org 2.4, insertion mode will not overwrite cells. Vertical insertion mode shifts cells to the right, and horizontal insertion mode shifts cells down. The procedure is the same for both, and OpenOffice.org uses an algorithm to select the insertion mode. The algorithm considers (1) the horizontal and vertical distances from source to target and (2) the aspect ratio of the selected area and (3) whether the source and target are within the same sheet.

  1. Highlight some cells.
  2. Drag the black selected area (by pressing and holding the left mouse button).
  3. After you start dragging, press and hold the ALT key. Notice the dragged box has a thick black border only on one side. A thick border on the left indicates vertical insertion mode, and a thick border on the left indicates horizontal insertion mode.
  4. Drop (by releasing the left mouse button).
  5. Release the ALT key.

Insertion mode is similar to Excel's Insert Cut Cells feature.

Copy mode

To copy cells:

  1. Highlight some cells
  2. Drag the black selected area.
  3. Press and hold the CTRL key. Notice the cursor changes to a plus sign.
  4. Drop.
  5. Release the CTRL key.

The result are two, independent copies of the selection. Copy mode may optionally be combined with insertion mode by holding down both ALT and SHIFT.

Insertion mode is similar to Excel's Insert Copied Cells feature.

Link mode

To link cells, hold down CTRL and SHIFT after the dragging and before the drop. The result are two copies of the selection, and the new copy is linked using formulas to the original. Link mode mode can optionally be combined with insertion mode by holding down ALT, CTRL, and SHIFT.

Video demo

Moving rows and columns

Before OpenOffice.org 2.4.0, to move a column row, you would:

  1. Insert a blank column or row.
  2. Cut the old column or row.
  3. Paste the column into its new place
  4. Delete the old column.

Alternatively, there are the options Shift cells down and Shift cells right available in Edit > Paste Special. Another option is recording a macro.

The new insertion mode makes moving rows and columns task quicker.

Related posts

11 comments:

Anonymous said...

In Calc, how do I drag and drop one single cell as in Quatro-Pro or Excel?
Open offoice only seems to work for two or more cells.

Andrew Z said...

How to drag a single cell
1. Click the cell. Notice the cell is outlined.
2. Drag down to highlight multiple cells. Notice they have a dark background.
3. Drag back up to highlight just the one. Notice the one still has dark background.
4. Continue normally.

Thanks to acknack for the tip.

Anonymous said...

Thanks, Andrew Z. It works. After I posted my message I kept on surfing and found another variation on your solution.
1 Select the cell.
2 Hold the shift and click the cell. The cell is now highlighted.
3 Let go of the shift key and you can drag the cell wherever.

Thanks very much for your help.

Anonymous said...

Strange.. All these great features also works on my current version 2.3 (platform Ubuntu, exact version in about box: openoffice.org-core 1:2.3.0-1ubuntu5.3. Tue Nov 27 18:52:42 GMT 2007.)
Anyone else can this confirm too?

Anonymous said...

How can I copy cells A1 and B1 and paste their contents into A2-A20 and B2-B20 respectively. Example:
Hello World
.
.
.
.
Hello World

Anonymous said...

I love Open Office, I hate that most institutions use Microsoft Word.

Anonymous said...

Copy mode may optionally be combined with insertion mode by holding down both CTRL and ALT.
I tried 2.4.1 on Linux. ALT+SHIFT is not working.

finalrune said...

oh wow... you just saved me a ton of time. thanks!

Fred

Sanj said...

I was getting really frustrated not being able to move some rows, Alt really did the trck thanks a ton!

Mick Reay said...

I have a calc sheet for storing addresses and other data, in my First sheet can I 'link' the name if an address (say in cell A5) so that when you click on it it will automatically link to the sheet (say sheet 10) where I have all of the info for that address stored.

I want it to behave like a hyperlink but only want it to link to another sheet in the same spreadsheet.

Unknown said...

I appreciate Sun's work on building a free software....but you know the saying....what is free ...sucks! If you want something professional get MS Office!!! Dont play with toys!