Groups & Formulas:
Exercise Excel 4-2

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 exercises uses files from Project 3 and an image 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. Save the changed documents to your Class disk in the excel project4 folder. This keeps the original files intact in case you need to start over.
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-2:

Theater Tickets: Delete & Repair with absolute reference
What you will do: insert and size an image
delete redundant data
repair formulas
format data groups

The worksheet on tickets sold needs to be cleaned up. There is a lot of repeated data that can be managed more efficiently.

Start with: Class disk, theater tickets3.xls (from previous exercise), tickets.gif from resource files

  1. Open the workbook theater tickets3.xls in the excel project3 folder of your Class disk.
     
  2. Insert image: Insert the image  tickets.gif from your resource files at the right of the Seating Capacity table, sized so that it is the same height as the table.
     
  3. Class disk Save as  theater tickets4.xls  to your Class disk in the excel project4 folder.
     
  4. Redundant data: Delete column D, price of the ticket. This data is also in the small table of ticket types. The formulas in the Total column will break - #REF!
     
  5. Repair the formulas: Rewrite the formula in E11 to multiply the number sold in D11 by the price per ticket in the Seating Capacity table. Use an absolute reference for the ticket price cell, C6. Similarly, correct the formulas for the next two cells.
     
    Recall that there is a pattern to the tickets, each performance has the same 3 types. Select E11:E13 and copy. Use Paste Special to copy the formulas to the remaining rows of tickets sold. [Skip the subtotals rows!] You should get the same values in the Total column as before.
     
  6. Format groups: Change the formatting of the subtotals for each play and the grand total at the bottom. Background: Blue; Font color: White
     
    Make the grand total row all bold and font size = 14.
     
  7. Header: Change the header to show Excel Project 4.
  8. Print Print.
  9. Class disk Save.

    Theater Tickets with image, repaired formulas, enhanced formatting for groups.