Homework answers / question archive / New Perspectives Excel 2019 | Module 2: SAM Critical Thinking Project 1c Bergamot Urgent Care Clinic FORMATTING WORKBOOK TEXT AND DATA GETTING STARTED Open the file NP_EX19_CT2c_FirstLastName_1

New Perspectives Excel 2019 | Module 2: SAM Critical Thinking Project 1c Bergamot Urgent Care Clinic FORMATTING WORKBOOK TEXT AND DATA GETTING STARTED Open the file NP_EX19_CT2c_FirstLastName_1

MS Excel

Share With

New Perspectives Excel 2019 | Module 2: SAM Critical Thinking Project 1c

Bergamot Urgent Care Clinic

FORMATTING WORKBOOK TEXT AND DATA

GETTING STARTED

• Open the file NP_EX19_CT2c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_CT2c_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 NP_EX19_CT2c_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. Jennifer Williams is the business manager at the Bergamot Urgent Care Clinic. Jennifer is adding formatting to the workbook she uses to track statistics on patient visits to make it easier to read and use.
   Change the theme of the workbook to Office.
2. Switch to the Visits worksheet. The headings on the Visits worksheet look pretty bland and could use some formatting. Merge and center the top row through column H.
3. Apply the Heading 1 cell style to the newly merged range.
4. Merge and center the second row in the same way.
5. Next, Jennifer needs to format the report dates. Change the background color of the dates and date labels to match the Week start cell.
6. Change the font style of the date labels to bold to distinguish them from the dates.
7. Change both date formats to show a two-digit month, a two-digit day, and a two-digit year.
8. She also wants to format the table headings. Change the font style to italic, and center the headers horizontally.
9. Merge and center the Service header cell with the cell beneath it, and center it vertically. Do the same to format the Total Visits header.
10. Merge and center the Payment header cell with the two cells to its right. Do the same to format the Percent of total visits header.
11. Change the background color of the Payment, Total Visits, and Percent of total visits headers to match the Service header.
12. Change the background color of the Insurance, Cash, and Credit headers to match the Insurance header under the Payment side.
13. Jennifer would like to spruce up the table data. Change the background color of the Lab test and Diagnostic imaging rows to match the color of the Lab test cell.
14. Change the number format for the Percent of total visits data to show percentages with zero decimal places.
15. The clinic is looking to evaluate where its revenue comes from. Use conditional formatting to identify cash visits under 6 percent with dark red text on a light red background.
16. The text to the left of the chart has been rotated oddly. Rotate the text so that it is horizontal, and then change the indent level to 2.
17. Find and replace all instances of the text "Ins" with Insurance, not including any punctuation.
18. Finally, she needs to format the workbook for printing. Change the print layout to start a new page at row 14.
19. Set the margins to Narrow.
20. Set rows 1 and 2 as print titles.
21. Jennifer wants to create a custom footer for the worksheet. Show the current page in the left footer section, and the sheet name in the center footer section. Be sure that the footer will update itself for each page and sheet name.

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: Visits Worksheet