Exercise Excel 4-3:
Computers Today: Subtotals
|What you will do:
create subtotals with Subtotals
create subtotals by hand
format groups with styles
The newsletter Computers Today ran special advertising in June. They
want to look at the new subscriptions that came in that month to see if
their ads had an effect. You will create a spreadsheet that will subtotal
the new subscriptions by country.
computer.wmf in the
- Open subscribers.xls from the numbers folder in the
resource files or
now. The default location is c:\My Documents\complit101\numbers\
- Title style and labels: Center and merge each title line
across the table columns. Create a style named Title with Arial,
font size 14, fill color Violet, font color Light Turquoise, Bold. Apply
this style to the title, and subtitles.
Make the column labels bold.
- Image: At the top left and right of the titles insert the
image of a computer from the resource files. (You can download just this
file, if necessary:
- Sort: Sort the table data on Country, City, and Last
- Count Subtotals: Use the Subtotal command to create subtotals
for each country using the function Count.
- Styles: Create a style named Subtotals: Arial,
Bold, fill color Violet, Font color Light Turquoise. Apply it to all the
Create a style named Grand for the Grand Count: Arial, Bold, fill
color Violet, font color Light Turquoise, Italic, font size 14
- Sum Subtotals: In the # of Years column, create a sum for the
# of Years for each country and a Grand Total at the bottom. You can not
use the Subtotal command to do this because it will remove the Count
subtotals. You must create these sums yourself.
- Format Sums: Use the Subtotal and Title styles to format the
totals you just created.
- Format columns: Center the data in the Date and # of Years
columns. Resize columns if necessary to show your labels for the new
- Headers: Setup the sheet header - name & date on the left,
workbook and sheet in the middle, Exercise Excel 4-3 on the right.
as subscribers4.xls to the
excel project4 folder of your Class disk.