Speaker 1: I'll show you the basics on how to build this assignment tracker. There'll be a drop-down menu with your subjects and courses, status, due date with an automatic countdown, priority level, the type of assignment it is, and the estimated time, and also how to make filters for each column. I do already have a template of this if you want to go download it on my website, or stay tuned in how to build this assignment tracker. First things first, open up a blank Google Sheet, and let's name this Assignment Tracker. Now I'm going to add all of my titles. Assignment, course, status, due date, priority level, type. Now I'm just going to highlight these bold and center them. I'm going to make column A a little bit smaller for the checkboxes, and let's just go to 50 for now. Insert, checkboxes. I like to make them black and a little bit smaller. Okay, let's leave this here for now, and we're going to add another tab. This is where we're going to put the information for our drop-down menu. So let's have subjects slash courses. It's a little bigger. And types of assignments. I'm going to put a border around these, center them, make them look nice. And let's just go ahead and add our courses or subjects. Math, science, history. Types of assignments can be essay, presentation, project, homework, tests, and quizzes. Now I'm just going to center these. You'll see a theme of that. Let's go back to our original sheet, and we're going to highlight where we want the drop-down menus for the subjects to be. So I'm going to highlight this column, right-click, view more cell actions, and data validation. Let's add a rule. Drop down from a range. You're going to click select data range. Go to the other sheet and highlight the subjects and courses. And click OK. Then click done. Now once you go back, you'll be able to see all the subjects and courses that you inputted. Now let's do the same thing for the type. Let's highlight this, right-click, view more cell actions, data validation, add a rule. Same thing. We're going to drop down from a range, select data range, and the type of assignments. And they're all there now. If you don't like them looking like a bubble, I don't typically like this. So you just go to advanced options and then arrow, and then it won't be in that bubble anymore. Now let's add the drop-down menu with statuses that don't need to be changed. So we're going to do the same thing by going to data validation and add a rule. But this time we're just going to manually input what we want the drop-down to have. So for now I'll just do not started, in progress, and completed. Now you can change the colors here. They'll just have to be inside the little bubble. So I'll click done, and then the colors will change. So not started, in progress, and completed. If you don't want them in the bubble, let's get rid of it first. We'll right-click and do conditional formatting. So format cells if text is exactly, and then we'll put not started. I'll make it black ink, and we'll make the cell red. Click done. We're going to add another rule. Text is exactly. We'll do this for in progress. Black ink. We'll make this one yellow. Click done. And the last rule is for completed. Black ink, and we'll make this green. And now whenever you select the status, it will change the color. Next for due date, when you click in here, you have to manually type in the date, but we want a small calendar to pop up. So let's highlight the column, right-click, view more cell actions, and data validation. Add a rule, and then we'll do is valid date, and reject the input, and then click done. And now when you click on these cells, a little calendar will pop up to make it a lot easier to put the date in. Now for the countdown. All you'll have to do is the formula equals, click on this cell, minus today, parenthesis, and parenthesis, and then click enter. And it'll tell you how many days until this date. Let's highlight this column. Go to format, number. Now when you drag this formula all the way down to copy it, these will pop up, but we don't want it to. So we're going to create one more conditional formatting. Highlight this row, view more cell actions, and conditional formatting. So let's do it so that if the value is less than negative 40,000, then it will just be white ink and a white background. Click done, and they'll disappear until you put a date in here. Let's do the dropdown menu for priority level. So highlight that column, right-click, data validation, add a rule. We'll do low, medium, and high. Conditional formatting. Text is exactly low. It's green. Text is exactly medium. You can make orange. And text is exactly high. So you can make red. Now we have our types already here, and our estimated time and notes we can make a little bigger. Now you can customize this however you like. I'm going to add a border and then maybe just a top border too, so it's a little more separated. And I'll create a border all the way around the assignment tracker, some in the middle. Now the next thing I'll show you is how to have it so that once this is clicked, these will strike out. So let's highlight this whole thing, right-click, conditional formatting. Let's add another rule. But we'll do a custom formula this time, and this formula will be equals dollar sign A4, which is this first checkbox here, equals true. Then we'll have it strike through. I like to make it a little gray of an ink and then white background. Click done. And let's drag that formula we just made to the top. And now that will become priority, and these will cross out. And it looks like I forgot to reset the status color, so you'll just highlight those and go to their conditional formatting and reset those colors next to the names. Now, once you cross these out, that last formula will take priority and everything will cross out. Now for this column, let's highlight, go to conditional formatting, and we'll just bring this one above just so it disappears. Okay, so let's add a section where it shows the total assignments and then the done versus incomplete assignments. So I'm going to add a few rows. Insert one above. Total assignments. The formula will be equals count A, parenthesis, highlight this column, and click enter. So now once we input something, it'll count how many are in that column. Again, center. So now let's do completed assignments. We'll do done and not done. This formula will be equals count if, highlight all of the checkboxes, comma, quotation mark, true, quotation mark, parenthesis, enter. So now once we click one of these, it'll count it here. And lastly, we'll do it for the not done assignments. It's going to fill in. So there's three assignments here total. Zero are done. Let's do the incomplete assignments. Equals count ifs, parenthesis, going to highlight the checkboxes, comma, quotation, false, quotation, parenthesis, minus. Let's do two parentheses here, and then we'll do count A. We're going to highlight those again. Parenthesis, minus, count A, parenthesis, all of the assignments, then three parentheses, and click enter. So now once we click, everything else will accommodate. Now I have two incomplete assignments, one complete assignment, and three total assignments. The last thing I'll show you is how to create a filter. It's very easy. You just highlight the entire assignment tracker table, right-click, and create a filter. And that'll create a filter for each heading. So if you want to filter by status, you just unclick what you don't want to see. So I just want to see in progress status. Click OK, and it'll just show me that. If you want to get everything back, just recheck them and click OK, and there's everything. So that's the basics for this assignment tracker. You can make it as pretty as you like. I obviously like to add a little bit of flair with automatic dates, a pie chart, and just overall cleaning everything up. But please let me know if you have any questions. I'd be happy to answer them.