FINAL PROJECT_EXCEL: DUE TUESDAY APRIL 21, 2009

Directions: Please follow the instructions below. Please work on this over the weekend as we will only have Thursday April 15, 2009, Monday April 20, 2009 and Tuesday, April 21, 2009 to work on the project.

1)Make sure you save in your ID Folder as Final Project Excel_xx.

A. Viewing in Print Preview and Adjusting Page Breaks in Page Break Preview

2)View the worksheet ‘Master List’ in Print Preview to see how it looks.Do you notice how the information is not together? Make sure the last column is included. To do this do the following:

  1. Change the vertical break of worksheet ‘Master List’ so that when you print it later in Section J it will print in only a total of five pages.
  2. View the Worksheet in Page Break Preview Mode and drag the Automatic Page Break that is in between column E and Column F off the Area.

B. Formattingwith Freeze Label and Print Column Headers with Repeat Rows

Skip Part B3- Because of the Protected Sheet

3)Create a Freeze Label for row 2 of worksheet ‘Master List’ so that you can view the Headers while working on other pages.

  1. To Freeze row 2 go to cell A3. You can select cell A3 by clicking in the cell or entering the position A3 in the namebox.
  2. After you are in the cell go to View tab, Windows Group and select freeze panes button, freeze top row.

4)Create a Repeat Rows so that row 2 will print on all of the five pages for the worksheet ‘Master List’. Also have all the gridlines print out.

  1. To Repeat Rows go to the Page Setup Dialog Box and in the Sheet Tab, then in the print titles group, rows to repeat at top option enter $2:$2
  2. To print the gridlines stay in the Sheet Tab, and in the Print group select the checkbox for gridlines.

C. Creating Page Headers for All Worksheets in Page Layout View or Page Setup Dialog Box

5)Make sure to create a Header for this worksheet, ‘Master List’. In the Header include your name, the name of the file Final Project_xx, and the date.

  1. You will also need to create the same Headers for the other two worksheets created in this workbook. Once the Custom Header is created just select it for the other two worksheets, ‘Exercise Analysis’ and ‘Personal Results’.

D. Using Averageifs/Countifs Function with 3-D Formulas

6)In the ‘Master List’ worksheet use the drop down arrow from the filter for the Exercise/Practice Column and view the exercises in alphabetical order. Write the exercises in that order into the other worksheet, ‘Exercise Analysis’ on column A, starting on cell A3.

7)In the Worksheet, ‘Exercise Analysis’ use Averageifs Functions with 3-D Formulas to find the average typing speed and the average number of errors for each of the exercise/practice in the ‘Master List.’ Enter the formulas into column B and column C in ‘Exercise Analysis’.

8)Answer the following questions:

  1. What exercise had the highest average? Highest number of errors?
  2. What exercise had the lowest average? Lowest number of errors?

9)In the Worksheet, ‘Exercise Analysis’ use a Countifs Functions with 3-D Formulas to find the count for each exercise/practice in the ‘Master List.’ Enter the formula into column D in ‘Exercise Analysis’. How can this data be useful?

E. Using Filters, Creating New Worksheets, Drag and Drop Copying and Paste Special-Paste Link

10)In the ‘Master List’ worksheet the headers on the second row have the data inFilters. Search for your ID number in the ID Number column and deselect the checkbox Select All and select your id number.

11)Use your filtered Typing Pal results information that you filtered in the ‘Master List’ worksheet and copy.

12)Using Paste Special then Paste link the copied cells to the worksheet, ‘Personal Results’ starting on cell A2. Make sure that what you pasted only your personal results, NOT all of the results.

13)Rename the worksheet from ‘Personal Results’ to ‘Your Name Results’ for example-‘Luis’s Results’.

F. Using Nested If/Functions to create another Column with Text Labels

14)Go to the worksheetfor your ‘Personal Results’. Create a new column to the right of the column Number of Errors and type the header Typing Speed Level on cell G2. Do the same for the last column in the worksheet ‘Master List’and type the header Typing Speed Level on cell G2.

15)For both ‘Master List’ and ‘Personal Result’, create a Nested If/Function using the levels in the table below to make a text label for each row of your results. z

16)Drag the formula down using the Fill Handle for the rest of the results. The levels are below. (Consider using Absolute Reference Markers like $C:$2)

Speed / Typing Speed Level
Greater than or equal to 50 / Super Fast
Greater than or equal to 40 / Very Fast
Greater than or equal to 30 / Fast
Greater than or equal to 20 / Medium
Greater than or equal to 10 / Medium to Slow
Greater than or equal to 1 / Slow

G. Using CountIf function and Create a Tables/Charts from your Personal Results and Overall Results

17)Create a table that counts how many Super Fast, Very Fast, Fast, Medium, Medium to Slow, and Slow Level Results you had for your personal results. Enter your results in worksheet ‘Personal Results’ from cells J6 to J11

18)Do the same for the Master List and enter the results in worksheet ‘Personal Result’ from cells M6 to M11.

19)Create two pie charts. One for your Personal Results and one for the Master List Results. Remember you need to use the text labels column and the value results column.

H. Creating/Formatting Charts for 1)Overall and 2)Personal- Typing Speed and Number of Errors

20)Create two Line Chartsone for all the Overall Typing Speed Results and one for your Personal Typing Speed Results. Show the Charts as their own sheets. Make sure that eachchart has a title, and axis titles.

21)Create two Line Charts for the Overall Number of Errors and one for your Personal Number of Errors then show the Charts as their own sheets. Make sure that each chart has a title, and axis titles.

I. Formatting Cells using Borders and Fill Color

22)Before printing every worksheet you need to create a border that goes around every block of data in outline form.

  1. If the data extends more than one page the border should end at the end of the page then have a new border at the start of the new page.
  2. For example for the ‘Master List’ worksheet you need to create a border that goes around cells for each page so for cells a2:f55, a56:f169 and so on and so on.

23)Before printing every worksheet you also need to Fill the cells that have formulas with a background color of yellow.

  1. So for Example in worksheet ‘Master List’ this means the cells with values in column G.
  2. In worksheet ‘Exercise Analysis’ this is the cells with values in columns B, C, and D.
  3. In worksheet ‘Personal Results’ this is the cells with values in columns A to G, J and M.

J. Printing Charts and Cell Table Results Using Multiple Print Areas

24)Print the ‘Master List’ worksheet in Portrait format. It should be only 5 pages. Make sure it has repeating rows and a custom page header.

25)Print the ‘Exercise Analysis’ worksheet in Landscape format.

  1. Make sure it has a custom header. Only print the area with values.
  2. Adjust the scale to page in the Page Setup Dialog Box to make the information appear as large as possible on most of the page.

26)Print the ‘Personal Report’ worksheet in Landscape format.

  1. Make sure it has a custom header. Only print the areas with values
  2. Use Print Areas and separate into two areas.
  3. Adjust the scale to page in the Page Setup Dialog Box to make the information appear as large as possible on most of the page.
  4. It should be a total of two pages.

27)Print the Two Line Charts- Overall Typing Speed Results and Personal Typing Speed Results.

28)Print the Two Line Charts- Overall Number of Errors and Personal Number of Errors.

29)Print the Two Pie Charts- Overall Typing Speed Level and Personal Typing Speed Level.

30)Make sure you save your work as Final Project Excel_xx and turn in printed pages to the Teacher.

31)Extra Credit: Protect the worksheets ‘Personal Results’ and ‘Exercise Analysis’ so that the data entered for your project into each worksheet cannot be changed but all other columns and rows can be used.