Preparing An Excel Roster File

Follow instructions to access the Class List - Name Only in Pipeline. Once your roster is displayed on the screen, drag your cursor over the entire table, and choose Edit - Copy from the menu bar. Open Excel and choose Edit-Paste from the menu bar. Delete the columns you don't need and you are now ready to add your grades.

Once all your students are listed in the Excel worksheet, you have to modify the file a bit. Immediately, all your data is listed in the first column as one big field.

Click on the letter A in the top gray row to select the whole column.
Go to the menu bar and choose Data
Select Text to Columns
A wizard will start to walk you through the process of dividing up your data into columns. Choose Fixed Width on the first screen and press the Next button at the bottom. You will see lines dividing up the data. Choose Next-Next and Finish. Now the data is separated into columns.
Go to File - Save and type in a meaningful filename next to Filename. (Take note of where you are saving the file by looking at the top next to Save In – which will probably be My Documents, so you can find it again).
Choose Save As Type: CSV Comma Delimited (which is a text file type that will preserve the column breaks).
Answer Yes to the dialog box that pops up warning you about saving as a text file.
Click on the A in the first gray row again to select the whole column. This should be showing numbers 1-24 or a count of how many students are in the class. Select Edit from the menu bar and choose Delete.

Now your first column is probably showing the last name, first name and middle initial all in one column. You may want to split this information up. First you need to make room to split up the information.

Click on B in the first gray row and from the menu bar, select Insert - Columns
Click on C in the first gray row and from the menu bar select Insert - Columns
Click on D in the first gray row and from the menu bar, select Insert - Columns
You now have three blank columns.
Select the first column again by clicking on the letter A in the top gray row. Again go to the menu bar and choose Data, then select Text to Columns…
This time when the wizard starts, choose Delimited.
Click in the box next to Comma to select. Make sure nothing else has a check mark next to it.
Press Next-Next-Finish.

You should now see the last name in the first column (A) and the first name and middle initial in the second column (B). Scroll down in column C to make sure there is no stray data (move any JR or other initials into the First Name field with the middle initials). If you are planning on using this roster file in WebCT, scroll down and remove any hyphens, apostrophes, extra spaces etc. that may appear in names as these are invalid characters in WebCT.

You can now delete any extra characters from Column C by selecting the data and pressing the Delete key.
Delete columns that contain any enrollment information you don’t need by clicking on the letter at the top of the column in the gray row and choosing Edit-Delete.

Use the empty Column C to put in your user ids. If you do not have any duplicate last names, you can use students' last names as their user ids and passwords. To do this you can position your cursor in front of the first letter of the last name in the first row and drag down to select all the last names.

Select Edit-Copy from the menu bar
Position your cursor in the top row of Column C and select Edit-Paste

If you do have more than one person in your class with the same last name, you can easily create user ids that consist of the first initial of the student's first name along with their last name. Excel has a special function that does this automatically for you.

Click in the top row of the empty Column C.
In the formula bar, type in the following: =Concatenate (Left (B1,2),A1) and press the Enter key. This will go to the first row of Column B (which is your first name column) and take the first initial, then go to first row of Column A and add in the last name automatically.
Copy this formula down the rest of the column until all the user ids have been created.

Now you need to label your columns. To label the columns you need to create a blank row at the top of the page.

Click on the number 1 in the first row so that the whole row is selected.
Go up to the menu and choose Insert - Rows.
Now to put in the labels, put your cursor in A1 and type in Last Name.
Tab over to B1 and type in First Name.
Tab over to C1 and type in User ID
Tab over to D1 and type in Phone

NOTE: if you have more than one section of the same course you may want to create an additional column for the section number. You would tab to the next empty column and type in the label Section. Then type the section number in the first cell of the next row and copy it down to the rest of the rows.

You are almost done! Excel has the capability of saving the Social Security number with the dashes. If you want to preserve this look:

Select all the rows in the column that contain the Student ID numbers by selecting the E in the gray row.
Go to Format on the menu bar, choose Cells, select Special as the Category and choose Social Security number.
Go to File-Save to save your changes.

Now your grade book is all set and you are ready to put in your formulas.

Next: Upload to WebCT