Guided Project 7-2

In this project, you complete the purchase order for Blue Lake Sports by adding a data validation list and check box form controls. You protect the sheet and the workbook, save it as a template, and share it for testing.

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

Completed Project File Names: [your initials] Excel 7-2Template.xltx, [your initials] Excel 7-2User1.xlsx, [your initials] Excel 7-2User2.xlsx, and [your initials] Excel 7-2Merged.xlsx

Skills Covered in this Project

• Set data validation to use a list.
• Use an error alert for data validation.
• Insert a check box form control.
• Unlock worksheet cells and protect a worksheet.
• Save a workbook as a template.
• Create a new workbook from a template.
• Share a workbook and track changes.
• Accept or reject changes.

1. Open the BlueLakeSports-07 workbook from your student data files.
2. Save the workbook as a template.
   1. Click the Save As button [File tab] and choose This PC.
   2. Click the More Options link.
   3. Type the file name [your initials] Excel 7-2Template in the File name box.
   4. Verify that the Save Thumbnail box is selected or select it.
   5. Choose Excel Template from the Save as type drop-down box.
   6. Click Save.
3. Set data validation with an error alert to use a list.
   1. Click the Purchase Order sheet tab, select B17, and click the Data Validation button [Data tab, Data Tools group].
   2. Click the Allow arrow and choose List on the Settings tab.
   3. Click the Source entry box.
   4. Click the Departments worksheet tab and select cells A2:A14.
   5. Click the Error Alert tab.
   6. Verify that the Show error alert after invalid data is entered box is selected.
   7. Use Stop for the Style to prohibit an invalid entry.
   8. Click the Title box and type Wait!.
   9. Click the Error message box and type Please choose from the list.
   10. Click OK.
4. Display the Developer tab and the Compare and Merge Workbooks button.
   1. Select the Options command [File tab] and click Customize Ribbon in the left pane.
   2. Select the Developer box in the Main Tabs group.
   3. Click Quick Access Toolbar in the left pane.
   4. Choose All Commands from the Choose commands from list.
   5. Click Compare and Merge Workbooks in the commands list.
   6. Click Add and click OK.
5. Insert a check box form control.
   1. Click the View tab and check the Gridlines box in the Show group to draw and position controls with gridlines visible.
   2. Click the Insert Controls button [Developer tab, Controls group] and click the Check Box (Form Control) button.
   3. Draw a control directly over cell B15.
   4. page E7-476Click the Properties button [Developer tab, Controls group].
   5. Verify that the Unchecked radio button is selected on the Control tab.
   6. Select the 3-D shading box.
   7. Click the Protection tab and deselect the Locked box.
   8. Click OK.
   9. Click two times near the word “Check” with the control to place an insertion point.
   10. Delete Check Box 1, press Spacebar, and type Yes
   11. Click cell E15 to deselect the control. If you accidentally placed a check mark within the control, point and click to remove it.
6. Copy a check box form control.
   1. Point to the check box control label Yes and right-click.
   2. Choose Copy from the menu and then press Ctrl+V to paste the control.
   3. Point to any border of the copied control to display a move pointer.
   4. Drag the copy to cell C15.
   5. Press Ctrl and right-click while pointing to the first check box control to select both controls (Figure 7-65).7-65 Two controls selected
   6. Click the Align button [Drawing Tools Format tab, Arrange group] and choose Align Middle.
   7. Select cell E15 to deselect both controls.
   8. Right-click the copied control and choose Edit Text to place an insertion point.
   9. Delete Yes and type No (Figure 7-66).7-66 Controls are aligned
   10. Select cell E15. Remove check marks that were accidentally entered.
7. Protect the worksheet and the workbook structure.
   1. Select D7.
   2. Press Ctrl and select cell D9, cells B11:D13, cell B17, and cells A20:D28.
   3. Click the Format button [Home tab, Cells group].
   4. Select Lock Cell to remove the Locked property.
   5. Click cell D7.
   6. Click the Protect Sheet button [Review tab, Changes group].
   7. Allow the options to select locked and unlocked cells. Do not use a password.
   8. Click OK.
   9. Click the Protect Workbook button [Review tab, Changes group].
   10. Do not use a password and click OK.
