Illustrated Excel 2019 Module 2:End of Module Project 2 Freewheeling Media FORMATTING A WORKSHEET GETTING STARTED Open the file IL_EX19_EOM2-2_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought3

Share With

Illustrated Excel 2019 Module 2:End of Module Project 2

Freewheeling Media

FORMATTING A WORKSHEET

GETTING STARTED

• Open the file IL_EX19_EOM2-2_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_EOM2-2_FirstLastName_2.xlsx by changing the “1” to a “2”.
• 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 IL_EX19_EOM2-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
• If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. As the membership director of Freewheeling Media in Los Angeles, California, Alberto Ovando keeps track of the members of a streaming media subscription service. He created an Excel workbook to identify when to send email reminders for subscription renewals to new members. He has asked you to format the workbook to make the information easier to interpret.

Go to the Membership worksheet. Delete column B, which does not contain any data.

2. In the range F2:F3, change the font size of the text to 11 point to coordinate with the other data in the worksheet.
3. Use AutoFit to resize columns A–D to fit their contents. Resize column E to a width of

19.00 to provide plenty of room for the contents.

4. In the range A3:E4, format the column headings to emphasize them. In the range A3:E3, apply the Accent 1 cell style to match the formatting of the column headings in row 4. In the range A3:E4, bold the text to make it stand out.
5. Center the contents of the range C5:C27 to make the data easier to read.
6. In the range D5:D27, apply the Comma format with two decimal places to display the complete Subscription Amount values.
7. Alberto wants to identify the members from California and Nevada, who are eligible for a discounted subscription renewal amount. In the range B5:B27 (the State values), use Conditional Formatting Highlight Cells Rules to format cells whose contents are equal to CA with Green Fill with Dark Green Text. In the same range, use another Conditional Formatting Highlight Cells rule to format cells whose contents are equal to NV with Yellow Fill with Dark Yellow Text.
8. Alberto also wants to identify subscriptions that are expiring this year and those that will expire within four years. In the range E5:E27, use Conditional Formatting Highlight Cells Rules to format cells whose contents are less than 2022 with Light Red Fill with Dark Red Text. Add the 3 Symbols (Circled) icon set in the Indicators section to the same range to include a visual reminder about when each subscription will expire.
9. Add a header to the worksheet by entering the text New Members into the center section of header.

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