Setting up a Gradesheet

The following steps will guide you through the process of creating a spreadsheet that will calculate grades. To help you review the process, use the following table to help you quickly find what you need.

Alignment of text used as labels
Wrapping text within a cell
Column width & Row height
How data is displayed
Entering formulas and functions
Relative versus absolute referencing
the If statement
Borders and Fills
Using test data
Completing the spreadsheet with real data
Sorting a spreadsheet
Completed spreadsheet example

First, we will enter some labels …

As you enter these labels, do not worry if they appear not to fit in a cell. We will make the necessary adjustments later!

entering the column labels

entering the row labels

your spreadsheet should now look something like this:

 Example gradesheet w/ labels only

 

Next we will adjust text alignment …

CheckText used as labels should always be aligned appropriately, that is, based upon the type of data that is being labeled. For example, since text is normally left aligned, a label over a column of text is normally left aligned but may be centered in some circumstances. Numeric data, on the other hand, is always right aligned. Thus a label over a column of numeric data is normally right aligned but may be centered in some circumstances as noted below.

Centering a Label Across Multiple Columns

The title of the gradesheet should be centered across all of the columns of the gradesheet. To do this:

Aligning Text to the Right

The data in columns B through F is numeric. Consequently:

The labels in A8 and A9 have nothing to do with the data in column A but instead refer to data in rows 7 and 8. Consequently:

Centering Text in a Column

Column G will contain only a single letter representing a grade. To help visually separate this data from the columns to the left and to make it easy to read we will center this data:

Align Text to the Left

We did not use the [Align Left] button for this spreadsheet. However, it works in the same manner as the [Align Center] and [Align Right] buttons described above.

Your gradesheet should now look something like this:

Gradesheet with labels aligned

 

Now We Will Wrap Text within a Cell

There is one other thing that you can do with text in a cell. You can “wrap it”, that is, you can display multiple lines of text with a single cell. This is very useful if one or more column labels are much wider than the data in the columns (consider, for example, the Total Points column in the gradesheet). There are two methods to do this:

Manual Method

  1. Click in the cell in which you wish to wrap the text
  2. Select FORMAT Cells
  3. Select the Alignment tab
  4. check the <Wrap Text> box
  5. Click <OK>
  6. If necessary, now manually adjust the width of the column until the text is wrapped without splitting any words as described below

Keyboard Shortcut

  1. Click in the formula bar where you want the split to occur (usually you also delete the space between words)
  2. Press <Cmd-Option-Return> (Macintosh) or <Alt-Enter> (Windows)
  3. Press <RETURN> / <ENTER>
  4. If necessary, now manually adjust the height of the row so that all text is displayed. This is done in a manner similar to adjusting column width as described below

Wrap the text in cells E2 and F2 by whichever method you prefer.

Your spreadsheet should now look something like this:

Gradesheet with text wrapped

 

Next We Will Adjust Column Width …

CheckA column should be adjusted to be just a little bit larger than the width needed to accommodate the widest item possible in the column. A row should be adjusted to be just a little bit taller than the height needed to accommodate the tallest item possible for that row. This ensures that you will have enough “white space” separating columns and rows to improve readability. Before you adjust width/height, consider the widest/tallest data that may possibly be placed in that column/row for the spreadsheet you are creating. Enter that “dummy data”, adjust, then delete the “dummy data”.

column A

column B

columns C and D

  • these columns must be exactly the same width as column B (why???)
  • click in any cell in column B
  • choose Format Column Width …

 

 Format - Column - Width Menu
  • make note of the number (your number may be different)
  • click <OK>
  • click in any cell in column C and drag over to the corresponding cell in column D
  • choose Format Column Width …
  • enter the number that was the width of column B, click <OK>
  • columns B, C, D are now exactly the same width!

 

column E

column F

column G

Now Let's Consider How data is displayed

CheckIt is important to note that this format dialog box ONLY affects how data is displayed. Calculations are based upon the actual numbers to the limit of the computer's memory and the capabilities of the program.

Cell Range B3:E9

You may wish to explore the other tabs in this dialog box!

You really should check out the Custom options. Click here now! Just do it!!

Now It is Time to Enter Formulas & Functions

CheckThe FILL commands result in a multiple copy/paste operation. The program will copy the contents and any formatting of the first cell in the range selected and then paste that information into each of the other cells selected.

