Zachary Sutton
GE ver. 17.0.1-rc0000
1.
1/1
Format a range as a table.
Apply a table style.
2.
0/1
Add a data validation rule to a range.
Add an input message to a data validation rule.
Add an error alert to a data validation rule.
3.
1/1
Remove a duplicate record from a table.
Add a record to a table.
4.
1/1
Sort a table.
5.
1/1
Format a table.
6.
1/1
Use an Advanced filter to copy data.
Change the column width.
7.
1/1
Remove a filter from a table.
8.
1/1
Apply a table style.
9.
1/1
Delete a record from a table.
10.
1/1
Add a column to a table.
Enter text in a cell.
Create a formula.
Change the number format.
Insert a total row in a table.
Summarize table data with a total row.
11.
1/1
Create a formula using a function.
12.
0/1
Create a formula using a function.
13.
0/1
Create a formula using a function.
14.
1/1
Filter a table.
Sort a table on multiple ranges.
15.
1/1
Format a table.
Filter a table.
16.
2/2
Add print titles to a worksheet.
17.
2/2
Format a range as a table.
18.
2/2
Enter values into a range.
Format a range as a table.
Apply a table style.
Set the name of a table.
19.
0/2
Create a formula using a function.
In the Course Details worksheet, the formula in cell E14 should contain the XLOOKUP function.
20.
2/2
Create a formula using a function.
SUBMISSION #1 | SCORE IS:
20
OUT OF
25
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.
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.
In the Training worksheet, the data validation rule in the range E4:E43 should have an input message titled
"Sessions" with the input message "Enter the number of sessions."
In the Training worksheet, the data validation rule in the range E4:E43 should have an error alert that uses the
Stop style with the title "Number of sessions" and the error message "Enter 1 or 2."
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.
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.
Apply a table style option to bold the employee IDs in the first column of the table to make it easier to differentiate.
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.
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.
Apply the
Lime, Table Style Light 9
table style to the Courses table to use a format similar to other tables in the
workbook.
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.
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.
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 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.
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 the
Courses
table (range A6:H29) including the headers and the data as the formula database.
c. Use 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.
In the Continuing Education worksheet, the formula in cell K4 should use Courses[[#Headers],[Total Charged]]
as the field argument.
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 the
Courses
table (range A6:H29) including the headers and the data as the formula database.
c. Use 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.
In the Continuing Education worksheet, the formula in cell L4 should use the Open Seats field header as the
field argument.
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.
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".
Prepare the
All Courses
worksheet for printing by using the data in row 6 as the print titles.
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.
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.
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
XLOOKUP
function.
b. Use the instructor name in cell
E12
as the value to look up.
c. Use a structured reference to the header row of the
Evaluations
table as the table to search.
d. Return the value in the range
E4:I4
.
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 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.
Illustrated Excel 365/2021 | Module 5: SAM Project 1a
