UCLA Office of Instructional Development - Creating a Grade Sheet with Microsoft Excel - Teaching Assistant Training Program - Setup a grading sheet

Sep 12, 2026

Upload by Jasmine

The tutorial explains how to create a grading sheet in Microsoft Excel, using formulas, functions, sorting, and grading automation such as predefined grade curves. It begins with entering and testing formulas, including operators and correct parenthesis use, and clarifies how results appear in cells versus formula bar display. It then teaches absolute versus relative cell references, showing how copying formulas changes referenced cells and can prevent incorrect student grades. It also notes compatibility with Excel 2000 for Windows and general behavior in Excel for Mac.

LanguageEnglish

CategoryPresentations

FormatPDF

Pages16

Reading Time6 min

AccessFree to download

Introductory Excel: Entering Formulas

Formula structure and operators

Parentheses and error handling

Introductory Excel: Absolute and Relative Cell References

Relative cell references and copying formulas

Impact on grading accuracy

UCLA Office of Instructional Development Creating a Grade Sheet With Microsoft Excel Teaching Assistant Training Program Creating A Grade Sheet With Microsoft Excel Microsoft Excel serves as an excellent tool for tracking grades in your course. But its power is not limited to its ability to organize information in rows and columns. Using formulas and functions in Excel, you can simplify the grading process. With Excel you can sort students by names, grades or whatever characteristics you choose. You can also setup a grade curve in advance and have Excel automatically assign letter grades (not just percentages) to each of your students. When you change the curve, the grades will change automatically. This tutorial will show you how to setup a grading sheet in Excel that makes use of all these functions plus some other helpful features that will be explained in detail later. This tutorial assumes the reader has a basic understanding of how to navigate a spreadsheet and enter data in cells. A reader who is experienced with Excel and is familiar with entering formulas and the difference between absolute and relative cell references can begin in section three. Also, note that this tutorial is based on Excel 2000 for Windows. Everything in this tutorial with the exception of keyboard shortcuts will work in Excel for Mac. 1) Introductory Excel: Entering Formulas In Excel, formulas allow a user to make new calculations based on data entered into a spreadsheet. In simple terms a formula is made up of a combination of numbers, cell references and mathematical operators. To input a formula, click once on the cell in which you wish to enter a formula. Then click on the formula bar to begin entering your formula. In Figures 1.1 and 1.2 we have entered the number 1 in cellA1 and the number 2 in cell B2 . We will add a formula into cell C3 to calculate the sum of cells A1 and B2 . Note that after clicking on cell C3 we type the formula in the formula bar just above the worksheet. Once the formula is complete, hit enter. Cell C3 now displays the result of your formula, the value 3. In other words A1+B1 = 3 because 1+2=3 . Whenever you enter a formula into a cell, the cell will always display the result of the formula and not the formula itself. However, if there is a formula in the cell, it will be displayed in the formula bar. Figure 1.1 UCLA Office of Instructional Development Creating a Grade Sheet With Microsoft Excel Teaching Assistant Training Program Figure 1.2 When entering formulas for your grade sheet, most likely you will use the typical mathematical operators to calculate your grade formulas. They include the following: Addition Subtraction Multiplication Division + - * / The order of calculation follows conventional mathematics. You can use parentheses to organize your formulas, but be aware that Excel calculates from the inside out where there are multiple sets of parentheses. For example, Excel calculates the formula =((2+3)*5) in the following way: 2+3 = 5 then 5 * 5 = 25 So the answer to the formula =((2+3)*5) is 25. Note that you always must have matching pairs of parentheses. If Excel finds they do not match it will give you an error message like that in Figure 1.3. The error message in Figure 1.3 appeared as the result of the formula =((2+3)*5 . The correct version would be =((2+3)*5) . Of course you could always mix cell references with numbers in your formulas. The formula =((A2+3)*5) would also equal 25. Figure 1.3 UCLA Office of Instructional Development Creating a Grade Sheet With Microsoft Excel Teaching Assistant Training Program Finally, note that capital letters were used in the formula in Figure 1.2. It is not necessary to use capital letters. Excel doesn’t care whether you use capitals or lower case letters when referencing cells. You may want to use lower case letters simply because it means less typing (less use of the shift key) . 2 Introductory Excel:

FAQs

How do you enter a formula in Excel for a grade sheet?

Click the target cell, start typing in the formula bar, complete the formula, then press Enter. The cell displays the calculated result rather than the formula text.

Why are parentheses important in Excel formulas?

Parentheses control the order of calculation, and Excel requires matching pairs. Missing a closing parenthesis triggers an error message.

What is the difference between absolute and relative cell references when copying formulas?

Relative references change based on where the formula is copied, while absolute references would keep specific parts fixed. Using relative references incorrectly can lead to wrong grade computations for students.

Sep 12, 2026

Upload by Jasmine