# Groups & Formulas: Exercise Excel 4-3

Did you want Working with Numbers: 2007,2010,2013,2016  or español

You need to use what you just learned, and maybe learn a little more. Complete all parts of the following exercises. Don't forget to backup your Class disk when you have completed the exercises or whenever you stop for the day and saved a document along the way.

This exercise uses files from the numbers resource files. The default location for these files is c:\My Documents\complit101\numbers\  Save the changed document to your Class disk in the excel project4 folder. This keeps the original files intact in case you need to start over or another student will be using this same computer.
How to handle a full disk

Where you are:
JegsWorks > Lessons > Numbers

Project 2: Excel Basics

Project 5: Design

Search
Glossary

Appendix

## Computers Today: Subtotals

 What you will do: sort create subtotals with Subtotals create subtotals by hand format groups with styles insert image

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.

Start with:  & computer.wmf in the resource files,

1. Open subscribers.xls from the numbers folder in the resource files or download now. The default location is c:\My Documents\complit101\numbers\

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

3. 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: )

4. Sort: Sort the table data on Country, City,  and Last Name.

5. Count Subtotals: Use the Subtotal command to create subtotals for each country using the function Count.

6. Styles: Create a style named  Subtotals: Arial, Bold, fill color Violet, Font color Light Turquoise. Apply it to all the country subtotals.

Create a style named Grand for the Grand Count: Arial, Bold, fill color Violet, font color Light Turquoise, Italic, font size 14

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

8. Format Sums: Use the Subtotal and Title styles to format the totals you just created.

9. Format columns: Center the data in the Date and # of Years columns. Resize columns if necessary to show your labels for the new totals.

10. Headers: Setup the sheet header - name & date on the left, workbook and sheet in the middle, Exercise Excel 4-3 on the right.

11. Save as  subscribers4.xls  to the excel project4 folder of your Class disk.

12. Print.