Return to On-line Lessons

Excel Tutorial for Educators

Printing this web page is approximately 8 pages.
Download these needed Excel '97 files: zgrades1.xls and zgrades1Answers.xls

Double click the Microsoft Excel icon to open the program. OR, click the Start button (bottom left corner of screen), move the mouse cursor to Programs, and click Microsoft Excel from the provided list.

A Grade Book

The spreadsheets are filled with cells such as A1, B2, C3, etc. Text, scores, and formulas are typed into these cells as you wish them to appear. Notice at the bottom of the screen there are tabs titled "Sheet #." Spreadsheets are layered so that related calculations can be done with a mouse click. Our purpose here is to produce a multi-layered grade book.

Setting Up

Double click the tab Sheet 1 at the bottom of the screen.

"Sheet 1" is shaded. Rename "Sheet 1" as Reading. Then click OK.

Next, set up the header row by typing the following in the cells beginning with cell A1:

	A	B	C	D	E	F	G	H	I
1	First Name	Last Name	ID #	Avg.	Total Pts.	No inserts	Test 3	Test 2	Test 1

Note:

When a cell is clicked, its identifier appears in a box just above column 1, such as A1. This box, a "Cell Finder," can also be used for searching for a particular cell. Simply type in the box the letter and number of the cell, followed by the Enter key, and Excel takes you directly to the cell.

Once a cell is clicked, you can immediately begin typing. With the first key pressed, three characters appear next to the "Cell Finder." They are an "X" for undoing work typed in a cell, a check mark for confirming what is typed in a cell (pressing the Enter key will do the same), and a function symbol "fx." that is used for configuring formulas (more on that later).

Save work. Title this file as your "first name Grades" (Ex: John Grades).
Click Save.

Type the following students’ names and ID numbers in columns A, B, and C, beginning in cell A2:

May	West	234
Bob	Hope	567
Eddie	Murphy	912
Sam	Kinison	789
Whoopie	Goldberg	345

Save work.

Getting Our Bearings

Without clicking, move the mouse cursor on to each shortcut button so that a flag tells its operation. Next, click the menus above the buttons to see Excel’s functions. Go ahead and click the different menu and button commands to see what happens.

When done exploring, click Close from the File menu. When asked to save, click No.

To reopen quickly, click the File menu. Your file should be listed at the bottom of the File menu, above "Exit." Click your file. (Or choose File: Open to find your file.)

Playing The Numbers Game

Now enter the students’ scores on the three reading tests as shown below:

	A	B	C	D	E	F	G	H	I
1	First Name	Last Name	ID#	Avg.	Total Pts.	No inserts	Test 3	Test 2	Test 1
2	May	West	234				85	100	90
3	Bob	Hope	567				85	75	85
4	Eddie	Murphy	912				93	90	100
5	Sam	Kinison	789				75	80	78
6	Whoopie	Goldberg	345				90	100	100
7

Save work.

Sorting Information

To sort the students by last name, click Cell B1. Next, click Sort under the Data menu.

Make sure that "Header row" is clicked at the bottom of the "Sort" box. Under "Sort By" click the first down arrow and click Last Name. Click the corresponding Ascending circle. Then click OK.

The students’ names are now sorted by last name and the test scores have moved with the students’ names.

Inserting Rows and Adding Information

If a new student, Bob Collins, is added to the roster, he could be entered in row 7 and then sorted later or he can be directly inserted into his alphabetical spot.

Click A2. Then click Rows under the Insert menu. A row appears just below the row containing the headings.

Type in the name Bob Collins and his ID # 123 in the new row 2 and include the following Reading Test scores:

Test 1= 84.5 Test 2= 86.5 Test 3= 81

Save work.

Formulating Numbers Into Grades

Click cell D2. Next, click the "=" sign, so that the "equal" sign appears in the Formula Bar above the alphabetical column headings. A row of power buttons appears--an "X" for "undoing" work in the cell, and a "check mark" for confirming work in a cell (or press the Enter key).

The Office Assistant appears with a funky animation. Click IT if you want guidance from it on understanding formulas in more depth than we go here.

