Introduction to Excel

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

Create an Excel document named hw7.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 using cell references 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 with cell references to fill in column C. The Markup is 35% of the Cost (.35 * Cost).
  • Use a formula with cell references 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 with cell references to find the average for each department (column G).
  • Use formulas with cell references 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 with cell references to compute the Overtime (OT) Rate in column E.
    The Overtime Rate is 1.5 times the Hourly Rate.
  • Add a formula with cell references to find the Total pay 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 with cell references that uses an absolute cell reference to compute the percent score (score / possible) for each test.
  • Using cell references 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
    • Format the numbers in columns C, E, G, and H as percentages with one decimal place.
    • Make each name and each average score bold.

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

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

    Homework 7, 1234, Samantha Spade

    (Replace Samantha Spade with your name and 1234 with the last four 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 instructor within one week of grades being posted. You will be asked to show the assignment in the sent email box with the attachment 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.