Preview
Organize and Clean Data
Continue to use data file that was saved in Practice Exercise 2 as “XYZ SWITCHBOARD.xlsx”
Practice Exercise 4 – Organize and Clean Data in the CUSTOMERS Table
STEP 1: Select the CUSTOMERS table by selecting the “CUSTOMERS” tab at the bottom of the Excel screen (FIGURE 1). We will work on cleaning the data.
STEP 2: We have 2 header rows. Let’s begin cleaning our data by eliminating the top header row that contains the field names, i.e. Field1, Field2.
Ensure you have a cell in the CUSTOMERS table as the active cell.
In the Ribbon contextual tab, Table Design, uncheck the box “Header Row” (FIGURE 2).
STEP 3: The header row with the temporary header field labels has been removed from the CUSTOMERS table (FIGURE 3).
STEP 4A: We will delete Row 1 since it is empty. Select the Row 1 header to highlight Row 1.
STEP 4B: Right-button mouse click Row 1 and select “Delete” from menu (FIGURE 4).
STEP 4C: We have removed the empty Row 1 (FIGURE 5)
STEP 5A: Let’s check and remove any duplicates.
STEP 5B: Select the Data tab.
STEP 5C: From the Riboon section “Data Tools” select the icon, “Remove Duplicates” (FIGURE 6).
STEP 6A: On the “Remove Duplicates” dialog box, select “Select All”, which will check the boxes for all fields in the table (FIGURE 7).
Select the OK button.
STEP 7: We will receive a notification of any duplicates found and removed. Select the OK button (FIGURE 8).
STEP 8: In Column K of our CUSTOMERS table, we only want a regular 5-digit zip code without the 4-digit secondary portion following it, so we need to remove it (including the hyphen) (FIGURE 9).
STEP 9A: We will use the LEFT() function to remove the 5 right-most characters of our zip code data.
STEP 9B: We must insert an additional column to the right of our Zip code column first.
STEP 9C: Select Column L header which will highlight the column; right-button mouse click the highlighted column and select “Insert” from the mouse menu (FIGURE 10).
STEP 10A: Make cell address L2 the active cell (FIGURE 11).
STEP 10B: Enter: =LEFT(K2,5)
STEP 10C: Note: the K2 can alternatively be entered by selecting cell address K2 with mouse
Press Enter key.
STEP 11: We get the data we desired; we now have the number reproduced, without the hyphen and last 4 zip code digits (FIGURE 12).
All values in column lose the last 4 digits and hyphen of the zip code.
STEP 12A: Make cell L2 active (FIGURE 13).
STEP 12B: Press CTRL + Shift + down arrow. All data in Column L is highlighted.
STEP 12C: Right-button mouse click the highlighted data and select Copy
STEP 12D: Make cell K2 active. Press CTRL + Shift + down arrow. All data in Column K is highlighted.
STEP 12E: Right-button mouse click the highlighted data and select Paste – Values.
We have successfully copied our desired zip code into the “Zip” column K.
STEP 13: Now we can remove Column L. Select Column L header; right-button mouse click data and select “Delete” from mouse menu (FIGURE 14).
We see Column L zip code data has been deleted.
STEP 14A: Select Column A header.
STEP 14B: Right-button mouse click Column A and select “Delete” from mouse menu (FIGURE 15).
STEP 15: In cell address A1, enter “Cust#” as header label for Column A data (FIGURE 16).
STEP 16: In our CUSTOMERS table, we have a StAddress field where we have a need to remove the street address number and place it in a separate column (FIGURE 17).
STEP 17A: Select the “City” column header to highlight the column.
STEP 17B: Right-button mouse click the highlighted data and select “Insert” from the mouse menu.
We now have a newly inserted Column H (FIGURE 18).
STEP 18: Enter “Maple Ave” into cell address H2 (Figure 19).
STEP 19: Ensure that cell address H3 is the active cell. Select the “Data” tab; in the Data Tools section, select the “Flash Fill” icon (FIGURE 20).
STEP 20: Column H has populated with all of the other street names in Column G (FIGURE 21)
STEP 21A: Select Column H header to highlight Column H.
STEP 21B: Right-button mouse click the highlighted column and select “Insert” from the mouse menu.
We now have a newly inserted Column H (FIGURE 22).
STEP 22: Enter “StAddress#” in cell address H1.
Enter “2432” in cell address H2.
Enter “34245” in cell address H3.
Enter “1279” in cell address H4 (FIGURE 23).
eSTEP 23: Ensure cell address H5 is the active cell. Select the “Data” Tab; in the Data Tools section select the Flash Fill icon (FIGURE 24).
STEP 24: Column H has populated with all of the other street address numbers as in Column G (FIGURE 25).
STEP 25A: Select the Column G header to highlight Column G.
STEP 26B: Right-button mouse click Column G and select “Delete” from the mouse menu (FIGURE 26).
STEP 26: Rename Column H header, “StAddressName” (FIGURE 27).
END OF LESSON
Leave a Reply