Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101
Home > Jan's CompLit 101 > Working with Numbers > Design > Ex. Excel 5-1
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Numbers

    Design: Exercise Excel 5-1

Exercise Excel 5-1You will apply conditional formatting to your previous sheet about The City Theater ticket sales. You will add and format a comment.

 

 

 

This exercise uses a file that you created in Project 4. Save the changed document to your Class disk in the excel project5 folder. This keeps the original file intact in case you need to start over.

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: Icon: Class disk, ex4-2-theatertickets-Lastname-Firstname.xlsx (created in previous exercise)

The theater management wants to know 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. Icon: Class diskOpen ex4-2-theatertickets-Lastname-Firstname.xlsx  from the folder excel project4 on your Class disk.
  2. Icon: Class diskSave as  ex5-1-theatertickets-Lastname-Firstname.xlsx  in the folder excel project5 on your Class disk.
  3. Clear unneeded cells: On Sheet1 clear the Price cells and heading from the Seating Capacity table and the Total column from the data table.
  4. Fix formatting: Headings are now wider than the table.
    Center the title Seating Capacity across the table columns.
    Remove unnecessary backgrounds from empty cells.
    Move tickets image under main title.
    Repair borders if necessary.
  5. Set conditional formats: Select the cells in column D for type A tickets - D11, D14,  D18, D21, D24, D28, D31, D34.
    Set the conditional formatting to Highlight cells where the Cell value is equal to $B$6, the number of type A tickets available.
    Set the Format to use Yellow fill with dark yellow text.
    Similarly select the cells in column D for type B tickets and set the condition to Cell value is equal to $B$7 and use the same format.
    Similarly select the cells in column D for type M tickets and set the condition to Cell value is equal to $B$8 and use the same format.
  6. Comment: In cell D11, insert a comment explaining your conditional formatting.
    Have comments displayed on the sheet all the time.
    Format your comment's text box with a new color for fill and a new color for the font.
    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.
  7. Prepare to print: Change the header to Exercise Excel 5-1.
    Set the page to print the comment as displayed.
    Check Print Preview.
  8. Icon: Class diskSave.
    [ex5-1-theatertickets-Lastname-Firstname.xlsx]
  9. Print Print only Sheet1.

Theater Tickets Sold- conditional formatting applied