It would be useful to have the data
about each of the special trips on a separate sheet. But no one wants to type all that over
There are several ways to copy
your data instead of re-typing. Each has different effects and limitations.
Drag: You can use CTRL + drag
to copy a selected range to a new spot on the same
sheet. The right drag menu also allows copying. However, you cannot drag
non-adjacent ranges at the same time.
Copy & Paste: This is usually best when copying
to a distant spot on the same sheet or copying to a different sheet. Formulas may
need to be corrected.
Link: Linking makes a copy that
changes whenever the original changes. This method is covered in the next
Where you are:
Project 1: Excel Intro
Project 2: Excel Basics
3: Format & Arrange
Project 4: Groups & Formulas
Project 5: Design
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
trips18.xls - Specials
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.
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.
don't see the + sign beside the arrow, you will Move the selection rather
than copy it.
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
Notice that the column widths were lost, but the cell formatting was
- 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.
To copy over a distance or to a different sheet, it is easier to Copy and
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
- 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
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
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.
you select | 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.
the Windows Clipboard will dump your copied or cut data as soon as you do anything
only cell A1 selected on the sheet Tahiti, select from the
menu | 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.
and Paste Special both may have problems with pasting some formulas. Be
cell D11 and click
the AutoSum button. The columns above D11 are added, which is what you want.
- 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
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.)