Formatting Dates
The dates in the column “Date (YYYY-MM-DD)” must be formatted as YYYY-MM-DD. If the dates are entered in another format (e.g., 12/31/1917), follow these steps to correct them:
- Select the column by clicking on its letter.
- Right click and choose “Format Cells.”
- Choose category “Date” and then under “Locale (location),” select an option that offers YYYY-MM-DD in the list of examples. (One such choice is Belarusian.) Click “OK.”
- Most dates will be automatically converted. However, you will have to look for any exceptions (such as dates with month and year only) and correct them manually.
- When all dates have been properly formatted, copy the column values and paste them into Notepad.
- In Excel, again select the entire column and “Format Cells.”
- Choose category “Text” and click “OK.”
- Copy the values from Notepad and paste them back into the Excel “Date (YYYY-MM-DD)” column.
Cleaning an Entire Excel Spreadsheet
- First open the spreadsheet you wish to clean.
- Then open a new blank spreadsheet.
- In the new spreadsheet, highlight cell A1.
- Click on the “Formulas” tab and “Insert Function.”
- Choose function “CLEAN.”
- When the “Function Arguments” window is displayed, toggle to your original spreadsheet.
- Place the cursor in cell A1 of your original spreadsheet and select all rows and columns (including the column headings) using the “Shift” and “Arrow” keys. Note the position of the last row and column, as you will need it in step 9 below. (Excel will display this information in the “Function Arguments” window.) Click “OK.”
- Switch back to your new spreadsheet, where the formula will be displayed in cell A1. Click “Copy” to copy the formula in cell A1.
- Use the “Arrow” keys to select the appropriate number of rows and columns in the new spreadsheet.
- Click “Paste” and your spreadsheet is populated with the cleaned data.
- You can make edits to the original spreadsheet, and these will be reflected in the cleaned version as long as the cleaned spreadsheet is closed while you edit. The next time you open the cleaned version (with the original closed), it will simply ask you to update the data links.
6/3/14 J. Dean
