Youth Camp

You are the new director of youth activities in Pittsburgh, PA. Your predecessor created an Excel workbook to track participation by location and sport. Last the interim director tried to create a workbook to provide data similar to the existing workbooks, but ran into several issues and has asked for your help to fix their errors.

Validation and Error Checking and Importing

You need to insert a new validation rule and identify any existing errors in the new workbook. You then need to manually enter the data on the Football worksheet.

1. Open exam3_data and save as Exam3_LastFirst (You may get a message about one or more circular references. Click the OK button to continue).

2. Add your name as the author for the workbook.

3. Add your information to the Advanced Properties box

• Participation in Youth Camp
• Youth activities in Pittsburgh, PA
• Your Name

4. After you open it, you realize the data for the Baseball program is missing.

• Import the file exam3_baseball_data.
• The data should be imported between the Basketball and Fee worksheets.
• The data you are importing has headers included in the file.
• While importing, preview the data and make sure each column imports correctly.
• Rename the imported worksheet tab from Sheet1 to Baseball.
• Format the new Baseball sheet to match the Football, Soccer and Basketball worksheets.
• Save your workbook.

5. Click the Football worksheet and create a data validation rule for B3:B6 with the following settings:

• Input Message = Enter number of participants
• Validation Rule = Whole number between 0 and 50
• Alert Type = Warning
• Alert Title = Too Many
• Alert Message = The number of participants should not exceed 50 unless additional counselors are available.

6. Enter the following data:

• B3 = 37
• B4 = 28
• B5 = 41
• B6 = 55

7. Verify that you receive the alert message for B6 and it will allow you to proceed with only a warnin

8. Use the Error Checking feature to identify and correct the circular reference.

Group Worksheets and Perform Edits

You need to group the sport worksheets together to calculate revenue and perform formatting changes that are uniform across all four worksheets.

1. Group the Football, Soccer, Basketball, and Baseball worksheets.

2. On the Football worksheet, enter a formula in C3 that calculates the Revenue by multiplying B3 by cell B3 in the Fees worksheet. Calculate C4:C6 using the same logic.

3. Enter a formula in C7 to calculate the total revenue.

4. Apply bold to A2:C2.

5. Format C3:C7 as Currency with 0 decimal places.

Create Location Totals

You need to calculate the location totals on the Totals worksheet.

1. On the Totals worksheet, enter a reference in B3 to the total number of football participants from the Football worksheet. Complete the formulas in B4:B6 by referencing the appropriate worksheet and cell.

2. Enter a reference in C3 to the total revenue from the Football worksheet. Complete the formulas in C4:C6 by referencing the appropriate worksheet and cell.

3. Enter a formula in B7 to calculate total number of participants and a formula in C7 to calculate total revenue.

Create Hyperlinks

You need to create hyperlinks from the Totals worksheet to the appropriate worksheet data.

1. On the Totals worksheet, add a hyperlink in A3 that opens the Football worksheet.

2. Add hyperlinks for A4:A6 that open the appropriate worksheets.

3. Save the workbook.

Add a Chart

You realize you need a way to visually represent the data on the Totals worksheet, so you need to add a chart.

1. Add a chart that you feel best represents the # of participates with the revenue being generated.

2. You need to include a legend that includes #Participates and revenue

3. The X-axis should list the sports being graphed.

Add a Theme

You need make the Totals sheet more professional and add some personal touches to it.

1. Apply a theme to the worksheet you feel is appropriate

2. Apply either a background image or graphic to the sheet that fits the theme of what your totals represent.

3. Save your workbook.

Save workbook as a PDF

You need to save your totals worksheet as a PDF so it preserves the layout and formatting. You want to ensure its not changed and the information can be published on the Web.

1. Save the worksheet as a PDF named Exam3_LastFirst.

2. Save and close both the files if still open.

