Illustrated Excel 2019 | Module 5: SAM Project 1a Georgia Medical Center

GETTING STARTED

1. Open the file IL_EX19_5a_FirstLastName_1.xlsx, available for download from the SAM website.

2. Save the file as IL_EX19_5a_FirstLastName_2.xlsx by changing the “1” to a “2”.

a. 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.

3. With the file IL_EX19_5a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

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

PROJECT STEPS

1. Justin Douglas is the director of professional development at the Georgia Medical Center. In addition to training courses offered to employees, the Georgia Medical Center also hosts continuing education seminars for medical professionals around the country. Justin has been tracking both types of courses in an Excel workbook and asks for your help in managing and analyzing the data. Go to the Training worksheet. Justin wants to sort and filter the data on the worksheet, which lists the employees and the training courses they have requested. Format the range A3:G43 as a table with headers using the Lime, Table Style Light 9 table style.

2. The training courses are offered in two sessions. Justin wants to make sure that employees who enter training course data enter only "1" or "2" in the Session column. Create a data validation rule for the Session column as follows:

a. For all cells in the Session column, create an in-cell drop-down data validation rule that accepts only entries from a list.

b. Use 1, 2 as the Source values.

c. Provide an input message that uses Sessions as the title and the following sentence as the input message: Enter the number of sessions.

d. Apply a Stop style error alert that uses Number of sessions as the title and the following sentence as the error message: Enter 1 or 2.

3. Justin received a request from another employee who wants to take a training course and wants to delete any duplicate records from the table. Add and remove records as follows:

a. Find and remove a record with a duplicate Employee ID number.

b. Add a new record to the end of the table and then insert the data shown in Table 1, using the in-cell drop-down list to enter the Session value.

Table 1: Data for the New Record

Employee ID | Employee Name | Department | Course | Session | Hours | Approved 2200 | Amy Fuller | Nursing | Business Writing | 2 | 3 | No

4. The table is currently sorted by course name, but Justin prefers to have it sorted by employee ID so that he can find the employee records quickly. Sort the table in ascending order (smallest to largest) by the values in the Employee ID column.

5. Apply a table style option to bold the employee IDs in the first column of the table to make it easier to differentiate.

6. After Justin receives a request from an employee, he contacts the department manager for approval so that the employee can attend the course. He wants to print a list of employees who have not been approved for training courses. Use an advanced filter to provide this information for Justin as follows:

a. In cell I4, enter No as the value to use for filtering the criteria range.

b. Create an advanced filter that copies the results to another location.

c. Use the table of employee and training course data (range A3:G43) as the List range.

d. Use the information you set for filtering (range I3:I4) as the Criteria range.

e. Copy the results to the range starting in cell I6.

f. Resize columns I:O to their best fit.

7. Go to the Continuing Education worksheet, which includes a filtered table named Courses. Remove the filter from the Courses table to display all the records.

8. Apply the Lime, Table Style Light 9 table style to the Courses table to use a format similar to other tables in the workbook.

9. Justin notices that no one has enrolled in Session 2 of the Holistic Nursing course (Course ID 6755). Remove the record for Course ID 6755 from the table.

10. Georgia Medical Center charges a per person fee for attending a continuing education course to cover the costs of hiring medical experts to teach the courses. Justin wants to display the total amount charged for each course, the total for all courses, and the number of continuing education courses offered. Provide this information for Justin as follows:

a. Insert a new column in the table to the right of the Open Seats column.

b. Use Total Charged as the column heading of the new column.

c. In cell H7, enter a formula without using a function that uses structured references to multiply the price of the first course ([@Price]) by the number of people enrolled ([@Enrolled]).

d. Apply the Accounting number format with zero decimal places to the newly created column.

e. Add a Total Row to the Courses table, which automatically totals the amounts in the Total Charged column.

f. In cell B29, use the total row to display the count of the continuing education courses.

