Format & Arrange:
Copy to a Group

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

If your sheets are grouped, that is selected together, then you can copy, cut, and paste to all of them at once. In fact even your typing will be entered on all the grouped sheets at once.

Sample: Grouped sheet tabs

Grouped sheets - white tabs
Ungrouped sheet - gray tabs

You must then ungroup to do something to a single sheet.

WarningBe very, very careful of what you do while sheets are grouped. Actions will apply to all the grouped sheets at once.

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
    footprint Copy
    Exercises To subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics


Icon Step-by-Step

Step-by-Step: Group

 Icon Step-by-Step

What you will learn:

to group sheets
to ungroup sheets
to copy to grouped sheets
to print the entire workbook 

Start with: Class disk trips20.xls (saved in previous lesson)

Group Sheets

The sheets for each trip will have the same information at the top. You can copy the titles and column labels to all of them at the same time by grouping the sheets.

WarningYou must be very careful what you do while sheets are grouped since it will be done to ALL sheets in the group.

You cannot Paste Link to all sheets at once, though you can link individual cells on all sheets at once. Another little quirk.

  1. Select the tab for sheet New Zealand and hold the SHIFT key down while clicking on Other. The three sheets New Zealand, World, and Other are grouped, shown by all three tabs being white. (You don't need to include the Tahiti sheet since its table is already done.)

    Sheets - grouped

    Titlebar - GroupIn the title bar, (Group) has been added after the file name.

Copy: To Grouped Sheets

  1. On sheet Specials, select range A1:F4, which is the titles and column labels. Copy.
    The sheets that you grouped were automatically ungrouped when you clicked in a sheet that was not part of the group.
  2. Group the sheets New Zealand, World, and Other again.
  3. Switch to the sheet New Zealand and select cell A1.
  4. Paste. The cells you copied are pasted onto all three grouped sheets. (Click on each one and look.) The formats are correct because the cells are not linked!
  5. While the sheets are still grouped, drag Column C wide enough to put "Customer" on one line. Drag Column D wide enough to put "Number of" on one line and "people" on another. Resize Row 4.
  6. Ungroup the sheets by right clicking on one of the tabs and choosing  Ungroup Sheets  from the popup menu. 

    tipAnother way to ungroup is to click on one of the sheets not in the group. But, that won't work if you grouped all the sheets in the workbook.

  7. Copy the appropriate parts of the sheet Specials and use   Edit  |  Paste Special  |  Paste Link  to paste to each sheet with cell A5 selected:

    A11:F15 to New Zealand
    A16:F17 to World
    A18:F23 to Other

  8. Copy the Totals cells on the sheet Specials, which is range A25:F25.
  9. Paste on each sheet below the last trip entry. The totals will show #REF!

    You can not paste this line to the sheets as a group. Since each sheet has a different number of rows in the table, the totals fall in a different row for each sheet.

    The Appendix has list of all the Error Values Table

  10. Correct the totals formulas by selecting the cells where the sums should go and clicking AutoSum.
  11. AutoFit all the columns and Row 4 on all three sheets. Sheets New Zealand, World, Other
  12. Group the three sheets again.
  13. Select  View  |  Header and footer…  and create a header for all of the new sheets - Name Date; File name - Sheet name; Excel Project 3.
  14. Class disk Save as  trips21.xls  on your Class disk in the excel project3 folder.

Print: Entire Workbook

Pay careful attention to what the Print dialog shows you are about to print. If sheets are grouped, they are all active sheets, which is the default choice.

  1. Dialog: Print - Entire workbookFrom the menu select  File  |  Print  and choose Entire workbook.
  2. Click on the Preview button at the bottom of the Print dialog. Do some sheets need more than one page to print? This rather awkward method lets you see and print all the pages of all the sheets in the workbook. But you can't choose print just some of the pages from here.
  3. Print Print. There are 6 pages since each sheet will fit on one page- Specials, Tahiti, New Zealand, World, Other, Tickets Sold Chart.
  4. After printing has finished successfully, close the workbook.

    Print out of entire workbook