Category: MS Office

Outlook: Moving up or down?

It’s been a few months since I posted, and I’m emerging from my technology sabbatical. Fall quarter is in full-swing; it’s time to share what’s new.

It’s often the day-to-day kinds of activities where a little change can make a big difference. My focus in this post will be changing a default setting in Outlook that affects the order in which Outlook shows you messages.

Outlook assumes that you want to start reading the most recent message first. After deleting or filing the first message, Outlook takes you to an earlier message.

But that’s not how I read my messages. I start with the earliest one I haven’t read and then move forward in time toward the most recent message.

If I had no other mail in my inbox, it wouldn’t be a problem. However, mail I haven’t decided what to do with yet stays in my inbox until I have time to get to it. For instance, in the example I’m using, I may begin reading with the email marked with the arrow below.

After I delete or file that message, Outlook automatically takes me to the message below it. But I’ve already read that message. I want to move to the one above it. To do that, I have to use the arrow keys or the mouse to navigate. Or I can change Outlook’s default setting so that it moves up instead of down.

In Outlook, go to the File tab and select Options. Click Mail. Scroll down to the very bottom of the screen. In the dropdown menu, select “open the previous item.”

Click OK.

Now when you delete or file email messages, Outlook will automatically advance to the next most recent item.









Learning Student Names: An Excel Solution

A few years ago I started letting students send me their assignments electronically if they wished. I found that I was writing more on the papers I was grading electronically, and my typing was much more legible than my handwriting! A year or so ago, I made this a course requirement. All assignments now need to be sent electronically. I’ve written before in this blog about how I manage this; for those posts, type ‘grading’ in the search box.

As much as I’ve enjoyed going paperless, I’ve discovered an unintended consequence. I’m having a harder time learning students’ names. When I had paper to pass back, I got practice in learning names. Without that, I have to make a greater effort to use student names in class. For students who are vocal in class, I get much practice calling on them. For the quiet students, it’s much harder.

It’s technology that’s caused this problem, so I turn to technology to get me out. For a pittance of extra credit, students email me photos of themselves. I keep my grades in an Excel spreadsheet, and using the ‘comments’ feature, I mouse over a student’s name to get a pop-up of that student’s photo. Because you can’t just add a photo to a comment, you have to do a little work-around. Essentially, you fill the background of the comment with the photo. See this article for an excellent step-by-step explanation of how to do it.

Mail Merge: Creating Individualized Assignments

After a longer-than-planned hiatus, I’m back. And hopefully I have some new stuff that will make your teaching life easier!

Last May I explained in a post how to use MS Word and Excel to create grade reports. As I was walking across campus this week, I ran into my colleague in Engineering, Rich Bankhead. He had a great idea.

He gives his Engineering students a take-home final that includes solving mathematical problems. This quarter, he gave each student their own data. Students aren’t supposed to work with each other on this assignment, but if they do, they at least have to work the problems separately for each person.

I’m guessing that Rich’s problems are more difficult than this, but for the purpose of this post, I’ll keep it simple.

In Word, create the assignment.

In Excel, you need one column of names, one column of email addresses (if you plan on emailing the assignments to students), and finally a column for each variable. Save the file and close it.


Let’s merge!

In your Word file, on the Mailings tab, click the down arrow next to ‘Select Recipients.’ Select ‘Use Existing List.’ Locate and open your Excel spreadsheet, and select the worksheet within that spreadsheet that you want. It will look like nothing’s happened, but these two Word and Excel files are now linked.

Now let’s tell Word which Excel fields we want and where we want to put them. Let’s start with names. In your Word document, put your cursor where you want to put the student’s name. On the Mailings tab, click the down arrow under ‘Insert Merge Field.’ Notice that all of the column names from your Excel file are here.

Select ‘First.’ This is added where your cursor was: <<First>>. You can treat this like any other text. I’m going to type a space, then add ‘Last’ using the same process, then I’m going to make the type a little bigger and bold, and add a space under the name.

Let’s add the data. I’ll select my first data field (MMM1), add a comma and a space after it, then my second data field (MMM2), then another comma and a space and so on.


Merge and print (or email).

Click ‘Preview Results’ to see how the assignment will look to students. If you click the arrows to the right of ‘Preview Results’ you can flip through the rest of the students in your class. If don’t like how something looks, you can edit your document here or you can click ‘Preview Results’ to go back to field view you were working with before.

