Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
This week on Teacher Tips, we're using Microsoft Office to create better worksheets for our students. Here in my screen, I have a Microsoft Office worksheet that we've already gone through, and made more student friendly. We've changed some of the fonts. We've allowed for some additional spacing and formatting to make things easier to read. And we've made sure that students have clear instructions such as arrows, as well as expectations. In this video, we're going to go through and talk about how we can quickly modify this assignment for a variety of needs in our classroom. We're going to do this actually in Microsoft Excel. Here in Excel I've imported a very basic student list.
My students are listed by first and last name. An you can create Excel lists from most electronic grade books, including PowerTeacher Gradebook, and Engrade. Next to the student names, I've also created a column called Modification. Here I can indicate whether my students have reduced work load or if I need to have a modified assignment. For this video I'm using only two different modification codes. In your own classroom you might have four, five, or even six modification codes based on the makeup of your students. The next thing I've done is I've created a column for the answer to the day vocab question.
For a page number for short answer number one and also for the number of sentences required for the worksheet. What we're going to do is write some formulas to determine whether or not which students will receive the answer to the day vocab question. If students will receive the page number to help them with the short answers, And also to determine how many sentences each student is required to write. And will also determine how many sentences each student needs to write for the short response. Let's go ahead and dive into the day vocab answer. There are five different vocabulary words that students have to match up.
For my students that have a reduced workload, I would like to give them the answer to one of those five to allow them to narrow down their odds. To do this I'm going to use an If then function. The formula's is broken up into an argument or evaluation and then two parts. One part of what's going to happen if it's true and the other of what's going to happen if it's false. I can write this formula by highlighting the cell. I'm going to use the equals and write the word If. Or use an opening bracket and now I'll need to write my logical test or the what do I want to check. For this particular cell I want to check if cell B2 is equal to RW.
Now because I want it to be equal to letters or a word I need to put RW in parenthesis. So, if B2 equals RW I'm going to put a comma. Then I want to put what’s going to happen if it’s true. If it does equal RW, I want to tell the students that the answer to this question is D. I’ll put another comma. And if nothings answered I want nothing to appear. In Microsoft Excel when you use quotation marks instead of a formula it says to literally put whatever information is between those quotation marks or evaluate whatever information is between those quotation marks.
So in this case, I’m just going to put two quotation marks for the false answer. So it'll eventually return nothing. Now I'm going to go and close that function and then hit the Enter key. Now for Kristen she has an RW modification and it's telling me that the is going to be D. What I can do now is just copy that cell and I can paste it all the way down for the rest of the class by using the keyboard short cut of Cmd+C and Cmd+V. I'm telling Excel it needs to put the letter D in the day vocab answer spot.
The next section is a short answer. For students that have modified assignments I want to give them a page number in the textbook to help them out. So I'm going to go ahead and write another if then function. I'm going to say if in my opening bracket. So if B2 Is equal to this time MA. Then what I'd like to do is tell the student that it's page 17 in the text book, and if not I want nothing to return there. I'll go ahead and hit Enter. You'll notice that for the first cell nothing happened. Well let's go ahead and copy that cell. And paste it through the entire class.
You can now see that every student that has an MA, or modified assignment, is going to have page 17 showing up on their particular worksheet. The last thing we need to do is determine the number of sentences. This particular one's going to be a little bit more complex. It's in the if then statements we've already written, here I'm going to go ahead and highlight the first cell. This particular formula's going to start off just as the same way the other ones did. I'm going to do equal, and if and opening bracket. I'm going to come over here and select B2 and say that if B2 is equal to RW, or reduced workload.
Then I would like the student to only write only one sentence. That's going to be my true variable. I'm going to put another comma and when we get to our false value instead of putting a value here. I'm going to put another formula. I'm going to do this by opening up a new bracket. I'm going to go ahead and write the word if again. Put an opening bracket for my logical argument and say if the B2 is equal to in this time MA. Then what I'd like it to return, I'll put a comma for my true value, is a two. If it's not true, I'd like it to put a three.
I'll go ahead and close out the argument. I'll close out the second if bracket, and I'll close out the first if bracket. So I'll end up with three different parentheses at the end of that particular formula. So let's go ahead and talk about what we just wrote there. What the formula's going to do is it's going to look at the modification. It's going to say does it have a reduced workload. If it says yes, hey you know what, you're going to write just one sentence it's going to move on to the next. If that student doesn't have a reduced workload then it's going to go ahead and ask does the student have a modified assignment. If it says yes, it's going to ask the student to write two sentences.
If it says no, it's going to ask the student to write three sentences. I'm going to go an hit the Enter key for this to take affect. I'm going to copy that cell, and paste that all the way down through the rest of the class. So here you can see the formulas working. We have student number one, Kristen Winney is writing one sentence because she's reduced workload. Dan Fishbach is an MA, or modified assignment, and he's going to write two sentences. Down here, Ash Poole, has no modification, and so she's going to write three sentences. Now that we have this Excel spreadsheet setup, I'm going to make sure we can save this.
And now I'm going to head back to Microsoft Office and attach this Excel spreadsheet to our Office document. So the way were going to utilize this spreadsheet is by using something called Mail Merge. I'm going to go up and select Tools in the upper navigation and I'm going to select the Mail Merge Manager. The first thing we need to is select a document type. What I'd like you to do is to create a form letter and I'd like to use the actual document we're currently using. I should see main document and the title of the document we have opened which is Earth and Space.docx. The merge type is a form letter merge. Meaning that it's going to take the entire letter and duplicate it as it goes through its merge process.
The next thing wee need to do is attach our Excel spreadsheet. I'm going to do that by clicking Get List under section two of the Mail Merge Manager. Here I'm going to chose to open a data source. On my desktop, here's that modified work excel spreadsheet that we just created and I'm going to go and click open that. Its telling me that it needs to be open by the Microsoft Excel workbook converter which may pose a security risk. For this particular mail merge that's okay. So I'll click the OK button. Now Microsoft Word's trying to figure how to read this particular worksheet. I'm asking it to read sheet number one, and for the cell range I want it to read the entire worksheet.
I'll go and click OK. You can now see that the titles that we had in row number one of Student Name, Modification, Day Vocab, Short Answer, and Number of Sentences. Are showing up as placeholders. What I can do at this point is drag these placeholders into various parts of the assignment and they will automatically be replaced by whatever's in our workbook. To get started, let's go ahead and enter the header. The first thing I want to do, is if I'm modifying this assignment, I want to know exactly which student I'm modifying it for. So I'm going to go ahead and delete out the student name section. And instead I'm going to replace it with the student name placeholder.
Now when these placeholders come in you can see that there's these double arrows on each side of the placeholder. All of the text, including the double arrows, will actually be replaced by the content in the cells inside of the workbook. What that means is if I don't put a space after student name and date, then the date would actually literally be attached to the student name. In fact I'm going to put a few spaces. I'll use the tab key to tab that over. And I'm actually going to take one tab out so in case it doesn't push it over too far when there's a student name in there. So now when the mail merge takes place, the student name we'll replaced with the names in the Excel spreadsheet.
I'm going to go ahead and close out of my header. The next thing we need to take care of is the day vocab answer. Right here under day, I'm going to highlight this section. I'm going to choose day vocab answer, and I'm going to go and just put it right there. Now you can see that it's kind of causing some formatting issues. And that's actually okay. What's going to happen is this de vocab answer is going to literally take the place of one letter or no letters. So as of right now, I'm going to leave it exactly as is. We'll take a look at the formatting once we get ready to complete our mail merge. As we scroll down, the next thing was short answer number one.
Here I have the opportunity to give the students a page number based on the modification. I'm going to go ahead and click on short answer one and drag it over to the document. Once again, we have a formatting issue. I'm just going to go ahead and leave this as is and we'll double check it again once we get ready to start the actual merge. The last piece that we need to take care of is the number of sentences. Down here I'm going to choose number of sentences, drag it into this particular area and release. I want to make sure there's a space after number of sentences and the word sentence. And I can actually take these leading spaces out. Okay.
So we've now taken all of the mail merge place holders and put them in our document. Over here in preview results, I'm going to go ahead and click on this ABC button and I'm going to view what this looks like as a merged document. As you can see, a lot of the issues that we had were automatically taken care of. Our first student, Kristen Whitty, and her name's appearing properly, had a reduced workload assignment. So you can see that I've given her the answer to this question, which is D. And also down here for the short answer, she only has to write one sentence.
So, so far Mail Merge seems to be working correctly I can also go under the preview results section inside the Mail Merge Manager. And choose to scan through the different records. Here at record three, which is for Greg Cruse, I can see that he does not have a reduced workload, so he's not getting the answer today. He does have a modified assignment, so he's getting page 17 to help him out with the short answer. And he's also only required to write two sentences for the last short answer. If everything looks correct, what I can now do is complete the mail merge. I have two options, I can either merge it straight to a printer.
What I like to do, though, is merge to a new document. When I do this, it's actually going to through and create the correct number of documents I need. At the very bottom I can now see that I have 22 pages in this document. As I scroll through it I can see that student's name have been properly added to the top of each document and the various elements of the documents now reflect that particular student's modifications. At this time I could quickly print these out, have a student in class pass them out to the correct students. And each student can receive an individualized assignment with their name RD on it and the modifications that they need, to be successful.
I hope you've enjoyed these tips and tricks in Microsoft Office and I look forward to seeing you guys next week with the next Teacher Tip.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90418 Viewers
80 Video lessons · 137882 Viewers
59 Video lessons · 56625 Viewers
52 Video lessons · 70239 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.