Sort and Filter Data Using Excel

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

Image of a dialog box that appears when opening the results downloaded from Blackboard.  The Yes button is outlined by a red circle.

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

Image of a bortion of an Excel Workbook with an arrow pointing to the upper right corner with instructions to click there to select all 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

Image of the Data Ribbon in Excel with an arrow pointing to the Sort button.

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

  1. Click the Data tab
  2. Click on the Sort button.  

The Sort dialog box will now appear on screen.

Sorting Data, Part 2

Image of the Sort dialog box with the following annotations: 1.In the Sort dialog box, click the Options button.  The Sort Options box will appear.2.Change the selection to Sort Left to Right.3.Click OK.  You will be taken back to the main Sort dialog Box.4.Under the option labeled Sort by, Select Row 1 from the drop down menu.  Leave the other menus as they are.5.When you are finished, click OK. The data will now be sorted from left to right.
  1. In the Sort dialog box, click the Options button.  The Sort Options box will appear.
  2. Change the selection to Sort Left to Right.
  3. Click OK.  You will be taken back to the main Sort dialog Box.
  4. Under the option labeled Sort by, Select Row 1 from the drop down menu.  Leave the other menus as they are.
  5. When you are finished, click OK. The data will now be sorted from left to right.

Cleaning up Data, Part 1

Image of an excel spreadsheet with annotations indicating users to keep the columns labeled Answers, and to select the remaining columns, right click on them, and then click Delete.

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

Image of the Find and Replace dialog box with the following annotations: 1.Click on the Replace Tab2.Enter the text in the spreadsheet you wish to replace3.Enter the replacement value4.Click Replace All

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.

  1. Click on the Replace Tab
  2. Enter the text in the spreadsheet you wish to replace
  3. Enter the replacement value
  4. 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.