Working with Data

1. Working with Data Lesson 7

2. Objectives

3. Software Orientation: Excel’s Data Tab • The command groups on Excel’s Data tab, shown in the figure below, enable you to sort and filter data, convert text to columns, ensure valid data entry, conduct what-if analyses, and outline data. You can also get external data into Excel by using Data commands. • In this lesson, you will learn many ways to use the command groups on Excel’s Data tab to manage spreadsheet data. Use this figure as a guide to these powerful commands.

4. Step-by-Step: Restrict Cell Entries to Data Types • Before you begin these steps, be sure to LAUNCH Microsoft Excel. Click the File tab in the upper-left corner of the Ribbon. • OPEN the Employee Data data file for this lesson. • Select the cell range D3:D50. • On the Data tab, in the Data Tools group, click Data Validation. You will now begin to set your validation criteria. • On the Settings tab of the Data Validation dialog box, select Whole number in the Allow box. This sets the number format for your validation.

5. Step-by-Step: Restrict Cell Entries to Data Types • Key 15 in the Minimum box and 40 in the Maximum box. You have now set your whole number range. The Data Validationdialog box should look like the figure. • Click the Error Alert tab in the dialog box. Be sure the Show error alert after invalid data is entered check box is selected. Key Invalid Entry in the Title box. This will display an alert when an invalid entry has been made to the cell.

6. Step-by-Step: Restrict Cell Entries to Data Types • Key Only whole numbers can be entered in the error message box as shown in the figure. This will display the error message that you want the user to see. • Click the Input Message tab and in the Input Message box, key Enter a whole number between15 and 40. Click OK. This will create the message for the user to follow to correct their error.

7. Step-by-Step: Restrict Cell Entries to Data Types • Select cell D6, key 35.5, and press Enter. The Invalid Entry dialog box (see the figure) opens, displaying the error message you created. • Click Retry to close the error message; key 36, and press Enter. • Use the following employee information to key values in row 29. Patricia Doyle was hired today as a receptionist. She will work 20 hours each week. • Create a Lesson 7 folder and SAVE the file as Contoso Data. • LEAVE the workbook open to use in the next exercise.

8. Step-by-Step: Allow Specific Values in Cells • USE the workbook from the previous exercise. • Select E3:E29. Click Data Validation in the Data Tools group on the Data tab. • Click the Settings tab, in the Allow box, select List. The In-cell drop-down check box is selected by default. • In the Source box, key Yes, No. Click OK to accept the settings. An arrow now appears to the right of the cell range. • Click E3. Click the arrow to the right of the cell. You now see the list options you created in the previous step.

9. Step-by-Step: Allow Specific Values in Cells • If the value in column D is 30 or more hours, from the newly created drop-down list, choose Yes. If it is less than 30 hours, select No. • Continue to apply the appropriate response from the list for each cell in E4:E29. • SAVE the workbook. • LEAVE the workbook open to use in the next exercise.

10. Step-by-Step: Remove Duplicates • USE the workbook from the previous exercise. • Select A3:E29. In the Data Tools group, click Remove Duplicates. The Remove Duplicates dialog box shown in in the figure opens. • Remove the check from Hours and Insurance. You will identify duplicate employee data based on last name, first name, and job title.

11. Step-by-Step: Remove Duplicates • My data has headers is selected by default. Click OK. Duplicate rows are removed and the confirmation box shown in the figure appears informing you that two duplicate values were found and removed. • Click OK. SAVE the workbook. • LEAVE the workbook open to use in the next exercise.

12. Step-by-Step: Sort Data on a Single Criterion • USE the workbook from the previous exercise. • Before you begin, Delete the contents of row 1 from your worksheet. • Select D2:D27 (column heading and data in column D). • On the Data tab, click the Sort button.

13. Step-by-Step: Sort Data on a Single Criterion • A Sort Warning message appears and by default prompts you to expand the data selection. Click Expand the selection option and click the Sort button. The Sort dialog box opens. Excel will automatically organize your column information. It recognizes the header, understands the numeric values, and gives you the sort options. You will accept what Excel has selected. See the figure. Click OK to accept the first single sort criteria.

14. Step-by-Step: Sort Data on a Single Criterion • Select any cell in column A and click the Sort A to Z button.Data is sorted by last name. You have chosen your second single criteria of sorting alphabetically from A–Z. • Select A2:E27 and click the Sort button to launch the Sort dialog box shown in the figure below.

