How to for secd 210

1. How to for secd 210 How to make an Excel Table & How to Mail Merge

2. Excel table GRADE BOOK

3. Excel Table • Step 1: • Enter your top headings starting in Row A Column 2 • All information will be in Row A • These are “students name”, “guardian name”, “address”, “HW”, “test”, “quiz”, “participation”, ect.

4. Excel cont. • Step 2: • Enter side headings starting in Row B Column 1 • All information will be in Column 1 • These are your student numbers, typically alphabetical

5. Excel cont. • Step 3: • Fill in your information • Names, addresses, grades, ect. • You need to add functions to calculate averages • This will be the AVERAGE or SUM function • You need to add comments on a secondary spreadsheet with a look up function • This will be the VLOOKUP function I have to do MATH??

6. Averages/Sums • Student averages can be created by selecting the AVERAGE or SUM button under the function or “fx” key • You must then select the cells you want that math done on • If a particular score is weighted, this is when you would enter the weights individually • Example: • =0.2*(SUM(L2:P2)/5)+0.3*(AVERAGE(Q2:R2))+0.5*(S2) • 20% on 5 HWs + 30% on 2 Quizes + 50% on a Test Note: fill all cells in which you want the averages, the program will automatically change the data for each student

7. Lookups *This is how you would add a comment to your spreadsheet depending on a students grade* • Comments need to be listen in a separate worksheet (bottom left of page) • Create the table giving the low end of the grade range in one column (0,60,70,ect.) and the comment in the cell next to it • Select the function “fx” button and choose VLOOKUP

8. Lookups cont. • “Lookup-Value” • These are the current overall grades of each student • Select the cells containing their overall grade formulas • “Table-array” • Select all the cells that have the cutoffs and their comments • Be sure to enter $ before the letters and numbers to signify that the cells don’t change (useful for the filling) • Col-index-num • Enter a number 2 here • “Range-lookup” • Enter TRUE Note: fill all the cells for the comments, the program will automatically change the lookup depending on the students grade

9. SAVE!!!!

10. Mail Merge Letters to Parents!! Bad News? or Good News?

11. Getting Started • Open up a Microsoft Word document • In the main bar, select “Mailings” • Then select “Start Mail Merge” • Under this choose “Letters”

12. And then… • Then select “Select Recipients” • Under this choose “Use Existing List” • Find your saved grade book spreadsheet • Then it will give you options which to choose, pick the worksheet with the names and grades entered on it

13. Next… • Enter your school districts mailing address at the top • The select “Insert Merge Field” • You should now see a list of your headings • Choose the appropriate information for the Guardians name and mailing address • Enter the body of the letter

14. Almost there! • Using “Insert Merge Field” will allow you to add a full listing of your students grades and comments • Now you are ready to “PREVIEW RESULTS” • This will give you a continuous list of all your students letters……..BE SURE TO MAKE SURE EVERYTHING IS CORRECT!!!

15. SAVE!!!!

16. Now you know how to create excel tables and do a mail merge letter! Go forth and enjoy your saved time from the calculator averages and the individually written letters home!!