Calendar page Assignmentspage Librarypage Gradespage Messaging page Agramon, BeatriceProfile page Open Help topics modal Excel 2021 Skills Approach – Ch 9 Skill Review 9.2

Skills List

1. Open the start file EX2021-SkillReview-9-2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify the workbook.
3. Use Consolidate to create a summary sheet totaling sales from the Northeast, Southeast, Southwest, and Northwest worksheets. Include links to the source data.
   1. Go to the Summary worksheet.
   2. Select cells B4:E11.
   3. On the Data tab, in the Data Tools group, click the Consolidate button.
   4. If there are existing references in the All References box, remove them by clicking each one and then clicking the Delete button.
   5. Verify that Sum is selected in the Function list.
   6. Add the first reference. Click in the Reference box.
   7. Click the Northeast worksheet tab and click and drag to select cells B3:E10.
   8. Click Add.
   9. Click the Southeast worksheet tab and then click Add.
   10. Click the Southwest worksheet tab and then click Add.
   11. Click the Northwest worksheet tab and then click Add.
   12. If necessary, click the Create links to source data check box to add a checkmark.
   13. Click OK.
4. Expand the details for the Equipment sales type category and add data validation to find any individual sales less than 125,000.
   1. On the Summary worksheet, click the + next to Equipment.
   2. Select cells B4:E7.
   3. On the Data tab, in the Data Tools group, click the Data Validation button.
   4. In the Data Validation dialog, expand the Allow list and select Whole Number.
   5. Expand the Data list and select greater than.
   6. Type 124,999 in the Minimum box.
   7. Click OK.
   8. On the Data tab, in the Data Tools group, click the Data Validation button arrow and select Circle Invalid Data. There should be four values circled.
5. Add a hyperlink from one of the circled cells to its cell in the source worksheet.
   1. On the Summary worksheet, click cell B5. Look in the address bar to see the cell reference for this particular value.
   2. On the Insert tab, in the Links group, click the Link button.
   3. Under Link to, select Place in this Document.
   4. In the Type the cell reference box, type: B3
   5. In the Or select a place in this document box, click Northwest.
   6. Click OK.
   7. Test the link by clicking cell B5.
6. Add a comment.
   1. Select cell B3 in the Northwest worksheet.
   2. On the Review tab, in the Comments group, click the New Comment button.
   3. In the Start a conversation box, type: Lowest sales. Why?
   4. Click the Post  Comment button.
7. Import sales associate addresses from the tab-delimited text file SalesAssociateAddressList. You will create a new worksheet when you import the data. NOTE: If you are unable to find the SalesAssociateAddressList text file or are unable to complete this step due to your school’s computer lab restrictions, download the file from the Resources link. Open the file and continue to complete step 7.
   1. On the Data tab, in the Get & Transform Data group, click the From Text/CSV button.
   2. Navigate to the location where the data files for this project were saved. Double-click the SalesAssociateAddressList text file to open it.
   3. Review the preview. If Excel does not automatically use the first row as headers, you will need to edit the query. Click the Transform Data button to open the Query Editor. On the Home tab, in the Transform group, click the Use First Row as Headers button. On the Home tab, in the Close group, click the Close & Load button.
8. Double-click the new worksheet tab and rename it: Addresses
9. Remove the text file connection.
   1. Click anywhere in the Table.
   2. On the Table Design tab, in the External Table Data group, click the Unlink button. Click OK.
10. Use Flash Fill to correct the phone number format.
    1. On the Addresses worksheet, type Correct Phone Format in cell D1. Make the column wider if necessary.
    2. Type the new phone number format in cell D2: (916) 555-1324
    3. Press Enter or click in cell D3.
    4. On the Data tab, in the Data Tools group, click the Flash Fill button.
    5. Delete column C.
11. Use Text to Columns to separate the address into columns for the street address, the city, the state, and the zip code.
    1. On the Addresses worksheet, insert three columns to the right of the Address column.
    2. Change the text in cell B1 to: Street Address
    3. Press Tab and type City in cell C1.
    4. Press Tab and type State in cell D1.
    5. Press Tab and type Zip Code in cell E1.
    6. Select cells B2:B9.
    7. On the Data tab, in the Data Tools group, click the Text to Columns button.
    8. In Step 1 of the wizard, verify that Delimited is selected. Click Next.
    9. In Step 2 of the wizard, if necessary, check the Comma check box. Click Next.
    10. Review the Data preview box to verify that the text will be separated as you expect and then click Finish.
    11. Autofit columns B:E.
12. Unlock the address and mobile phone cells so changes can be made if you later apply worksheet protection to the workbook.
    1. On the Addresses worksheet, select cells B2:F9.
    2. On the Home tab, in the Cells group, click the Format button. Click Lock Cell to remove the highlight.
13. Apply worksheet protection so users can only select cells that are locked, not edit them.
    1. On the Review tab, in the Protect group, click the Protect Sheet button.
    2. Verify that the Protect worksheet and content of locked cells check box is checked.
    3. Verify that Select locked cells and Select unlocked cells are the only two options checked.
    4. Click OK.
14. Check for and fix accessibility issues.
    1. On the Review tab, in the Accessibility group, click the Check Accessibility button.
    2. There are three instances of Hard-to-read Text Contrast. Expand the first instance, A2:E2 (Southeast), point to Cell Styles, and click Accent1 under Themed Cell Styles.
15. Mark the workbook as final
    1. Click the File tab. If necessary, click Info.
    2. On the Info page, click the Protect Workbook button. Select Mark as Final. Click OK in the message box.
16. Close the workbook.
17. Upload and save your project file.
18. Submit project for grading.