This lesson will demonstrate how to sort and format data downloaded from Blackboard and reorganize the data into a more workable format. (The instructions for this step were developed for Excel 2010. Appearances and procedures may vary for different versions)
Opening the Data
Open the Excel spreadsheet that was downloaded from Blackboard. If you receive a warning saying that the document is in a different format than specified by the file extension, click Yes and open the file.
Selecting Data
Click the button in the upper left hand corner of the workbook to select all the data (as indicated by the red arrow in the image above) or use Control+A (Command+A for Mac) on your keyboard.
Sorting Data, Part 1
In order to make use of the downloaded data, the data must be sorted, and the like data items must be grouped together (e.g. answers, questions, etc.)
- Click the Data tab
- Click on the Sort button.
The Sort dialog box will now appear on screen.
Sorting Data, Part 2
- In the Sort dialog box, click the Options button. The Sort Options box will appear.
- Change the selection to Sort Left to Right.
- Click OK. You will be taken back to the main Sort dialog Box.
- Under the option labeled Sort by, Select Row 1 from the drop down menu. Leave the other menus as they are.
- When you are finished, click OK. The data will now be sorted from left to right.
Cleaning up Data, Part 1
It is now time to clean up the sorted data. The only columns we need to keep are the Answers columns. The remaining colums can be deleted.
To delete the columns, select all the columns except for the Answers columns. Right click on the header of one of the selected columns, and choose Delete from the menu.
At this point is is recommended to save the file with a different file name.
Cleaning up Data, Part 2
In order for the results to be analyzed, the responses must be converted to a numeric format. (E.g. convert "Excellent" to 5, and "Very Poor" to 1)
To replace the data, press Control+F (Command+F for Mac) to bring up the Find and Replace dialog box.
- Click on the Replace Tab
- Enter the text in the spreadsheet you wish to replace
- Enter the replacement value
- Click Replace All
The existing data in the spreadsheet will then be replaced. Repeat this step until all data is in a numeric format. It is also recommended to change the column heading from "Answer" to something that would be more helpful.
Once the data has been cleaned up, save a final version of the spreadsheet.