Jan's Working with Numbers

Step-by-Step: Format Data Groups

---

What you will learn:

to format data group - background, border, merge duplicates to move a table to print in black and white to check formatting to revise colors based on how they print

---

Start with: trips22-Lastname-Firstname.xlsx - Specials sheets (saved in previous lesson)

You will format the data to emphasize the sorting categories you created on the Specials sheet and to spotlight the grand totals. You may need the original data later, with no formatting or cute layout tricks. You will work with a copy of the sheet. This data does not have the Subtotals. That's on a different sheet.

To visually separate the groups of trips, you will apply a fill color to the whole group, and a border below the last row in the group. Since the name of the trip is repeated for each record, you can merge those cells and show the trip name just once for the whole group. To make the grand totals more visible, you will move the second table to the left and down.

Disadvantages to merging duplicate cells:

• Creating charts is more difficult.
• To sort again you must split the merged cells and replace the data in the new cells.

---

Copy Sheet

1. Open trips22-Lastname-Firstname.xlsx to the  Agent Totals sheet.  
2. Save As trips23-Lastname-Firstname.xlsx in the excel project4 folder of your Class disk.  
3. Right click on the sheet tab for Specials and select  Move or Copy... 
4. In the Move or Copy dialog, check the box to Create a copy.

5. In the first text box select  trips23-Lastname-Firstname.xlsx  (with your own first name and last name, of course) and in the second select from the drop list Tahiti-linked. Click OK.

   A new sheet named Specials (2) appears at the between Agent Totals and Tahiti.

6. Rename this sheet  Formatted Groups .
   Having your data in its original state can save a lot of time and aggravation if you decide that your groupings were not such a good idea. It's another safety net.
   
7. Save.
   [trips23-Lastname-Firstname.xlsx]

---

Format Group: Fill, Border, Merge Duplicates

You will format the new sheet and leave the original data in place.

1. On the sheet Formatted Groups, select cells A5:A10, all of which contain the word Tahiti.
2. On the Home tab click on the button Merge & Center.
3. Click on OK on the message about losing cell contents when merging.
4.  Click alignment buttons Top Align and Align Text Left.
    
5. Make Tahiti Bold .
6. Select cells A5:F10, which are the records about Tahiti trips.
7. Apply a fill Accent 1, Lighter 60% or 80%.
   
8. Apply a thick bottom border with the Border button on the Formatting bar.
   

9. Repeat the procedure for the New Zealand trips, using a different color at the same Lighter %.

   The theme colors are somewhat different in Excel 2013 and 2016

   Your color choices may be different.

   [Select cells A11:F15. Apply a fill color and the same bottom border. Select cells A11:A15 and merge. Set alignment to Left with Vertical alignment as Top. Make 'New Zealand' Bold. If necessary, widen column A to show all of the "New Zealand". Bold text takes more space! ]

10. Repeat the procedure for the World trips, using another color at the same Lighter %.
11. Repeat the procedure for Other trips, using yet another color at the same Lighter %.
12. Save.
    [trips23-Lastname-Firstname.xlsx]
    

---

Format Sheet: Move Table

1. Select the second table, in the range C28:G38.
2. Drag the selection left to A28.
   The column width is too narrow for the values in the Total column.
3. AutoFit column D.
4. Save.
   [trips23-Lastname-Firstname.xlsx]
   

---

Preview and Print

Printing in gray scale costs less than printing in color. What will your pretty colors look like in shades of gray?

Design Tip: Think about how colors print in gray scale when picking out your colors!

1. Check Print Preview.
   Now the grand totals on row 25 are more noticeable and both tables still fit on one page.
2. If you have a color printer, open Printer Properties from the Print dialog or the Print pane and change the color options to print in Gray Scale.
   (Exactly how to do this varies because the dialog vary depending on the exact model of printer.)
   The preview does not change to show what your document will look like without colors.
3. Print in gray scale, if it is available, or black and white.
   

   
   Formatting for all groups used 60% lighter fill with dark text
   Most groupings look the same in gray scale.

   In this formatting plan the groups had different colors but the same Lighter %. Did the groups stand out from each other? Apparently using the same percentage for all of the groups was not a good plan!
4. Close Print Preview.
   

---

Use Alternating Formatting

By alternating the formatting between groups, you can guarantee that printing in gray scale will still show the groupings clearly.

Design Tip: For best results in gray scale printing, use a lighter color fill with a dark font color alternating with a darker color fill with a light font color.

1. Change the fill color for the New Zealand group of cells to use Darker 25% and change the font color to White.
2. Change the Other group of cells similarly.
3. Save.
   [trips23-Lastname-Firstname.xlsx]
   
4. Preview.
   The groupings are much more noticeable in gray scale.
5. Print.
6. Open Printer Properties again and change back to Color, if you wish.
     

   
   Formatting for groups alternates between light fill/dark text and dark fill/light text.
   Groupings show well in gray scale.