Only a few functions are used. Once you know which ones you’ll commonly use, it becomes unnecessary to take all the moves we’re about to do.

Click Cancel on the "Formula Result" bar.
Click Function under the Insert menu. The "Function Wizard" box appears. There is a column of "Function Category" from which "Most Recently Used" is shaded. You’ll rarely go beyond this category. Under "Function Name" are the commands mostly needed, such as Average, Sum (summary), Max (Maximum), and Min (Minimum).
Click Average from the "Function Category" column. Then click OK.
Change C2 in the Number 1 box to F2 (for cell F2, Test 3)
Type G2 in the Number 2 box (for cell G2, Test 2), H2 in the Number 3 box (This box appears as you type in Number 2), and I2 in the Number 4 box.
Press the Enter key.

=AVERAGE(F2,G2,H2,I2)

appears in the Formula bar.

And 84 appears in cell D2 as the average of the three tests.

A Faster way to create a formula

Click D3. In the formula bar type: =Average(

Formulas such as averages and sums are based on a range of numbers in consecutive cells. Within a parenthesis, this range is shown with a colon, for example- =Average(F3:I3). What this formula tells the computer is to add the numbers in cells F3 through I3, and then divide by the number of cells with entries (cells that are blank or containing letters will not be counted for applying the formula).

In the open left parenthesis, type F3:I3).
OR click on cell F3 and drag across until cells F3 through I3 are shaded. In the info box, the selected cells appear. Click the ")" to close the formula.

Press the Enter key or click the check mark on the formula bar.

Bob Collins and Whoopie Goldberg now have averages.

An Even Faster Way to Copy Formulas into other cells.

Click on cell D3 and drag down to D7 so that all is highlighted (Cell D3 will seem as if it’s not highlighted but the special thick bordering signals that it is selected).

Click Fill, then the Down from the Edit menu. The formula we made for Whoopie is now copied for all of the students. Click their formulas and you’ll see that Excel automatically sets up each row without you having to type the formula into each cell.

Click E2. Create the following formula for calculating the sum of points using the preceding steps, including Fill Down. Instead of "Average," use "Sum."

=sum(f2:I2)

Save work.

Adding More Information

The class takes another test. Click the "G" column heading so that the entire column becomes shaded.

Click Columns from the Insert menu. A new column "G" appears as the rest of the tests are pushed to the right.

Type Test 4 in cell G1. Then type in the following scores beneath the heading of column "G":
G2= 80, G3= 90, G4= 81, G5= 60, G6= 85, G7= 92

Save work.

If you check the formulas for Average and Total Pts., you will notice that the range has automatically extended to include Test 4. This is because we inserted the column inside the range ("after" column F). Insert pushes the designated column/row over. Clicking G stretched the range; whereas clicking F would have moved the range over.

The Fourth Dimension

What if you wanted to keep grades for different categories, and you wanted to calculate them separately. But you also want to see the overall grade without any clutter. This is possible, because Excel workbooks contain numerous spreadsheet "pages."

Under the File menu, open zproject folder. Then open Zgrades1 from the "MyGrade" folder.
Immediately Save As "Grade your name" (ex: Grade John).

A more complete version of the grade book you composed appears. Notice next to the "Reading" tab below there are three more: Vocabulary, Writing, and Quarter Score.

Click through them. All are in similar format as "Reading." Cutting and Pasting is a quick way to move names and headings quicker than retyping everything. In this case, work is divided into three categories so that you can easily look at each separately, or look at them all under "Quarter Score."
When you’re done exploring, click the Quarter Score tab. The "Quarter Score" is incomplete.
Click through row 2 to see how this will eventually be set up. All of Bob Collins’ work in each category is linked to this sheet where his total average is calculated, including his Semester grade.

Let’s see how.

