Jan's Working with Numbers

Step-by-Step: Group

---

What you will learn:

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

---

Start with: trips19-Lastname-Firstname.xlsx (saved in previous lesson)

---

Group Sheets

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

You 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 grouped sheets at once, though you can link individual cells on all sheets at once. Another little quirk.

1. Save As trips20-Lastname-Firstname.xlsx in the excel project3 folder of your Class disk.
   
2. Click on the tab for sheet New Zealand and hold the SHIFT key down and click 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 or Tahiti-linked since those are already done.)

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

2. Copy.

   Whoops. The sheets that you grouped were automatically ungrouped when you clicked in a sheet that was not part of the group.
   

3. Group the sheets New Zealand, World, and Other again.
4. Switch to the sheet New Zealand and select cell A1.
5. 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!
   
6. Ungroup the sheets by right clicking on one of the tabs and choosing  Ungroup Sheets  from the context menu. 
   The sheets are no longer grouped and the one you right clicked on is the current sheet.

   Another 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 A11:F15 and Paste Link to New Zealand sheet at A5. Then Paste Formatting .
   Copy A16:F17 and Paste Link to World sheet at A5. Then Paste Formatting .
   Copy A18:F23 and Paste Link to Other sheet at A5. Then Paste Formatting .
   

8. Copy the Totals cells on the sheet Specials, which is range A25:F25.
   

   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.

9. Paste on each sheet below the last trip entry.
   (You can copy once and then paste to each sheet in turn if you don't do anything else in between.)
   The totals will show #REF!

   The Appendix has list of all the Error Values

10. Paste the same Totals row again Column Widths.
    
11. If necessary, remove the unneeded zeros.
12. Correct the totals formulas on each sheet separately by selecting the two cells where the sums should go and clicking AutoSum.
13. Group the three sheets again.
14. AutoFit all the columns and, if necessary, Row 4. 
    All three sheets are changed at once but the actual column widths can vary. For example, the New Zealand sheet uses more space for column C than the other two sheets because of 'Midland Associates'. What other width differences do you see?

    

     
15. Switch to Page Layout View and create a header for all of the new sheets while they are still grouped -
    Name Date; File name - Sheet name; Excel Project 3.
16. Right click on a sheet tab and select Ungroup sheets!
    NEVER leave sheets grouped after you have finished working with the group. The next time you open the workbook those sheets will still be grouped... but you probably won't notice. Major opportunity for disaster!!
17. Save.
    [trips20-Lastname-Firstname.xlsx]
    

---

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. The default choice for printing is to print All active sheet.

1. To print the entire workbook:
   Excel 2007: Office button > Print > Print > Entire workbook.
   Excel 2010, 2013, 2016: File tab > Print > change Print Active Sheets to Print Entire Workbook
   

2. Look at the Print Preview.
   There are 7 pages since each sheet will print on one page- Specials, Tahiti-linked (in Landscape orientation), Tahiti, New Zealand, World, Other, Tickets Sold Chart.

   Do all sheets have a header? If not, create the missing headers.

   Do some sheets need more than one page to print? Check column widths. If the extra pages are blank, you can change which pages to print in addition to picking which sheets to print.

   Excel 2007: Preview matches choices of what to print.
   Clicking the Preview button in the Print dialog opens a preview that reflects all of your choices in the Print dialog.

   Excel 2010, 2013, 2016: Preview may NOT match choices of what to print.
   Changing which pages to print does NOT change the preview. Picking Print Active Sheets, Print Entire Workbook, or Print Selection DOES change the preview.

3. Print... unless your instructor tells you differently.
4. Save and close the workbook.
   [trips20-Lastname-Firstname.xlsx