Day 59

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

  • Sort data in a worksheet.
  • Use AutoFilter to display only specified data in a worksheet.
  • Apply conditional formatting to highlight data.
  • Hide worksheet columns and rows.
  • Insert a shape in a worksheet.
  • Insert a picture in a worksheet.
  • Use a template to create a new workbook.
  • Insert a hyperlink in a worksheet.
  • Download a workbook in a different file format.
  • Insert, edit, and delete comments.

Project 6-1

  1. Open the Impact Data File. Rename the workbook as Impact Salaries followed by your initials. The worksheet contains the annual salaries and ratings of Level 10 employees.
  2. Sort the data in the range A6:E20 by the Performance Rating in descending numerical order (largest to smallest).
  3. In cell F5, enter Salary Category as the label.
  4. In cell F6, enter the following formula to indicate the employee’s salary category (low or high) based on his annual salary: =IF(D6<32001, $I$7, $I$8).
  5. Copy the formula in cell F6 to the range F7:F20.
  6. Close the workbook.

Project 6-2

  1. Open the City Data File. Rename the workbook as City Facts followed by your initials.
  2. Select the range A2:G36 and turn on the filter arrows.
  3. Run the following AutoFilters to answer the questions on this form. Remember to restore the records after each filter by clearing the filter.
  4. Close the workbook.

Project 6-3

  1. Open the Paper Data File. Rename the workbook as Paper Sales followed by your initials.
  2. Hide columns B through E to remove the quarterly data from view.
  3. Unhide columns B through E to restore the quarterly data.
  4. Hide rows 7 through 14 to remove the regional data from view.
  5. Unhide rows 7 through 14 to restore the regional data.
  6. Close the workbook.

Project 6-4

  1. Open the School Data File. Rename the workbook as School Bus followed by your initials.
  2. In the Insert menu, click the Image option. The Insert Image dialog box appears.
  3. Insert the School Bus.bmp Data File that has been shared with you.
  4. Drag the picture so that it fits within the range E1:E3.
  5. Close the workbook.

Project 6-5

  1. Open the Compact Data File. Rename the workbook as Compact Cubicle followed by your initials.
  2. In cell C8, insert the following comment: Shut down for two hours for maintenance.
  3. In cell C9, insert the following comment: Production time increased two hours to make up for maintenance on Machine 102.
  4. In cell G9, insert the following comment: Shut down for major repairs.
  5. Insert a cube shape in the upper-left corner of the workbook.
  6. Change the Shape Fill to Orange.
  7. In the Drawing dialogue box, Copy and paste the cube shape, and then drag the copy so it overlaps the lower-right corner of the first cube.
  8. Close the workbook.