OpenOffice/Calc 3/Working with and in cells/Duplicate, move, delete

From WikiEducator
Jump to: navigation, search




We've discussed how Calc will delete selected rows or columns. Now let's look at how to work with copying, moving, or deleting one or more cells, that may not be a whole row or column.

Copy the contents of a cell or cell range

This operation is essentially the same as copy and paste, a common task in word processing tools, like OpenOffice.org Write. Here we will be duplicating the content of a single cell as well as of a range of cells. Further we will be executing the operation in a single worksheet, between worksheets and between spreadsheets. The procedure throughout is as follows:

  • Select the cell(s) whose contents are to be duplicated.
  • Copy the contents to the clipboard, that is select Edit > Copy.
  • Select the target, that is the cell(s) to which the content is to be copied.
  • Select Edit > Paste. The data from the previous cell(s) will now appear in the target cell(s).

Note that when you paste data, the contents of the target cells will be overwritten.

Copy within a single worksheet

Overall expense budget
Continuing with the conference budget example, suppose you wish to create an expense budget for each specific day in the conference. Rather than retype the headings, you can copy and paste the overall heading in row 3 to another section of the worksheet to list the first day's expenses.

Note that a range of cells is denoted using the upper left cell, followed by a colon, and then the lower right cell: the cells A13, B14, C13 and D13 are written A13:D13.


Icon activity.jpg Follow Along
Before we start copying, let's create some data in the target cells so you can see the effect of copy and paste. Let's use A13 through D13 as our target.
  • Type some text into cells A13, B13, C13, and D13.
    Row 13 displays some random text

Now copy cells A3:D3 and paste into cells A13:D13.

  • Select cell range A3:D3.
  • Press the key combination Ctrl+C, OR select Edit > Copy OR click the Copy icon to copy the selected cells to the clipboard.
  • Select the leftmost cell in the target range, A13.
  • Press the key combination Ctrl+V, OR Edit > Paste OR click the Paste icon to paste the copied cells into cells A13:D13.
    Warning: Do you really want to overwrite the existing data?

A dialog box displays indicating that you are about to overwrite some existing data. That's OK; you entered the random text in row 13 to demonstrate this situation.

  • Select Yes in the dialog box.
The header information from row 3 displays in row 13.
Header text copied to row 13


Copy between worksheets

Recall that each spreadsheet contains three worksheets by default. You can move between worksheets of an open spreadsheet by clicking on the appropriate tab at the bottom of the work area.

Tabs representing the three sheets included by default

Suppose you wish to copy a range of cells from Sheet 1 to a cell range in Sheet 2.

  • Select the range of cells to be copied (in Sheet 1).
  • Copy (Ctrl+C) the contents to the clipboard.
  • Click on the Sheet 3 tab.
  • Select the upper left cell in the target range
  • Paste (Ctrl+V) the contents of the clipboard.

Copy between open spreadsheets

It is possible to have several spreadsheets open at the same time. Note that spreadsheets are stored in different files whereas the worksheets of a particular spreadsheet are all stored in the same file. You can switch between spreadsheets by selecting Window on the menu bar, and then clicking on the particular file.

Suppose you have two spreadsheets open, XBudget.ods and YBudget.ods. You now wish to copy a range of cells from XBudget to YBudget.

  • Select Window > XBudget.ods, to bring XBudget.ods into the workspace.
  • Select the cells to be copied.
  • Copy (Ctrl+C) the cell contents to the clipboard.
  • Select Window > YBudget.ods
  • Select the upper left cell in the target range.
  • Paste (Ctrl+V) the contents of the clipboard into the target range.

Move the contents of a cell or cell range

There are two ways to move cells in Calc: using the cut function or using drag and drop.

Using cut and paste to move cells

You can use the same procedure for moving the contents of cell(s) as for copying the contents of cell(s) except that instead of the copy function you use the cut function, followed by paste. Note that the cut function stores a copy of the cell contents on the clipboard for later use. The contents may be moved within the same worksheet, from one worksheet to the next within the same spreadsheet, or between different spreadsheets.


Icon activity.jpg Follow Along
Let's continue with the conference expense budget example. Let's say that you've changed your mind and you want to put the budgets for specific days on separate sheets. To get started move the header data from row 13 in Sheet1 to row 3 in Sheet2.
  • A13:D13 selected
    Select the cells A13:D13 on Sheet1.
  • Cut (Ctrl-X OR Edit > Cut OR Click the Cut icon) the selected cells from Sheet1.

Row 13 in Sheet1 is now empty and the previous cell contents are stored on the clipboard.

  • Click on the Sheet2 tab.
  • Select A3, the upper left cell in the target range.
  • Header text moved to row 3 in Sheet2
    Paste (Ctrl-V) the cut cells at the new location.

The header text is now displayed in row 3 on Sheet2.


Icon present.gif
Tip: The cut function affects only the contents of the cells. The physical cells are left in place, emptied of content.


Using drag and drop to move cells

Drag and drop is a useful tool for quickly moving cell contents from one location to another:

  • Highlight two or more cells that you would like to move.
  • Click on either cell with the mouse and, keeping the left button depressed, drag the cell to its desired location.
Icon present.gif
Tip: As with the paste command, the drop action will overwrite the contents in the target cells.



Icon activity.jpg Practice
If you are not already proficient in using drag and drop in other applications, take a moment to try it out in an open spreadsheet file. Use the undo function to return the spreadsheet to its original content.


Delete the contents of a cell or cell range

Data can be removed from a cell in several additional ways. These methods differ from using the cut function in that the deleted data is not copied to the clipboard for later use.

Removing data only

The data alone can be removed from a cell or range of cells without removing any of the formatting of the cell. Select the cell(s) and then press the Backspace key.

Removing data and formatting

The data and the formatting can be removed from a cell or range of cells at the same time.

Delete Contents dialog
Press the the Delete key on your keyboard or select Edit > Delete Contents... to open the Delete Contents dialog. From this dialog, the different aspects of the cell can be deleted. To delete everything in a cell (contents and format), check Delete all. Or, choose which features to delete and which to retain.

The contents of the selected cell(s) are emptied. The cells themselves are not moved.