# Groups & Formulas: Exercise Excel 4-2

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

Where you are:
JegsWorks > Lessons > Numbers

Project 2: Excel Basics

Project 5: Design

Search
Glossary

Appendix

## 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.

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. 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.