Advanced MS Excel (Mailmerge & Gradebook)
For this unit, you will apply the knowledge you learned in the Basic MS Excel Module as a productivity tool to serve the needs of your lesson plan / teaching. Your task is to use MS Word's Mailmerge feature to create a mass mailing / personalized letter. In addition, you will learn about "HLOOKUP" and "VLOOKUP" functions to assign students' letter grade.
Before we begin, if you haven't done so, spend sometime reviewing the overview videos about Microsoft Word, Excel, Publisher, and PowerPoint. This will help you familiarize yourself with the software interface and how to navigate around. Pay close attention to how you can access and navigate the software features and functionalities.
| Microsoft Office Overview Videos |
Resources
Mail-Merge in MS Word 2007
- Use mail merge to create and print letters and other documents
[http://office.microsoft.com/en-us/word-help/use-mail-merge-to-create-and-print-letters-and-other-documents-HA010081976.aspx] from Microsoft.com - Word mail merge: A walk through the process
[http://office.microsoft.com/en-us/word-help/word-mail-merge-a-walk-through-the-process-HA001034920.aspx] from Microsoft.com
Lookup funstions in MS Excel 2007
- Use HLookup and VLookup functions to find records in large worksheets
[http://office.microsoft.com/en-us/excel-help/use-hlookup-and-vlookup-functions-to-find-records-in-large-worksheets-HA001056320.aspx] from Microsoft.com - Learning VLOOKUP in Excel
[http://www.timeatlas.com/5_minute_tips/general/learning_vlookup_in_excel] from Productivity Portfolio
Basic MS Excel Tutorials
- Microsoft Excel 2007 Tutorials
[http://www.fgcu.edu/support/office2007/excel/index.asp] from Florida Gulf Coast University - Microsoft Excel 2007 Overview (Document version)
[http://its.psu.edu/training/handouts/excel_overview.pdf] from Pennsylvania State University - Microsoft Excel 2007 Overview (Recorded Training: Required Adobe Connect Plug-in)
[https://breeze.psu.edu/p55384547/] from Pennsylvania State University
Module Activities
Activity #1 (Individual): Mail-Merge AND HLOOKUP/VLOOKUP for Your Individual Lesson Plan
Purpose: To incorporate MS Word and Excel spreadsheet to produce a mass mailing and utilize MS Excel's HLOOKUP / VLOOKUP functions to lookup data / information in a spreadsheet of your lesson plan.
Process: Individually, you will identify contents and create a mass mailing / personalized letter (such as: letter to parents, students, oretc.) and a spreadsheet information (such as: gradebook) as one of your lesson plan's instructional materials.
EX #1: Mail-Merge
For this unit you will follow below procedures, and they are:
- Create a personalized letter and identify each location for your data
- In-class activity files: [Invitation Letter to Parents]
- View Letter to Parents Examples for ideas
- Create a data file spreadsheet containing all information identified above
- In-class exercise files: [Students Database]
- Merge a spreadsheet data to a letter (Tutorial by Dr. Hossain)
Important: A complete mail-merge project must have:
- A "data file" using MS Excel that contains
- At least ten (10) field names (columns)
- At least fifteen (15) records (raws)
- A finished and merged "letter" file using MS Word that has
- At least fifteen (15) pages (one-page letter for fifteen individuals is equal fifteen pages: 1x15 = 15
- Been saved and titled as
- "INDV_MM_DATA_YOURNAME.xlsx" for Excel "data file"
- "INDV_MM_LETTER_YOURNAME.docx" for Word "letter template"
- "INDV_MM_MERGED_YOURNAME.docx" for Word "merged letter"
EX #2: HLOOKUP or VLOOKUP
Important: A complete budget spreadsheet must have:
- a grading scale identified
- In-class activity file: [Students Grade]
- a minimum of 20 students / records
- a minimum of 6 grading categories
- all Final, Overall, Percent, and Letter Grade cells auto calculated by using formula
- be saved and titled as "INDV_LOOKUP_YOURNAME"
Activity #2 (Collaborative): Mail-Merge OR HLOOKUP/VLOOKUP for Your Thematic Unit (Teacher Education) / Organization (Sport Administration)
Purpose: To incorporate MS Word and Excel spreadsheet to produce a mass mailing and utilize MS Excel's HLOOKUP / VLOOKUP functions to lookup data / information in a spreadsheet as part of your team thematic unit / organization.
Process: Collaboratively, you and your team members will identify contents and create a mass mailing / personalized letter (such as: letter to parents, students, oretc.) and a spreadsheet information (such as: gradebook) as part of your thematic unit / organization.
Group exercise: Choose one of the below exercises to complete as a collaborative project
EX #1: Mail-Merge
Important: A complete mail-merge project must have:
- A "data file" using MS Excel that contains
- At least ten (10) field names (columns)
- At least fifteen (15) records (raws)
- A finished and merged "letter" file using MS Word that has
- At least fifteen (15) pages (one-page letter for fifteen individuals is equal fifteen pages: 1x15 = 15
- Been saved and titled as
- "COLL_MM_DATA_YOURTEAM.xlsx" for Excel "data file"
- "COLL_MM_LETTER_YOURTEAM.docx" for Word "letter template"
- "COLL_MM_MERGED_YOURTEAM.docx" for Word "merged letter"
EX #2: HLOOKUP or VLOOKUP
Important: A complete budget spreadsheet must have:
- a minimum of 20 records
- all cells auto calculated by using either "HLOOKUP" or "VLOOKUP"
- be saved and titled as "COLL_LOOKUP_YOURTEAM"
Submission
When finished, you, as an individual, should have FOUR (4) final files:
- INDV_MM__DATA_YOURNAME,
- INDV_MM_LETTER_YOURNAME,
- INDV_MM_MERGED_YOURNAME, AND
- INDV_LOOKUP_YOURNAME
In addition, as a group, you and your team member should have FOUR (4) files submitted for collaborative project:
- COLL_MM_DATA_YOURTEAM.xlsx
- COLL_MM_LETTER_YOURTEAM.docx
- COLL_MM_MERGED_YOURTEAM.docx AND
- COLL_LOOKUP_YOURTEAM
Upload the finished files along with other assignments to the appropriate dropbox (as indicated by your instructor).