Guided Project 7-1

Guided Project 7-1

In this project, you develop a template for an instructor team at Sierra Pacific Community College District (SPCCD). You add data validation, insert a combo box control, check accessibility, and share a workbook created from the template. (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.6, 7.7]

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

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

Skills Covered in This Project

• Create a workbook from a template.
• Save a workbook as a template.
• Set data validation criteria with an error alert.
• Set data validation criteria with an input message.
• Insert a combo box form control.
• Check accessibility.
• Add worksheet protection.
• Share a workbook.

1. Open the SPCCD-07 workbook from your student data files.
2. Save the workbook as a template.
   1. Click the File tab and select Save As.
   2. Choose This PC and click the File type drop-down list.
   3. Choose Excel Template. The default templates folder opens.
   4. Type the file name [your initials] Excel 7-1Template in the File name box.
   5. Click Save. When you save the file from the Save As pane, you do not see an option for a thumbnail.
   6. Click the File tab and choose Info.
   7. Click the Properties drop-down list and choose Advanced Properties.
   8. Verify that the Save Thumbnail box is selected or select it (Summary tab).
   9. Close the dialog box and return to the template.
3. Set validation criteria for a range.
   1. Click the Grade Sheet tab and select cells C6:C30.
   2. Click the Data Validation button [Data tab, Data Tools group] and verify that the Settings tab is selected.
   3. Click the Allow arrow and choose Decimal.
   4. Verify that the Data option displays between.
   5. Type 5 for the Minimum and 20 for the Maximum.
   6. Do not click OK.
4. Create an error message for data validation settings.
   1. Click the Error Alert tab.
   2. Verify that the Show error alert after invalid data is entered box is selected.
   3. Click the Style arrow and choose Warning to allow an invalid entry.
   4. Click the Title box and type Class Points.
   5. Click the Error message box and type Enter a value between 5 and 20..
   6. Click OK.
   7. Select cell C6, type 21 and press Enter to display the error alert (Figure 7-66).
   8. Click Cancel to keep the current value.
5. Set data validation with an input message.
   1. Select cells D6:D30 and click the Data Validation button [Data tab, Data Tools group].
   2. Click the Allow arrow and choose Decimal on the Settings tab.
   3. Verify that between is the Data option.
   4. Click the Minimum box and type 50.
   5. Enter 100 for the Maximum.
   6. Click the Input Message tab.
   7. Verify that the Show input message when cell is selected box is selected.
   8. Click the Title box and type Project Points.
   9. Click the Input message box and type Enter a value between 50 and 100. (Figure 7-67).
   10. Click OK. The message box displays when a cell in the range is selected.
6. Set validation criteria without messages.
   1. Select cells E6:E30.
   2. Set data validation to limit the entry to any decimal number between 150 and 300.
   3. Do not use an input or error alert message.
   4. Click cell E9 and click its Trace Error button. Invalid data is automatically located in an Excel table when criteria is applied after data have been entered (Figure 7-68).
   5. Click cell E9, type 275 and press Enter.
7. Display the Developer tab.
   1. Click the File tab and choose Options.
   2. Click Customize Ribbon in the left pane.
   3. Select the Developer box in the Main Tabs pane and click OK.
8. Insert a combo box form control.
   1. Click the Insert Controls button [Developer tab, Controls group].
   2. Click the Combo Box (Form Control) button.
   3. Draw a control to cover cell J17.
   4. Right-click any border of the control and choose Format Control.
   5. Click the Input range box, click the Data worksheet tab, and select cells A1:A4.
   6. Click the Cell link box and select cell H4.
   7. Select the 3-D shading box (Figure 7-69).
   8. Click OK. Click cell I3 to deselect the combo box control.
   9. Click the combo box arrow and choose Summer Semester. The label displays in the control and 4 displays in the linked cell (H4).
9. Use INDEX to display the label from the control in the linked cell.
   1. Right-click the combo box control and choose Format Control.
   2. Click the Cell link box and delete the reference to cell $H$4.
   3. Type j17 as the Cell link address and click OK.
   4. Select cell H4 and press Delete.
   5. Click the Lookup & Reference button [Formulas tab, Function Library group].
   6. Choose Index and select the array, row_num, column_num argument group.
   7. Click the Array box, click the Data sheet tab, and select cells A1:A4. The Array for the INDEX function must be the same as the Input range for the combo box control.
   8. Click the Row_num box, type j17, and click OK.
   9. Select cell H4 and apply italic.
