Join Aaron Quigley for an in-depth discussion in this video Creating a dropdown list of assignment types, part of Office for Educators.
For this video, we're going to continue with the layout of our Grade Book by creating a very simple drop down menu under the Type category for our assignments. Our goal is to have a simple drop down with the type of assignments that we'll commonly use. That way we don't have to type them in each individual time. If you're just catching up with us, we're starting out with the Grade Book Layout that's available in Chapter Six of the exercise files. To create our drop down menu, we're going to use a technique called Data Validation. To do data validation we need to have just a simple list. I'm going to scroll down the page just past where our Grade Book ends.
In a previous movie we went ahead and highlighted all of the borders of our grade book, so that we knew the cells that we're working with. I'm going to go ahead and put my cursor down here in B37, which is just a few cells outside of where the Grade Book ends. The list we would like to choose between is going to be comprised of Classwork, Homework, Participation, Test, Quiz. And then we're going to go back to the top of our list and add another item with an opening square bracket. We're going to type the word select and put a closing square bracket. That way, when we've got a drop-down menu available, the first thing it's going to say is select.
We'll be able to drop-down that menu, and choose one of these five assignment types. So let's go and scroll back up to the page where we want to put our drop-down menu. Our drop down menu is going to be the type of all of our assignments. Highlighting the cell that's right next to type, I'm going to go ahead and create our drop-down menu. Click on Data up in the top ribbon. Choose Data Validation, and then Data Validation again. Here, the Data Validation dialogue box opens up. We want to allow it to create a list for us. And we're going to give it the source. The best way to indicate the source of a list is simply to put your cursor inside the source box, and then click on the Cell button to the right of that box.
Let's go and scroll down to the list that we typed. I'm going to use my cursor. Click and Hold to select the entire list. I'm going to go back to my Data Validation box, and I'm going to click on the icon to the right of the box again. What we are essentially telling Validation to do is to create a list using the values from B36 to B41. I'm going to go ahead and click OK. Scroll back up to the top of the list, and now see a small icon appear that tells me I can choose items in my drop down menu. I'm going to go through this real quick, and you can see that I'm able to select anything in the drop-down menu, open it back up and reselect anything else.
Now what we still need to do is make sure this drop-down menu is in every single box under the Type section. To do that I'm simply going to highlight its box, use the keyboard shortcut for Copy which is Ctrl+C. I'm going to click the box to the right of it, hold my cursor down, highlight everything that would be a type for these assignments. All the way to the end of our Excel spreadsheet, or the end of our Grade Book. And I'm going to Paste those using the keyboard command of Ctrl+V. You'll notice now that each one of these has a drop-down menu with it, where I can select the items that we created, such as Participation, Homework, Classwork, Tests, and Quizzes.
The last thing we're going to do, which is more of a housekeeping activity than anything else, we're going to scroll back down to where our list is at. Now, since we're also going to be creating a printable version of this grade book I'm going to go ahead and hide this list. The best way to do this is put your cursor between the last row and the row just past it which in our case is row 41 and 42. I'm going to Click and Hold and I'm going to Drag all of these rows up, until 42 touches 35. Letting go, all of the sudden our list disappears. When I look at the numbering on the left hand side it goes 33, 34, 35, 42.
Those cells have just been hidden from sight, which means that they won't show up when we print our Grade Book.
- Creating lesson plan templates
- Creating worksheets with math equations, charts, and graphs
- Grading papers
- Creating a gradebook in Excel
- Creating an animated presentation
- Setting up a school email account in Outlook
- Storing documents online with SkyDrive
- Creating a class website with SharePoint
Skill Level Intermediate
Q: This course was updated on 10/01/2014. What changed?
A: We added a brand new chapter on Office Mix, the PowerPoint plugin that allows educators to record interactive presentations and test students with quizzes.
Q: This course was updated on 10/15/2015. What changed?
A. We added videos for OneNote, OneDrive, and Office Online. OneDrive replaced SkyDrive as the cloud-based file service.