15. Step-by-Step: Sort Data on a Single Criterion • In the Column section’s Sort By box, click the arrow to activate the drop-down list and select Job Title. Click OK. You have selected the third single sort criteria. Note that your worksheet is now sorted by Job Title. • Click the Sort button in the Sort and Filter group; the data range is automatically selected and the Sort dialog box opens. Select Hours in the Column section’s Sort By box. Change the order options to smallest to largest. Click OK. You have now selected you last single sort criteria. Note that your worksheet is once again sorted by the data in the Hours column. • LEAVE the workbook open to use in the next exercise.

16. Step-by-Step: Sort Data on Multiple Criteria • USE the workbook from the previous exercise. • Select the range A2:E27, if it isn’t already selected. • Click Sort in the Sort and Filter group on the Data tab to open the dialog box. • Select Job Title in the Column section’s Sort By box and A to Z in the Order box. • Click the Add Level button in the dialog box to identify the second sort criteria. A new criterion line is added to the dialog box.

17. Step-by-Step: Sort Data on Multiple Criteria • In the Then By box in the Column section select Last Name as the second criterion. A to Z should be the default in the Order box as shown in the figure. Click OK. You have now sorted using multiple criteria; first byjob title and then alphabeticallyby last name. • SAVE the workbook. • LEAVE the workbook open to use in the next exercise.

18. Step-by-Step: Sort Using Conditional Formatting • USE the workbook from the previous exercise. • On the Home tab, click Find & Select in the Editing command group, and click Conditional Formatting in the drop-down menu. A message is returned that no cells in the worksheet contain conditional formatting. Click OK to close the message box. This step is to make sure there are no conditional formatting rules in place.

19. Step-by-Step: Sort Using Conditional Formatting • Select D3:D27. Click Conditional Formatting in the Styles group, and then open the Icon Sets gallery. See the figure below.

20. Step-by-Step: Sort Using Conditional Formatting • Click the 3 Arrows icon set. Each value in the selected column now has an arrow that represents whether the value falls within the high, middle, or low range of your data. • Select A3:E27. On the Home tab, click Sort & Filter and then click Custom Sort (see the figure); the Sort dialog box opens.

21. Step-by-Step: Sort Using Conditional Formatting • Select Hours in the Sort By box. Select Cell Icon in the Sort On section’s drop-down list. Click the green arrow under Order(see the figure). • Select Hours in the Then by box. Select Cell Icon under Sort On and accept the yellow arrow and On top in the Order field. Click OK. Data sorts by icon set. The criteria caused the data to sort first by the Green arrow Icon and then by the Yellow. • SAVE your workbook as Contoso Icons. CLOSE the workbook. • LEAVE Excel open to use in the next exercise.

22. Step-by-Step: Sort Data Using Cell Attributes • OPEN the MA Assignments data file for this lesson. • Select the entire data range (including the column headings). On the Data tab, click Sort. • In the Sort dialog box, accept Last Name in the Sort By box. Under Sort On, select Cell Color. • Under Order, select Pink and On Top. • Click the Add Level button in the dialog box and select Last Name in the Sort By box. In the Sort On section, select Cell Color. Select Yellow and On Top in the Order section.

23. Step-by-Step: Sort Data Using Cell Attributes • Using the same method you used in step 4, add a level for Green and then add a level for Blue. You should have a criterion for each color as illustrated in the figure. Click OK. • SAVE the workbook in your Lesson 7 folder as MA Assignments. • LEAVE the workbook open to use in the next exercise.

24. Step-by-Step: Use AutoFilter • USE the workbook from the previous exercise. • Select A3:E28. Click Filter on the Data tab in the Sort & Filter group; a filter arrow is added to each column heading. • Click the filter arrow in the Job Title column. The AutoFilter menu shown in the figure is displayed.

25. Step-by-Step: Use AutoFilter • Currently the data is not filtered, so all job titles are selected. Click Select All to deselect all titles. • Click Accounts Receivable Clerk and Receptionist. Click OK. Data for six employees who hold these titles is displayed. All other employees are filtered out. See the figure. • LEAVE the workbook open with the filtered data displayed to use in the next exercise.

