Jan's Excel Format & Arrange (97- 2003): Copy

Step-by-Step: Copy

What you will learn:

to copy by dragging
to not drag non-adjacent ranges
to copy by pasting on the same sheet
to copy by pasting to a different sheet
to use Paste Special

Start with: trips18.xls - Specials (saved in previous lesson)

First you will experiment with copying ranges to new spots on the same sheet. Then you will copy ranges to the new sheets you created in the last lesson. Eventually you will find out how to copy and paste so that the pasted cells update automatically when the original data is changed. Powerful!

Each method uses below is good in certain situations. No method is "best".

Copy: Drag range

Click on the tab for the sheet Specials and select range A1:F10. This is the titles, labels, and data for the Tahiti trips.
Hold the CTRL key down and drag the selection by its border to the right to columns H - M. The pointer changes to the Copy shape.
If you don't see the + sign beside the arrow, you will Move the selection rather than copy it.
Drop with the upper left corner at cell H1. You may have to drag out of the window to get to H1. The window will scroll, sometimes far too quickly!
Notice that the column widths were lost, but the cell formatting was kept.
Undo since what you really want to do is to copy this material to a different sheet.

Copy: Drag non-adjacent range?

Select ranges A1:F10 and A25:F25, which includes the totals row.
Try to drag this selection of non-adjacent ranges. It won't work! Your pointer refuses to change from the Select shape. You simply cannot drag a selection of non-adjacent ranges.

Copy: Paste

To copy over a distance or to a different sheet, it is easier to Copy and Paste.

Paste to the same sheet

If necessary, select range A1:F10, which contains the titles, column labels, and Tahiti trips, together with A25:F25, which contains the Totals.
Copy.
Scroll until you can click on cell S1 to select it. (You may need to use the arrow at the right end of the horizontal scroll bar.)
Paste. It's much easier this way than to drag the selection this far. No speedy scrolling to try to rein in.
Undo since you really want this data to be on a different sheet .

Paste to a different sheet

Click on the tab for sheet Tahiti to make it active.
With cell A1 selected, paste. The copied cells are pasted in order, with the non-adjacent rows now adjacent. This could be a problem!
Select cell F5. Look at the Formula bar. There is no formula showing like there was on sheet Specials. Whoops!

What happened: The values for the calculated totals in Column F and Row 11 are pasted instead of the formulas. This freezes everything in place. If you make changes back on the sheet Specials, or if you change values on this table itself, the totals will not be updated. This is not usually what you want to happen!

Notice that the column widths are lost, also.

The ##### in cell E11 means that the column is not wide enough to show the complete number.
Undo and try this in a way that will preserve the formulas.
You can drag a selection to another sheet by holding the ALT key down while you drag over the tabs until you make active the sheet you want. Continue dragging to the destination cell and drop. This requires a steady hand.

Copy: Paste Special

With Paste Special you have more control over what is copied. This command will attempt to copy formulas. You will often have problems with copied formulas so be prepared to do repairs.

Dialog: Paste Special for pictures If you select Edit | Paste Special and see a dialog about pictures instead of one about Paste, the clipboard has lost your copied data. You will have to copy it again.

Remember that the Windows Clipboard will dump your copied or cut data as soon as you do anything else.

With only cell A1 selected on the sheet Tahiti, select from the menu Edit | Paste Special and choose All. Click on OK.
The copied cells are pasted again, this time attempting to paste the formulas instead of just current values. Again the column widths are lost.
Click on each of the totals cells in Column F to see what the Formula bar shows. The formulas in most of Column F are working fine. But the ones in Row 11 have a problem.
#REF! in a cell means that the cell references in the formula don't work. It is usually because you deleted the data or the cells don't contain numbers anymore. In cells D11 and F11, the formulas are themselves sitting in cells that are used in the original formulas. This creates a circular reference, by trying to calculate using the cell that the formula itself is in. The cure is to rewrite the formulas.

Paste and Paste Special both may have problems with pasting some formulas. Be careful.
Select cell D11 and click the AutoSum button. The columns above D11 are added, which is what you want. Press ENTER.
Repeat for F11.
AutoFit all columns.
Drag column D wide enough to put "Number of" on one line and "people" on a second line. AutoFit does not look at text that is wrapped to decide how wide to make the column.
Resize Row 4 with AutoFit.
Save as trips19.xls on your Class disk in the excel project3 folder.
Experiment by changing some of the values on the Tahiti sheet that are used in formulas. The calculated values should change also. Undo your changes.
Experiment by changing some of the values on the Specials sheet and checking to see if the Tahiti sheet values match. (They won't.) Undo your changes!
Point of Confusion: Your table will now adjust the totals if you change the values on the Tahiti sheet, but nothing will change on this sheet if you make changes on the sheet Specials.

Be sure to Undo all changes you made with your experiments. (Hint: You saved before you began your experiments. If you now close without saving changes, you can reopen the file to get back to your last saved version.)

Format & Arrange: Copy