Homework answers / question archive / Illustrated Excel 2019 | Module 2: SAM Project 1a Missoula University Hospital Format Worksheets GETTING STARTED Open the file IL_EX19_2a_FirstLastName_1

Illustrated Excel 2019 | Module 2: SAM Project 1a Missoula University Hospital Format Worksheets GETTING STARTED Open the file IL_EX19_2a_FirstLastName_1

MS Excel

Share With

---

Illustrated Excel 2019 | Module 2: SAM Project 1a

Missoula University Hospital

Format Worksheets

GETTING STARTED

• Open the file IL_EX19_2a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_2a_FirstLastName_2.xlsx by changing the “1” to a “2”.
• If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file IL_EX19_2a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
• If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

    PROJECT STEPS

1. Anita Cruz is a trainer for Missoula University Hospital in Missoula, Montana. She is using an Excel workbook to analyze the courses she teaches and to track her progress toward the Train the Trainer certification. She has asked you to format her workbook to make the information clearer and easier to understand.
   
   Go to the Evaluations worksheet. Rename the Evaluations worksheet to Spring 2021 Evaluations, and then change the tab color of the worksheet to Blue-Gray, Text 2 (4th column, 1st row of the Theme Colors palette).
2. Use AutoFit to resize column A to fit its contents.
3. Change the width of column E to 15.00 so that the text "Professionalism" is completely visible in cell E4.
4. Apply the Title cell style to the range A1:E1, and then increase the font size in that range to 20 point to make the worksheet title stand out.
5. Merge and center the range A3:E3, then bold the cell contents to clarify that the text is a title for the range A4:E12.
6. Center the text in the range B4:E4 and change the font color of the range to White, Background 1 (1st column, 1st row of the Theme Colors palette) to make the column headings easier to read.
7. Use the Format Painter to apply the format from the range A7:E7 to the range A9:E9 to create consistent shading and borders in the worksheet.
8. Italicize the contents of the range A11:A12 and change its font to Calibri Light to separate those row headings from the others above them.
9. In the range A11:E12, apply a Bottom Double Border to the cells, and then change the fill color to Blue-Gray, Text 2, Lighter 80% (4th column, 2nd row of the Theme Colors palette).
10. Anita wants to know at a glance in which courses she is receiving excellent evaluations on average.
    
    In the range B12:E12, use Conditional Formatting Highlight Cells Rules to format cells whose contents are greater than 17 with Light Red Fill with Dark Red Text.
11. Apply the Short Date number format to cell C14 to use a more common date format.
12. Apply the Percentage number format to cell C15 to clarify the cell contains a percentage value and then decrease the number of decimal places shown to zero.
13. Anita would like to see the dollar amount of bonuses she received in a rounded format.
    
    Apply the Accounting number format to cell C16 and then decrease the number of decimal places shown to zero. (Hint: Depending on how you complete this action, the number format may appear as Custom instead of Accounting.)
14. Enter the score 15 in cell E10, and then delete row 18 because Anita no longer needs the reminder note in cell A18.
15. Check the Spelling in the workbook to identify and correct any spelling errors.
16. Anita also keeps track of her requirements for the Train the Trainer certificate in this workbook.
    
    Go to the Certificate Requirements worksheet. Find and replace all occurrences of the text "Cr." with:
    
    Credits
17. For the range B3:C8, increase the number of decimal places displayed to one.
18. In the range B6:C6, use Conditional Formatting to create a Data Bars rule, and use the Gradient Fill Blue Data Bar color option so that Anita can clearly see her progress toward certification in all subject areas.
19. Move the Certificate Requirements worksheet after the Spring 2021 Schedule worksheet to use a more logical order. (Hint: The order of the worksheets in the workbook should be as follows: Documentation, Spring 2021 Evaluations, Spring 2021 Schedule, and Certificate Requirements.)

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

 

 

      Final Figure 1: Spring 2021 Evaluations

 

 

 

      Final Figure 2: Certificate Requirements