New Perspectives Excel $2019|$ Module $6$: SAM Project $1$a

Oval Lake Hospital

MANAGING YOUR DATA WITH DATA TOOLS

$$

$$ GETTING STARTED

$$ Open the file NP$\_$EX$19\_6$a$\_$FirstLastName$\_1.$xlsx$,$ available for download from the SAM website.

$$ Save the file as NP$\_$EX$19\_6$a$\_$FirstLastName$\_2.$xlsx by changing the $1$ to a $2.$

o$$ 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$\_$EX$19\_6$a$\_$FirstLastName$\_2.$xlsx still open, ensure that your first and last name is displayed in cell B$6$ of the Documentation sheet.

o$$ If cell B$6$ does not display your name, delete the file and download a new copy from the SAM website.

$$ PROJECT STEPS

$1.$ Donnie Cruz is a finance assistant for Oval Lake Hospital in south Florida. His current project is to format departmental data for the years $2021-2023$ to make it easier to analyze.

Switch to the Cancer Center worksheet. Unfreeze the top row of the worksheet.

$2.$ Sort the data in the CancerCenter table first in ascending order by the Admission field and then in ascending order by the Service field.

$3.$ Insert a Total Row in the CancerCenter table, and then use the Total row to calculate the total of the values in the $2021,2022,$ and $2023$ fields.

$4.$ The administrator overseeing the cancer center is considering mandating that all inpatient service names be different from outpatient service names, and wants to see which duplicate names are currently in use. Apply a conditional formatting rule to the range C$4$:C$11$ that uses Light Red Fill with Dark Red Text for duplicate values.

$5.$ Switch to the Cardiac Care worksheet. Freeze the first column of the worksheet.

$6.$ Format the range A$3$:F$10$ as an Excel table with headers using the Gold, Table Style Medium $5$ table style. Enter CardiacCare as the name of the table.

$7.$ Donnie notices that the CardiacCare table is missing a record. Add the record shown in Table $1$ to the end of the CardiacCare table.

$$ Table $1$: Data for the Range A$11$:F$11$

$$

$$

A

B

C

D

E

F

$11$

Cardiac Care

Inpatient

Other

$2855$

$3540$

$3752$

$$

$8.$ The Maternity department has more admission criteria than other major divisions of the hospital. Donnie wants to summarize Maternity admissions data using subtotals to show the relationships between admission type and number of patients. Switch to the Maternity worksheet and complete the following steps:

a$.$ Sort the table by the Admission field in ascending order.

b$.$ Convert the table to a normal range.

c$.$ Insert subtotals into the range A$3$:F$13,$ with the subtotals appearing at each change in the Admission column value. The subtotals should use the SUM function and include subtotals for the $2021,2022,$ and $2023$ fields.

$9.$ Switch to the Psychiatric worksheet and remove the duplicate record based on the values in the Admission and Service columns.

$10.$ The data bars in the last three columns make some of the numbers hard to read. Change the Data Bars conditional formatting for the range D$4$:F$12$ to use the Solid Fill Green Data Bar format.

$11.$ Donnie wants to calculate total admissions for the hospital for each year. Switch to the All Departments worksheet, then create the following formulas:

a$.$ In cell D$40$ use the SUM function with a structured reference to total the values in the $2021$ column of the AllDepartments table.$$

b$.$ In cell E$40$ use the SUM function with a structured reference to total the values in the $2022$ column of the AllDepartments table.$$

c$.$ In cell F$40$ use the SUM function with a structured reference to total the values in the $2023$ column of the AllDepartments table.$$

$12.$ Donnie wants to prepare the AllDepartments table for $2024$ data when it becomes available.

Add a table column to the end of the table.