SPREADSHEET FORMATTING

Before opening the email, turn off the "wrap long lines" feature in your email software. To do this, from the Menu bar (File Edit Mailbox . . .) select Tools, Options, Sending Mail or Composing Mail, depending upon your software. It will open an Options dialog box. Then Click the word wrap feature to OFF, then Enter or OK.

Open the email.

When viewing the email that contains your class list, you'll see the confirmation of the semester, campus, course, section, and schedule number.

You will NOT, however, see the meeting days and times, nor the classroom assignment. You will be able to confirm when and where the course meets using this website: http://soc.our.psu.edu/soc/

See page 43 of this document for more information about confirming your class meeting days and times, and room assignment.

Highlight the portion of the email that contains your raw data. To highlight, click, hold and drag the cursor to the end of the selected text.

Copy the data using one of the following methods:

Ctrl+C,

OR

Edit, Copy from the file menu,

OR

the Copy icon if your software has one.

Note: the link shown in the above diagram goes to the "Help for Class Lists" document described on page 7.

Open a NOTEPAD file. From the Start button in the bottom left-hand corner, go to Programs, Accessories, Notepad.

Paste the highlighted information from your email into the blank document using Edit, Paste from the Menu Bar.

Save the NOTEPAD file as a TEXT file (.txt). From the menu bar: File, Save As. This will open the Save As dialog box.

In the Save In section, use the pull down menu to go to the desktop.

In the File name, give your NOTEPAD file an appropriate name.

Under Save as Type use the pull down menu to select "Text Documents (*.txt)

Click Save or enter to save your file.