m. baseball data - Notepad File Edit Format View Help Pittsburgh Youth Cup Age Participants Reverus 5-7 11 12 13.15 33 Windows In Colt O Type here to search 0 e @ o0a A 1 ENG 704009 em_data - Eoce Comments Arnar P 9-1. E Fle Home Insert Page Layout X Cut Calibri 3 Format Painter BIL R Formulas Data Review IILAA == > A = Vic Developer Hep 3. Wrap Test Merge Center - Power Pivot Gurural $ % Search ! Normal & Conditional Formats calculation Furmaling - Tube AutoSum Bad Check Cell Put the Copy Good o Share 47 O Sort Find Filer Sull- Neutral tory..tput Delete commst dens -cler MIE C D E F G H I J K L M N O P Q R S T U V W X Y Z AA Pittsburgh Youth Camp Fee 1 2 Age 35-7 8-10 S 11-12 13-15 Basketbal Fees + Totals Football Soccer Crutal Reenences Accessiblity imestigate 1 O Type here to search - + 1005 o e QOw 3 A. 1:32 PM ING 10400 m3date - Ence Rurinev File Home E Insert Search Comments - GEO Page Layout Calibri BI . X Cut R Formulas Data Review IIA * === > A = View ; Developer Help 22 Wrap Test Merge Center Tower Pivot Gurwiel $ % Share 0 AutoSum49 4 Normal Calculation Good p lanatory. Neutral pl Check Cell Deinem dens Insert $.$ Conditional Format Uncov Format Panter Clipboard Clear Furmalin - Table File - Select- D E J K L M N O Q R S T U V W X Y Z Pittsburgh Youth Camp 6 13-15 Basketball Fees + Totals Football Soccer Cellula resbymegate Type here to search A 3:32 PM AENG W m3date - Ence Rurinev File Home E Insert Search Comments - GEO Page Layout Calibri BI . X Cut R Formulas Data Review IIA * === > A = View ; Developer Help 22 Wrap Test Merge Center Tower Pivot Gurwiel $ % Share 47 0 > Mito um Normal Calculation Good p lanatory. Neutral pl Check Cell dens Incet Deine formet Conditional Format $.$ Uncov Format Panter Clipboard Clear Furmalin - Table File - Select- DE KL MN OR S T U V W X Y Z Pittsburgh Youth Camp 6 13-15 Basketbal Fees + Totals Cellula Football Soccer restimate te - A 3:32 PM AENG W em_data - Eoce Vic Share Comments Arnar P 9-1. E Fle Home Insert Page Layout X Cut Calibri - 3 Format Panter BIL R Formulas Data Review I A A === > A = Developer Hep Wrap Test Merge Center - Power Pivot General $ % Search Normal & Conditional Formats calculation Furmaling - Tube Un copy Auta um Bad Check Cell Good o Copy Neutral tory..tput 49 Sort Pare Ince Delete commet Find Sulici dens Clear Fitur 87 X =SUMB3:37] C Pittsburgh Youth Camp a Participants Revenue D E F G H I J K L M N O P Q R S T U V W X Y Z 1 2 Age 3 5-7 8-10 S 11-12 13-15 7 Total Fees + Tota's Football Soccer Basketbal Orcular References: 07 Acessibilty nuestgate - Type here to search + 1005 3 A. - 4 :32 PM ING 10400 em_data - Eoce - X Comments Arnar P 9-1. E Fle Home Insert Page Layout X Cut Calibri - 3 Format Panter BI . Clipboard R Formulas Data Review IILAA == > A === Vic Developer Hep 3. Wrap Test Mega Corte Power Pivot General $ % Search Normal Conditional formatas Calculation Furmaling - Tube Autobum Bad Check Cell Good u Neutral put Share 19 O Sort Find Filer Suli- cory ... Inet Delete commst dens Clear X Sport D E F G H I J K L M N O P Q R S T U V W X Y Z C Pittsburgh Youth Camp a Participants Revenue 2 Sport s Basketball o Baseball Fee Totals Football Soccer Basketbal Orcular References Acessibilty nuestgate - Type here to search + 1005 3 A. - 4 :32 PM ING 10400 m. baseball data - Notepad File Edit Format View Help Pittsburgh Youth Cup Age Participants Reverus 5-7 11 12 13.15 33 Windows In Colt O Type here to search 0 e @ o0a A 1 ENG 704009 em_data - Eoce Comments Arnar P 9-1. E Fle Home Insert Page Layout X Cut Calibri 3 Format Painter BIL R Formulas Data Review IILAA == > A = Vic Developer Hep 3. Wrap Test Merge Center - Power Pivot Gurural $ % Search ! Normal & Conditional Formats calculation Furmaling - Tube AutoSum Bad Check Cell Put the Copy Good o Share 47 O Sort Find Filer Sull- Neutral tory..tput Delete commst dens -cler MIE C D E F G H I J K L M N O P Q R S T U V W X Y Z AA Pittsburgh Youth Camp Fee 1 2 Age 35-7 8-10 S 11-12 13-15 Basketbal Fees + Totals Football Soccer Crutal Reenences Accessiblity imestigate 1 O Type here to search - + 1005 o e QOw 3 A. 1:32 PM ING 10400 m3date - Ence Rurinev File Home E Insert Search Comments - GEO Page Layout Calibri BI . X Cut R Formulas Data Review IIA * === > A = View ; Developer Help 22 Wrap Test Merge Center Tower Pivot Gurwiel $ % Share 0 AutoSum49 4 Normal Calculation Good p lanatory. Neutral pl Check Cell Deinem dens Insert $.$ Conditional Format Uncov Format Panter Clipboard Clear Furmalin - Table File - Select- D E J K L M N O Q R S T U V W X Y Z Pittsburgh Youth Camp 6 13-15 Basketball Fees + Totals Football Soccer Cellula resbymegate Type here to search A 3:32 PM AENG W m3date - Ence Rurinev File Home E Insert Search Comments - GEO Page Layout Calibri BI . X Cut R Formulas Data Review IIA * === > A = View ; Developer Help 22 Wrap Test Merge Center Tower Pivot Gurwiel $ % Share 47 0 > Mito um Normal Calculation Good p lanatory. Neutral pl Check Cell dens Incet Deine formet Conditional Format $.$ Uncov Format Panter Clipboard Clear Furmalin - Table File - Select- DE KL MN OR S T U V W X Y Z Pittsburgh Youth Camp 6 13-15 Basketbal Fees + Totals Cellula Football Soccer restimate te - A 3:32 PM AENG W em_data - Eoce Vic Share Comments Arnar P 9-1. E Fle Home Insert Page Layout X Cut Calibri - 3 Format Panter BIL R Formulas Data Review I A A === > A = Developer Hep Wrap Test Merge Center - Power Pivot General $ % Search Normal & Conditional Formats calculation Furmaling - Tube Un copy Auta um Bad Check Cell Good o Copy Neutral tory..tput 49 Sort Pare Ince Delete commet Find Sulici dens Clear Fitur 87 X =SUMB3:37] C Pittsburgh Youth Camp a Participants Revenue D E F G H I J K L M N O P Q R S T U V W X Y Z 1 2 Age 3 5-7 8-10 S 11-12 13-15 7 Total Fees + Tota's Football Soccer Basketbal Orcular References: 07 Acessibilty nuestgate - Type here to search + 1005 3 A. - 4 :32 PM ING 10400 em_data - Eoce - X Comments Arnar P 9-1. E Fle Home Insert Page Layout X Cut Calibri - 3 Format Panter BI . Clipboard R Formulas Data Review IILAA == > A === Vic Developer Hep 3. Wrap Test Mega Corte Power Pivot General $ % Search Normal Conditional formatas Calculation Furmaling - Tube Autobum Bad Check Cell Good u Neutral put Share 19 O Sort Find Filer Suli- cory ... Inet Delete commst dens Clear X Sport D E F G H I J K L M N O P Q R S T U V W X Y Z C Pittsburgh Youth Camp a Participants Revenue 2 Sport s Basketball o Baseball Fee Totals Football Soccer Basketbal Orcular References Acessibilty nuestgate - Type here to search + 1005 3 A. - 4 :32 PM ING 10400