Search: People | Departments | Penn State

Manage your Grades Electronically with Excel

TLT Home : TLT Suggestions

Main Menu

Enter Scores

This page will discuss the basics of entering data into a spreadsheet and formatting cells.

This Page

1. Insert a New Worksheet and Copy Names
2. Insert a Blank Row for Assignment Titles
3. Format Cells
4. Format Numbers as Percentage or Decimal
5. Adjust Rows and Column Size
6. Move and Delete Rows and Columns
7. Freeze Panes

Insert a New Worksheet and Copy Names

The eLion class list contains a variety of useful data columns, but, aside from the names, it is not needed for grading purposes. Therefore it is recommended that you cut and paste the extra data into another worksheet within the actual grading spreadsheet.

1. Open the Excel file with the text imported from E-lion and highlight all columns of data.
2. Cut the text (Control+X on Windows or Command+X on the Mac or use commands from the menu or toolbar).

3. Insert a blank worksheet into the Excel file. A blank grid will appear and a new tab will be visible at the bottom.

   Office 2007 (Win): On the Home tab in the Cells group, select Insert, then Insert Sheet
   Macintosh/Pre Office 2007 Win: To insert a worksheet, go to the Insert menu.

4. Move your cursor to cell A1 (top-most left cell) and and paste to insert the student names.
5. Return to the original worksheet by selecting the appropriate worksheet tab at the bottom. Now you can delete all the data except for student names.

Top of Page

Insert a Blank Row for Assignment Titles

To insert a blank row for listing names and assignments, do the following:

1. Highlight row 1 (the topmost row) with your cursor.
2. Right-click (or Command-click on a Mac) and select Insert.
3. A blank row will be inserted at the top. Fill in each cell to identify the column as needed.

Top of Page

Formatting Cells

There are several options for formatting text in a cell.

Office 2007 (Windows)

The basic formatting tools are available in the toolbar. For more options, press Shift+Control+F or click the arrow icon next the Font tab label in the toolbar. A pop-up window with additional options will open.

Office 2008 (Macintosh)

The formatting tools are available in the formatting palette. If the palette is not open, go to View menu and check Formatting Palette. For additional options press Command+1.

Older Versions of Excel

The basic formatting tools are available in the toolbar. For more options, go to Format » Cells or press Control+1 in Windows or Command+1 on the Mac.

Top of Page

Format Cells as Percentage or Decimal

1. Highlight the cell or range of cells you wish to format, then press Control+1 (Windows) or Command+1 (Mac).
2. Select the Numbers tab.
3. Select the Percentage category to the left if you wish to display fractions as percentages (e.g. .9 as 90%). You can select a fixed number of decimal places as desired.
4. Select the Numbers category if you wish to show a fixed number of decimal points for a score or grade. You can also change the format of negative numbers here.

Note: These are also available through the formatting toolbars.

Top of Page

Adjusting Row and Column Size

To resize columns, move your cursor between the column letters until you see a double-arrow icon. Then hold down the mouse button to adjust the column width.

For rows, move the cursor between row numbers until you see a double-arrow icon.

Top of Page

Move and Delete Rows and Columns

1. To move a row, highlight the row number to the right, then right click (Control+click on a Mac) and choose Cut. The row will be highlighted with "marching ants" (moving dots).
2. Move your cursor and highlight the row BELOW where you wish to move the row. Right click (Control+click on a Mac) and choose Insert Cut Cells.
3. To move a column, highlight the column letter on top, then right click (Control+click on a Mac) and choose Cut. Move your cursor to the row to the RIGHT of the insertion point, then right click (Control+click on a Mac) and choose Insert Cut Cells.
4. To delete a row, highlight the row number to the right, then right click (Control+click on a Mac) and choose Delete.

CAUTION: When removing student data (e.g. a late drop or a deleted assignment), it is better to move the data than to delete it in case an grading issue arises later.

Freeze Panes

If your course has many assignments and grades or a large number of students, it may be convenient to "freeze" the top and left so that you can always see assignment titles on the top and student names on the left. To freeze these areas.

Office 2007 (Windows)

1. Place the cursor in the cell to the RIGHT of the last column you want to freeze and BELOW the last row you want to freeze. For instance, if the student names are in Column A and assignment rows are in Row 1, then your cursor would be in Cell B2.
2. Click the View tab to open the view options.
3. In the Window group to the right, click the Freeze Panes drop down menu and select Freeze Panes.
4. To unlock the freeze, click the Freeze Panes drop down menu and select Unfreeze Panes.

Macintosh/Pre Office 2007 (Win)

1. Place the cursor in the cell to the RIGHT of the last column you want to freeze and BELOW the last row you want to freeze. For instance, if the student names are in Column A and assignment rows are in Row 1, then your cursor would be in Cell B2.
2. Once the cursor is in the correct location, select Window » Freeze Panes. You will see a dark horizontal and vertical line corresponding to the zones which have been "freezed." As the cursor moves to the far right or bottom, the frozen areas will remain static.
3. To unlock the freeze, select Window » Unfreeze Panes.

Top of Page

Last Update: May 17, 2010