Groups & Formulas:
Exercise Excel 4-3

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

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.
Full floppy disk How to handle a full disk

 


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
    SubtotalsTo subtopics
    Images & DrawingsTo subtopics
    Changes & FormulasTo subtopics
    Summary
    Quiz
    Exercises To subtopics
    Exercise Ex. 1 Amazon Pings
    Exercise Ex. 2 Theater Tickets
    Exercise Ex. 3 Computers Today
    Exercise Ex 4 On Your Own

Project 5: DesignTo subtopics

Search 
Glossary  
Appendix

Exercise Excel 4-3:

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: Class disk,  subscribers.xls & 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: computer.wmf)
     
  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. Class diskSave as  subscribers4.xls  to the excel project4 folder of your Class disk.
     
  12. Print Print.

Preview: Exercise Excel 4-3 - Computers Today subscribers