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? Those subtotals can get lost in the mass of rows.
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.
Colors: Background colors may all look the same when you print in black and white
instead of color. The darker colors may all look black.
The lighter colors may be the same shade of gray.
Alternate
light/dark: 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.
![]() |
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:
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.
You will format the new sheet and leave the original data in place.
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! ]
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!
Formatting for all groups used 60% lighter fill with dark text
Most groupings look the same in gray scale.
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.
Formatting for groups alternates between light fill/dark text and dark
fill/light text.
Groupings show well in gray scale.