Lesson 5

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.

Figure 1

​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).

Figure 2

STEP 3: ​The header row with the temporary header field labels has been removed from the CUSTOMERS table (FIGURE 3).

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).

Figure 4

STEP 4C: We have removed the empty Row 1 (FIGURE 5)

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).

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.

Figure 7

STEP 7: We will receive a notification of any duplicates found and removed. Select the OK button (FIGURE 8).

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).

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).

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.

Figure 11

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.

Figure 12

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.

Figure 13

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.

Figure 14

STEP 14A: Select Column A header.
STEP 14B: Right-button mouse click Column A and select “Delete” from mouse menu (FIGURE 15).

Figure 15

STEP 15: In cell address A1, enter “Cust#” as header label for Column A data (FIGURE 16).

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).

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).

Figure 18

STEP 18: Enter “Maple Ave” into cell address H2 (Figure 19).

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).

Figure 20

STEP 20: Column H has populated with all of the other street names in Column G (FIGURE 21)

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).

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).

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).

Figure 24

STEP 24: Column H has populated with all of the other street address numbers as in Column G (FIGURE 25).

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).

Figure 26

STEP 26: Rename Column H header, “StAddressName” (FIGURE 27).

END OF LESSON


Back to: Intro to Excel 2016 Data Analysis > Module 2 – Intro to Excel 2016 Data Analysis

Leave a Reply