The Total Points Column

click in cell E3
  • what does this cell represent?
    • the sum of the three cells to the left in the same row
    • that is, Total Points equals the sum of Test 1 + Test 2 + Test 3
    • what we want, then, is a function to perform that summation
  • choose Insert Function… or click the Paste Function button on the Standard ToolbarFunction Wizard Button
  • the Paste Function Dialog Box appears
    • the scrollable list on the left, Function Category, lists the groupings or categories of functions (Excel contains over 200 functions!); note the category “Most Recently Used”
    • the scrollable list on the right, Function Name, lists the functions contained within the category selected
    • below these boxes is a message area showing the format of the selected function with an explanation of what that function does
  • find the SUM function
    • click once on it
    • then click <OK>
    • the “function wizard” dialog box opens as shown on the right
    • Check Markthere is no need to use this, it is a little bit of overkill … and if you do, the argument of the function may not be expressed as a proper range … which can be a problem if you insert new column(s)!
  • click <OK> in the “function wizard” dialog box
    • Check MarkExcel may not like this, you may get an error message
    • if so, disregard it, click <OK>
  • an incomplete SUM function will now appear in the formula bar
    • note the cursor is flashing between the parentheses as Excel is waiting for you to complete the argument of the function
    • drag over the cells B3, C3, and D3; note how the argument is updated
    • Check MarkWe have employed the “drag method” to build a formula/function
    • the correct function will read =SUM(B3:D3)
  • press <RETURN> / <ENTER> to enter the function, or click on the green check mark

 

Paste Function Dialog Box
Function "Wizard" Excel 97/98

The Function Wizard dialog box.

SUM function to be completed
The incomplete SUM funtion in the formula bar.

You may also use the AUTOSUM buttonon the Standard Toolbar to enter the SUM function.

 

The Average Row

CheckIf you know the function name and how its arguments are structured, you can bypass the Paste Function command and “the Wizard”, and enter the function in the formula bar manually. In fact, the only reason to use the Paste Function command is to look up the name of a function when you are not sure how the function name is spelled. For example, you want to compute the average of a column of numbers. Is the function name MEAN, AVERAGE, AVE, or AVG? All are commonly accepted as meaning average but which is the function name used by Excel? When you are unsure, look it up using the Paste Function command. When you already know, just enter the function directly.

The % Score Column

Relative v. Absolute Referencing

By default, spreadsheets utilize Relative Referencing in formulas and functions. A Relative Reference expresses the location of a cell relative to the location of the current cell. For example, consider the function currently in E3

=SUM(B3:D3)

This calculation is actually interpreted as

go 3 cells to the left in row 3 and get the number
then 2 cells to the left and get the number
then 1 cell to the left and get the number
then add the 3 numbers
and put the result in E3

When you copy and paste, or Fill, Microsoft Excel will change the references to reflect the relative position from the new source cell. Thus, Relative Referencing will not work for the Fill Down operation for the % Score calculation because the divisor must always be the data in E8. For this formula, you need to tell the spreadsheet to employ Absolute Referencing. In other words, take the data one cell to the left (Total Points) and always divide by E8 (Total Possible). An Absolute Reference stays the same when you copy and paste, or Fill. To enter an Absolute Reference, type a dollar sign ($) before each part of the cell reference. Specifically in this case, change the formula in F3 to read

=E3/$E$8

This formula is interpreted by Microsoft Excel

as go one cell to the left

and always divide by E8.

The Grade Column — Using the IF Statement

explaining the IF function

=IF(F3>=0.8995, "A", IF(F3>=0.7995, "B", "C"))

  • if the student achieved 90% or better, the result of the IF test would be True and the action if true would be to assign the grade of “A”
  • if the result of the IF test is False, does the computer “know” if the student should get a “B” or a “C” ?
    • the answer is “NO!”
    • so the action if false is to pose another test, i.e., ask the question is F3>=0.7995?
      • if the student achieved 80% or better, the result of the second IF test would be True and the action if true would be to assign the grade of “B”
      • if the student achieved less than 80% then the result of the second IF test would be False and the action if false would be to assign the grade of “C”
         

Entering the IF Statement

Why are Quotation Marks Used in the IF Statement?

