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

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.

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
    your spreadsheet should look something like
    this after wrapping text in cells
  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.

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 FormatColumnWidth …

 
  • make note of the number (your number may be different)
  • click
  • click in any cell in column C and drag over to the corresponding cell in column D
  • choose FormatColumnWidth …
  • enter the number that was the width of column B, click
  • 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 what we are about to do 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

Format Cells Dialog Box, Number Tab Selected
Excel XP (Excel X is identical)

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 InsertFunction… or click the Insert/Paste Function button on the Standard Toolbar
    • the button is called Insert Function in Excel XP and it may need to be added to the Standard Toolbar
    • the button is called Paste Function in Excel X and it still is part of the Standard Toolbar
  • the Insert/Paste Function Dialog Box appears
    • Excel XP:
      • you can search for the appropriate function by typing a description of what you want to do in the box at the top, then click
      • or you can use the drop-down list (shown open in the picture) to select the category of functions to explore, then choose the function from the scrollable list below
      • note the description of the selected function appears below the scrollable list
    • Excel X:
      • 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

 


Insert Function Dialog Box Excel XP


Paste Function Dialog Box Excel X

 

 
 
  • the “function wizard” dialog box opens as shown below

    Check MarkThere is no need to use this as suggested by the appearance of the box, 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)! It is imperative that you use it as described below.

 
Function Wizard Dialog Box
Excel XP
Function Wizard Dialog Box
Excel X
  • click on the (Excel XP) or (Excel X) button  in the “function wizard” dialog box
    • the dialog box will “collapse” as shown below
    • and an incomplete SUM function will now appear in the formula bar
The Collapsed Function Wizard Dialog Box
and Incomplete Sum Function
Excel XP
The Collapsed Function Wizard Dialog Box
and Incomplete Sum Function
Excel X
  • note the cursor is flashing in the collapsed box as Excel is waiting for you to complete the argument of the function
  • drag over the cells B3, C3, and D3 directly in the worksheet; note how the argument is updated in the formula bar
  • Check MarkWe have employed the “drag method” to build a formula/function
  • the correct function will read =SUM(B3:D3)
  • now click on the button at the right end of the collapsed box to expand it again
  • clickto enter the function, or click on the green check mark

 

  • cells E4 through E8
    • what do these cells represent?
      • the same as E3, that is the sum of the three cells to the left in the same row
    • select the cell range E3 through E8
    • choose EditFillDown (or press Ctrl + D in Windows) or use the Fill Handle (use the Back button in your browser to return to this point)
    • all cells now have the correct formula

 
 
The EditMenu Options
Excel XP (Excel X is the Same)

The Average Row

CheckIf you know the function name and how its arguments are structured, you can bypass the Insert/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 Insert/Paste Function command. When you already know, just enter the function directly.

Using the AutoSum Button to Enter Functions

The AutoSum button offers an alternative to the Paste Function Dialog Box. Note that the button actually contains two parts, the button itself and a drop-list to select other functions. Click on the triangle pointing down to open the list.

AutoSum Button
Excel XP
AutoSum Button
Drop-Down List Open
Excel XP
AutoSum Button
Excel X
AutoSum Button
Drop-Down List Open
Excel X

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

Borders are set using either the Borders button on the Formatting Toolbar or the FormatCellsBorders command. The border button, like the AutoSum button, actually consists of two parts. The button itself, and a drop-down list to choose a border style.
Border Button
Excel XP
Border Button
Drop-Down List Open
Excel XP
Border Button
Excel X
Border Button
Drop-Down List Open
Excel X

Select the cell range A2 through G2

  • open the drop-down list of the Borders 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 button, if you want to use that border again, just click on the button)

 

Select the cell range A8 through G8

  • unfortunately, in Windows, 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 borderbutton in the <Border> section of the box
    • click <OK>

 
 
the Format Cells Dialog Box
Border Tab Selected
Excel XP (Excel X is the Same)

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

The Fill Color Button
Drop-Down List Open
Excel XP (Excel X is the Same)

The Fill Color button works the same way as the Border and AutoSum buttons, that is, this button also consists of two parts. In the case of the Fill Color button, the colored band indicates the current color. Click on the button to fill a cell(s) with that color. If you wish a different color, click on the triangle to open the drop-down list and select a different color.

Select the cell range A1 through G1 and apply the desired fill color.

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

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

bullet when you enter names, should you enter them “first/last name” or “last/first name”? If you want to sort by names, you should do the latter
bullet if this were an actual spreadsheet and you wanted to have it keep track of the grades during the semester, you would enter the total possible for a given test only when you were about to enter the scores for that test. Otherwise, every student would have an F until you entered the last test.
 

Saving the Worksheet

If you wish to keep this worksheet for later reference, choose FileSave 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 Box

Sort Dialog Box
Excel XP
(Excel X is the Same)
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