 Hi, this is Leanne Hagen, and I'm going to show you how to make Google Sheets work for you. Google Sheets is a powerful tool that can be used to organize data quickly and easily, and if you know how to use it, Google Sheets can be a teacher's best friend. We're going to begin with a data set that I used to organize a field trip for our entire grade level. It's a lot of data, and right now it's not very useful for me. Let me show you a few things we can do right away to make the sheet easier to read. The first thing I want you to notice is that when I have set up my spreadsheet, there is a standard width for columns. So that's actually not what they're at right now because I kind of adjusted them just so you all could see this, but normally you're going to see that they're all the same width as i, j, k, and l over here. What I want to do is show you a quick way that you can make the columns all the right width so that you can read everything in the column very easily. So I'm going to start by highlighting the column, and I'm just clicking and dragging all the way over from a through h because that's where I have data. If I then come over here to h and you see how my mouse is changing into a little arrow going both directions, if I double click that, what happens is every column becomes just wide enough to fit all of the information in. So that's one way that we can quickly make this a little bit easier to read. The next thing that I would like to do is add some borders so that we can see the data very clearly and have it divided up nicely. I'm kind of funny about borders, and I don't like them all to be the same. So I'm going to show you some ways that you can make the borders a little bit different. So what I want to do first is I'm going to highlight all of my data. So I'm just going to scroll down and see it's a lot of information. So I'm going to scroll down and get all of my data highlighted, and then I'm going to click where it says borders up here. So when I do that, this gives me a lot of options. Now a quick and easy way to make all your borders the same is just to click all borders, and it's going to make boxes all around everything. I don't really love that. So I'm going to undo that, and I'm going to show you just the way that I would do this. So I actually like to work on my interior borders first. So if I click inner borders, that's going to only do the borders within the data. So I'm going to change the format. I like to make those a dashed line. So I can come in here and I can click dashed line. It's going to give me the color options here. I'm not going to change the colors. I'm going to keep it black, and then I'm going to click inner borders. And what's happened now is I've got these kind of nicely separated without it being a major harsh line. That's completely user preference. I just like it to look like that. Now I'd also like to have a heavier border around the outside of my data. So if I do that, I'm going to come back over here and I'm going to click on, actually I have to click this button right here. I'm going to click the outer border, and then I'm going to change the format of that. I don't want it to be dotted. I'd rather that be a solid line, and it's heavy there. If you don't like it that heavy, you can click the options here, and you can click a lighter border. So it's not quite so heavy, but you can make it solid. You can change the color of that border if you wanted to. There's lots of different options for borders. So don't feel limited by what's here. Know that there's lots of different ways that you can do this. So now my data has lines in it. I'm happy with that. But the next thing I want to do is I'm going to create alternating colors on the rows to make that even easier to read. So in order to do that, again, I'm going to highlight all of my data. That's kind of the first step to anything in Google Sheets is just get your data highlighted. And then I'm going to go to format, and then I'm going to select alternating colors. Now this gives me so many options. I personally really like purple, so I'm going to click that button right there, and it's kind of purple. And then what you can see is you can change the alternating colors if you want to. You can adjust the different shades that's totally up to you if you want to go with school colors or whatever makes you happy. The other thing that happens in this menu is you have the option for there to be a header. So I'm going to just move my mouse over here and scroll up and see what this is looking like at the header. You'll notice the header, the first row, is a darker color than the data. I think that that helps to kind of distinguish that header. And so I like that. I'm going to keep that as it is. I bring my mouse back over here, scroll down, and then click done. And now I've got a data set where I've got alternating colors, and I've got some lines on there that are making me feel like this is a little bit more put together. The next thing I want us to notice is that when I scroll down right now, I'm losing my headers. They're not staying with the data. And I might need that as I move forward and I'm looking at all of this data. The easiest thing that you can do is freeze this first row, and you can freeze as many rows as you want to. But what that's going to do is it's going to allow you to then scroll down and keep your headers in place. So the easiest way to do that is if you come right up here to this kind of empty box in the top right, I'm sorry, that's left, top left, you're going to move your mouse down. You see that little hand grabs that gray bar. So it turns that gray bar darker. If I click on that and then drag it down, what that does is it moves this gray line underneath of that first row. And now when I scroll down, you'll see that that first row is frozen. That's a really nice feature. You can also freeze columns. The same procedure, you just come up here to the column line right here and grab that over there. So maybe what you want is to keep the name of the student there. So when you scroll over, you can look right next to their name. That helps to make things a little bit easier to read. Okay, and I'm collecting a lot of information in this spreadsheet. And I think one of the easiest things that I can do is add check boxes for when I receive information from students. If you notice, I'm collecting a permission form from them, a deposit, and then a final payment. So what I want to do then is I'm going to insert check boxes. And that's going to make it possible for me to just check off on this list when students bring information in. So I would go up here to insert and I would click checkbox. And then what happens is a little box gets put in there. I can click on that box and it becomes a check mark. There's a couple of ways that I can add check boxes to the whole column. If I put the one in the first cell there, I can click and drag on that little corner. And if I do that and then let go at the bottom, it's put a check box in every single one of those rows. Additionally, what I can do is if I highlight both of these columns where I want check boxes to be, then I can come in here again, hit insert and hit check box. And now they're all there. So there's different ways to go about doing that, but that it's an easy thing to do. Notice once you have alternating colors set up to the check boxes are different colors too. So that makes it kind of nice and easy to see. The last thing that I want to make sure is on this spreadsheet before I start collecting data and doing anything with it is I'm going to need my student's t-shirt sizes for this field trip. So the easiest way I have found for inputting that kind of data consistently is to use what Google Sheets calls a drop down. So I'm going to click right here in this t-shirt column and I'm going to click the insert menu. And when I do that, there's a drop down option. Again, we're going to see a menu pop up on the right and this is where we're going to set the rules for this. So I have several different t-shirt size options. So I come in here where it says option one, I'm going to put extra small and I might want extra small to be pink. Then I might change option two to small and maybe I want small to be green. Now I've got several sizes so I can use add another item to be able to add another option to my drop down. I'm going to kind of keep going over with this and go large and large can be purple and then add another item as my last one extra large. I'm going to leave it that gray color. So once I've got that set up, I'm going to click done and what we'll see is that there's this little check box that is now in my t-shirt column. If I click on that, I have all of those options. So now I can copy them. So now my drop down menu is everywhere and I'll just double check like can I make sure I get extra large will fit in there? Not really. So I'm going to double click again to make it that fit and now I can go through and when the children bring in their their permission forms that have the t-shirt size on there, I can input that and it'll be a consistent way of inputting the data. So what we've done here is we've just made the the sheet look a little bit better. There is one more thing that I would probably do just because I'm a little bit crazy about this and that is I would go to my first row and I would probably wrap the text. It's kind of bothering me that permit the permission form column is so wide and that's just again I'm just a little bit crazy about that. So I'm going to highlight that first row and then I don't see my wrap text option up here. So I'm going to click this dot dot dot and this little arrow right here that says text wrapping if I click on text wrapping now what I can do is I can actually bring permission form sorry I can bring permission form you see what happened there wrap that text around and I might do the same with final payment and then I'm going to get crazy and I'm going to probably want to bold my headings and then I'm going to want to center them because I just like the way things look nice and neat. Now you saw what happened there I have to come in here and resize so that I can get the word permission the word deposit and there's no funniness no funny business happening there. Okay so now my my sheet looks a little bit better it's easier for me to read and I'm ready to start collecting data in here and then I'm going to show you some other tricks that we can use that will allow us to make this even easier to go back and look and see who's missing what. Google Sheets has a tool called conditional formatting that can be very useful for teachers. This tool will automatically format cells based on the conditions that you set. I've got a list of student scores from a quiz I gave my students using Google Forms. I'd like to focus on the students who did not pass the quiz and I can use conditional formatting to easily do this. First I'll select the range of data that I want to apply the conditional formatting to. So that would be all of the scores of the students. Once I have that highlighted I'm going to click the format menu and open up conditional formatting. This brings a toolbar to the right hand side and it allows me to set my my rules for the conditional formatting. There is a default that applies and that default is that if the cell that I've highlighted is not empty then it's being changed to green. That's just always going to happen when you open the conditional formatting menu. We're going to adjust that. What I'm looking for is students who failed which would be scoring less than a 60 on the quiz. So I want to come here to where it says is not empty and click the drop down. There are just a ton of different options that you can use to set conditional formatting rules here. You can kind of explore all of these. They all work the same way that I'm going to show you but with different kinds of data. So what I'm going to do is just click less than and I'm looking for a value of less than 60. Now you'll notice on the left hand side that that green kind of adjusted. It's looking for those values that are less than 60. It's defaulting to changing to green. I think when they're failing something that that's a red flag so I'm going to change that color to red and then I'm going to click done. What happens here is it shows me where I've got my conditional formatting rules and that's going to stay there. If you decide you don't want it you just click that little delete button and it removes the rule. But what I can see now very easily is I have a quick snapshot in that red color of the students who did not pass the quiz. And so that is one use of conditional formatting that I find can help me to see things a little bit easier with Google Sheets. Once you understand the basics of conditional formatting you can begin to apply formulas that can make your Google Sheet follow whatever rules you would like. I have a Google Sheet here that I'm using to keep track of information for a field trip I'm planning for my students. I'm collecting several different things from each student and I'd like for the row that contains the student's information to change to a different color once I've collected everything from a student. Conditional formatting to the rescue. I'm first going to go ahead and highlight all of my data that I would like to follow the conditional formatting rule and that for me is everything here. You can kind of adjust this it doesn't have to be everything. So I'm collecting a permission form a deposit in a final payment. What my goal here is is that when I click that final payment button I want that whole row that contains Brandon's information here to change to a different color and that's going to let me know that I've got everything from him. So I'm going to go into the format menu and open up conditional formatting and my menu is going to pop up on the right. The next thing I want to notice is that everything's now turned green. That's following that default that Google Sheets has set for conditional formatting but we're going to adjust this. My range is already highlighted if it wasn't I could just click this button right here and then it would allow me to select everything in the sheet but I went ahead and did that to start with. So here I'm going to go to my format rules and rather than is not empty and really rather than anything that Google already has set here for me I'm going to go ahead and make a custom formula. Now this is one that I had to just Google and figure out how to do and this is one that I used all the time so I have this in my brain I just know how to do this. Anytime you're using a formula on Google Sheets you have to start with equals and what I want is for any time I click this final payment button I want the whole row to turn yellow. So the final payment is in row I'm sorry in column E and I'm going to start with cell E2. I want that to happen in cell E2, E3, E4, E5 and in Google Sheets the indicator of that is the dollar sign I don't know why it just is. So I want everything from E2 and below to change to a specific color when I click the box. So I'm going to click the equals button and what I want is to tell Google Sheets that if the box is checked then I want this to happen. So the way you tell Google Sheets to look for a checked box is you type the word true and it needs to be all capitals okay and then what I want is for instead of it turning green I want it to change to yellow okay. So now I can check to make sure this works by coming over here and just clicking this button. See how when I clicked final payment this whole row turned yellow so it won't happen when I click permission form. It won't happen when I click deposit but once I get that final payment that student is going to turn yellow and that's going to let me know okay I've got everything I need from that child. Click done and my rule is here and this is going to work no matter which row that I'm in and I just love it when things like that happen. If you have a lot of data that you want to sort in order to see it easier or if you just want to look at a smaller set of your data you can use filters in Google Sheets to do that. Filters are going to pull out only the data that you want to look at. I'm going to show you how to make that happen. So I've got a set of students quiz scores and what I want to do is see which students made an A and really I want to find out how many A's did I have. So what I'm going to do is I'm just going to come into the data tab and I'm going to click the little option that says create a filter. What that does is it puts this little funnel right here and that's going to give me a way of filtering very easily. So when I click on this I've got all these different options for how I can filter. I can filter by color which doesn't make any sense here. I can filter by condition which is what I'm looking for. When I click this what I'm looking for is who made an A. So I want to know which of these numbers are greater than or equal to a 90 and when I do that and I scroll down and I click okay and now what it's done is it's only got those students who made an A. Now I have their names hidden but normally I could be able to see that and what's really nice about this is in addition to showing the scores and just showing me the A's it gives me a count at the bottom right hand side of my screen. It shows me that 44 students made an A on that quiz which is great and so this is just an easy little snapshot. If I'm done with that filter I can come back in and click remove filter under my data menu and then I can go back and I can sort of the different way. So a really really cool tool. Let me show you in a little bit more data how I might apply those filters. So I've got this data sheet again that has all this information about students and what I'm going to use this for is a field trip. So I've got their t-shirt size. Let's say that I really wanted to know like how many large t-shirts do I need to order for my for my trip. So what I would do is come back to data and I'm going to click create a filter again. Now this defaults to putting a filter on every single column and it does kind of mess up your formatting but that goes away once you take the filters off so that's going to be totally fine. So I want to know in this column I'd like to see the largest only. So in this case I'm going to filter by values. I'm just clicking that little funnel and I'm going to go down to filter by values. I don't want anything except for the larges. So I can hit clear and then I can click large or you can uncheck the other ones kind of either either way it works for for this. When I click okay what it's done is it's now taken my large t-shirts and that's all I've got on my sheet now and I know I need 24 larges. I can do the same thing and come back in and say okay well I want to know. I'm going to select them all I want to know how many smalls there are. So I can click clear and hit small and now I'm going to know I need 57 smalls. This makes say ordering t-shirts really really simple. What I really love is when I can start stacking those filters so I'm going to go ahead and just select all of these get everything back on my sheet and I'm going to now filter by their bus number. So what I might want to do after I've got my t-shirts ordered it's the day of the trip and I want to hand out t-shirts by bus and I'm going to say okay well how many small t-shirts does bus three need. Well that would be really complicated to figure out unless I use filters. So I come in here and I say okay well look at only at bus three click okay. So now I've only got bus three on here. Now I can take that data first of all it shows me I've got 47 students on bus three which is good and then I can come in here and say okay well how many small t-shirts do I need for bus three. So I've kept the filter on for bus three now I'm on the t-shirt size and I click okay and now I know that bus three is going to need 21 t-shirts. Now this is a really practical application but there are so many different options that you can use with sorting your data with filters but I think this is a great tool that everyone who uses Google Sheets should know about. Thanks for watching don't forget to like and subscribe for more video tutorials