Data Groups & Formulas:
Format Data Groups

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

Data is often sorted into data groups, like the subtotal groups you created in the previous lesson.  How can you make this kind of data easier to read?

  • Separate groups with color or borders or spacing
  • Format summary data (like subtotals and averages) different from groups
  • Do not use too many different formats

Too many different formats can make a sheet harder to read instead of easier. But a careful choice of background, border, alignment, spacing, font style and size, and font color can help the eye follow the flow of data.

WarningColors: Background colors may all look the same if you don't print in color. The darker colors may all look black. The lighter colors may be the same shade of gray. Follow a light background group with a darker background group. If you use a dark fill, change the font color to a light color or your text may vanish into the darkness.


Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & Formulas
    Subtotals To subtopics
    FootprintSort
    FootprintSubtotal
    FootprintFormat Groups
    Images & DrawingsTo subtopics
    Changes & FormulasTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics

Project 5: DesignTo subtopics

Search 
Glossary  
Appendix
Icon Step-by-Step

Step-by-Step: Format Data Groups

  Icon Step-by-Step

What you will learn:

 to format data group background
to merge duplicate data
to move a table
to create a drop shadow

Start with: Class disk trips23.xls - Specials sheets (saved in previous lesson)

You will format the data to emphasize the sorting categories 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.

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.

There are disadvantages to merging duplicate cells. It may make creating charts more difficult. You cannot sort again without splitting the merged cells and replacing the lost data.

Copy Sheet

  1. Select the sheet tab for Specials. Right click on it and select  Move or Copy... 
     
  2. In the Move or Copy dialog, check the box to Create a copy. In the first text box select  trips23.xls  and in the second select from the drop list Tahiti. Click OK.

    A new sheet named Specials (2) appears at the between Agent Totals and Tahiti.
     
  3. 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.

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:F10, which are the records about Tahiti trips.
     
  2. Buttons: Borders = bottom; Background = light yellowApply a Light Yellow fill using Fill Background button on the Formatting bar.
     
  3. Apply a thick bottom border with the Border button on the Formatting bar.
     
  4. Select cells A5:A10, which all contain the word Tahiti.Message: will lose data in merge
     
  5. Select  Format  |  Cells…  and on the Alignment tab choose Merge cells and set Vertical alignment to Top.
     
  6. Click on OK to close the dialog and click on OK on the message about losing cell contents when merging.
     
  7. Tahiti data group formattedMake Tahiti Bold
     
  8. Repeat the procedure for the New Zealand trips, using a different color, Lavender.

    [Select cells A11:F15 and apply a fill color of Lavender and the same bottom border. Select cells A11:A15 and merge with Vertical alignment as Top. Make New Zealand Bold.]
     

  9. Widen column A to show all of the text "New Zealand".
     
  10. Repeat the procedure for World, using another color, Tan.

    [Select cells A16:F17 and apply a fill color of Tan and the same bottom border.
    Select cells A16 and A17 and merge with vertical alignment as Top. Make World Bold.]
     

  11. Specials after data groups formattedRepeat the procedure for Other, using yet another color, Light Turquoise.

    [Select cells A18:F23 and apply a fill color of Light Turquoise and the same bottom border.
    Select cells A18:A23 and merge with vertical alignment as Top. Make Other Bold.]
     

Format Sheet: Move Table

  1. Select the second table, in the range C28:G38.
     
  2. Drag the selection left and down to A35.
     
  3. Check Print Preview. Now the grand totals on row 25 are more noticeable and both tables still fit on one page.
     
  4. Close Print Preview.

Format Title

  1. If necessary, show the Drawing toolbar.
     
  2. To make the titles stand out a bit more, select cells A1 and A2 and on the Drawing bar apply a drop shadow Button: Shadow- down and right.

    The text Anniversary Specials is a bit close to the bottom.
     

  3. Drag the heading for Row 2 down to a height of 30.00.Specials after formatting data groups
     
  4. Select cell A2 and set the vertical alignment to Top. (Format | Cells… | Alignment)
     
  5. Check the Print Preview again. Then close Print Preview.
     
  6. Class disk Save as  trips24.xls .
    Full floppy disk How to handle a full disk