Independent Project 7-4

Independent Project 7-4

Classic Gardens and Landscapes (CGL) keeps track of payments and receipts in a check register worksheet. You use an Excel template to build the workbook and share it with others in your department. (Note: You must have access to a secondary email address or work with a team member to complete this project.)

[Student Learning Outcomes 7.1, 7.2, 7.3, 7.4, 7.5, 7.7]

File Needed: ClassicGardens-07 (Student data files are available in the Library of your SIMnet account.)

Completed Project File Names: [your initials] Excel 7-4Template.xltx and [your initials] Excel 7-4Shared.xlsx

Skills Covered in This Project

• Create a workbook from a template.
• Save a workbook as a template.
• Edit input messages for data validation.
• Add a list box form control.
• Share and co-author a workbook.
• Check accessibility and add alt text.

1. Open the ClassicGardens-07 workbook from your student data files. The Register sheet was copied from an online Excel template.
2. Save the workbook as a template with a thumbnail and name it [your initials] Excel 7-4Template in the default Custom Office Templates folder.
3. Find data validation.
   1. Click the Find & Select button [Home tab, Editing group] and choose Data Validation. The cells in the header row include input messages without validation criteria.
   2. Select cell A7. The input message displays.
4. Edit data validation settings.
   1. Confirm that cell A7 is selected and open the Data Validation dialog box.
   2. Delete the input message.
   3. Type the new input message Enter the check number if any. If payment is automatic or online, leave blank. including the periods and click OK.
   4. Select cell D7.
   5. Change the input message to Choose Yes or No to indicate if the payment has cleared. including the period.
   6. Change the label in cell D7 to Cleared?.
   7. AutoFit column D.
5. Create data validation with a list.
   1. Select cells D8:D18.
   2. Create data validation to use a list.
   3. Use Yes or No as Source entries.
6. Insert a list box form control.
   1. Insert a new sheet and name it Months.
   2. Type January in cell A1. Fill the months to December in cell A12.
   3. Return to the Register sheet and display the Developer tab if necessary.
   4. Unlock cell H2.
   5. Insert a List Box (Form Control) at cell H2.
   6. Format the control to use cells A1:A12 on the Months sheet as the input range, cell H2 as the linked cell, and 3-D shading (Figure 7-82).
7. Mark the Classic Gardens image on the Register sheet as decorative.
8. Review formulas and protect the template.
   1. Select and delete the contents of cells A9:F12 and cell F8.
   2. Format cells E8:G18 as Currency.
   3. Select cell G8 and click the Insert Function button in the Formula bar to open the Function Arguments dialog box. The IFERROR function has nested IF, AND, and ISBLANK functions.
   4. Click IF in the Formula bar to display its Function Arguments dialog box.
   5. Click AND in the Formula bar to display its Function Arguments dialog box.
   6. Click IFERROR and click Cancel.
   7. Set the row height for rows 8:18 to 24.
   8. Remove the Locked property from cells A8:F18.
   9. Select cell F8.
   10. Protect the sheet without a password.
   11. Save and close the template.
9. Create a new workbook based on the template and save it as [your initials] Excel 7-4Shared on OneDrive in your Documents or preferred folder.
10. Complete the records as shown here. Key today’s date and other dates as described in the figure. Choose the appropriate month in the list box control (Figure 7-83).
11. Share a workbook.
    1. Share the workbook with your secondary email address or your team member.
    2. Launch your email program and sign in to your secondary email account or ask your colleague to do the same.
    3. Open the email message and the shared workbook.
12. Complete the following records in Excel for the web (Figure 7-84).
13. Close the Excel browser window and your email message.
14. Return to Excel and [your initials] Excel 7-4Shared.
15. Save a copy of the file to your usual location for saving files and close the workbook.
16. Move the [your initials] Excel 7-4Template file from the Custom Office Templates folder to your folder and hide the Developer tab if instructed to do so.