Electronic gradebook in excel tutorial

Review The Excel Resources In The Week 5 Resources Module And Complete The Simple Excel Tutorial Prior To Completing The Assignment. The Class Gradebook Assignment Must Be Completed Using Microsoft Excel Or Equivalent Spreadsheet Software. Save This Ass

Simple Excel Tutorial

Probably the most recognizable difference between a word-processing document and a spreadsheet is that a word-processing document uses the paragraph as the standard method of presenting information, and the spreadsheet uses rows and columns. The reason is simple - most data entered onto a spreadsheet is numerical and is most easily read, understood, and manipulated when the numbers are presented this way.

In this exercise you will learn how to:

· Create a grade book.

· Enter text into a worksheet.

· Enter data into a worksheet.

· Format text and data.

· Create simple formulas.

· Save worksheet files.

Exercise: Creating a Grade Book

Objective

It's the beginning of the year and you want to create an electronic grade book to keep track of student achievement and do more sophisticated reporting of student progress. You will use the Microsoft Excel program to accomplish these and other tasks by entering the student information onto an Excel worksheet.

Create it

1. Open Microsoft Excel.

Entering information onto a worksheet

1. Click cell A1 to select it.

2. Type your name and press enter.

3. In cell A2, type the name of your class.

4. Press the down arrow key to go to cell A3.

5. Type in Day/Time the class meets.

6. Press the down arrow key to go to cell A4.

7. Type in Spring 2011.

8. Press enter twice to go to cell A6.

Next you'll add some headers for the types of entries you're going to make

1. Type Last Name

2. Press the tab key.

3. Type the following items in the following cells, pressing tab after each entry:

Cell
B6

C6

D6

E6

F6

G6

H6

I6

J6

Label

First Name

HW-1

HW-2

HW-3

Quiz-1

Quiz 2

Mid-Term Test

Average Score

Grade

Notice that not all entries fit neatly into their cells. You will return to adjust the size of the columns in a few moments.

Formatting text

1. Right-click cell A1.

2. Click Format cells. The Format cells dialog box appears, where you can enhance cells in a variety of ways.

3. Click the Font tab, if it's not already selected.

4. In the Font Style box, click Bold.

5. In the Size box, scroll down and click 14.

6. Click OK.

7. Highlight cells A2 through J4 by clicking A2 and dragging to row J4.

8. Right-click the selection.

9. In the Shortcut menu, click Format Cells.

10. Click the Alignment tab.

11. Under Horizontal Text Alignment, select Center Across Selection.

12. Click OK.

13. With these cells still highlighted, click the arrow next to the Font Color button on the Formatting toolbar, and click the Light Blue color.

14. On the Formatting toolbar, click the Bold button.

15. Click the Font Size box and then click 16.

16. Select row 6 by clicking on the Row Header (the numbered gray area to the left of row 6).

17. On the Formatting toolbar, click the Bold button and the Italic button.

18. Click the Center button to center the headings in each cell.

Changing the column widths

1. Select columns A through J by clicking the Column Header for column A and dragging across to column J. Selected columns are highlighted.

2. Move the mouse pointer over the border between any two column headers until it changes to a double arrow.

3. Double-click to automatically get the best fit for all the columns.

4. At this point your spreadsheet should look like this:

image1.png

Completing the data entry

It's time to fill in the names of your students and their scores. When finished, you'll do a little more formatting and add some formulas.

1. Type the following names into cells A7 through A11, pressing enter after each entry: Morton Bosco Wong Atwater Brown

** If the names above come up blue, you will need to reformat the text. Highlight the cells, right click on the highlighted region, and click “Format cells”. Click the “Font” tab, and check the “Normal font” box under the font size box.

2. Type the following names into cells B7 through B11: Myron Bill Hu Allyson Latavia

3. Next, complete the grid by entering the following data for each column: image2.png

4. Type Class Average in cell A13.

Sorting lists

1. Select cells A7 through H11.

2. Click the Sort Ascending button.

Additional cell formatting

1. Bold and italicize the entry in A13.

2. Select cells A6 through J13.

3. Right-click the selection, then click Format Cells in the shortcut menu.

4. Click the Border tab.

5. In the Style box, click the Medium-Weight Solid Line.

6. In the presets area, click the Outline button.

7. Click OK.

8. Select cells C7:J13.

9. Click the Center button on the Formatting toolbar.

Adding formulas

First you want to get a class average for each homework assignment and the quiz and test.

1. Click cell C13.

2. Type =average(C7:C11)

3. Press enter. Notice that the average of the scores shows up in cell C13. Also notice that the formula itself shows in the formula bar when the cell is selected. To modify or change the formula, simply click in the formula bar to make the changes, and then press enter.

You have the average for the homework assignment, but you need the average for the other columns. You could type the formulas in cells D13 through H13. But that would take some time, and you might make some mistakes. Excel can make it very easy for you.

4. To fill all of the other cells, click and drag from the lower-right corner of cell C13 to I13. Excel not only copies the formula into each cell but adjusts the formula for each column so that it is correct for that set of numbers. Of course, there is an error in cell I13 because there are no numbers here to sum yet.

5. Click cell I7. You need a formula here that adds up the points for all of the work and divides by the number of possible points. Then you'll convert it to a percentage.

6. Type =sum(

7. Select cells C7:H7. Notice that Excel puts the range you have selected into the formula for you.

8. Type )/165

9. Press enter. Now, just as you copied the formulas in row 13, you drag the fill handle to copy the formula in I7 to I11.

Formatting numbers

1. Select cells I7 through I13.

2. Click the Percentage button on the toolbar.

Now here's your grade book:

image3.png

Saving your grade book

If you have not saved your grade book yet, do it now.

1. Click the Save button.

2. Name your file, then choose Save from the Standard toolbar.

All that's left is to decide what grade each student should get.

Calculating student grades

Finally, let's add a function that automatically calculates student grades based on their averages in column I.

Look at the grading scale below:

image4.png

The function required to return a letter grade is listed below, and is called a nested function. Be very careful to type it exactly as it is written. Typos and misplaced parentheses will result in an error.

1. Click cell J7.

2. Type =IF(I7>.89,"A",IF(I7>.79,"B", IF(I7>.69,"C",IF(I7>.59,"D","F"))))

3. Press enter.

This formula assigns a letter grade to the numerical grade in column I. It gives the values for letter grades A-D, and tells Excel that anything else is an F.

The average in cell I7 is 82, therefore the student gets a "B". What about assigning letter grades for the other students? Copy the formula from Cell J7:J11, using Autofill.

You can make as many modifications as you need, and of course, you can easily copy this sheet several times to make sheets for your other classes.

Graphing Student Grades

Now let’s create a graph that shows the average score distribution. Graphs are a quick way of viewing numerical information. For this graph, you will be required to include information from two columns that are not adjacent. We want to graph the average scores and include the students’ names on the graph.

1. Highlight the range A6:A11.

2. Hold down the CTRL key and highlight the range I6:I11

3. Click the Insert tab on the ribbon.

4. In the Charts box, click Line to create a line graph.

5. That’s it! Your graph has been created.

6. Double-click on the box around the graph. The Chart Tools menu will appear on the ribbon. Use the menu to customize the graph.