Moving data on the worksheet

Copying and Pasting Data, Slide 1 Copyright © 2004, Jim Schwab, University of Texas at Austin

Like other windows applications, Excel has the ability to move data on the worksheet by copying and pasting or cutting and pasting. Both copying and cutting use the clipboard as an intermediary location.Copying duplicates the data on the clipboard. Cutting copies the data to the clipboard, but deletes it from the original location when it is pasted.

We can move single cells, a range of cells, a column, or row, or even an entire worksheet.

There are two versions of the paste command: paste and paste special. Paste copies the data and formatting to the destination cell. Paste special providers options for copying the values or individual formatting elements.

Excel 2003 uses smart tags when an object is pasted, giving the user additional options to refine the paste command.

The copy, cut, paste, and paste special commands are available on the menu bar, as tool buttons on the standard tool bar, on the popup menu, and as keyboard shortcuts.

Copying data to the clipboard

In cell A1, the number 127 has been entered and formatted as bold style in the color violet.

In cell A1, the number 127 has been entered and formatted as bold style in the color violet.

Second, we click on the Copy tool button on the Standard tool bar.

Second, we click on the Copy tool button on the Standard tool bar.

To copy it to the clipboard, we first make sure that cell A1 is selected. Cell A1 is referred to as the source cell for the copy and paste.

To copy it to the clipboard, we first make sure that cell A1 is selected. Cell A1 is referred to as the source cell for the copy and paste.

Cells that have been copied to the clipboard are framed by a moving dashed border called a marquee.

Cells that have been copied to the clipboard are framed by a moving dashed border called a marquee.

Copying and Pasting Data, Slide 2 Copyright © 2004, Jim Schwab, University of Texas at Austin

Pasting clipboard data to another cell

First, select the destination cell where the clipboard data is to be pasted, e.g. C3.

First, select the destination cell where the clipboard data is to be pasted, e.g. C3.

Second, click on the Paste tool button on the Standard tool bar. Click on the icon on the tool button, not on the drop down menu.

Second, click on the Paste tool button on the Standard tool bar. Click on the icon on the tool button, not on the drop down menu.

The contents and formatting of the cell copied to the clipboard is pasted into cell C3.

The contents and formatting of the cell copied to the clipboard is pasted into cell C3.

Excel puts a Smart Tag near the lower right-hand corner of the destination cell. The smart tag provides options for further refining the paste command.

Excel puts a Smart Tag near the lower right-hand corner of the destination cell. The smart tag provides options for further refining the paste command.

Copying and Pasting Data, Slide 3 Copyright © 2004, Jim Schwab, University of Texas at Austin

The smart tag

The smart tag provides options for what is pasted and how it is formatted. The default selection is to keep the formatting applied to the source cell.

Since our choice is to keep the formatting, we press the Enter key and the smart tag disappears.

The smart tag provides options for what is pasted and how it is formatted. The default selection is to keep the formatting applied to the source cell.

Since our choice is to keep the formatting, we press the Enter key and the smart tag disappears.

Smart tags were added in Excel 2002 (version 10). If you are using an older version you will not find them.

Smart tags were added in Excel 2002 (version 10). If you are using an older version you will not find them.

Copying and Pasting Data, Slide 4 Copyright © 2004, Jim Schwab, University of Texas at Austin

Copying a selected range of cells to clipboard

First, select the source cells to be copied, cells A1 to A3.

First, select the source cells to be copied, cells A1 to A3.

Second, click on the Copy button on the Standard tool bar.

Second, click on the Copy button on the Standard tool bar.

When the cells have been copied to the clipboard, the selected cells are outlined with a marquee.

When the cells have been copied to the clipboard, the selected cells are outlined with a marquee.

Copying and Pasting Data, Slide 5 Copyright © 2004, Jim Schwab, University of Texas at Austin

Pasting the clipboard cells to a new destination

First, select the destination cells for the copied cells D1 to D3.

First, select the destination cells for the copied cells D1 to D3.

Second, click on the Paste tool button on the Standard tool bar. Click on the icon on the tool button, not on the drop down menu.

Second, click on the Paste tool button on the Standard tool bar. Click on the icon on the tool button, not on the drop down menu.

The contents and formatting of the cell copied to the clipboard are pasted into cells D1 through D3. Since this is what we wanted, we hit the Enter key to dismiss the Smart Tag.

The contents and formatting of the cell copied to the clipboard are pasted into cells D1 through D3. Since this is what we wanted, we hit the Enter key to dismiss the Smart Tag.

Copying and Pasting Data, Slide 6 Copyright © 2004, Jim Schwab, University of Texas at Austin

Pasting the clipboard cells to a destination cell