26. Step-by-Step: Create a Custom AutoFilter • USE the workbook from the previous exercise. • With the filtered list displayed, click the filter arrow in column D. In the AutoFilter menu, point to Number Filters. As shown in the figure,the menu expands toallow you to customizethe filter.

27. Step-by-Step: Create a Custom AutoFilter • Select Less Than on the expanded menu and key 30 in the amount box. Click OK. The AutoFilter menu closes and the filtered list reduces to four employees who work fewer than 30 hours per week. • Click the Filter button in the Sort & Filter group on the Data tab to display all data. With the data range still selected, click Filter again. • Click the filter arrow in column D to open the AutoFilter menu. Point to Number Filters and select Greater Than. Key 15 and press Tab twice to move to the second comparison operator criteria box.

28. Step-by-Step: Create a Custom AutoFilter • Click the arrow for the comparison operator drop-down list and select is less than as the second comparison operator and press Tab. Key 30 and click OK. The list should be filtered to six employees. • Click the Filter button to once again display all data. • SAVE and CLOSE the workbook. • LEAVE Excel open to use in the next exercise.

29. Step-by-Step: Filter Using Conditional Formatting • OPENConditional Format from the data files for this lesson. • Select A3:E32. On the Data tab, click Filter. • Click the filter arrow in column D. Point to Filter by Color in the AutoFilter menu that appears. Click the green flag under the Filter by Cell icon. Data formatted with a green flag (highest number of work hours) is displayed. • Click the filter arrow in column D. Point to Filter by Color. Click the red flag under the Filter by Cell icon. The data formatted by a green flag is replaced in the worksheet by data formatted with a red flag (lowest number of work hours). • Click Filter to remove the filter arrows. • LEAVE the workbook open to use in the next exercise.

30. Step-by-Step: Filter Data Using Cell Attributes • USE the workbook from the previous exercise. • Select any cell in the data range and click the Filter button in the Sort & Filter group on the Data tab. • Click the arrow next to the Title header (Contoso, Ltd.) and point to Filter by Color. Click More Font Colors. A dialog box opens that displays the font colors used in the worksheet.

31. Step-by-Step: Filter Data Using Cell Attributes • As in the figure below, the first color appears in the Selected field. Click OK. The heading rows are displayed. These are the colors in the Office theme that was applied to this worksheet.

32. Step-by-Step: Filter Data Using Cell Attributes • Click the filter arrow next to the Title header again and click Clear Filter From “Contoso, Ltd.” • Click the Title header filter arrow again and point to Filter by Color. Select Purple in the Filter by Font Color drop-down menu (3rd selection). Data for Dr. Blythe (new physician) and his two medical assistants is displayed because you chose the Purple font in your sort criteria. • Click the Filter button to clear the filter arrows. • CLOSE the file. You have not made changes to the data, so it is not necessary to save the file. • LEAVE Excel open to use in the next exercise.

33. Step-by-Step: Group and Ungroup for Subtotaling • OPEN the Salary data file for this lesson. • Select any cell in the data range. Click Sort on the Data tab. • In the Sort dialog box, sort first by Job Category in ascending order. • Add a sort level, sort by Job Title in ascending order, and click OK.

34. Step-by-Step: Group and Ungroup for Subtotaling • Select row 14, press Ctrl, and select row 27. On the Home tab, click the Insert arrow in the Cells group and click Insert Sheet Rows from the drop-down menu that appears. This step inserts rows to separate the job categories. Refer to the figure.

35. Step-by-Step: Group and Ungroup for Subtotaling • In C14, key Subtotal. Select F14 and click the Sum icon (also shown in the figure on the previous slide). The values above F14 are selected. Press Enter and Excel subtotals the category. • In C28, key Subtotal. Select F28 and click Sum. Press Enter. • In C36, key Subtotal. Select F36 and click Sum. Press Enter. • In C37, key Grand Total. Select F37 and click Sum. The three subtotals are selected. Press Enter.

36. Step-by-Step: Group and Ungroup for Subtotaling • Select a cell in the data range. On the Data tab, click the Group arrow in the Outline group, and then click Auto Outline from the drop-down menu. A three-level outline is created. The figure shows your worksheet complete with subtotals and grand total. To view your worksheet as in the figure, adjust your zoom to 75%. • LEAVE the workbook open to use in the next exercise.