Open EXCEL, using the icon on the task bar (icon with green "X" in top right corner of the previous page's frame. Or use the START button in the lower left corner of your screen and go to Programs, Microsoft Office (or MS Office, depending upon your set up), the Microsoft Excel. See the frame on page 14 as a guide.

Then open a new document using one of the following methods:

From the Menu bar: File, Open OR Ctrl+O OR the open icon

In the Open dialog box:

Use the pull down menu in the Look In section to go to the desktop files (or to the directory to which you saved the text file).

AND from the lower left corner, use the pull down menu to define Files of Type as "Text files." You will not see the file appear until you define Files of Type as "Text!"

When you see the text file you want to open, either click once, then Open or enter, or open the file by double clicking on the name of the file.

Immediately a dialog box called Text Import Wizard will appear.

Step 1

As the Original Data Type, you will want to select "Delimited." Excel will guess (correctly!) that your

Start Import at Row and File origin should remain as their default values.

Then click Next at the bottom.

Text Import Wizard, Step 2

Will ask you to select a type of delimiter. In the Delimiters section, deselect "Tab" and select "Comma."

Treat consecutive delimiters as one should not be checked.

Confirm that the Text Qualifier if a double quotation mark (").

Then go to the next step by clicking Next.

Text Import Wizard, Step 3

This step allows you to define the columns appropriately. Highlight the column to define the column data format by either:

Clicking the radio button in the Column Data Format section

OR

Highlighting the column and using the appropriate letters as defined in the Column Format Data section:

G= General

T= Text

D=Date

I=Skip

Note: it is extremely important that you select the first column, student ID, as a "text" column. If you do not define this column as "text," you will lose all leading zeros.

Continue to highlight columns and select each column as "text." Click on Finish.

Microsoft Excel automatically puts the data into columns.

The next step is to add column titles. Refer to the printout called "Help for Class Lists" that you printed earlier for field names and descriptors. Or see Appendix A.

To add the necessary blank row for column titles:

The active cell should be A1. There are two ways to tell which cell is the active cell: the cell will have a heavy border around it. AND the active cell will be referenced above column A. A1 is the active cell in the frame below.

To insert a row, from the menu bar: Insert, Row

A row of blank cells will appear in line 1.

Type column names per the "Help for Class Lists" that you printed from eLion earlier. Abbreviate as you see fit.

NOTE: You'll see in the example below that Columns I & J contain no information. We do not recommend that you delete the columns at this time. We do recommend that you title them as indicated in the Help for Class Lists document.

If you prefer not to view the empty fields, we recommend hiding the blank columns of audit and Graduation Indicator.

To hide columns, you'll need to highlight the columns first. Place the cursor over the column letter you'd like to hide. If hiding more than one consecutive column, you may click and drag.

Then from the menu bar, select Format, Column, Hide

Note: To once again show these columns, highlight the shown columns before and after the hidden columns, then select, Format, Column, Unhide. If Columns I & J were hidden, you'd highlight Columns H & K to unhide columns I & J.

To make your list easier to read, resize your columns. There are several ways to do this.

Method #1

Highlight your entire document. To do this, place your cursor in the empty space above Row 1 and to the left of Column A, then click. The empty cell will be the same color as the column letters and row numbers when highlighted correctly.

Then place your cursor on the dividing line between any two column letters, e.g., between the columns heading rows "A" and "B" and double click. You'll know that your cursor is in the correct location when it changes from a white cross to a thin black cross with left-right arrows.

Method #1 has the advantage of speed—it resizes all of your columns to fit existing records at once.

Method #2

This method is similar to Method #1, but resizes columns individually.

Place your cursor on the dividing line between any two column letters without highlighting the entire column, e.g., between the columns heading rows "A" and "B" and double click. Again you will know when your cursor is in the correct position because it will change from a white cross to a thin blank cross with left-right arrows. When you double click, excel resizes the column immediately to the left of where your cursor lies.

This method is useful when you've resized the entire document and one column is wildly out of kilter with the rest of the document. Example, one of the student's names is extremely longer than the others. Even if the field length was shorter, and part of the field contents were not shown, you would still know which student's record you'd be viewing.

You may also click the right boundary of a column header letter and drag to resize the column.

Method #3

This method is the most cumbersome to use, but the easiest to explain with diagrams.

Highlight the column you'd like to resize by single clicking on the empty rectangle above row 1.

From the menu bar, Format, Column, [make appropriate selection from options].

Here's what the document looks like resized using Method #1, above:

Optional Enhancements using the Page Setup function:

Change the page orientation to have the entire list print on one page. There are a few ways to do this.

Method #1

From the menu bar: File, Page setup

This opens the Page Setup dialog box. In the Page section, change the Orientation from "Portrait" to "Landscape."

In the Margins section of the Page Setup box, you'll need to experiment with what margins will fit the printout onto one page. If you've decrease the margins as much as possible, and are still unable to fit the entire printout onto one page, you'll need to go back to the previous step to resize the columns to fit.

Hint: You can also change the margins from the Print Preview command.

Use Print Preview button in the dialog box

OR

icon on the toolbar

OR

from the menu bar: File, Print Preview.

In the Header/Footer section of Page setup, you can add titles, file names, dates, etc, to appear at the top and bottom of each page of your printout.

The Sheet section of Page Setup contains many features you may choose to or choose not to use.

The most commonly used features on this page are "Rows to repeat at the top" and "Gridlines."

"Rows to repeat at top" is especially useful if you have a long list of students in your class that won't print all on one page. The format to define the rows to repeat is n:n. If you only want to repeat row 1 at the top of each page because it contains the column names, the format is "1:1." Entering "1" results in an error message.

The other nice feature on this page is whether or not you'd like to show gridlines. Simply click the feature on or off to your liking.

Click OK when finished.

This is a sample of the document after cosmetic changes have been made.

Save your document using the save command above.

In the Save As dialog box, under Save as Type, don't forget to save your document as an "Microsoft EXCEL Workbook", not as a "text" document. For instructions to change the file type, refer to page 17.

To print your class list, either

From the Menu Bar: File, Print, OK

OR Ctrl+P

OR use the print icon