Data Cleaning with Excel

Custom lists are used to make sort orders appear in Excel in a specified way. For instance, if you had a column where the data values were the spelled-out months of the year and sorted it, the order would be alphabetical (April, August, etc.) rather than the order in which the months occur in the year. Here we'll demonstrate how to apply a custom list while also doing more data wrangling on the Months column from the Splitting Multi-Valued Cells exercise. If you completed that exercise with the NEISS small subset dataset, you should have a column called Month with the months represented by their numbers.

Steps to Apply a Custom Sort to the Month column

• Insert a new column next to Month called 'Month Spelled Out'
• In the first cell (row 2) enter the formula =TEXT(E2*29,"mmmm") This converts the number representing the month to the spelled out month name
• Double click in the lower right corner to copy this formula down the spreadsheet

• Next, in the Ribbon go to File, then Options, then Advanced, then General, then click the 'Edit Custom Lists' button. This is simply to demonstrate how to find Excel's built-in collection of custom lists. A list already exists for the months of the year spelled out. Notice that there is an option to create a new list, which will be saved in the system for future use. Click OK.

• Back in the worksheet, go to the Data Tab on the Ribbon, then Sort. In the dialog box that pops up, check that your data has headers, for Sort By choose the Months Spelled Out column, and in the order option choose Custom List. Choose the months option.

• The result will be that your spreadsheet's data will now sort in the order in which the months occur in the year.