Excel for Student Tracking

Excel for Student Tracking

Presented by Victoria Rasmussen

UEN Professional Development

Session Description: For the true beginner! Learn very basic Excel skills, and create a simple spreadsheet for tracking student information.

Anatomy of a Spreadsheet

Cell – where a row and a column intersect, named A1, A2, B4, B212, etc.

Row – numbered

Column – alphabetized

Range – a group of cells specified by upper left cell address and lower right cell address like this: (A1:Z50)

Worksheet or Sheet – a tab in the workbook, may be multiple pages

Workbook – an entire document or file that may include many worksheets

Plan your spreadsheet BEFORE you begin entering row and column headings. Consider what you will want to total, where you will put labels, how your data can be best organized so that you can add to it or replace it conveniently.

Data Entry

Type characters in a cell. Use the tab key to move across and the enter key to move down. Or, use the arrow keys to move from cell to cell.

Note that you are not limited to the space of the cell displayed on the screen. Text may run across several cells, but may not print correctly unless you use the Format Menu options to wrap text, or change the size of the cell.

Cell contents may run across an unlimited number of cells, but unless you wrap the text, they will not move down. If you use the enter key to force a line break, you move into a new cell.

Quick Tips

1. Select cells or data and use copy, cut, paste and format features as in any Windows program.
2. To drag and drop: position the cursor on the EDGE of a selected area and drag.
3. The Fill Handle is a small box in the lower right corner of any selected cell area. Click on this “handle” and drag to fill cells with repeated information, or to continue a sequence of information such as dates.
4. Double-click the separator between column letters to automatically adjust the column width to show all data. Drag the separator to size the column manually.
5. Use Ctrl + click to select non-adjacent cells for charts and formatting.

Basic Functions

• When you enter a function in the cell, only the result displays on screen and printout. To edit a formula, use the formula bar (bottom tool bar). Click the cell so the formula displays in the bar.
• To enter a function in a cell, always begin with the equal sign =.
• You may then either enter numbers or cell addresses. Use cell addresses if the number might change as you develop the sheet.
• You need to understand the math function or formula you wish to use.
• Excel follows this “order of precedence” when calculating formulas: Exponentiation, Multiplication and Division, then Addition and Subtraction. Use parentheses to change the order of precedence.

Here are examples of how you would enter basic math formulas.

Sort

To sort a single column alphabetically or numerically in increasing or decreasing order:

• Click on the column letter to select the entire column
• Click either the “A-Z”  or the “Z-A”  button on the toolbar.
• NOTE: If you do this sort, the row items will not longer be together.

To keep cells on a row all together, but still put the rows in increasing or decreasing order, do the following:

• Select ALL OF THE COLUMNS.
• Click the Data menu, then Sort.
• In the dialog box, select the column heading by which you wish to sort.

Format Features

• Under the Format menu choose Cells. Change the format of numbers, the alignment including angle of text, merging and wrapping text and cell borders and colors. These features apply to any selected cell(s).
• Under the Format menu choose Rows. Hide or Unhide rows.
• Under the Format menu choose Columns. Hide or Unhide columns, and also set column width.
• Under the Format menu, choose AutoFormat. SELECT AREA TO FORMAT before choosing an AutoFormat. Otherwise, it will try to format the entire possible spreadsheet which will overrun the computer’s memory.

On the Toolbar there is a button for “Merge and Center” (immediately right of the alignment buttons). This is useful for page headings or titles. Select the cell containing title text, and the other cells you wish the title to span. Then, click the button. This “merges” all the selected cells and centers the text across them in one step.

Charts

To create charts, be sure that the category headings or labels are at the top and/or the left of the data you wish to plot. Then, select the headings and data you wish to chart. Click the chart creation button on the toolbar, or select Chart from the Insert Menu. Follow the steps in the wizard. Be sure you carefully consider the finished product – not all chart types are appropriate for all data, and sometimes, your data does not get represented in quite the way you would like.

Print Options

Print Preview, under the File Menu, is extremely important in Excel. It is the only way you will know what the printout looks like. The Excel worksheet does not always print the same way it displays. After you once look at Print Preview and then return to normal view, you will see a dotted line indicating where page breaks are located.

Many spreadsheets lend themselves to a landscape paper orientation – wider than they are tall. Also, in some cases, you may want to include the “gridlines” in the printed copy of the worksheet. Under the File Menu, choose Page Setup. In the dialog box, paper orientation is under the “page” tab. Gridlines may be added under the “sheet” tab. You can also set margins and add a header and footer to your printout in the Page Setup dialog box.

Guidance for Creating a Grade Book

Although you can design your grade book spreadsheet in any way you wish, most people create a layout very similar to the traditional paper grade book.

1. Leave two or three rows at the top for your document heading(s).
2. In the first row, list the assignments, tests, etc. for which students will have scores. These are your column headings. Be sure that the data collected is consistent. For example, if tests are scored with points, homework should also have point values assigned rather than a checkmark.
3. You may wish to list the total points possible on a second row under the headings. Avoid mixing text and numerical data in a single cell, since you can’t calculate using cells that have text in them (and you may want to total the points possible at the end of the row).
4. In the first column, enter students’ names. Generally, it’s preferable to enter the first name and last name in separate columns.
5. Totals may be calculated in the right most column of your design. For example, you may have 4 test columns, 10 homework columns, and then in column 17 total all the points. Column 18 can be used to average the columns, or for other calculations or data.
6. Sometimes people want to use an “IF” statement formula to convert numerical scores or percentages to letter grades. Although this is possible, it generally takes much more time that it is worth. The “IF” statement must “nest” the response for each grade range, so if you use an A-F scale with plusses and minuses, you have to combine 12 IF statements. It’s usually easier to “eyeball” and type in the letter grade manually. The “Vlookup” function can automatically assign a letter grade for a percent or point total. This function is a little harder to understand, but can be helpful and save time in the long run.
7. Some people prefer to have all information in one sheet with student names listed only one time. However, if you have a lot of student information this can be cumbersome. Consider copying the two columns of first and last names and pasting them into a second worksheet. One sheet can be used for tracking scores, and another sheet for tracking contact information or other non-numerical data.
8. Making charts for an individual student’s progress, or to compare all the students in a class is relatively easy. Line charts show change over time (progress of one student). Column charts make it easy to compare individuals, or individual performance on several assignments. With some additional data manipulation, Pie charts can compare groups. For example, count the number of students who passed a test and the number who failed. Select the number failed and the number passed and chart it as a pie with two segments.
9. Add worksheets for new time periods, for example have one sheet that covers quarter one, a second sheet for the second quarter, etc. Consider incorporating another worksheet that shows only the students’ names and their quarter totals.
10. Pivot tables are an advanced feature of Excel, but Microsoft recommends their use to help you “query” data in the spreadsheet.

For an in-depth tutorial on creating a grade book with Excel, refer to the Microsoft Web site: