Guided Project 7-3

Guided Project 7-3

Agents for Placer Hills Real Estate (PHRE) have built a worksheet to help clients analyze the cost of a new home. You clear and reset data validation, insert a spin box form control, and save the workbook as a template.

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

 DO NOT use the files below if you are completing an autograded SIMnet Project. Using the linked files below will prevent you from uploading and submitting your work in SIMnet. The files for all autograded SIMnet Projects MUST be downloaded from your class assignment.

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

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

Skills Covered in This Project

• Save a workbook as a template.
• Find and clear data validation settings.
• Set validation criteria with an error alert.
• Insert a spin box form control.
• Protect a worksheet.
• Create a workbook from a template.

1. Open the PlacerHills-07 workbook from your student data files. The #NUM! error appears in four cells because arguments for the formulas are missing.
2. Save the workbook as a template.
   1. Select Save As [File tab] and choose This PC.
   2. Click the More options link to open the Save As dialog box.
   3. Type the file name [your initials] Excel 7-3Template 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.
   7. Select the Agents sheet tab. Agent names and data display.
3. Find and clear data validation settings.
   1. Click the Find & Select button [Home tab, Editing group] and choose Go To Special.
   2. Select the option button for Data validation (Figure 7-77).
   3. Click OK. No cells on the Agents sheet have validation criteria.
   4. Click OK in the message box.
   5. Click the Comparison sheet tab.
   6. Click the Find & Select button [Home tab, Editing group] and choose Data Validation. Cells with validation settings are highlighted.
   7. Click the Data Validation button [Data tab, Data Tools group], and verify the Settings tab is selected. The current criteria requires a whole number greater than or equal to 10.
   8. Click Clear All on the Settings tab and leave the dialog box open.
4. Set validation criteria with an error alert.
   1. Verify that the following cells in the worksheet are selected: C9:C12, H9, H11, H13:H14, and H16. (If they are not, close the Data Validation dialog box, select the cells, and click the Data Validation button [Data tab, Data Tools group].)
   2. Click the Allow arrow and choose Decimal on the Settings tab.
   3. Click the Data arrow and choose greater than or equal to.
   4. Click the Minimum box and type 0.
   5. Click the Error Alert tab and verify that the Show error alert after invalid data is entered box is selected.
   6. Verify that Stop displays as the Style.
   7. Click the Title box and type Required.
   8. Click the Error message box and type Please enter a positive value. including the period (Figure 7-78).
   9. Click OK.
5. Display the Developer tab on the Ribbon if it is not shown.
   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 and click OK.
6. Insert a spin button form control.
   1. Show the gridlines on the worksheet [View tab].
   2. Click the Insert Controls button [Developer tab, Controls group].
   3. Click the Spin Button (Form Control) button.
   4. Draw a control directly over cells I21:I22 (Figure 7-79).
   5. Right-click the control and choose Format Control.
   6. Click the Minimum value box on the Control tab and type 1.
   7. Set a maximum value of 8. These are agent identification numbers (1 through 8). The incremental change is 1 so that each click of a spin button goes up or down a number.
   8. Click the Cell link box and select cell H22. The choice made by the spin button will be placed in this cell (Figure 7-80).
   9. Verify that the 3-D shading box is selected and click OK. The linked cell displays the minimum value 1.
   10. Click cell H23 to deselect the control.
7. Use an INDEX formula to display the agent’s name.
   1. Verify that the active cell is H23.
   2. Click the Lookup & Reference button [Formulas tab, Function Library group].
   3. Choose Index and select the array, row_num, column_num argument group.
   4. Click the Array box, click the Agents sheet tab, and select cells A2:A7.
   5. Click the Row_num box, and select cell H22, and click OK.
   6. Center align cell H23.
   7. Turn off the gridlines.
8. Protect the template for data entry.
   1. Click the Find & Select button [Home tab, Editing group] and choose Data Validation. The cells are selected.
   2. Press Ctrl and select cells D4:E4, H22:H23, and E22 to add them to the selection.
   3. Click the Format button [Home tab, Cells group].
   4. Select Lock Cell to remove the Locked property.
   5. Delete the contents of cells C9:C10, H9 and H16.
   6. Select cell D4 to position the insertion point on the template .
   7. Click the Protect Sheet button [Review tab, Protect group].
   8. Allow the options to select locked and unlocked cells.
   9. Do not use a password and click OK.
   10. Save and close the template.
9. Create a workbook from a template.
   1. Click the New button [File tab] and click Personal.
   2. Click [your initials] Excel 7-3Template.
   3. Save the workbook as a regular Excel workbook named [your initials] Excel 7-3 in your usual location for saving files.
   4. Select cell C9, type 450000, and press Enter. The cells are formatted and you need not type any symbol or decimal point unless there is a significant decimal amount.
   5. Complete the worksheet as shown in Figure 7-81.
10. Save and close the workbook.
11. Move the template from the default folder to a local folder.
    1. Click the File tab, choose Open, and click Browse to display the Open dialog box.
    2. Expand the Quick access list and choose the Documents folder.
    3. Double-click Custom Office Templates in the list of folder names on the right.
    4. Right-click [your initials] Excel 7-3Template and choose Cut.
    5. Use the left pane to navigate to and open 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.
12. Remove the Developer tab.
    1. Click the Options button [File tab] and click Customize Ribbon in the left pane.
    2. Deselect the Developer box in the Main Tabs group and click OK.