11. Justin has created an area in the range J1:L4 for looking up data in the Courses table. First, he wants to find the number of people enrolled in a specific course. He has already entered the course ID in cell J2. Look up the number of people enrolled in the course with Course ID 6120 as follows:

a. In cell J4, begin to enter a formula using the VLOOKUP function.

b. Use the Course ID in cell J2 as the value to look up.

c. Use a structured reference to the Courses table as the table to search.

d. Display the value in column 6 of the table.

e. Specify an exact match (FALSE) for the range lookup.

12. Justin also wants to calculate the total charges for courses offered in a specific session. He has already entered the session number in cell K2. Calculate the total charges for courses offered in Session 3 as follows:

a. In cell K4, begin to enter a formula using the DSUM function.

b. Use a structured reference to the Courses table (range A6:H29) including the headers and the data as the formula database.

c. Use a structured reference to the Total Charged field header (in cell H6) as the field to summarize.

d. Use the values Justin set up in the range K1:K2 as the criteria.

13. The third calculation Justin wants to make is to identify the number of open courses, which are courses that have at least one open seat. Calculate the number of open courses as follows:

a. In cell L4, begin to enter a formula using the DCOUNTA function.

b. Use a structured reference to the Courses table (range A6:H29) including the headers and the data as the formula database.

c. Use a structured reference to the Open Seats field header (in cell G6) as the field to count.

d. Use the values Justin set up in the range L1:L2 as the criteria.

14. Justin wants to list the courses in each session that are priced at more than $500. Filter and sort the Courses table as follows:

a. Use a number filter in the Price column to display courses with prices greater than $500. (Hint: Click the Filter button in the Sort & Filter group on the Data tab to display the filter arrows in the table.)

b. Sort the filtered table in ascending order first by Session and then by Price.

15. Go to the All Courses worksheet, which includes a table named All_Courses that lists training and continuing education courses. Justin wants to identify courses concentrating on the emergency room (ER).

a. Apply banded rows to the All_Courses table.

b. Filter the All_Courses table using a custom AutoFilter to display only course names beginning with "ER".

16. Prepare the All Courses worksheet for printing by using the data in row 6 as the print titles.

17. Go to the Course Details worksheet, which includes course data such as classroom locations and instructor evaluations. Justin included a table named Locations in the range A3:C9 but does not plan to filter or sort the data or use any other table features with it. Convert the Locations table to a range.

18. Justin needs to insert a table listing the courses available online. Insert the new table as follows:

a. Beginning in cell A11, enter the data shown in Table 2.

b. Format the range A11:C14 as a table using the Lime, Table Style Light 9 table style, specifying that the table has headers.

c. Use Online as the name of the new table.

Table 2: Data for the New Table

| A | B | C 11 | Online Course | Credits | Certification? 12 | Preventing Falls | 1 | N 13 | Diabetes Care | 1 | Y 14 | Allergies | 1 | Y

19. The Evaluations table in the range E3:I9 ranks the six most popular courses and notes the average evaluation scores that the four full-time instructors received. Justin has created an area in the range E11:F14 for looking up data in the Evaluations table. Look up the average evaluation score earned by instructor Egan for the Emergency Care course:

a. In cell E14, begin to enter a formula using the HLOOKUP function.

b. Use the instructor name in cell E12 as the value to look up.

c. Use a structured reference to the entire Evaluations table as the table to search.

d. Display the value in row 2 of the table.

e. Specify an exact match (FALSE) for the range lookup.

20. Find the rank in popularity of a course listed in the Evaluations table as follows:

a. In cell F14, begin to enter a formula using the MATCH function.

b. Use the course name in cell F12 as the value to look up.

c. Use a structured reference to the Instructor column in the Evaluations table (range E4:E9) as the column to search.

d. Specify an exact match (0) for the match type.

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

Final Figure 2: Continuing Education Worksheet

Final Figure 3: All Courses Worksheet

Final Figure 4: Course Details Worksheet