First, select only the first destination cell for the copied cells D1.

First, select only the first destination cell for the copied cells D1.

Second, click on the Paste tool button on the Standard tool bar. Click on the icon on the tool button, not on the drop down menu.

Second, click on the Paste tool button on the Standard tool bar. Click on the icon on the tool button, not on the drop down menu.

The contents and formatting of the cell copied to the clipboard are pasted into cells D1 through D3. Since this is what we wanted, we hit the Enter key to dismiss the Smart Tag.

The contents and formatting of the cell copied to the clipboard are pasted into cells D1 through D3. Since this is what we wanted, we hit the Enter key to dismiss the Smart Tag.

We can also choose only the first cell of the destination and paste a range to it, provided we are careful to make sure the destination cells are empty. If they are not empty, Excel will over-write them with no warning.

We can also choose only the first cell of the destination and paste a range to it, provided we are careful to make sure the destination cells are empty. If they are not empty, Excel will over-write them with no warning.

Copying and Pasting Data, Slide 7 Copyright © 2004, Jim Schwab, University of Texas at Austin

Pasting the clipboard to another worksheet

We can also copy a cell or range of cells from one worksheet to another.

We can also copy a cell or range of cells from one worksheet to another.

First, select the source cells to copy, A1 to A3, and click on the Copy tool button.

First, select the source cells to copy, A1 to A3, and click on the Copy tool button.

Second, click on the tab for another worksheet, Sheet2, to navigate to it.

Second, click on the tab for another worksheet, Sheet2, to navigate to it.

Third, select the destination cells on the second worksheet, A1 to A3, and click on the Paste tool button.

Third, select the destination cells on the second worksheet, A1 to A3, and click on the Paste tool button.

The contents and formatting of the cell copied to the clipboard are pasted into cells A1 through A3 on Sheet2. Since this is what we wanted, we hit the Enter key to dismiss the Smart Tag.

The contents and formatting of the cell copied to the clipboard are pasted into cells A1 through A3 on Sheet2. Since this is what we wanted, we hit the Enter key to dismiss the Smart Tag.

Copying and Pasting Data, Slide 8 Copyright © 2004, Jim Schwab, University of Texas at Austin

Accessing copy and paste through menus

The Copy and Paste commands are also available on the Edit menu.

The Copy and Paste commands are also available on the Edit menu.

Copying and Pasting Data, Slide 9 Copyright © 2004, Jim Schwab, University of Texas at Austin

Accessing copy and paste through popup menus

The Copy and Paste commands are also available on the popup menu when you right click the mouse on a cell.

The Copy and Paste commands are also available on the popup menu when you right click the mouse on a cell.

Copying and Pasting Data, Slide 10 Copyright © 2004, Jim Schwab, University of Texas at Austin

Cutting and Pasting Data

Copying and Pasting Data, Slide 11 Copyright © 2004, Jim Schwab, University of Texas at Austin

In cell A1, the number 127 has been entered and formatted as bold style in the color violet.

In cell A1, the number 127 has been entered and formatted as bold style in the color violet.

Second, we click on the Cut tool button on the Standard tool bar.

Second, we click on the Cut tool button on the Standard tool bar.

To put it to the clipboard, we first make sure that cell A1 is selected. Cell A1 is referred to as the source cell for the cut and paste.

To put it to the clipboard, we first make sure that cell A1 is selected. Cell A1 is referred to as the source cell for the cut and paste.

Cells that have been cut to the clipboard are framed by a moving dashed border called a marquee.

Cells that have been cut to the clipboard are framed by a moving dashed border called a marquee.

Pasting clipboard data to another cell

First, select the destination cell where the clipboard data is to be pasted, e.g. C3.

First, select the destination cell where the clipboard data is to be pasted, e.g. C3.

Second, click on the Paste tool button on the Standard tool bar. Click on the icon on the tool button, not on the drop down menu.

Second, click on the Paste tool button on the Standard tool bar. Click on the icon on the tool button, not on the drop down menu.

The contents and formatting of the cell copied to the clipboard is pasted into cell C3.

The contents and formatting of the cell copied to the clipboard is pasted into cell C3.

The source data in cell A1 is deleted when the cut and paste is complete.

The source data in cell A1 is deleted when the cut and paste is complete.

Copying and Pasting Data, Slide 12 Copyright © 2004, Jim Schwab, University of Texas at Austin

Accessing cut and paste through menus

The Cut and Paste commands are also available on the Edit menu.

The Cut and Paste commands are also available on the Edit menu.

Copying and Pasting Data, Slide 13 Copyright © 2004, Jim Schwab, University of Texas at Austin

Accessing cut and paste through popup menus

