Chapter Nine Excel Lesson

Adding Data Validation 1. Add data validation to the selected list of prices. Require whole numbers greater than 30. Do not ignore blank cells. 2. Display the circles to find invalid data. 3. Hide Validation Circles

1. Click the Data tab. 2. Click the Data Validation button. 3. Click the Allow drop-down arrow. 4. From the menu, select Whole Number. 5. Click the Data drop-down arrow. 6. From the menu, select greater than. 7. Type 30 in the Minimum box and press Enter to submit your answer. 8. Click the Ignore blank check box to uncheck it. 9. Click OK. 10. Click the Data Validation button arrow. 11. Select Circle Invalid Data. 12. Click the Data Validation button arrow again. 13. Select Clear Validation Circles.

Adding an Input Message and Error Alert to Data Validation 1. Add the text Enter the service price. as an input message to the data validation rule for the selected cells. Do not include a title. 2. Type the text Price must be a whole number greater than 30 in the error message box.

1. Click the Data tab. 2. Click the Data Validation button. 3. Click the Input Message tab in the Data Validation dialog. 4. Type the text Enter the service price. in the Input message box. 5. Click OK. 6. Click the Data tab. 7. Click the Data Validation button. 8. Click the Error Alert tab. 9. Type the text Price must be a whole number greater than 30. in the Error message box. 10. Click Ok.

Creating a Drop-Down List for Data Entry 1. In cells H2:H23, use data validation to display an in-cell drop-down list of values from the Locations named range. Allow blanks.

1. Click the Data tab. 2. Click the Data Validation button. 3. Setting tab is selected. Click the Allow-drop down arrow. 4. Select List from the menu. 5. Type =Locations in the Source box. 6. Click OK.

Converting Data to Columns 1. Split the selected text into separate columns using comma as the delimiter.

1. Click the Data tab. 2. Click the Text to Columns button. 3. The data are separated by a common delimiter. Click the Next button. 4. Click the Comma check box. 5. Click the Next button. 6. Click the Finish button.

Importing Data from a Text File 1. Import data from the Services text file. Allow Excel to Import the data into a table in a new worksheet.

1. Click the Data tab. 2. On the Data tab, click the From Text/CSV button. 3. Click the Services file. 4. Click the Import button. 5. Click the Load button.

Exporting to Text Formats 1. Export (save) this worksheet as a CSV comma-delimited text file. 2. Export (save) this worksheet as a tab-delimited text file.

1. Click the File tab. 2. Click Save As. 3. On the Save As page, click the Save as type arrow. 4. Select CSV 5. Click the Save button. 6. Click OK. 7. Click the File tab. 8. Click Save As. 9. On the Save As page, click the Save as type arrow. 10. Select Text (Tab delimited) (*.txt). 11. Click the Save button. 12. Click OK.

Saving a Workbook in Other Formats 1. Save this workbook as an Excel template. 2. Save the workbook in the format compatible with Excel 97-2003. Allow the file to save with compatibility issues.

1. Click the File tab. Click Save As. On the Save As page, click the Save as type arrow 2. Click the File tab. Click Save As page, click the Save as type arrow and select Excel 97-2003 Workbook (*xls). Click Save. Click Continue.

Running the Document Inspector 1. Run the Document Inspector. 2. Remove the Comments and Annotations and Document Properties and Personal Information identified by the Document Inspector and then close the Document Inspector window.

1. Click the File tab. Click the Check for Issues button, and select Inspect Document. Click Yes. Click Inspect. 2. Click the Remove All button next to Comments and Annotations. Click the Remove All button next to Document Properties and Personal Information. Click Close.

Checking for Compatibility with Previous Versions of Excel 1. Open the Compatibility Checker to check if this workbook contains elements that are not compatible with earlier versions of Excel. 2. Review the compatibility issue identified, and then close the Compatibility Checker.

1. Click the File tab. Click the Check for Issues button, and then click Check Compatibility. 2. Click OK.

Finalizing the Workbook 1. Mark the workbook as final so it opens as read-only to discourage editing.

1. Click the File tab. Click the Protect Workbook button, and select Mark as Final. Click OK. Click OK.

Adding a Password 1. Encrypt the workbook with this password: eXp3ns$s