Click on cell D3.
Next press the equal sign so that it appears in the Formula Bar above. Linking spreadsheets is done with clicks of the mouse.
Click the Reading tab.
Click D3 (or where this student's Average is located) in the "Reading" spread sheet.
Press the Enter key.
Excel returns to "Quarter Score" and we see that Whoopie Goldberg’s average in reading is now listed.
Save work.
Follow the above procedures for "Let’s see how" and link Whoopie’s Vocabulary and Writing scores.
Next, use the Fill Down command from the "Edit" menu to copy the linking formula to the remaining students’ scores.
Save work.

Before using Fill Down for "Quarter2 Score" and "Semester," click Bob’s so that you can see how formulas serve different purposes:

Quarter 2 Score weighs the values of the categories differently.

Reading is 30%, Vocabulary is 20%, and Writing is 50%.

This formula is: Reading X .30 + Vocabulary X .20 + Writing X .50 = Quarter 2.

The semester is also weighted.

Quarter 1 is 40%, Quarter 2 is 40%, and the Final Exam is 20%.

This formula is: Quarter 1 X .40 + Quarter 2 X .40 + Final Exam X .20 = Semester Grade.

Use the Fill Down command to copy formulas for Quarter2 Score and Semester into all of the students’ grades. Save work.
Click cell D10 and D11. See how the Maximum and "Minimum" commands replace "Average" for its own range. Unlike the one’s done earlier, these are based on a range within one column, rather than a row.
Click on D10 and highlight through to J10. Then click Fill , then Right from the "Edit" menu. The formula is copied across, instead of down. Save work.
Click on D11 and highlight through to J11. Then click Fill , then Right from the "Edit" menu. The formula is copied across, instead of down. Save work.

Formatting Stat Appearance

Highlight the entire table, from cell A1 to J11 and open AutoFormat from the "Format" menu. Choose a table design and click OK.

Save work.

Making Charts

Charts can be very useful visuals of work and tracking progress. Here’s a simplistic way of getting started. Afterwards, experiment with different variables. Use the "Help" button to get a more detailed explanation.

Highlight cells D1 through F7.
Click Chart under the "Insert" menu.

The Chart Wizard box appears. It provides 4 steps to making a chart. Use the following instructions to make the first chart, just so you get your feet wet. Then go back and try different variations using the "Office Assistant" that appears as you work (If it does not, click the "?" in the bottom left corner).

Step 1: Pick the Column chart style (you can come back soon and try other charts).

Click "Press and hold to view sample" to see a preview (Neat huh!).

Click the Next button.

Step 2: View the Data Range (In Column view) and Series tabs.

Click the Next button.

Step 3: Click through the tabs.

Name Chart Title: Semester Grades

Choose a Legend Placement other than Right.

Click the Show Data Table.

Click the Next button.

Step 4: Decide where to locate the chart on any of the worksheet tabs. Click the down arrow next to Quarter Score and choose Reading.

Why under Reading? Because you can (and just to see how it can be moved).

Click the Finish button.
The black squares on the chart allows you to resize by clicking and dragging on the squares. Click on the center of the chart and drag it below the spreadsheet information.
Make sure your information is highlighted and try a different chart. Click the Quarter Score Tab before making other charts.
When you’re done, Save your work.

Creating a Web Page

Open the Quarter Score tab.

Highlight C1 through J7.

When posted on a web-site, this grade sheet will list student grades by their ID #, to keep results anonymous.

Click Save As HTML… under the File menu.

4 Steps

Click Next, since we've already selected our range. If you want to know depth about this step, click the "?" at the bottom left corner for the Office Assistant who will appear and give guidance.
Click Next. You have the option to create a new page or attach results to previously made web page. Let's make a new page (Top option).
Make sure your name is listed in the "by:" box at the bottom. If not, type it in. Also, change the Header to "Semester Scores"
Click Next.
This step determines where the page is saved. Click the Browse button and select the zworkshop folder to save your file. Rename the file as 1mygrades.htm
Click Finish.
To view the file, click Open in the File Menu.
Under Files of type, click All Files.
Search for and click your file in the zworkshop folder.
Click Open.
When you are done viewing your web page, click Exit from the File menu.

Note:

Spread sheets can be used to merge data on to a form letter in Word. Just follow the steps in Word to merge.

You’ve created a grade book using various formulas and book links, explored chart making, and created a web page.

CONGRATULATIONS!!!