Homework answers / question archive / Illustrated Excel 2016 Module 7: SAM Project 1a Jocelyn Durham 1

Illustrated Excel 2016 Module 7: SAM Project 1a Jocelyn Durham 1

MS Excel

Share With

---

Illustrated Excel 2016 Module 7: SAM Project 1a

Jocelyn Durham

1. Noah Pries is an assistant in the Alumni Office at Lakeside College. He is helping the director

with reunion planning for the 5-, 10-, 15-, and 20-year celebrations and needs to analyze the

current data for activity information and payment. To make his work easier, he decides to

format the existing worksheet data into a table.

Switch to the Lakeside Reunion worksheet, and then format the range A8:M38 as a table with

headers using the Aqua, Table Style Dark 11 table style. (Hint: Depending on your version of

Office, the table style may be written as Table Style Dark 11.)

Add a style to a table.

Include a header row.

2.Noah's boss has decided that the donor information isn't necessary in the planning.

Delete the Donor column from the table. (Hint: Step 4 must be completed correctly to receive

credit on this step.)

Delete a column.

3. Noah needs to calculate the cost for each person attending the reunion. Recent graduates

will receive a discounted price.

In cell L9, create a formula using the IF function and structured references to determine the

cost based on the following criteria:

a. If the value in the Reunion Year column ([Reunion Year]) is less than or equal to 10, the

cost is 210.

b. If not, the cost is 255.

If the formula in cell L9 does not automatically fill the table column, fill the remaining cells

with the formula you just created.

Enter a formula into a cell.

Copy a formula into a range.

4. Noah also needs to calculate the balance owed for each person. Insert a new table column to the right of the Cost column.

Insert a new table column.

5. Enter the column label Balance in cell M8, and then use AutoFit to widen the column to fit

the new label.

Enter a column label.

Use AutoFit to change the width of a column.

6. Use structured references to enter a formula without using a function in cell M9 that

calculates the balance by subtracting the value in the Deposit column ([Deposit]) from the

value in the Cost column ([Cost]).

If the formula in cell M9 does not automatically fill the table column, fill the remaining cells

with the formula you just created.

Enter a formula into a cell.

7. Format the Deposit, Cost, and Balance table columns using the Currency number format with

zero decimal places and $ as the symbol.

Format cells using a number style.

8. Michael Nelson will not be able to attend the reunion as planned.

Delete row 23, which contains Michael Nelson's data, from the table.

Delete a record from a table.

9. Noah wants to check if any records were entered twice by mistake and delete any he finds.

Remove any duplicate records from the table. (Hint: There should be one duplicate record.

Step 12 must be completed correctly to receive credit for this step.)

Remove duplicate records.

10. Noah needs to distribute reunion information to the leaders for each reunion year. To make

the information easily scannable, he decides to organize the data first by reunion year, then

by school.

Sort the table on two levels using the following criteria:

a. 1st Level: Reunion Year field in ascending (Smallest to Largest) order

b. 2nd Level: School field in ascending (A to Z) order

Sort a table on multiple ranges.

11. Noah received a late registration from Jane Craig and needs to enter her information into the

table. Add a record to the last row of the table using the data shown in bold in Table 1 in the

Instructions file.

Add a record to a table.

12. The final table should display the total cost and balance information.

Add a Total row to the table. In cell L38, update the total row functions to display the sum of

the values in the Cost column.

Insert a total row in a table.

Enter a formula into a cell.

13.Noah recently found out that the school of Health will be absorbed into the Liberal Arts

school and needs to update the table to incorporate this change.

In the range D9:D37, replace all occurrences of Health with Liberal Arts. (Hint: Don't type the

period. You should make seven replacements.)

Replace occurrences of a word.

14. Because the worksheet will print on three pages, Noah thinks it best to display the first and

last names and the reunion year on each printed sheet.

Add the range A:C as print titles that repeat at the left of each printed page.

Repeat rows and columns in Print Layout.

15. Before Noah hands over the workbook, he wants to identify the name of the worksheet on

the header.

Add a header using the sheet name to the center header section of the worksheet. (Hint: Use

the button on the Header & Footer Tools Design tab.)

Switch back to Normal View, if necessary.

Add a header to a worksheet.