# Design: Exercise Excel 5-1

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

Where you are:
JegsWorks > Lessons > Numbers

Project 2: Excel Basics

Project 4: Groups & Formulas

Search
Glossary

Appendix

## Theater Tickets - Conditional Formatting

 What you will do: delete columns repair formatting apply conditional formatting create a comment display comment on sheet

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. Open theater tickets4.xls  from the folder excel project4 on your Class disk.

2. Save 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. Save. [theatertickets5.xls]

10. Print only Sheet1.