Connect with Me

Strategic Communication Insights blog

Entries in automation (2)

Use appointment slots in Google Calendar to automate office hours scheduling

If you're anything like me, you spend too much time emailing back and forth with students to schedule meetings (and let's be honest - any time that you're spending doing a menial task that could be eliminated with technology is time that you aren't being productive or relaxing). Today, Google announced a new feature of their Calendar web app that will seriously simplify office hour scheduling.

The new feature, dubbed 'appointment slots,' appears when you click on a free time space while viewing your calendar.

Like a regular events, you can schedule recurring times every week when you hold office hours. Google also provides the option to specify appointment length. Provide your students with the URL to your personal appointment sign-up page (the personal URL will appear when editing details of an appointment slot), and they'll be able to see your available slots and sign up for an appointment.

When a student schedules an appointment, the slot he or she selected will disappear from the appointment sign-up page.

You'll see student appointments auto-populate your calendar and appear on top of your scheduled office hour blocks.

Ready to try it out? Book an appointment with me!

Additional Tips

  • Consider using a URL shortening service ( is my favorite) to create a short (and memorable) URL that redirects to your appointment page. You can paste the short URL on your syllabi, your course website, or even print it out and post it on your office door.
  • Traditionally, instructors offer office hours during the same times each week. However, using Google Calendar to automate student appointment scheduling opens up the possibility of having variable office hours, since students can always look online to see when the next available times are and reserve a slot.
  • You may need to remind students to put their name in the "Description" field when signing up for appointments. Many people have Google account IDs that are something other than their name (e.g.,
  • If you manually create an appointment during your scheduled office hours, the concurrent appointment slots will still be available through your appointment sign-up page. Don't forget to change duration of the block so you don't end up double-booked.

Use Microsoft Excel to eliminate manual grade calculation

I walked through the grad student computer lab today, and heard one of my colleagues talking to herself: "I hate calculating final grades! It takes forever, and I'm always afraid I'm going to make a mistake and keep someone from graduating." It turned out that she was sifting through the gradebook by hand to drop low quiz scores and was manually entering final grades based on the point total.

The great news is - Microsoft Excel can automate these tasks, ensuring mistake-free final grades. Here's how:

Drop lowest score(s)

It's fairly common for instructors to allow students to drop the lowest score or two, especially on assignments like quizzes or short weekly writing exercises. This method will also work for assignments where students are only required to complete a subset of the available tasks (e.g., students must respond to at least 8 of the 10 weekly writing prompts).

Drop single score

To drop a single score in Excel, it is easiest if the columns containing the scores are adjacent to one another. The example below shows a gradebook containing scores for six quizzes in columns B through G.

To drop the lowest score and add the remaining scores for hypothetical student Jakob Aamot, use the formula:


In words, this formula adds up all of the scores in row 2 columns B through G, then subtracts the minimum (lowest) score in row 2 columns B through G. Once you have the formula in your Excel sheet, you can copy and paste it down through your total column for as many students as you have - Excel will intelligently change the row number in pasted formulas.

Drop multiple scores

To drop multiple scores, a slightly different formula must be used.

To drop the lowest two scores for hypothetical student Alisa Carmichael, use the formula:


In words, this formula is adds up all of the scores in row 3 columns B through G, then subtracts the first smallest score in row 3 columns B through G, then subtracts the second smallest score in row 3 columns B through G. The ",1" or ",2" after the cell range B3:G3 tells Excel to drop the first or second smallest score, respectively. If you wish to drop more than two scores, just add an additional "-SMALL(B3:G3,#" to the end of the formula, replacing "#" with the rank of the next highest score to drop.

Convert points or percentages to letter grades

 Instead of going through the gradebook and manually entering letter grades based on point totals or class percentages, an Excel formula can automatically report the appropriate letter grade based on your grading scheme.

In this hypothetical example, the course percent earned determines the final grade according to a typical +/- grading scheme. The formula looks big and complicated, but it's really just the same formula repeated over and over with minor modifications. To calculate the final grade for Jakob Aamot, use the formula:


 In words, this formula looks at the score in row 2 column B. If the number is larger than or equal to 97, it sets the value of row 2 column C to A+. If the number is not larger than or equal to 97, it continues down the formula path until the number in row 2 column B is larger than or equal to the listed value. If none of those numbers work, it sets the value of row 2 column C to F.

If using a point system rather than percentages, you can use the same formula, just replace the percentages with the appropriate cut-off point totals for each grade. Similarly, if your grading scale is different, just replace the current grade designations with your own.