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.

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

File Needed:BlueLakeSports-07.xlsx(Student data files are available in theLibraryof 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 theBlueLakeSports-07workbook from your student data files.
2. Save the workbook as a template.
3. Click theSave Asbutton [Filetab] and chooseThis PC.
4. Click theMore Optionslink.
5. Type the file name[your initials] Excel 7-2Templatein theFile namebox.
6. Verify that theSave Thumbnailbox is selected or select it.
7. ChooseExcel Templatefrom theSave as typedrop-down box.
8. ClickSave.
9. Set data validation with an error alert to use a list.
10. Click thePurchase Ordersheet tab, selectB17, and click theData Validationbutton [Datatab,Data Toolsgroup].
11. Click theAllowarrow and chooseListon theSettingstab.
12. Click theSourceentry box.
13. Click theDepartmentsworksheet tab and select cellsA2:A14.
14. Click theError Alerttab.
15. Verify that theShow error alert after invalid data is enteredbox is selected.
16. UseStopfor theStyleto prohibit an invalid entry.
17. Click theTitlebox and typeWait!.
18. Click theError messagebox and typePlease choose from the list.
19. ClickOK.
20. Display theDevelopertab and theCompare and Merge Workbooksbutton.
21. Select theOptionscommand [Filetab] and clickCustomize Ribbonin the left pane.
22. Select theDeveloperbox in theMain Tabsgroup.
23. ClickQuick Access Toolbarin the left pane.
24. ChooseAll Commandsfrom theChoose commands fromlist.
25. ClickCompare and Merge Workbooksin the commands list.
26. ClickAddand clickOK.
27. Insert a check box form control.
28. Click theViewtab and check theGridlinesbox in theShowgroup to draw and position controls with gridlines visible.
29. Click theInsert Controlsbutton [Developertab,Controlsgroup] and click theCheck Box (Form Control)button.
30. Draw a control directly over cellB15.
31. page E7-476
32. Click thePropertiesbutton [Developertab,Controlsgroup].
33. Verify that theUncheckedradio button is selected on theControltab.
34. Select the3-D shadingbox.
35. Click theProtectiontab and deselect theLockedbox.
36. ClickOK.
37. Click two times near the word "Check" with the control to place an insertion point.
38. DeleteCheck Box 1, pressSpacebar, and typeYes(Figure 7-64).
39. Click cellE15to deselect the control. If you accidentally placed a check mark within the control, point and click to remove it.
40. Copy a check box form control.
41. Point to the check box control labelYesand right-click.
42. ChooseCopyfrom the menu and then pressCtrl+Vto paste the control.
43. Point to any border of the copied control to display a move pointer.
44. Drag the copy to cellC15.
45. PressCtrland right-click while pointing to the first check box control to select both controls (Figure 7-65).
46. 7-65Two controls selected
47. Click theAlignbutton [Drawing Tools Formattab,Arrangegroup] and chooseAlign Middle.
48. Select cellE15to deselect both controls.
49. Right-click the copied control and chooseEdit Textto place an insertion point.
50. DeleteYesand typeNo(Figure 7-66).
51. 7-66Controls are aligned
52. Select cellE15. Remove check marks that were accidentally entered.
53. Protect the worksheet and the workbook structure.
54. SelectD7.
55. PressCtrland select cellD9, cellsB11:D13, cellB17, and cellsA20:D28.
56. Click theFormatbutton [Hometab,Cellsgroup].
57. SelectLock Cellto remove theLockedproperty.
58. Click cellD7.
59. Click theProtect Sheetbutton [Reviewtab,Changesgroup].
60. Allow the options to select locked and unlocked cells. Do not use a password.
61. ClickOK.
62. Click theProtect Workbookbutton [Reviewtab,Changesgroup].
63. Do not use a password and clickOK.
64. Save and close the template.
65. Create a workbook from a template.
66. Click theNewbutton [Filetab] and clickPERSONALnear the top of the gallery.
67. Click[your initials] Excel 7-2Templateto create a workbook.
68. TypeBLS00120in cellD7and pressTab.
69. page E7-477
70. Type=toin cellD9, pressTabto selectTODAY, and pressEnter.
71. Type the following in cellsB11:B13:

• Outdoor Apparel, Inc
• 4232 South Water Street
• Omaha, NE 68107

1. Select the check box control forYes.
2. Select cellB17, click the data validation arrow, and chooseApparel.
3. Save the workbook as an Excel workbook named[your initials] Excel 7-2User1in your usual location for saving files.
4. Inspect the workbook.
5. Click theFiletab. You cannot inspect a worksheet that is protected.
6. ClickUnprotectin theProtect Workbookarea (Figure 7-67).
7. Click theCheck for Issuesbutton and chooseInspect Document.
8. ChooseYesto save the document before inspection.
9. ClickInspectin theDocument Inspectordialog box.
10. ClickRemove Allto remove all personal information and document properties.
11. ClickClose.
12. Check compatibility.
13. Click theProtect Workbookbutton [Reviewgroup,Changesgroup]. In order to generate a compatibility report, theProtect Workbookproperty must be disabled.
14. Click theCheck for Issuesbutton [Filetab,Infogroup] and chooseCheck Compatibility.
15. ClickCopy to New Sheet.
16. Click thePurchase Ordersheet tab.
17. Share the workbook.
18. Click theFiletab.
19. ClickAllow this information to be saved in your filein theInspect Workbookgroup. This property was activated when you removed metadata; it prohibits sharing the workbook.
20. Return to thePurchase Ordersheet and click theShare Workbookbutton [Reviewtab,Changescategory].
21. Select theAllow changes by more than one user at the same timebox.
22. Click theAdvancedtab and set theKeep change history foroption to15days.
23. ClickOKto close theShare Workbookdialog box.
24. ClickOKto resave the shared workbook.
25. Make a copy of the shared workbook in the same folder.
26. Click theFiletab and chooseSave As.
27. SelectThis PCand click theMore Optionslink.
28. Navigate to and choose your folder and save the workbook as[your initials] Excel 7-2User2.
29. page E7-478
30. Type the following in cellsA20:D20:

• Ladies ParkaLP10Blue487

1. Save and close the workbook.
2. Open[your initials] Excel 7-2User1and select cellA21.
3. In cellsA21:D21, type the following:

• Men's ParkaMP16Black498

1. Save the workbook and leave it open.
2. Merge copies of the shared workbook.
3. Click theCompare and Merge Workbooksbutton. (Save the file if prompted.)
4. Navigate to the folder with[your initials] Excel 7-2User2in theSelect Files to Merge Into Current Workbookdialog box.
5. Select[your initials] Excel 7-2User2and clickOK.
6. Accept or reject changes.
7. Click theTrack Changesbutton [Reviewtab,Changesgroup] and chooseAccept/Reject Changes.
8. Verify thatNot yet revieweddisplays as theWhenoption.
9. ClickOK. The first edit is cellA21.
10. ClickAcceptto locate the next edit.
11. ClickAccept Alland then pressCtrl+Home.
12. Save the workbook as[your initials] Excel 7-2Mergedin your folder and close it (Figure 7-68)
13. Move the template from the default folder.
14. Click theFiletab, selectOpen, and clickBrowse.
15. Expand theQuick accesslist in the left pane and selectDocuments.
16. Double-clickCustom Office Templates.
17. Right-click[your initials] Excel 7-2Templateand chooseCut.
18. In the left pane, navigate to and select the folder for saving files.
19. Right-click an unused area of the dialog box and choosePaste.
20. ClickCancelto close the dialog box.
21. Remove theCompare and Merge Workbooksbutton and theDevelopertab.
22. Select theOptionscommand [Filetab] and clickQuick Access Toolbarin the left pane.
23. ClickCompare and Merge Workbooksin the list on the right.
24. ClickRemove.
25. ClickCustomize Ribbonin the left pane.
26. Deselect theDeveloperbox in theMain Tabsgroup and clickOK.