You should have noted the use of quotation marks in constructing the IF statements described above. In each example, the grade was enclosed in quotation marks such as "Pass", "Fail", "A", "B", "C". However, quotation marks are not always used to enclose the action-if-true and action-if-false portions of the statement. So when do you used quotation marks and when to you not? The answer is whether the action is a number or it is text.

When is a number a number and not text? The easiest way to tell the difference is to ask yourself if it would make sense to add or subtract numbers within the spreadsheet. If addition/subtraction (or any other mathematical operation!) makes sense, then it is a number; if not, then it is text. This is very important when you construct IF statements. Reexamine the example IF statements above. In the IF statements involving grades, the words Pass, Fail, A, B, C, D and F are all enclosed in quotes. These items are nonnumeric, math operations do not make sense. In fact, whenever any character is enclosed in quotation marks, the computer treats that character(s) as nonnumeric. On the other hand, if the action of the IF statement is to place a number in a cell, and that number is to be mathematically manipulated, then you must never enclose that number in quotation marks!

 

The Final Touches, Borders and Fills

Borders

Select the cell range A2 through G2

  • press and hold onBorder Button Arrow of the Borders buttonBorder Button on the Formatting Toolbar
    • the borders options palette will “pop-down”
    • select the bottom border
    • release the mouse button
    • (note that the last selected border appears on the toolbar, if you want to use that border again, just click on the button and notBorder Button Arrow )

Select the cell range A8 through G8

  • unfortunately, the convenient Borders button does not include a top border by itself so
  • choose Format Cells…and click on the [Borders] tab
    • click on the Top borderTop Border Buttonbutton in the <Border> section of the box
    • click <OK>

Note that the dialog box for borders gives you several options:

  • you may select from three preset borders, None (to remove borders), Outline, and Inside
  • you may create any combination of borders by clicking on one or more buttons in the <Border> section
  • you may select the style of border line
  • you may select the color of the line
  • in any case, whatever you select will be displayed in the white box with the word Text twice

Add any other borders that you desire to the gradesheet

Fills

Select the cell range A1 through G1

  • press and hold onBorder Button Arrow of the Fill buttonFill Color Button on the Formatting Toolbar
    • the fill colors palette will “pop-down”
    • select the color of your choice
    • release the mouse button

(Note that the last selected fill color appears on the toolbar, if you want to use that color again, just click on the button and notBorder Button Arrow )

You may also want to explore the dialog box for fills by selecting Format Cells…and click on the [Patterns] tab

 

Format Cells Border

 

Now It is Time to Test the spreadsheet

test 1

the other tests

Make any necessary corrections

It is imperative that you test any spreadsheet BEFORE you begin to use it with actual data. When performing such tests, use data that you can calculate in your head so you know what the results should be! Try entering data that is at the “break points”. That is why, for example, it was suggested that you enter 90, 80, 70, 60, and 59 in the above test. If the gradesheet correctly assigns a C to a student with 60, then it should assign as C to a student with 60-something and so forth.

Now Let's Enter Actual Data

Check MarkClick here to review the example completed spreadsheet

Clear any test data

Enter names and data for the three tests

 

Saving the Worksheet

If you wish to keep this worksheet for later reference, choose File Save and give it the filename GRADES.XLS. See also the discussion on templates (be sure to press the Back button in your browser to return to this point).

Now Let's Sort the Data

A single-key sort considers only one item when sorting. For example, you may sort a list of students alphabetically without regard to grade. A multiple-key sort considers first one item, then a second, etc. For example, you may sort a list of students based upon grade, then sort the data alphabetically. The result will be that all students that earned an A would be sorted alphabetically, then all of the B students, etc. Another example of a multiple-key sort is a list of references in a bibliography. The first “ key ” is the author's last name, the second is the author's first name, etc.

Using the Sort Buttons

sorting your gradesheet by name (alphabetic sort)
sorting your gradesheet by grade (numeric sort)

The Sort buttons on the Standard Toolbar permit single-key sorts, while multiple key sorts require the Sort command. When using the Sort buttons, the sort key is determined by the active cell. Thus, to sort by name, the active cell must be in column A; to sort by grade, the active cell must be in column F (or column G).

Using the Sort Dialog BoxSort Dialog Box

This dialog box permits you to perform multiple key sorts as well as utilize other sorting options not available by using the sort buttons. You must select the range of data to be sorted first before opening this dialog box.

Go Back

Copyright©2003
last updated August 12, 2003