8. Save and close the template.
9. Create a workbook from a template.
   1. Click the New button [File tab] and click PERSONAL near the top of the gallery.
   2. Click [your initials] Excel 7-2Template to create a workbook.
   3. Type BLS00120 in cell D7 and press Tab.
   4. page E7-477Type =to in cell D9, press Tab to select TODAY, and press Enter.
   5. Type the following in cells B11:B13:
      • Outdoor Apparel, Inc
      • 4232 South Water Street
      • Omaha, NE 68107
   6. Select the check box control for Yes.
   7. Select cell B17, click the data validation arrow, and choose Apparel.
   8. Save the workbook as an Excel workbook named [your initials] Excel 7-2User1 in your usual location for saving files.
10. Inspect the workbook.
    1. Click the File tab. You cannot inspect a worksheet that is protected.
    2. Click Unprotect in the Protect Workbook area 
    3. Click the Check for Issues button and choose Inspect Document.
    4. Choose Yes to save the document before inspection.
    5. Click Inspect in the Document Inspector dialog box.
    6. Click Remove All to remove all personal information and document properties.
    7. Click Close.
11. Check compatibility.
    1. Click the Protect Workbook button [Review group, Changes group]. In order to generate a compatibility report, the Protect Workbook property must be disabled.
    2. Click the Check for Issues button [File tab, Info group] and choose Check Compatibility.
    3. Click Copy to New Sheet.
    4. Click the Purchase Order sheet tab.
12. Share the workbook.
    1. Click the File tab.
    2. Click Allow this information to be saved in your file in the Inspect Workbook group. This property was activated when you removed metadata; it prohibits sharing the workbook.
    3. Return to the Purchase Order sheet and click the Share Workbook button [Review tab, Changes category].
    4. Select the Allow changes by more than one user at the same time box.
    5. Click the Advanced tab and set the Keep change history for option to 15 days.
    6. Click OK to close the Share Workbook dialog box.
    7. Click OK to resave the shared workbook.
13. Make a copy of the shared workbook in the same folder.
    1. Click the File tab and choose Save As.
    2. Select This PC and click the More Options link.
    3. Navigate to and choose your folder and save the workbook as [your initials] Excel 7-2User2.
    4. page E7-478Type the following in cells A20:D20:
       • Ladies Parka LP10Blue 4 87
    5. Save and close the workbook.
14. Open [your initials] Excel 7-2User1 and select cell A21.
    1. In cells A21:D21, type the following:
       • Men’s Parka MP16Black 4 98
    2. Save the workbook and leave it open.
15. Merge copies of the shared workbook.
    1. Click the Compare and Merge Workbooks button. (Save the file if prompted.)
    2. Navigate to the folder with [your initials] Excel 7-2User2 in the Select Files to Merge Into Current Workbook dialog box.
    3. Select [your initials] Excel 7-2User2 and click OK.
16. Accept or reject changes.
    1. Click the Track Changes button [Review tab, Changes group] and choose Accept/Reject Changes.
    2. Verify that Not yet reviewed displays as the When option.
    3. Click OK. The first edit is cell A21.
    4. Click Accept to locate the next edit.
    5. Click Accept All and then press Ctrl+Home.
17. Save the workbook as [your initials] Excel 7-2Merged in your folder and close it 

1. Move the template from the default folder.
   1. Click the File tab, select Open, and click Browse.
   2. Expand the Quick access list in the left pane and select Documents.
   3. Double-click Custom Office Templates.
   4. Right-click [your initials] Excel 7-2Template and choose Cut.
   5. In the left pane, navigate to and select the 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.
2. Remove the Compare and Merge Workbooks button and the Developer tab.
   1. Select the Options command [File tab] and click Quick Access Toolbar in the left pane.
   2. Click Compare and Merge Workbooks in the list on the right.
   3. Click Remove.
   4. Click Customize Ribbon in the left pane.
   5. Deselect the Developer box in the Main Tabs group and click OK.