How to merge Grade book in Excel with a Progress Report ...



Multi-sheet Grade Book - Using MS Excel

Created by Lynnette D. Morrison

MS Exemplary Teacher

Clarksdale Public Schools

dakota@

Downloadable in three versions from

Look on the Tools page.

Contents of Grade Book 4 x 4

1st Block A

2nd Block A

3rd Block A

Progress A

1st Block B

2nd Block B

3rd Block B

Progress B

1st Block Semester 1

2nd Block Semester 1

3rd Block Semester 1

1st Block C

2nd Block C

3rd Block C

Progress C

1st Block D

2nd Block D

3rd Block D

Progress D

1st Block Semester 2

2nd Block Semester 2

3rd Block Semester 2

Absentee 1

Absentee 2

Absentee 3

Absentee 4

Roll Call

Open House

Info

Contents of Grade Book 6 Class

1st Period A

2nd Period A

3rd Period A

5th Period A

6th Period A

7th Period A

Progress A

1st Period B

2nd Period B

3rd Period B

5th Period B

6th Period B

7th Period B

Progress B

1st Period Semester 1

2nd Period Semester 1

3rd Period Semester 1

5th Period Semester 1

6th Period Semester 1

7th Period Semester 1

1st Period C

2nd Period C

3rd Period C

5th Period C

6th Period C

7th Period C

Progress C

1st Period D

2nd Period D

3rd Period D

5th Period D

6th Period D

7th Period D

Progress D

1st Period Semester 2

2nd Period Semester 2

3rd Period Semester 2

5th Period Semester 2

6th Period Semester 2

7th Period Semester 2

Absentee 1

Absentee 2

Absentee 3

Absentee 4

Roll Call

Open House

Info

Contents of Grade Book Elementary

Social Studies 1

Science 1

Math 1

Reading 1

English 1

Spelling 1

Progress 1

Social Studies 2

Science 2

Math 2

Reading 2

English 2

Spelling 2

Progress 2

Semester 1

Social Studies 3

Science 3

Math 3

Reading 3

English 3

Spelling 3

Progress 3

Social Studies 4

Science 4

Math 4

Reading 4

English 4

Spelling 4

Progress 4

Semester 2

Yearly Average

Absentee 1

Absentee 2

Absentee 3

Absentee 4

Info

How to merge your Excel Grade book using Word, to make a Progress Report listing only the Average:

Open the Grade Book that you have been given. Add student names and grades for the period covered by the progress report; make certain that you have moved the progress page for that grading period to the front of the grade book. In order for the merge process to work with Excel, the information that you are looking to merge into the Word file must be in a worksheet that is in the front of the workbook. To do this you should click once on the PROGRESS tab. Then click on the tab and hold the mouse button down while dragging that sheet to the front of the workbook.

You must create a Word document that is designed in the way that your school district's progress report is designed. The items you include in your merge will depend on what you school district requires on your progress reports.

*After you have created a progress report for your school district, you should save it. With this progress report open you should then:

Go to TOOLS

Select MAIL MERGE

Click on CREATE

Click on FORM LETTER

Click on ACTIVE WINDOW

Click on GET DATA

Click on OPEN DATA SOURCE. You must now find your grade book that was created in Excel by changing the FILE OF TYPE to MS Excel Worksheets and selecting the worksheet you want and then click OPEN.

Click OK for it to use the Entire Spreadsheet

Click EDIT MAIN DOCUMENT.

This process adds two new buttons on the upper left corner of your tool bar. These buttons say, INSERT MERGE FIELD and INSERT WORD FIELD.

Place the cursor in the place in your progress report document that you want to insert the field (information from your Excel Grade Book). Click on the INSERT MERGE FIELD button on the tool bar and select the field you want to enter there. Repeat this until you have included all the information needed into your progress report.

Then:

Click on the MERGE TO PRINTER button on your mail merge tool bar.

This will create a new document that contains one page for each student listed on that worksheet page. You will be printing a separate sheet for each student in your progress report.

To list all of the grades earned during a grading period:

If your district requires that you list each of the individual grades and not just the average you will need to do the following to each class worksheet. Remember DO NOT SAVE THE CHANGES when exiting.

Move the class sheet you will be working with to the front of the Excel Workbook. To do this you should click once on the tab of the class sheet that you want to use. Then click on the tab and hold the mouse button down while dragging that sheet to the front of the workbook.