When your document is how you want it to look, click ‘Finish & Merge.’ If you select ‘Print Documents,’ you’ll print a separate page for each student in your spreadsheet. If you choose ‘Send E-mail Messages’, you’ll get this pop-up window:

If you have a column labeled ’email’ Excel is smart enough to default to that. If what it chooses is incorrect, click the arrow to the right to select a different column from your spreadsheet. Type in something appropriate for the subject line. When you hit OK, Word will use email program to send an email to each of your students.


Tips for creating individualized data in Excel.

Let Excel do the work. Use the RANDBETWEEN command to generate your data. For example, if you wanted to generate a number between 1 and 30, in an Excel field, type =randbetween(1,30). Notice the solid border around cell D2? If you mouse over the little box in the bottom right corner of that cell, your pointer will turn into a plus sign.

Left click and drag it to the right. This will copy your formula in every cell you highlight.

Now highlight all of the cells in that row you’d like to copy, mouse over the box in the bottom right corner of your selected cells, left click, and drag down.

IMPORTANT: Notice that each time you do something with a cell, such as copy a formula from one cell to the next, the number in all of the RANDBETWEEN cells changed. Each time you enter something into a cell in this spreadsheet, all of your randomly generated numbers will change.


Convert formulas to values. If you’d like your randomly generated numbers to stick so they’re not changing every time you do something in this spreadsheet, highlight the cells that contain the formula, click ‘copy’ (or CTRL-C), then click the down arrow under ‘paste,’ and select ‘Paste Values’ (or ALT-H,V,V).


What else do you need?

Are you looking for easier ways to do things in Word, Excel, or Outlook? Leave your questions in the comments below, and I’ll see if I can find some shortcuts for you.

Electronic Grading: Germ-Free!

If you’re concerned about the flu virus and you haven’t moved to electronic grading, now might be a good opportunity to start.

Managing email. As soon as I get an assignment, I hit reply, type “Got it,” then hit send. This eliminates follow-up emails from students asking, “Did you get my assignment?” In my email program, I keep a folder called “Grade these.” All student assignment emails are moved there so they don’t get lost in my inbox. (SimplyFile makes this easy to do with the click of one button. See this post for more information about SimplyFile.) After I’ve emailed students their graded assignments, I move their emails into the “Graded” folder.

Outlook folders:

Managing the documents. The papers themselves are saved to a “Student papers” folder in “My Documents.” Each file I save is renamed with standard nomenclature: Student last name, assignment, and whether the assignment was turned in late. For instance, if Alan Ladd turned in his second reaction paper on time, I would name the file LaddRP1. If he turned in his experimental design assignment late, I would name it LaddXD-Late. After grading the assignments, I move them into the “Graded” folder located in the “Student papers” folder. (UPDATE 10/10/09: See a more recent blog post on EZDetach for an easier way to save files from email messages.)

My Documents:

Once I’ve sent a graded assignment back to a student, I move the file into a “Sent” folder.

Attaching files to Outlook email: A tip. You can drag and drop files into open emails to attach them. (You can also drag attachments out of emails that have been sent to you into folders or onto your desktop.) See this video:

Using MS Word 2007 to grade assignments. Select the “Review” tab. Click “Track Changes.” Any change you make shows up in red. Deletions are struck-through; additions are underlined.

Track Changes

To add a comment, with your mouse highlight the text on which you’d like to comment. Click “New Comment,” then type your comment.

Add Comment

When you’re done, save your file, record the grade, and send the file back to the student. That’s it!

TabletPC users. On the Review tab, select “Start Inking.”

Start Inking

That produces the “Pens” toolbar. Just write like you normally do.

Grade Reports: Merging Excel and Word

I confess that I’ve never quite caught the course management system bug.  I used Blackboard (Bb) years ago but since then I’ve found ways to accomplish my pedagogical goals without it.  As my college makes the switch from Bb to Angel, I promise to take another look at moving some of my course into Angel.  But until then…

I still keep my grades in an Excel spreadsheet.  After each exam I send my students grade reports by doing a mail merge in MS Word; it’s kind of like a mind meld for MS Office products.  I used to print these grade reports and hand them out in class, but email works just fine — and reaches those students who seem to have disappeared from the course.