10. Check accessibility.
    1. Select cell A5.
    2. Click the Check Accessibility button [Review tab, Accessibility group].
    3. Expand the Missing alternative text group. (Some Excel versions may identify merged cells as an error.)
    4. Choose Picture 3 (Grade Sheet) in the Accessibility pane and click its drop-down list.
    5. Choose Mark as decorative. The image is cleared from the Accessibility pane.
    6. Choose Drop-Down 1 (Grade Sheet) in the Accessibility pane and click its drop-down list.
    7. Choose Add a description and click the entry box in the Alt Text pane (Figure 7-70).
    8. Type This is the semester list. and close the Alt Text pane.
    9. Close the Accessibility pane.
11. Unlock cells and protect the worksheet.
    1. Select cells A6:E30. These are cells in which team instructors enter names and points.
    2. Press Ctrl and click cell H4. The linked cell needs to be unlocked.
    3. Right-click cell A6 and choose Format Cells.
    4. Click the Protection tab and deselect the Locked box to turn off the property.
    5. Click OK.
    6. Point and right-click the combo box control.
    7. Drag the combo box control to reveal cell J17.
    8. Select cell J17, click the Format button [Home tab, Cells group] and choose Lock Cell.
    9. Select and reposition the combo box control.
12. Protect the worksheet.
    1. Select cell H4 and click its Trace Error button. Because it has a formula, the cell is flagged as being unprotected. Leave the cell unchanged.
    2. Select cell A5.
    3. Click the Protect Sheet button [Review tab, Protect group].
    4. Allow options to select locked and unlocked cells.
    5. Do not use a password and click OK.
13. Delete sample data and resave the template.
    1. Select cells A6:E27 and delete them.
    2. Select cell F6 and view its SUM formula.
    3. Review the formulas in cells G6 and H6.
    4. Select cell A6.
    5. Click the Save button in the title bar.
    6. Close the template.
14. Create a workbook from a template.
    1. Click the File tab and select New.
    2. Click Personal to display the templates in the Custom Office Templates folder.
    3. Click the icon for [your initials] Excel 7-1Template to create a new workbook.
15. Save the workbook to OneDrive.
    1. Click the File tab and select Save As.
    2. Click OneDrive – [Identifier] and then click Documents or your preferred cloud folder.
    3. Type the file name [your initials] Excel 7-1Shared in the entry box.
    4. Click Save.
    5. Verify that AutoSave is on.
16. Share the workbook.
    1. Click the Share button in the upper-right corner of the Ribbon.
    2. Click the To: line and type or select your secondary email address or the email address of your team member.
    3. Leave the message box empty and click Send.
    4. Close the confirmation window.
17. Collaborate on a workbook.
    1. Launch your email program and sign in to your secondary email account or ask your team member to do so.
    2. Open the email message about the shared workbook and click the link.
    3. Click Viewing and choose Editing.
    4. Type the following data in cells A6:E6. When the error alert message displays, click Yes to complete the entry.GoldstoneViolet2975295
    5. Close the Excel browser window.
    6. Close your email message and program.
    7. Return to the [your initials] Excel 7-1Shared workbook in Excel.
    8. Type the following data in cells A7:E7:GomezAndre1295250
    9. Click the combo box drop-down list and choose Winter Semester.
    10. Select cell A8.
18. Save a copy of the workbook.
    1. Select the Save a Copy command [File tab].
    2. Click This PC and then click the Up arrow to navigate to your usual location for saving workbooks.
    3. Type or verify the file name as [your initials] Excel 7-1Shared.
    4. Click Save and close the workbook.
19. Hide the Developer tab.
    1. Click the File tab and chose Options.
    2. Click Customize Ribbon in the left pane.
    3. Deselect the Developer box in the Main Tabs pane and click OK.
20. Move the template from the default folder.
    1. Click the File tab, select Open, and click Browse.
    2. Select Documents on the Quick access list in the Open dialog box.
    3. Double-click Custom Office Templates in the right pane. (If the Custom Office Templates folder is not listed, click This PC in the left pane and select the Documents folder. Then click Custom Office Templates.)
    4. Right-click [your initials] Excel 7-1Template and choose Cut.
    5. In the left pane, navigate to and select your folder for saving files.
    6. Right-click an unused area of the dialog box and choose Paste.
    7. Click Cancel to close the dialog box and return to Excel.