Select the first box by clicking one time on the box. You will then click and while you are still holding down drag down to line 9 and across to row AB. (This is the last column in the grade book given to you. You will need to make certain this is the last column before you do this and make any necessary adjustments. ) Let go and this entire section should be highlighted. Click edit and delete. A delete dialog box will appear. Make certain SHIFT CELLS UP is highlighted and click OK. Do NOT close this sheet.

You have done this to make the first row on that grade book worksheet be the row that has the heading of Last name, First name, 1st 9 Avg., and the names of each of your tests. This will make these headings to be those that will appear in the INSERT MERGE FIELD drop down menu when you are inserting the fields into your Progress Report document. Go to the beginning and open the Progress Report in Word. Follow the previous directions starting with the *. Remember when you are done DO NOT SAVE CHANGES TO YOUR EXCEL WORKSHEET.

To change the Name of the Worksheet Tab:

Click one time on the tab to highlight it.

Right click.

Click on Rename.

Type a new name.

To Move a Worksheet:

Click once on the worksheet tab.

Right click on the tab.

Select MOVE or COPY.

A move or copy dialog box will appear. Highlight the name of the worksheet that you want this worksheet to be placed in front of in the Before Sheet section

Click OK

To add a comment to a cell:

A comment appears as a small red triangle in the upper right corner of a cell. To add a comment to a cell you will do the following.

Click in the cell to which you want a comment added

Click on INSERT

Click on COMMENT

Type in the information that you want to appear in the comment box.

Example of the Merge Helper Dialog Box. The buttons are grayed out until they become available for use.

[pic]

Two new INSERT fields will appear.

[pic]

Field Choices to put in your document.

[pic]

To change the type of file from a Word Document to an Excel Worksheet.

[pic]

[pic]

Rename Worksheet Tab

[pic]

Excel Basics for creation of the Grade Book template:

Connecting multiple worksheets:

To reference cells in other sheets in a workbook, you use the sheet name, also called the Sheet Reference. For example, you refer to cell A2 on the 1st block A sheet in the following fashion:

=1st Block A!A2

( ( (

( ( (

( ( Cell Reference

( (

( (

Sheet (

Name (

Exclamation

Point

Thus, one way to have the same information from Cell A2 in all of the sheets is to reference it from the first sheet to each of the other sheets. Therefore, in Cell A2 in sheet 2nd Block A you would have the following input.

=1st Block A!A2

You can enter a sheet reference by typing it or by clicking the sheet tab to activate it. When you click the sheet tab, Excel automatically adds the name followed by an exclamation pint at the insertion point in the formula bar and activates the sheet. Next, slick or drag through the cells you want to reference on the sheet. This technique allows us to copy information found in one sheet to a place in another sheet. As the information found in each sheet, that gives us the 1st Nine weeks average. This information is also needed in the Progress report sheet and in the Semester averages sheets. The students names are needed in each of the nine weeks sheets and we only want to type the information in once. All we need do is reference this information in each sheet that it is needed and it will be there.

Entering formulas in Excel:

Valid Excel arithmetic operators include + (addition), * (multiplication), / (division), % (percentage), and ^ (exponentiation).

Order of Operations:

When more than one operation is involved in a formula, Excel used the same order of operations that algebra follows. Moving from left to right in a formula, the order of operations is a follows" first negation (-), then all percents (%), then all exponentiations (^), then all multiplications (*), and divisions (/), and finally all additions (+) and subtractions (-). You can use parentheses to override the order of operations. All operations within parentheses are performed before the operations outside the parentheses.

Functions:

A function is a prewritten formula that takes a value or values, performs an operation, and returns a value or values. The values that you give to a function to perform operations on are called the arguments. All functions begin with an equal sign and include the arguments in parentheses after the function name. For example in the function =AVERAGE (B4: B8), the function name is AVERAGE and the argument is the range B4: B8. The following is a list of functions that are used in the Grade Book template workbook.

AVERAGE - for an average of a range of cells

MIN - to find the minimum number in a range of cells

MAX - to find the maximum number in a range of cells

SUM - to add the numbers in a range of cells

HLOOKUP - to look up the proper letter grade when a number grade is given.

-----------------------

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download