1. Click the File tab. Click the Protect Workbook button. Click Encrypt with Password. Type eXp3ns$s. Click OK. Type eXp3ns$s in the Confirm Password dialog. Click OK.

Checking for Accessibility 1. Display the Accessibility Checker task pane.

1. Click the Review tab. Click the Check Accessibility button.

Inserting data Using Flash Fill 1. On Sheet1, in cell F2, type Rose Carnation and press Enter. In cell F3, begin typing Felix Clauson and when Flash Fill makes an autofill suggestion, accept it. 2. Cell C3 has been selected. Use the Flash fill command to autofill the remaining cells in this column with the appointment date and time pattern shown cell C2. 3. Enter the text 30 minutes in cell E2 and then use Flash Fill to autofill the remaining cells in the column.

1. On Sheet1, in cell F2, type Rose Carnation and press Enter. 2. In cell F3, type F and press Enter. 3. Cell C2 has been selected. Click the Data tab. 4. Click the Flash Fill button. 5. Enter the text 30 minutes in cell E2 and press Enter. 6. Enter 45 and press enter to use flash fill.

Consolidating Data in a Summary Worksheet 1. In the selected cells, use Consolidate to sum data from cells B3:D6 in the Old Town, Kingstowne Salon, and Online Sales Office worksheets. 2. Now use Consolidate to enter values in cells B10:D15 by summing data from cells B10:D15 in the worksheets Old Town Spa, Kingstowne Salon, and Online Sales Office. Be sure to remove any existing references before adding new ones. Include links to the source data.

1. On the Data tab, click the Consolidation button. 2. Click the Old Town Spa tab. 3. Select cell range B3:D6 4. Click the Add button. 5. Click the kingstowne salon worksheet tab to add it as a new reference. 6. Click the Add button. 7. Click the Online Sales Office tab to add it as a new reference. 8. Click the Add button. 9. Click OK. 10. Click the first reference in the All References box. 11. On the data tab, in the data tools group, click the consolidate button. Click each of the existing references in the All References box and then click the delete button. Click in the Reference box and then click the Old Town Spa worksheet tab. Click and drag to select B10:D15. Click the Add button. Click the kingstowne Salon worksheet tab. Click Add. Click the Sales Office worksheet tab. Click Add. Click the Create links to source data check box. Click OK.

Protecting Worksheets and Workbooks from Changes 1. Unlock the cells B3:D6 so the user can edit the cells when the worksheet is protected. 2. Enable worksheet protection for the Summary worksheet so users can select both locked cells and unlocked cells. 3. The Old Town Spa worksheet has been protected. Unprotect it so users can make changes to locked cells. 4. Enable workbook protection so users cannot change worksheet names. 5. Remove workbook protection so users can add new worksheets.

1. On the Home tab, in the Cells group, click the Format button. Click Lock Cell to remove the highlight. 2. On the Review tab, in the Changes group, click the Protect Sheet button. Click OK. 3. Click the Old Town Spa worksheet tab. On the Review tab, in the Changes group, click the Unprotect Sheet button. 4. On the Review tab, in the Changes group, click the Protect Workbook button. Click OK. 5. On the Review tab, in the Changes group, click the Protect Workbook button.

Inserting Links 1. Add a hyperlink from cell F4 to link to cell B1 in the Old Town Spa worksheet. 2. Remove the Hyperlink from cell A3.

1. On the Insert tab, in the Links group, click the Link button. Under Link to: click Place in this document. In the Type the cell reference box, type: B1 In the Or select a place in this document box, click Old Town Spa. Click Ok. 2. Right click cell A3 and select Remove Hyperlink.

Working with Comments and Notes 1. Add this comment to cell F4: Need links to details here. 2. Open the Comments task pane to review all the comments in this worksheet. 3. Delete the comment from cell f5. 4. Reply to the comment in cell F4 with the text Done.

1. On the Review tab, in the Comments group, click the New Comment button. Type: Need links to details here. Click the Post button. 2. On the Review tab, in the Comments group, click the Show Comments button. 3. In the Comments task pane, click More thread actions for the comment in cell f5. Select Delete Thread. 4. In the comments task pane, type Done in the Reply box below the comment for cell f4. Click the Post button.