New Perspectives Excel 365/2021 | Module 2: SAM Critical Thinking Project 1c Bergamot Urgent Care Clinic
FORMATTING WORKBOOK TEXT AND DATA
· Excel Projects Help
GETTING STARTED
1. Save the file NP_EX365_2021_CT2c_FirstLastName_1.xlsx as NP_EX365_2021_CT2c_FirstLastName_2.xlsx
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
2. With the file NP_EX365_2021_CT2c_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
a. If cell B6 does not display your name, delete the file and download a new copy.
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 website to submit your completed project.
Final Figure 1: Visits Worksheet
Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved.