Introduction to Excel

Please click on the 'Lab Manual' for detailed instructions.

If you have not done so already, create a folder for this class on your flash drive. You can find instructions in 'Week 1's Assignment.

Create an Excel document named hw6.xlsx.

Exercise 1

  • Name the first sheet of the workbook 'Exercise 1'.
  • Enter the following data in red into the 'Exercise 1' worksheet:
  •       A       B        C        D       E
        1 Expenses	 	 	 	 
        3         Sept.    Oct.     Nov.    Dec.
        4 Rent    500.00   500.00   500.00  500.00
        5 Food    257.20   238.30   158.75  157.77
        6 Clothes 72.29    45.88    68.33   187.21
        7 Total

  • Add a formula in Row 7 to find the total of each column.
  • Include your name on this worksheet in bold in any cell.

  • Exercise 2

  • In the same workbook, name a second worksheet 'Exercise 2' and enter this table:
  •       A         B        C       D
        1 Price List	 	 
        3 Item #    Cost     Markup  Price
        4 A35-334   52.37	 	 
        5 A35-349   59.87	 	 
        6 A37-741   160.96	 	 
        7 A40-662   59.46	 	 
        8 A46-173   97.20

  • Use a formula to fill in column C. The Markup is 35% of the Cost (.35 * Cost).
  • Use a formula to fill in column D. The Price is the Cost plus the Markup.
  • Include the date in bold in any cell for this worksheet.

  • Exercise 3

  • Using the same workbook add a third worksheet named 'Exercise 3' and enter this table:
  •         A          B      C      D      E      F      G
        1   NSM&E Enrollment
        3   Dept.      1999   2000   2001   2002   2003   Avg. 
        4   Math       140    150    160    170    180 
        5   Biology     20     30     40     50     60
        6   Chemistry   55     66     77     88     99
        8   Total

  • Use formulas to find the average for each department (column G).
    The formula for the average should add up the numbers in all five columns
    then divide the total by five. Use parentheses around the enrollment
    numbers so that they are added before the division.
  • Use formulas to find the total for each year (row 8).
    When adding the columns, be sure you do not include the year.
  • Add your major in bold in any cell in this worksheet.

  • Exercise 4

  • Using the same workbook, add a new worksheet by clicking the 'Insert Worksheet' button at the bottom of the window. Name it 'Exercise 4' and enter this table:
  •       A           B       C              D          E         F      
        1 Payroll
        3             Hours   Hourly Rate    OT Hours   OT Rate   Total
        4 Albee          40            14        1.75   
        5 Mamet       38.75         13.47        3.00
        6 Shepard      35.5            12        5.25
        7 Williams       39         15.65         2.5

  • Add a formula to compute the Overtime (OT) Rate in column E.
    The Overtime Rate is 1.5 times the regular Rate.
  • Add a formula to find the Total in column F.
    The Total pay is (Hours * Hourly Rate) + (OT Hours * OT Rate).
  • Make the worksheet neat by doing this:
    • Make the first column wide enough for the longest name.
    • Make the widest column (C) wide enough to fit the data.
    • Make all other columns the same width as column C.
    • Format all the numbers with 2 decimal places.
    • Right align the headings for all the columns with numbers.
    • Center the title across the entire table and make it bold.

  • Insert this new row between rows 5 and 6:
  •       A             B      C       D      E        F      
        6 Miller        33     15.6    0 

  • Copy the formulas for columns E and F.

  • Exercise 5

  • Using the same workbook add a new worksheet and title it 'Exercise 5'. Enter this information.
  •        A         B      C       D      E        F       G        H
        1  Test Scores	 	 	 	 	 	 
        3            Test1          Test2           Test3            Average 
        4  Possible  73             59              66	 	 
        5            Score  Percent Score  Percent  Score   Percent	 
        6  Mi, J     72             59              63	 	 
        7  Ray, J    70             46              52	 	 
        8  Doe, J    68             55              57	 	 
        9  Deer, J   62             57              60	 	 
        10 Buck, J   65             54              58	 	 

  • Use a formula that incorporates an absolute cell reference (for the 'Possible' points) to compute the percent score (score / possible) for each test.
  • Find the average of all the test percentages using this formula:
  •     (test1 + test2 + test3) / 3

  • Make the worksheet neat by doing this:
    • Make column A wide enough to hold all the names.
    • Center the title across all the columns (A through H). Make it bold and enlarge the font.
    • Center the column headings in row 3 and the possible scores in row 4 across the two columns (B and C, D and E, F and G) that contain information about that test.
    • Format the numbers in columns C, E, G, and H as percentages with one decimal place.
    • Make each name and each average score bold.
    • Change the background color for rows 6, 8, and 10.

    Save the Excel workbook as 'hw6.xlsx' in the 'Excel Workbook' format (it should be default). Send hw6.xlsx as an email attachment to your TA.

    In the subject line for your email, include the assignment number, your name and the last 3 digits of your RunnerID in this form:

    Homework 6, 123, Samantha Spade

    (Replace Samantha Spade with your name and 123 with the last three digits of your student ID). The subject line must be exactly in this format (including capital letters and commas) in order for my grading script to download your assignment correctly, so please follow these instructions carefully.

    Errors in your homework grade must be worked out with your TA within one week of grades being posted. You will be asked to show the assignment in the sent email box with the attachment to the TA as proof that you submitted your work on time.

    Remove your USB drive and take the drive with you when you leave. This should be the last action when you leave the lab every time. The lab computers are wiped clean every night and all data stored on them is deleted. USB drives left plugged into the computer usually find a new owner.