GRADING EXCELS

WITH MICROSOFT EXCEL SPREADSHEET

Created by: Brenda Richardson

Description

In this activity you will use Microsoft Excel to create a Student Grade Sheet. You will learn to format, align and use fonts in cells. You will set up the page and use averaging and totals to assign Grades.

Step 1 – Open A New Spreadsheet in Excel

  1. Click on START, PROGRAMS, EXCEL (Green X) to open a blank Excel spreadsheet.
  2. SAVE the blank spreadsheet toYOUR FOLDER on the desktop (To make a new folder while on the desktop: RIGHT CLICK while on the desktop, NEW, FOLDER, RIGHT CLICK, RENAME).
  1. PLEASE SAVE YOUR SPREADSHEET OFTEN DURING THE LESSON.

Step 2 – Set up Your Grade Sheet Make a Page

Header

1.Click on FILE in menu bar, scroll down to PAGE SETUP.

  1. In PAGE SETUP window choose HEADER/FOOTER tab.
  1. Click on CUSTOM HEADER button.

  1. Type your header, “Grade Sheet”, here.
  1. Change font type, size etc. to your preference.
  1. Click on OK.

Step 3 – Enter Information Into Your Spreadsheet

In Excel each cell is given an ADDRESS that is a combination of its column letter and row number. Thus the cell at the intersection of column E and row 5 is cell E5.

  1. Enter the titles into your spreadsheet. Click in cell and begin typing. Move from cell to cell by clicking or using arrow keys.

  • Type English in cell A1
  • Type Student Name in cell A3
  • Type Assignment 1 in cell B3
  • Type Assignment 2 in cell C3
  • Type Assignment 3 in cell D3
  • Type Assignment 4 in cell E3
  • Type FinalScore in cell F3
  1. Add Student Names:
  • Type Billy in cell A5
  • Type Bobby in cell A6
  • Type Fred in cell A7
  1. Enter the following numbers (grades) in your spreadsheet:
  • 80,75, 80, for Assignment 1, in cells B5, B6, B7
  • 40, 99, 90, for Assignment 2, in cells C5, C6, C7
  • 30, 87, 45, for Assignment 3, in cells D5, D6, D7
  • 0, 68, 99, for Assignment 4, in cells E5, E6, E7

Step 4 – Formatting columnS

To change column width:

  1. Highlight Columns A, B, C, D, E, F, CLICK AND DRAG from top of column A thru Column F.

  1. Click on FORMAT, scroll to COLUMN and WIDTH.
  1. Change the number of the COLUMN WIDTH to 12.
  1. Click OK.

STEP 5 – FORMATTING TEXT

Center text in rows 5, 6, & 7:

  1. CLICK AND DRAG from row 5 to 7 to highlight.
  2. Click on CENTER button in tool bar.

Change the text size and font in cells A1 and A3:

  1. Highlight cell A1 and A3 at the same time by clicking on cell A1 then holding down the CTRL KEY (bottom right or left of keyboard) while clicking in cell A3.
  2. For BOLD, click on the B in the tool bar, while highlighted.
  3. Change the FONT SIZE to 14 in the font drop down shortcut on the toolbar.

STEP 6 – FORMATTING CELLS AND ROWS

RESIZING COLUMNS:

Resize column A to fit text:

  1. Move mouse to right top side of the column A cell until it turns

into a symbol similar to this:

  1. DOUBLE CLICK when arrow turns into this symbol. Column will automatically adjust to fit text.
OR

3. At the top of any column, when the arrow turns into the symbol, CLICK AND DRAG the column divider to whatever width you need.

BORDERS:

To accent the subject, add a red border around Cell A1:

  1. Highlight cell A1, click on FORMAT, CELLS, and on BORDER tab.
  2. From COLOR BOX click the color red.
  3. Choose OUTLINE and under STYLE click on the solid thick line.
  4. Click OK.

CELL COLORS:

To accent certain cells, fill them with color:

  1. HIGHLIGHT cells F3, F4, F5, F6, F7.
  2. Click on FORMAT, CELLS.
  3. Select the PATTERN TAB, choose yellow.
  4. Click OK.

ADDING AND DELETING ROWS:

To Delete row 2:

  1. HIGHLIGHT by clicking on row 2 on the number 2 on the far left hand side of the row.
  2. Choose EDIT, DELETE.

STEP 7 – FORMATTING CELLS WITH FUNCTIONS

Excel does the Math for you. To do this you must tell it the RANGE OF CELLS you need to do the function you ask it to do. There are some quick keys that will help you do this more quickly as you gain knowledge of the spreadsheet. Basically, select what cell you want the answer in and type in the FORMULA.

To AVERAGE Billy’s grades:

  1. Highlight Cell F5.
  2. Type in the formula =AVERAGE(B5:E5) this tells Excel that this cell should be equal to the average of the range of cells between B5 and E5.

Please note: E5 could be a blank cell but to average it you must insert a 0. If you need to leave it blank, you would want to change the formula to only be B5:D5. Experiment with this, delete the 0 and average, see the difference in the grade) it will change from a 37.5 average to a 50 average.

You can delete numbers and text from the actual cell when the cell is highlighted by using the backspace or delete key. Should you need to delete a function you have typed, you will need to have the cell highlighted and delete in the FORMULA BAR at the top of the spreadsheet.

PASTE FEATURE - If you want the same functions for other rows or columns you can easily use the PASTE feature.

Paste the function for Billy’s grades in for Bobby and Fred as well:

  1. HIGHLIGHT cell F5.
  2. Hold the mouse over the small square in bottom right hand corner until the + appears.
  3. CLICK AND DRAG down to cell F7.

IT’S EASY TO ADD A RANGE OF NUMBERS

Sum the total numbers in cells C5, C6, C7:

  1. Click in the cell you want the total ….C8.
  2. Choose Auto Sum icon from tool bar.
  1. This gives you the formula automatically to total these

cells.

  1. Hit ENTER.
STEP 8 – GRAPHING YOUR SPREADSHEET DATA

Make a graph of Fred’s grades:

  1. HIGHLIGHT row 6.
  1. Click on the CHART WIZARD icon on the toolbar.
  1. This defaults to column type of chart.
  1. Choose by selecting what type of chart you would like.
  1. Follow through wizard steps by clicking on NEXT button where you can rename the graph, name the axis, select a different page you want to graph to be put, or select FINISH.
  1. Click and drag chart down below text in spreadsheet area

STEP 9 – FORMAT THE PAGE

Because the spreadsheet is an infinite size, you want to format the page to your specifications.

To change PAGE LAYOUT to Landscape:

  1. Select FILE, PAGE SETUP.
  2. In the PAGE SETUP window choose PAGE TAB.
  3. Click on LANDSCAPE.
  4. Click OK.

To change MARGINS to ½ inch on top, bottom, right and left sides:

  1. While still in PAGE SET UP, select the MARGINS TAB
  2. Change top, bottom, side margins to .5
  3. Click OK.

To have lines around the cells when they are printed:

  1. Select FILE, PAGE SETUP.
  2. On the SHEET TAB select the radio button for GRIDLINES in thesquare under Print. This will show the lines around the cells when printed.
  3. Click OK.

To do a PRINT PREVIEW of this page:

  1. Click on the PRINT PREVIEW icon on toolbar (holding mouse over any icon will give you the function of that icon).
  1. Choose CLOSE from this view when done (this takes you back to original page)

STEP 10 – SET UP FOR PRINTING

To set the area for a print of this page:

  1. HIGHLIGHT the cells you want to print by CLICKING AND DRAGGING.
  1. Go to FILE, PRINT AREA.
  1. Choose SET PRINT AREA. This will tell the printer what part of the page you wish to print.

1