Format & Arrange:

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

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 again!

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 lesson.

Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & Arrange
    Format CellsTo subtopics
    Format ChartTo subtopics
    Arrange Open arrow to subtopics
    Exercises To subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics


Icon Step-by-Step

Step-by-Step: Copy

 Icon Step-by-Step

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: Class disk 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

  1. Click on the tab for the sheet Specials and select range A1:F10. This is the titles, labels, and data for the Tahiti trips.
  2. Hold the CTRL key down and drag the selection by its border to the right to columns H - M. The pointer changes to Pointer: Arrow with plus signthe Copy shape.

    WarningIf you don't see the + sign beside the arrow, you will Move the selection rather than copy it.

  3. Whole table copied to columns H-MDrop 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.

  4. Undo since what you really want to do is to copy this material to a different sheet.

Copy: Drag non-adjacent range?

  1. Select ranges A1:F10 and A25:F25, which includes the totals row.
  2. Try to drag this selection of non-adjacent ranges. It won't work! Your pointer refuses to change from Pointer: Select 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

  1. If necessary, select range A1:F10, which contains the titles, column labels, and Tahiti trips, together with A25:F25, which contains the Totals.
  2. Copy.
  3. 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.)
  4. Paste. It's much easier this way than to drag the selection this far. No speedy scrolling to try to rein in.
  5. Undo since you really want this data to be on a different sheet .

Paste to a different sheet

  1. Table copied by pasting to different sheetClick on the tab for sheet Tahiti to make it active.
  2. With cell A1 selected, paste. The copied cells are pasted in order,  with the non-adjacent rows now adjacent. This could be a problem!

  3. 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.

    tip The ##### in cell E11 means that the column is not wide enough to show the complete number.

  4. Undo and try this in a way that will preserve the formulas.

    tip 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 picturestipIf 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.

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

  1. Dialog: Paste SpecialWith 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.

  2. Tahiti table after Paste Special, showing #REF! errorsClick 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.

    tip #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.

    Warning Paste and Paste Special both may have problems with pasting some formulas. Be careful.


  3. Tahiti sheet after fixing #REF errorsSelect cell D11 and click Button: AutoSum the AutoSum button. The columns above D11 are added, which is what you want. Press ENTER.
  4. Repeat for F11.
  5. AutoFit all columns.
  6. 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.
  7. Resize Row 4 with AutoFit.
  8. Class disk Save  as  trips19.xls  on your Class disk in the  excel project3  folder.
  9. 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.
  10. 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!

    Icon: ConfusionPoint 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.)