Day 45

Upon completion of Lesson 3, you will be able to:

  • Copy and move data to other cells.
  • Use the drag-and-drop method and Auto Fill options to add data to cells.
  • Insert and delete rows and columns.
  • Freeze panes in a worksheet.

Project 3-1

  1. Open the Store Data File.
  2. Rename the workbook as Store Assets-(your initials).
  3. Insert a column to the left of column B.
  4. Change the width of column A to fit.
  5. Move the contents of the range D3:D16 to the range B3:B16.
  6. Change the width of columns B and C to fit.
  7. Underline the contents of B3:C3.
  8. Close the workbook.

Project 3-2

  1. Open the Imports Data File.
  2. Rename the workbook as Trade Imports-(your initials).
  3. Freeze rows 1 through 6.
  4. Change the orientation of the worksheet to portrait.
  5. In cell A5, enter your name.
  6. Close the workbook.

Project 3-3

  1. Open the Inventory Data File.
  2. Save the workbook as Supply Inventory-(your initials).
  3. Organize the worksheet so inventory items are grouped by supplier, as shown below. Be sure to insert suitable headings and format them appropriately. Some of the data is out of order and needs to be moved.
    • Item Ordering Code Quantity
      Mega Computer Manufacturers    
      Mega X-39 Computers X-39-25879 20
      Mega X-40 Computers X-40-25880 24
      Mega X-41 Computers X-41-25881 28
      Xenon Paper Source    
      Xenon Letter Size White Paper LT-W-45822 70
      Xenon Letter Size Color Paper LT-C-45823 10
      Xenon Legal Size White Paper LG-W-45824 40
      Xenon Legal Size White Paper LG-C-45825 5
      ​MarkMarker Pen Company    
      MarkMarker Blue Ball Point Pens MM-Bl-43677 120
      MarkMarker Black Ball Point Pens MM-Bk-43678 100
      MarkMarker Red Ball Point Pens MM-R-43679 30
  4. The following inventory item has been accidentally excluded from the worksheet. Add the item by using the Fill command and then editing the copied data.
    • Item Ordering Code Quantity
           
      MarkMarker Green Ball Point Pens MM-G-43680 30

       

  5. Delete the following item.
    • Item Ordering Code Quantity
           
      Mega X-39 Computers X-39-25879 20
  6. Change the page orientation to landscape.
  7. Close the workbook

Project 3-4

  1. Open the Time Data File.
  2. Rename the workbook as Time Record-(your initials).
  3. Delete rows 4 and 5.
  4. Enter the following data in the time record below the last entry, December 6.
    • Date From To Admin. Phone Work Description
      9-Dec 8:15 AM 12:00 PM 1.00 2.75 Staff meeting and called clients
      10-Dec 7:45 AM 11:30 AM 2.00 1.75 Paperwork and called clients
      11-Dec 7:45 AM 11:30 AM   3.75 Called clients
      13-Dec 8:00 AM 12:00 PM 2.00 2.00 Mailed flyers and met w/KF

       

  5. Freeze headings above row 8.
  6. Insert a blank row above row 16.  Enter the following information:
    • Date From To Admin Phone Work Description
      12-Dec 7:45 AM 11:30 AM 2.00 1.75 Paperwork and called clients

       

  7. Change the orientation of the worksheet to landscape.
  8. In the range B1:D1, enter your name.
  9. Close the workbook.

Project 3-5

  1. Open the Biology Data File.
  2. Rename the workbook as Biology Grades-(your initials).
  3. Merge and center the range A1:H1. Merge and center the range A2:H2.
  4. Insert a column between the current columns A and B.
  5. In the range B3:B9, enter the following data:
    • Cell       Data

        B3          First Name

        B4          Mike

        B5          Owen

        B6          Cindy

        B7          Raul

        B8          Alice

        B9          Cameron 

  6.  Change the worksheet to landscape orientation.

  7.  Close the workbook.

Project 3-6

  1. Open the Booster Data File.
  2. Rename the workbook as Booster Club-(your initials).
  3. Bold and center the column headings in row 2.
  4. Insert a row above row 3.
  5. Freeze the column headings in row 2.
  6. Insert a row above row 8, and then, in cell A8, enter Bats.
  7. Copy cell E4 to the range E5:E11.
  8. Format the Cost (D4:D11) and Total (E4:E12) columns as currency with two decimal places.
  9. In the Sport and Cost columns, enter the following data, and then widen the columns as needed to display all of the data:
    • Item Sport Cost
      Basketballs Basketball 28
      Hoops Basketball 40
      Backboards Basketball 115
      Softballs Softball 5
      Bats Softball 30
      Masks Softball 35
      Volleyballs Volleyball 25
      Nets Volleyball 125

       

  10. In the Quantity column, enter the following data.
    • Basketballs 5 Bats 5
      Hoops 2 Masks 1
      Backboards 2 Volleyballs 7
      Softballs 20 Nets 1

       

  11. You have $1210 to spend on equipment. Use any remaining cash to purchase as many basketballs as possible. Increase the number of basketballs and watch the dollar amount in the total. You should use $1203.00 and have $7.00 left over.
  12. In cell A16, enter Prepared by: followed by your name.
  13. Close the workbook.

Project 3-7

  1. Open the Pool Data File. The workbook contains attendance data for a neighborhood swimming pool.
  2. Rename the workbook as Pool Attendance-(your initials).
  3. Move data as needed to better organize the worksheet.
  4. Format the worksheet in an appropriate and appealing way.
  5. Close the workbook.

Activity 3-1

  1. As a zoo employee, you have been asked to observe the behavior of a chimpanzee during a three-day period. You need to record the number of minutes the animal displays certain behaviors during the time that the zoo is open to visitors. Set up a worksheet to record the number of minutes that the chimpanzee participates in the following behaviors during each of the three days.
    • Sleeping
    • Eating
    • Walking
    • Sitting
    • Playing

    Format the worksheet to make it attractive and easy to read. Name the workbook with the file name Chimpanzee Behavior-(your initials).