Get documents about "How to Simplify Writing Evaluations Using Excel and MailMerge
Document Sample
EvalsWare Tutorial 3/19/2002
How to Simplify Writing Evaluations
Using Excel and MailMerge
by Araceli Valle, Chris Lee, and Barbara Rogoff
This tutorial shows how to simplify writing student performance evaluations by using MailMerge to automatically
insert phrases from student records (in an MS Excel spreadsheet) into the appropriate place in a MS Word template.
The result is a personalized evaluation for each of your students in a Word document that you can edit freely.
How it works:
1. Create a template for the evaluations in Word, with <<PLACEHOLDERS>> to fill with individual students’
information (such as performance on homework, essays, presentations, and tests; timeliness and improvement).
2. Enter student information in an Excel spreadsheet.
Entry of phrases is aided by Excel’s AutoComplete feature (or a Lookup-Key approach).
3. Each student's information is automatically merged into the template (using Word’s MailMerge), and the
resulting text is easily edited in Word.
For example, the sample evaluation below shows the template common to all students in a psychology class, in
normal font, and information specific to each student in bold font. (You'll craft your own to fit your needs.)
&&&&,KELLY,DAVID,JR.,609123598,M,
Overall, David's performance indicated a good working understanding of the course material. His written work was
usually of good, sound quality, reflecting active engagement with the topic but needing to be pushed further.
David's score on the multiple choice exam was 75 which demonstrated satisfactory knowledge of the material.
In this example, to create the sentence evaluating David’s written work, the instructor formats a template phrase,
with <<PLACEHOLDERS>> for the students’ information:
<< HIS/HER>> written work was usually <<WRITTEN_WORK>>.
The software automatically inserts the description of David’s work from the spreadsheet, and fills out phrases
designated by the instructor. The instructor can further edit the resulting Word file before submitting it.
The tutorial assumes you know how to use MS Word to word process, but are not familiar with Excel.
(Using Excel is similar to Word. The inside of this cover gives basic tips for using Excel.)
For help:
Please contact Chris Lee, Evaluations Coordinator (459-1573, chrislee@ucsc.edu) for assistance with:
• The software (your faculty services staff or computer coordinator may also be of help)
• Designing your evaluations or writing a template
• Using an earlier version of Microsoft Word and Excel than 98 (Mac) or 97 (PC)
UCSC’s Guidelines for Performance Evaluations are very helpful for tips on what to include in evaluations,
available at: http://senate.ucsc.edu/cep. This tutorial is available online at: http://ic.ucsc.edu/evalsware.
EvalsWare Tutorial 3/19/2002
If you haven’t used Excel, here’s basic Excel tips for your reference for the tutorial (which starts on the next page).
To select a row, click on the button at the left of that row, which contains the row number.
To select a column, click on the button at the top of that column, which contains a letter that identifies that column (e.g., the
first column is column "A").
To select a rectangular area of the spreadsheet, e.g. from cell B3 through D5, select the cell in the upper left hand corner of
the rectangle (cell B3). Hold the mouse button and move to the lower right hand corner of the rectangle (cell D5).
To insert a row (or column), select the row (or column) after where the new row (or column) will be inserted. From the menu
bar, select Insert, then Rows (or Columns). To insert multiple rows (or columns) at once, select the desired number of rows (or
columns). (For example, to insert 3 rows after row 2, select rows 3-5, then Insert, and Row.)
To delete a row (or column), select the row (or column) to delete. On the menu bar, select Edit, then Delete.
To edit text in a cell, double-click on the cell. Or you can work in the Formula Bar, after
selecting the cell. The formula bar is the area above the spreadsheet itself.
To copy one cell into a whole column, select the cell you want to copy. On the menu bar, select Edit, then Copy. Select the
area you want to copy to. Select Edit, then Paste.
To adjust column widths, move the pointer to the line between the column designators (A, B, C... at the very top) until you see
a double sided arrow: and hold the mouse button while you expand or contract the width of the column. You
can also let the program automatically adjust widths. Choose Format from the menu bar, select Column, then AutoFit Selection.
To align text within a column, select the column, then click on the Align Left, Center, or Align Right menu bar button.
To allow column headings to take up multiple lines within a single cell, first select the cells in the title row by selecting the
cell with the first column heading (e.g., LNAME) and holding the mouse button while you move the mouse to include one cell
after the last column heading. Choose Format from the menu bar, then "Cells". A menu will pop up. Select the "Alignment"
tab, and click on the "Wrap text" button.
To color or shade the title row, select the "Patterns" tab (under Format; Cells) and select a Cell Shading Color.
To get gridlines (helpful if you print your spreadsheet), choose "File" from the menu bar, then "Page Setup…". Select the "Sheet"
tab, check the "Gridlines" option, then click OK.
To keep student names and column headings visible at all times, to facilitate the entry of student performance, select the first
cell containing performance information for the first student. With this cell selected, use the menu bar to select Window, then
Freeze Panes.
You can hide columns you don't need as you enter student performance (e.g., FMNAME, SUFFIX, SID, and GENDER). Select the
columns, then select Format from the menu bar, then Column, then Hide. (You can see these columns again by selecting the
columns to the left and right of them and selecting Format, then Column, then Unhide.)
Note: MS Word will not see any hidden columns. If you hide any, you must unhide them before the MailMerge.
EvalsWare Tutorial 3/19/2002 Page 1
Before starting, it will be helpful to have
1. Your classlist in Excel spreadsheet format. You can request this from nes@cats.ucsc.edu.
2. A draft of an evaluation template like this example:
Overall, (Student)’s performance indicated (overall eval) understanding of the course material. (His/Her)
written work was usually (written work eval). (Student)’s score on the multiple choice exam was
(mult choice score) which demonstrated (mult choice eval) knowledge of the material. (Improvement.)
To develop your draft template, you could look at 3-4 evals you’ve written previously for this class.
If you need help developing your template, contact Chris Lee (chrislee@cats.ucsc.edu, 459-1573).
Then, just follow these steps
A. Create a spreadsheet format for student performance (using Excel) ___________________________________________ Page 1
B. Create a template for the written evaluations (using Word MailMerge) ________________________________________ Page 2
C. Record students’ performance (using Excel's AutoComplete function*) ________________________________________ Page 3
D. Merge the template and student records, and edit the evaluations (using Word MailMerge) _________________________ Page 4
E. Add the class header and coursework description, and finish (in Word) ________________________________________ Page 4
*Optional alternative: A Lookup-Key to record students’ performance _________________________________________Appendix
This is a more systematic approach to recording student performance than AutoComplete. However, it takes longer to set up.
(You create formulas to tell Excel how to translate abbreviations or scores into phrases, using keys to list the alternatives.)
If you’re new to Excel, we recommend using AutoComplete. If you want to use the Lookup-Key approach, start with the Appendix.
A. Create the spreadsheet format for your student performance records (using Excel)
1. Open the Registrar’s classlist in Excel spreadsheet format, and make yourself a copy of it
(Save As… a new name).
In the copy, delete all information other than student name (and suffix), student ID, and gender.
(See the inside of the front cover for Excel tips, if you’re new to Excel.)
The Registrar does not need the student records to stay in the order in which they are sent to you. If it is more
convenient to submit the students’ names in another order, such as by TA section, that is fine.
2. Enter column labels for each type of student performance and one for the name the student "goes by", as
shown in the example below.
(Making a few optional cosmetic changes at this point makes the spreadsheet easier to use. The Excel editing
tips inside the front cover show how.)
3. Enter sample information (by inserting the 2 fictitious student rows above), to be able to check your
format in the MailMerge in step B. Save your file.
EvalsWare Tutorial 3/19/2002 Page 2
B. Create a template for the written evaluations (using Word MailMerge)
1. To connect your template to the spreadsheet, open a new document in MS Word, and on the menu bar,
select Tools, then MailMerge. A MailMerge Helper menu will pop up.
You will complete only steps 1 and 2 of that menu now.
In Step 1: choose Create, then select Catalog, and Active Window. Special Instructions for Word 2001 (Mac)
and Word 2002 (PC) users only:
In Step 2: choose Get Data; then select Open Data Source. The MailMerge Helper is now called
Using the menu that appears, locate and select the Excel spreadsheet Data Merge Manager. When you choose
you created in step A. Create, then Catalog, it automatically uses
the active window.
(You may need to select files of type "All files" to see the file.)
Another menu pops up asking for "Named or cell range:" Click OK. Ô After you select your Excel data file in
[If you’re using the optional Lookup-Keys, specify "StudentRecordsSection".] step 2, you’ll need to input the cell range
of your Student Records in this format:
A message pops up telling you that there are no merge fields in your A1:M15 (the range will vary depending
main document. Select "Edit Main Document". on the area of your student records).
This puts you back in the normal work area of Word, with two new In step B-2, simply drag and drop your
placeholders (merge field terms) into your
buttons right above the work area: "Insert Merge Field" and "Insert template. You’ll also find the
Word Field". You will use these buttons to tell the software to bring "If…Then…Else" feature (for handling
gender) in the Data Merge Manager.
information from the student records into your template.
2. To create your template, you simply type the phrases and punctuation that apply to all students,
and tell Word what information from each student record to include in places marked by
<<PLACEHOLDERS>>, to create a template like this:
&&&&,«LNAME»,«FMNAME»,«SUFFIX»,«SID»,«GENDER»,
Overall, «GOES_BY»'s performance indicated «OVERALL» understanding of the course material. His written
work was «WRITTEN_WORK». «GOES_BY»'s score on the multiple choice exam was «MC_EXAM» which
demonstrated «MC_EVAL» knowledge of the material. «IMPROVED»
Selecting the Insert Merge Field button will display a set of choices
that correspond to the column names in your spreadsheet. You’ll use
the labels from the Insert Merge Field menu to indicate in your
template which information specific to each student should be inserted.
These menu choices will appear as «PLACEHOLDERS » in your template.
For example, to set up the first line of your template to produce the
"student header" information required by the Registrar:
Type: &&&&,
Select Insert Merge Field, choose LNAME, and then type a comma
Select Insert Merge Field, choose FMNAME, and type a comma
Select Insert Merge Field, choose SUFFIX, and type a comma
Select Insert Merge Field, choose SID, and type a comma
Select Insert Merge Field, choose GENDER, and type a comma
Hit return
[If you’re using optional Lookup-Keys, use your Lookup columns, not the columns with abbreviations, for your placeholders
(e.g., OVERALL_EVAL instead of OVERALL).]
• Be sure to put a paragraph return at the end of your template so each student’s Eval starts on a new line.
• If you want a list or table format in your template, just format it in Word (as in ordinary word processing).
• You can have optional categories that will not be used for some students, by just leaving those student's
cells blank in that column of the spreadsheet (as with «IMPROVED» in the template and spreadsheet above).
• Note that you cannot copy your template to a new document – you have to rebuild the template so that
the placeholders link up with the student records.
• Note: The coursework description for the class as a whole does NOT go in the Evaluation template
(see step E-2).
EvalsWare Tutorial 3/19/2002 Page 3
(step B-2: Create a template, continued)
To insert the appropriate GENDER pronoun, don’t type His or Her. Instead, place the cursor at the spot in
the template where you need the gendered pronoun, then select Insert Word Field (the other new button),
and choose "If...Then...Else".
The Insert Word Field: IF menu pops up.
Select GENDER and fill in as shown, to get
"His" or "Her" as the starting word in the
second sentence of the evaluation.
You have to do this or a related IF statement at
each spot where you want to use a gendered
pronoun in the evaluation.
Insert Word Fields are not flagged in an
obvious way in the template text. However, if
you click on one, it appears in gray.
(You can also use "If...Then...Else" statements to make other kinds of contingent wording.)
3. When you think the template looks right, save the file as "MyTemplate".
4. To test your template, merge the file: On the menu bar, select Tools, then MailMerge and go to Step 3 –
Merge to: "New document", selecting "All" records to be merged. A new document comes up with the
merged evaluations.
5. You'll probably have something that doesn't work quite right. Just close the merged file without saving
it and go back to edit the template. When you are satisfied, save the final template as "MyTemplate".
C. Record students’ performance using AutoComplete (using your Excel spreadsheet from step A)
1. Enter the name each student normally uses in the "GOES_BY" column. This is the name that will
appear in the evaluations, so be sure to capitalize only the initial letter.
Save the file often while you work.
2. Enter each student’s performance information in the cells. Excel's AutoComplete function will
suggest the phrase you want, based on the phrases you have already entered that begin with the
same initial letters. (Be careful with phrases that start similarly, such as "excellent" and
"extremely"!) You can edit the phrases once AutoComplete fills in the cell, to customize.
You can use long phrases or sentences if desired, as in David’s WRITTEN_WORK on the cover page,
taken from this menu of phrases for WRITTEN_WORK:
• extraordinary, with coherent analyses that integrated ideas and evidence in well-developed reflections
• very well developed, with clear connections between ideas and evidence to support the arguments
• of good, sound quality, reflecting active engagement with the topic but needing to be pushed further
• just satisfactory, reflecting some engagement with the issues, but not sufficiently grounded in the course material
• not satisfactory, either without adequate engagement with the topic or not turned in at all
To set up the AutoComplete function, go to the Excel Tools menu, choose Preferences, then Edit,
and then Enable AutoComplete for Cell Values.
(For some versions of Excel, it may be in the Edit menu of the menu bar.)
EvalsWare Tutorial 3/19/2002 Page 4
D. Merge the template and student records, and edit the evaluations (using Word MailMerge)
1. Make sure you have saved your latest version of the spreadsheet, so that the connections to Word can be
made properly. Open the final template you created in step B ("MyTemplate", in Word).
On the menu bar, select Tools, then MailMerge. The MailMerge Helper menu will appear. Skip Step 1.
In Step 2, select Get Data, then Open Data Source, and open your Excel file.
In the "Named or cell range" menu that pops up, click OK.
[If you’re using optional Lookup-Keys, choose "StudentRecordsSection".]
(Word 2001 Mac and 2002 PC users: Consult the note in step B-1.)
In Step 3, select Merge, and Merge to: "New document", selecting "All" records to be merged.
If you have lots of students, this can take a few minutes.
(A record counter is at the bottom of your Word window.)
2. You can now read through the evaluations and make any final edits or individual comments that
aren't reflected in the template.
You can fix common glitches in template-generated evaluations by selecting Edit, then Replace.
For example, use of "a" with words starting with a vowel, such as "a extraordinary" can be replaced
with "an extraordinary". Select the Replace All button to fix this glitch in all records.
[If you’re using Lookup-Keys, be sure to look through the columns to ***customize*** and to make sure things look right
(e.g., invalid abbreviations may appear as "#N/A").]
3. Save your Evaluations file (in Word) and close "MyTemplate" without saving it.
E. Add the class header and coursework description, and finish
1. Now add the class header information required by the Registrar: Open the classlist sent by the
Registrar and find the line that starts with "@@@@" and ends with the instructor’s name, just
before the start of the student lines. Copy this line and paste it to the very beginning of your
Evaluations file, before any of the students’ evaluations.
(If you’re copying it from an Excel file, you may need to use "Convert Table to Text" from the Word "Table" menu.)
2. Insert your coursework description between this line and the first student header line.
Note that you provide the coursework description only in this one place in your Evaluations
file. It is automatically included with each student evaluation by the Registrar.
The first few lines of the Evaluations file should now look like this:
@@@@,PSYC 080D 01 ,54996,011,ROGOFF,B.
Interdisciplinary course on the cultural basis of human development; involved twice weekly essays. 138 students.
&&&&,KELLY,DAVID,JR.,609123598,M,
Overall, David's performance indicated a good working understanding of the course material. His written work was
3. Save the Evaluations file.
Save your file once more, this time as a text file to submit (in Save As… select the "Text Only" option).
Because student information is confidential, please be sure to save your file in a private location.
Keep your template and spreadsheet files, to be able to edit rather than creating them from scratch
the next time you or a colleague teaches the course.
EvalsWare Tutorial 3/19/2002 Appendix
Appendix. Optional alternative for entering student performance: Lookup-Keys
The Lookup-Key approach is a more systematic way to enter student performance, but takes more set-up than the AutoComplete approach (on page 3).
You can combine the AutoComplete approach with Lookup-Keys, as desired. This appendix shows how to create Lookup-Keys to tell Excel how to
expand abbreviations or numbers into phrases.
1. Follow step A-1 (page 1) and create column headers and 2 new rows for 2 mock students (as shown below). In the column header line,
you will have paired columns for each type of performance, one for abbreviations or scores, and the next one for Excel to expand into a
phrase (e.g., the OVERALL_EVAL column to the right of the OVERALL column).
2. Name the part of the spreadsheet that contains student record information, so MS Word can find it. Select the area containing student
records, starting with the row containing the column names and ending a few columns beyond the last column of the last student record.
On the menu bar, select Insert, then Name, then Define. Enter StudentRecordsSection , and OK.
3. Make a key area by inserting rows above
the header row containing the LNAME cell.
(Insert enough rows for the evaluation
category with the most choices.)
In the first row, column F, enter Key: .
Then enter Key abbreviations and scores
as shown. Save your file often.
Creating an evaluation phrase from an abbreviation, using OVERALL to demonstrate.
(You could use the same procedure for creating much longer phrases or sentences, like those for WRITTEN_WORK in step C-2 on page 3.)
1. In the "Key" area of the OVERALL column, enter the abbreviations for OVERALL performance,
one per row, starting in the first row.
Then in the OVERALL_EVAL column, enter the evaluation phrase you want to use, next to each
abbreviation. Be sure to use lower case and caps as you want the phrases to appear in the evaluation.
("TBD" is for customizing evals for performance that does not quite fit pre-set categories.)
2. To identify the OVERALL Key, select the section of the spreadsheet containing the abbreviations for
OVERALL and their corresponding evaluation phrases.
On the menu bar, select Insert, then Name, then Define. A Define Name menu pops up.
Enter OVkey in the box labeled "Names in workbook", then select "OK".
Note that what you enter is case-sensitive, and spaces are treated as characters.
3. To get Excel to look up the translations for your abbreviations, go to the first student’s
OVERALL_EVAL cell, and enter: =vlookup( . Then use the mouse to select the cell to the left, for
the first student's OVERALL abbreviation. (The program will insert the coordinates of the cell
selected, e.g., G10 or +G10.) Continue entering: ,OVkey,2,false) .
4. After you hit return, the appropriate evaluation phrase will appear in that student’s cell.
Copy the cell containing the vlookup function (which now has the first student's Eval phrase) and
paste it in all the other students' cells in the OVERALL_EVAL column. Excel will insert the phrases for each student.
Creating an evaluation phrase from a numeric score, using MC_EXAM to demonstrate.
1. In the "Key" area of the MC_EXAM column, enter the lowest score you want to allow for each evaluation phrase.
Enter the evaluation phrases in the adjacent cells in the Key, in the MC_EVAL column (in lower case).
To handle ranges, the scores must be in ascending numerical order, so your first entry will always be 0.
(In our example, "good" is used to describe scores of at least 80 but below 90.)
2. To identify this Key, select this section of the spreadsheet with the scores and their evaluation phrases.
On the menu bar, select Insert, then Name, then Define. Enter MCkey as the name for this table, then OK.
3. To get Excel to look up the translations for the students’ scores, go to the first student’s MC_EVAL cell, and enter: =vlookup( .
Then use the mouse to select the cell for the first student's MC_EXAM score. (The program will insert the coordinates of the cell selected.)
Continue entering: ,MCkey,2) .
4. After you hit return, the appropriate evaluation phrase will appear in that student's cell.
Copy that cell and paste it in the rest of the students' cells in the MC_EVAL column. Excel will insert the phrases.
Then follow steps B, D, and E (pages 2-4) to create the template, merge the files, and finish.
When you enter your students’ performance, be careful not to enter anything in your Lookup (_EVAL) columns.
(If you accidentally do, you can recover the cell’s formula by copying the cell above the one you wiped out into the current cell.)
Suggestions of specialized uses for using optional Lookup-Keys (from Appendix)
It is possible to calculate many statistical functions in Excel. These can be included in your spreadsheet and used to set range
cutoffs for numeric entries. (See the statistical functions help topic in Excel.)
In the Lookup-Key approach, you may find it convenient to customize an evaluation as you enter text. Instead of flagging the
parts of the evaluation that need to be customized, you can customize a cell for a single student in Excel itself. Copy the cell, then
Edit and Paste Special, selecting "Values". The cell now contains the text rather than the formula, and can be edited like any other
text cell.
You can create entire sentences in a single cell by nesting functions. For example, to create a WRITTEN_WORK_EVAL sentence
(including the pronoun selection) entirely in Excel, enter the following formula in the WRITTEN_WORK_EVAL cell:
=CONCATENATE(IF(E10="M","His","Her")," written work was usually",VLOOKUP(I10,WWkey,2,FALSE),".")
You can use the preceding two suggestions in combination to build each sentence of your evaluation in Excel itself.
The Word template would then simply include the student header row, followed by something like this:
<<OV_SENTENCE>> <<WW_SENTENCE>> <<MC_SENTENCE>>
Related docs