To send grade reports, you need three things: grades in an Excel spreadsheet, a form letter in Word, and, if you’re going to email grade reports, an email program that Word can use, such as Outlook.

In this post I explain how to set up your files to send grade reports. Once they’ve been set up, a couple clicks will allow you to send them out again.

Set up your Excel spreadsheet.

Let’s start with the grade sheet. My grade sheet is much more complicated than this (perhaps a future blog post) but let’s pretend this is my spreadsheet for a class that’s part way through the term.

In my spreadsheet, the top row helps me keep my content organized (“unit tests” and “papers”), the next line contains my column headings, and the student data begins on line 3. When you do a mail merge, Word likes to have just one row for column headings, so I just copy and paste lines 2 through however many students into a new worksheet. Let’s call that worksheet “GradeReport.”

Now we have this worksheet.

Save your Excel file and close it. Let’s head on over to Word.

Set up your Word document.

Imagine writing a letter to each of your students. What would you want it to say? Here’s one possibility.

I have my grade report document set up so that the date is automatically entered. To do this in Word 2007, go to Insert –> Quick Parts –> Field –> Date. Then you can choose the date format you like. Every time you open that document, the date will be updated.

Notice the lone comma. I’m going to have Word insert the student’s name in front of the comma and insert my ‘notes’ on the other side.

I put in the /50s and /40s to remind students how many points those were worth.

Let’s merge!

With both files set up, we’re ready to have Word ask Excel for the data.

From your Word document, go to the Mailings tab, click “Select Recipients” then “Use Existing List.”

Word will ask you to locate the file you’d like to use. Navigate to your Excel grade file. You’ll be asked to select the worksheet you want. Choose your “GradeReport” worksheet. That window will close and, anticlimactically, nothing will appear to have happened. In fact, Word is now poised to pull in data from your grades; you just have to tell Word which data you want.

Which data would you like?

Put your cursor where you would like the field to go. Let’s start with the student’s first name, so I’ll put the cursor in front of the comma. Now, on the Mailings tab, click the down arrow next to “Insert Merge Field.” Here you will see the column headings from your Excel file.

Here, I’ve selected “First” to insert the student’s first name. And this is what that field looks like:

Now I just place my cursor where I want the rest of the fields to go and select those fields. Here is what my Word document looks like when I’m done. I can format those fields like I do any other text. For example, I can change their font color or size or make them bold. If you didn’t get the field exactly where you want it, you can move it just like any other text.


I don’t want to send this out without taking a look at what the students are going to see, so I’ll click the “Preview Results” button on the Mailings tab. This gives me the data from the first person on my grade sheet.

This all looks pretty good except the course percentage is a little more precise than what I need. There are a couple ways to solve this. The easiest way is back in my Excel file. When I copy and paste the data from my main spreadsheet into my “GradeReport” spreadsheet, if I “paste special” and just paste the values, I’ll get 62.6 here instead of this super long calculation. The second way looks a little scarier but it’s just as easy. But if you’re already a little anxious, just plan on handling this the easiest way and skip this next section.

[For those who aren’t afraid of a little code, right click anywhere on the percentage. Word will highlight the entire number and give you this menu. Select “Toggle Field Codes.” The number will change to this: {MERGEFIELD percent}.

Now we’re going to add a little code that tells Word to shrink this number to something more reasonable. Add ‘\# 0.x’ to the field so it reads {MERGEFIELD percent \# 0.x}. This tells Word that after it goes to Excel to get this number, truncate the number to one decimal place. The 62.608… number is replaced by 62.6.


Now that we’re satisfied with how it looks, let’s mail out the grade reports. On the Mailings tab, click “Finish & Merge.” I’m going to select “Send E-mail Messages” but you could just print them if you prefer.

I now get this popup box where Word has guessed that the column in my spreadsheet labeled ‘Email’ contains my students’ email addresses. If Word guesses incorrectly, clicking the down arrow will allow you to choose a different field. After typing in my subject line, all I need to do is click “OK” and Word will tell Outlook to send each student’s grade report to that student’s email address.

Next time.

Now that you have your grade report file set up, next time all you have to do is open your grade report document and click “Finish & Merge.”