37. Step-by-Step: Subtotal Data in a List • USE the worksheet from the previous exercise. • In the Outline group on the Data tab, click the Ungroup arrow and then click Clear Outline. • Select rows 14, 28, 36, and 37, right-click and Delete all selected rows. Remember, to select nonadjacent rows, click the first row then press and hold the Ctrl key and proceed to click the additional rows you wish to select. • Select the data range (A3:F33), including the column labels in the selection. • Click Subtotal in the Outline group on the Data tab. The Subtotal dialog box is displayed. • Select Job Category in the At Each Change in box.

38. Step-by-Step: Subtotal Data in a List • Under Add Subtotal To, Salary should already be checkmarked. Click OK to accept the remaining defaults. Subtotals are inserted for each of the three job categories, and a grand total is calculated at the bottom of the list. The figure has the outline groups with subtotals and grand total. • SAVE the workbook as Salaries. • CLOSE the workbook, but LEAVE Excel open for the next exercise.

39. Step-by-Step: Format a Table with a Quick Style • OPENSalary from the data files for this lesson. • Select any cell in the data. Click Sort on the Data tab. • In the Sort dialog box, sort first by Job Category in ascending order. • Add a sort level and sort by Job Title in ascending order. Click OK. Your data has been sorted first by Job Category and then by Job Title. • On the Home tab, click Format as Table in the Styles Group. The Table styles gallery opens.

40. Step-by-Step: Format a Table with a Quick Style • Mouse over the Table styles to Table Style Medium 5 from the gallery. The Format as Table dialog box opens. • Click the hide dialog box iconin the Where Is the Data for Your Table? box to collapse the Format as Table dialog box so you can select the data to be included in the table.

41. Step-by-Step: Format a Table with a Quick Style • Select A27:F32 as shown in the figure and press Enter. The Create Table dialog box appears. Your table does not have headers, so click OK. The Table Style Medium 5 format is applied and filtering column headers are inserted as illustrated in the figure on the next slide.

42. Step-by-Step: Format a Table with a Quick Style • SAVE the workbook as Table in the Lesson 7 folder. • LEAVE the workbook open to use in the next exercise.

43. Step-by-Step: Use the Total Row Command • USE the workbook from the previous exercise. • Select a cell inside the table and click the Total Row command box in the Table Style Optionsgroup on the Designtab. A row is inserted below the table and the salaries in column F of the table are totaled. This is illustrated in the figure.

44. Step-by-Step: Use the Total Row Command • Click any blank cell to deselect the table. Adjust the column width to display the total amount if necessary. Click on any cell inside the table. • SAVE the workbook. • LEAVE the workbook open to use in the next exercise.

45. Step-by-Step: Add and Remove Rows or Columns • USE the workbook from the previous exercise. • On the Design tab, in the Properties group, click Resize Table. The Resize Table dialog box opens. • Collapse the Resize Table dialog box by clicking the collapse dialog box button, and select A27:F35. Press Enter to accept the new range of cells. Click OK to accept the change to the table and apply the new settings. The physician assistant data is moved above the total line, and the total is recalculated. Refer to the figure.

46. Step-by-Step: Add and Remove Rows or Columns • Select C28. On the Hometab, click the Delete arrow in the Cells group, and click Delete Table Columns from the drop-down menu that appears. Column C is deleted. Refer to the figure for the drop-down menu. • Click the Column1 heading and key Last Name. Press Tab to move to the Column 2 heading. Key First Name in that column heading and press Tab to advance to the next heading. Key Job Title in the column 4 heading and press Tab.

47. Step-by-Step: Add and Remove Rows or Columns • Key Hours in column 5. Press Tab. In the Invalid Entry dialog box that displays, click Yes to continue. Clicking Yes here will override the data restrictions and dismiss the Invalid Entry dialog box. Key Salary in the column 6 heading and press Enter. Your table headings should appear as illustrated in the figure.

48. Step-by-Step: Add and Remove Rows or Columns • If necessary, adjust the column E width to display the total salary amount. • In the Properties group on the Design tab, select the text in the Table Name box and key Schedule and press Enter. This table represents the individuals with whom patients schedule appointments. • SAVE the file and then CLOSE the file. • CLOSE Excel.

49. Lesson Summary