The Cut and Paste commands are also available on the popup menu when you right click the mouse on a cell.

The Cut and Paste commands are also available on the popup menu when you right click the mouse on a cell.

Copying and Pasting Data, Slide 14 Copyright © 2004, Jim Schwab, University of Texas at Austin

Drag and drop for moving data - drag

Data can be moved from one cell to another without passing through the clipboard with a technique called "drag and drop".

Data can be moved from one cell to another without passing through the clipboard with a technique called "drag and drop".

First, position the cursor on one of the borders of the source cell or cells, in this case cell A1. Hold down the left mouse button. The cursor changed to an error with four directional arrow behind.

First, position the cursor on one of the borders of the source cell or cells, in this case cell A1. Hold down the left mouse button. The cursor changed to an error with four directional arrow behind.

Second, holding down the mouse button, move the cursor over the destination cell. The address if the destination cell will be displayed next to the cursor arrow.

Second, holding down the mouse button, move the cursor over the destination cell. The address if the destination cell will be displayed next to the cursor arrow.

Copying and Pasting Data, Slide 15 Copyright © 2004, Jim Schwab, University of Texas at Austin

Drag and drop for moving data - drop

When the mouse button is released over the destination cell, the contents of the source cell or cells is moved to the destination cell or cells.

When the mouse button is released over the destination cell, the contents of the source cell or cells is moved to the destination cell or cells.

Drag and drop can also be used to copy data from one cell to another if the Ctrl key is held down while dragging and dropping.

Drag and drop can also be used to copy data from one cell to another if the Ctrl key is held down while dragging and dropping.

Copying and Pasting Data, Slide 16 Copyright © 2004, Jim Schwab, University of Texas at Austin

Drag and drop to a destination cell containing data

If the destination cell in a drag and drop already contains data, Excel will ask you to confirm the that you want to replace the existing data.

If the destination cell in a drag and drop already contains data, Excel will ask you to confirm the that you want to replace the existing data.

First, position the cursor on one of the borders of the source cell or cells, in this case cell A1. Hold down the left mouse button. The cursor changed to an error with four directional arrow behind.

First, position the cursor on one of the borders of the source cell or cells, in this case cell A1. Hold down the left mouse button. The cursor changed to an error with four directional arrow behind.

Second, holding down the mouse button, move the cursor over the destination cell which contains data. The address if the destination cell will be displayed next to the cursor arrow.

Second, holding down the mouse button, move the cursor over the destination cell which contains data. The address if the destination cell will be displayed next to the cursor arrow.

Copying and Pasting Data, Slide 17 Copyright © 2004, Jim Schwab, University of Texas at Austin

Drag and drop confirmation for replacing data

When data is dropped on a cell that contains data, Excel presents a confirmation dialog box to make sure data is not accidentally overwritten.

When data is dropped on a cell that contains data, Excel presents a confirmation dialog box to make sure data is not accidentally overwritten.

If your intent is to replace the data in the destination cell, click on the OK button.

If your intent is to replace the data in the destination cell, click on the OK button.

If you do not want to replace the data in the destination cell, click on the Cancel button.

If you do not want to replace the data in the destination cell, click on the Cancel button.

Copying and Pasting Data, Slide 18 Copyright © 2004, Jim Schwab, University of Texas at Austin

The paste special command

Copying and Pasting Data, Slide 19 Copyright © 2004, Jim Schwab, University of Texas at Austin

The Paste Special command offers more options over what is or is not pasted in the destination cell.

The Paste Special command offers more options over what is or is not pasted in the destination cell.

After the contents of a cell or range has been copied to the clipboard, select the destination cell and then select the Paste Special command from the Edit menu.

After the contents of a cell or range has been copied to the clipboard, select the destination cell and then select the Paste Special command from the Edit menu.

The paste special dialog box

Copying and Pasting Data, Slide 20 Copyright © 2004, Jim Schwab, University of Texas at Austin

The Paste Special command opens the Paste Special dialog box. We have the option of pasting the cell and all of its formatting, or pasting only the value, or pasting one aspect of the formatting.

The Paste Special command opens the Paste Special dialog box. We have the option of pasting the cell and all of its formatting, or pasting only the value, or pasting one aspect of the formatting.

First, click on the Values option to paste only the value with no formatting.

First, click on the Values option to paste only the value with no formatting.

Second, click on the OK button to complete the paste.

Second, click on the OK button to complete the paste.

The pasted value

Copying and Pasting Data, Slide 21 Copyright © 2004, Jim Schwab, University of Texas at Austin

The pasted value appears in the destination cell. None of the formatting from the source cell is included.

The pasted value appears in the destination cell. None of the formatting from the source cell is included.