Design:
Exercise Excel 5-1

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 Project 4. Save the changed documents to your Class disk in the excel project5 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 & FormulasTo subtopics

Project 5: Design
    Analysis To subtopics
    What If...To subtopics
    Logical TestsTo subtopics
    Sharing DataTo subtopics
    Summary
    Quiz
    Exercises To subtopics
    Exercise Ex. 1 Theater Tickets
    Exercise Ex. 2 Soccer - IF
    Exercise Ex. 3 Soccer - What If
    Exercise Ex. 4 Computers Today
    Exercise Ex. 5 Ticket subtotals
    Exercise Ex. 6 On Your Own

Search 
Glossary  
Appendix

Exercise Excel 5-1:

Theater Tickets - Conditional Formatting
What you will do: delete columns
repair formatting
apply conditional formatting
create a comment
display comment on sheet

Start with: Class disk, theater tickets4.xls (created in previous exercise)

The theater management wants to see for which performances all the available tickets of a type were sold. You will create a conditional format for the # sold column to show when this happened.

  1. Class diskOpen theater tickets4.xls  from the folder excel project4 on your Class disk.
     
  2. Class diskSave as  theater tickets5.xls  in the folder excel project5 on your Class disk.
     
  3. Delete unneeded cells: On Sheet1 delete the Price and Total data cells and headings.
     
  4. Fix formatting: Headings are now wider than the table. Center the titles across the table columns. Remove unnecessary backgrounds. Move tickets image under main title. Repair borders.
     
  5. Set conditional formats: For D11 set the condition to Cell value is equal to $B$6, the number of type A tickets available. Set the Format to use Pattern = Light Yellow.

    Similarly for cell D12 set the condition to Cell value is equal to $B$7 and use Light Yellow fill. For cell D13 set the condition to Cell value is equal to $B$8 and use Light Yellow fill.
     
  6. Copy formats: Copy cells D11, D12, and D13. Select the other cells that show the number of tickets sold. Since the tickets are in groups of three, you can use Edit | Paste Special | Formats. [Do not include the totals rows in you selection. When you paste, remember that not all cells will change to light yellow. Only those that meet the condition.]
     
  7. Comment: Insert a comment explaining your conditional formatting in cell D11. Have comments displayed on the sheet. [Tools | Options | View]. Format your comment's text box with the same fill as the conditional format. Position the comment so that it is clear of the data cells. You may format the comment's text box in other ways, if you like.
     
  8. Prepare to print: Change the header to use Exercise Excel 5-1. Set the page to print the comment as displayed. Check Print Preview.
     
  9. Class diskSave. [theatertickets5.xls]
     
  10. Print Print only Sheet1.

 Theater Tickets Sold- conditional formatting applied