2.2 Organize and Clean Data Using Data Tools Section on the Ribbon (Using the Data tab)
Flash Fill – good for splitting up data when too much is in one column. This is similar situation to Text to Columns but resolved differently. In an adjacent column, enter the zip code of the first listing of data in an adjacent column. Select “Flash Fill” feature on Data Tools section. Excel will enter all of the remaining zip codes below the first.
Remove Duplicates – checks and automatically removes duplicates in a table.
Data Validation – shows instructions for the Excel user to input data within a specific criteria range. If data enter is outside of that range, it is not allowed and the user is prompted to reenter within the range.
Consolidate – good for combining multiple sheets of data into one sheet
Relationships – Shows relationships (linked tables through a common field) between tables or create a new relationship by linking 2 or more tables together through a common field).
Manage Data Model – interfaces with the Power Pivot Excel Add-in, that allows you to create more sophisticated pivot tables or add larger datasets that a workbook can not handle directly.
Practice Exercise #3 Organize and Clean Data in the EMPLOYEES Table
STEP 4A: We can use just about any of the fields to sort on. We will use Last_Name (FIGURE 4).
STEP 4B: For the “Sort by” option, select the Last_Name choice in the drop-down menu.
STEP 4C: For the “Sort On” option, leave the default choice, “Cell Values”.
STEP 4D: For the “Order” option, leave the default choice, “A to Z”.
STEP 4E: Select OK button
STEP 10: Select First_Name column column header to highlight it. Right-button mouse click it and select Insert from mouse menu. Inserted column appears in Column B with Column1 header label.
STEP 11A: In cell address B2: begin by entering the equal sign = since this is a formula (FIGURE 10).
STEP 11B: Enter: RIGHT(A2,7)
STEP 11C: Click on cell address A2 with mouse, since that is the first number we want to change. A2 appears in the parenthesis.
STEP 11D: Enter a comma, and then a 7. We want to enter 7 since that is the number of characters we want to retain from the right side of the data.
STEP 11E: Then enter the closed parenthesis )
STEP 11F: Press the Enter key.
STEP 13A: Move mouse down to bottom right corner of cell address B2 until we see the small thin black cross (FIGURE 12).
STEP 13B: Once you get that cross, keep mouse still and double-click mouse.
STEP 13C: All numbers in the Employee_ID column will be reproduced in Column1 without the first 2 numbers and hyphen.
STEP 14A: Make cell B2 active. Press CTRL + Shift + down arrow. All data in Column B is highlighted (FIGURE 13).
STEP 14B: Right-button mouse click the highlighted data and select Copy.
STEP 14C: Make cell A2 active. Press CTRL + Shift + down arrow. All data in Column A is highlighted.
STEP 14D: Right-button mouse click the highlighted data and select Paste – Values
END OF PRACTICE EXERCISE 3