10 BEST Google Sheets HACKS for Teachers

hello there and welcome back to my channel we need to talk about Google Sheets which is the Google equivalent of Microsoft Excel so it's used to create spreadsheets but I feel like it's very underutilized especially in the teaching Community if you are interested I do already have a full tutorial video on how to use Google Sheets to create a student checklist like this so I will link that video for you down in the description box but today I'm going to be sharing 10 of the best Google Sheets hacks for teachers foreign you can use Google Sheets to create formulas and functions and sort things I want to focus on some hacks kind of above and beyond that that you may not be familiar with but they can really take your Google Sheets to the next level I thought how can I take it to the next level kicking it off with hack number one dragging cell data there is probably that's what I'm calling it Google Sheets is very intuitive and you can easily drag data from one cell to multiple cells and have it either copy the exact amount or adapt to the text let me explain what that means because that was super confusing first of all I can copy the exact same value from one cell to other cells if let's say I'm tracking weekly data and let's say most of my students aim for 30 minutes I can go ahead and type 30 in this cell and if I select the cell so it has that blue outline in the bottom right corner there's this little blue square and if I put my mouse over it becomes a plus sign once I have that plus sign I can click and drag in order to copy that exact same value across multiple cells now I can still use copy and paste the exact same way so for example I can select this cell copy and then I can click and drag to select as many cells as I want right click and paste and put the exact same value there but sometimes just clicking and dragging is a little bit faster but I mentioned that Google Sheets is intuitive so it also notices patterns for example if I have my students listed with a count of consecutive numbers I can just select let's say the last three numbers so eight nine ten once I have selected those I have that blue little square I can click and drag all the way down to the bottom of my list and it will continue counting consecutively it also can notice patterns such as dates so let's say I collect the minutes on the Monday of every week I can put the first date in so let's say 10 3 20 22 one week later so plus seven days would be 10 10 20 22. now that I've kind of established this pattern of one week later I can click and drag to select both of these cells once again I get that little blue square and this time instead of dragging up and down I'm going to drag across and once I Let Go each of these will be exactly one week apart hack number two is freezing cells if I have a spreadsheet like this with a lot of students once I start scrolling down I can no longer see those column titles which is a problem so I can actually freeze those rows so that as I scroll they will stay up at the top so in order to do that I'm going to come up to view select freeze and then I can have it select no rows one row two rows but ideally I really need these three rows I need one two and three all to freeze so what I'm gonna do is I'm actually going to select this third row I'm going to come back up to view come to freeze and now this has updated to say up to row three so I'm going to select that you will notice it puts this kind of thicker Gray Line just to show me that those are the rows being frozen but now as I scroll down those rows will actually stay so I can see exactly what those categories are hack number three is to use alternating colors with a spreadsheet like this as you start moving into different cells it gets a little bit confusing as to which row it is I know it does make it gray over here on the side but it would be much easier if the entire row was gray just to kind of create that differentiation especially actually if I plan on printing this at all so in order to add my alternating colors I'm going to come up to format and then choose alternating colors and then I can play around with the range so for example I want this to apply let's see a three so starting there all the way to Q well I don't want it to go Q I want it to go all the way across so that will be a l and then all the way down to 38 yes that is accurate click ok so it is now going to do from all the way across and I do have a header so I can leave that checked but I want the header to stay white and now I want color one so this first row underneath to be kind of a medium gray so I'm going to select this one right here light gray one and then color two I want just to be plain white and it now has those alternating colors I'm going to click done the great thing about this rather than doing it by hand obviously it's faster but if I add more rows so at the bottom let's say I add four more rows it's going to maintain that pattern of the alternating colors you should know those colors are sacred so personally if I want those alternating colors I always go through alternating colors versus trying to highlight the rows and do it by hand hack number four is to split data so I can actually take one cell well and split it into multiple cells for example currently all of the student names in column B have the first name and the last name in the same cell let's say I wanted to split it so that the first names were all in one column and the last names were all in one column first I want to go ahead and add another column where I want those last names to go so I'm going to right click on column B I'm going to choose insert one column right so now I have a blank column next to the names I'm going to highlight all of the student names so from B4 down to b38 so with B4 selected I'm going to scroll down hold down shift on my keyboard and click b38 that's going to select all of those cells in between now I'm going to come up to data and select split text to columns it could detect the split automatically and Google Sheets is pretty good at this but you can also click in order to give it something specific to detect so for example space that space is marking where I want it to split and since there is a space between the first name and the last name that will work perfectly and once I select that it has now moved all of the last names into the next column hack number five is to add a drop down list to a cell so let's move to another spreadsheet as an example let's say I have this student data overview sheet where I'm going to print it out and keep it in a student data binder but as I'm creating these in order to make it faster I would love to have a drop down for example of services that the student might receive so for example sped speech language occupational therapy and so on so in order to create this drop down we need to use something called Data validation so I'm going to show you how to do this using student names on this next sheet I actually already have a roster ready to go and you might notice over on the side I have those documents and services listed that you see in the drop down that I have here so what I'm doing is pulling data from this other spreadsheet so I'm going to select the student name cell I'm going to come to data and I'm going to select data validation now it's going to ask for the cell range so personally I'm just putting it in this one cell so I don't need to select a range I can have a list from a range which is what I'm going to show you how to do so I'm going to select this little grid button I'm going to come over to the other spreadsheet and I'm going to click and drag in order to select all of those student names from my list click ok and I wanted to show drop down list in cell and I could have it show a warning I'm not really using this as a way to like only type in certain texts that's what true validation is so I'm not going to worry about that I'll just have it show the warning that looks good click save now if I come back to that blank student overview I have a drop down I can click and then I could select from that name list that I have created but you also can do this by just typing in values so for example if I come here to subject data data validation instead of having a list from a range I can actually choose a list of items and I'm just going to separate them by a comma so for example math science social studies reading click save and now that drop down list are those different subject areas that I have typed in so if you are creating some sort of a data sheet or a checklist and you want to have different standards you could type in all of your standards and then have it create a drop down list so you can easily just select the standard instead of retyping it every time hack number six is to autofill cells across sheets now if you own my student checklist templates or my student data sheets templates first of all if you don't have them I will link them for you down below but you may notice that they are set up where you have a roster page and the student names you type on that roster page automatically fill on all the other sheets included it's a great time saver Time Thief Time Thief fire him I'm going to show you how to create that so if I have this student roster sheet right here and let's say I have this clipboard crazing so I'm going to print this out and I'm going to check off students for you know various data collection and I don't want to have to retype all my student names now I could copy and paste them for the roster sheet or I can have it actually pull that data so in order to set this up I'm going to select the first cell where I want the first name to appear which would be B4 with it selected it has that blue outline I'm going to type in an equal sign from here I'm going to navigate to the sheet where I want to pull the data from which is this roster sheet and I'm going to click the cell that I want it to select from which would be right here the first name which is B5 so I'm just going to double check it's pulling from the roster sheet and it's pulling cell B5 that looks good I'm going to click enter and now it is automatically filling that name and if I replace that name so for example Michelle Emerson of course I have all caps on whatever and if I come back you will notice it has automatically changed which is why I prefer this to traditional copying and pasting but I'm gonna undo that go back to Michael Scott now I want to take this formula and I want to apply it to all the other cells in this list so with it selected I'm going to come to this blue square so I get the plus sign click and drag and it's going to automatically adapt to that formula so it is now pulling from B6 B7 and so on hack number seven is to add conditional formatting conditional formatting is a way to highlight a cell a specific color based on parameters that you give it now how I like to use this as a teacher is for example I can highlight missing data it's a really easy way to see which students are missing an assignment or how haven't taken an assessment yet so just to show you what that would look like on my student data sheets I have this little check box now that's making it much fancier but I can toggle it on or off when I have it toggled on it's going to highlight any pieces of missing data but I can also use it to highlight scores that fall within a certain parameter a certain color so for example if I want to highlight data less than a score of 50 I can have it turn red if I want data between a 50 and a 75 I can have it turn yellow and then data greater than a 75 green I can toggle those colors on so in order to show you how to do that I'm going to come here to this assessment data spreadsheet just as an example so the first thing we're going to do is highlight missing data so in order to do that I want to First select all of the cells with the data that I want to possibly be colored so all of the number cells essentially so that means I want to highlight from C4 all the way to j38 with C4 selected I'm holding down shift on my keyboard and selecting the last cell so it selects all of them I'm going to come up here to format and choose conditional formatting so you will notice the range is already what I want it to be and I want it to highlight yellow if that cell is empty so format cells I'm going to change this to is empty and I'm going to change the color to Yellow so now I actually have them highlighted yellow I can click done if I want to get rid of this if I no longer want them to be yellow I can just click the trash can but I do have a set of student checklists and student data sheets where you have that toggle with the little check box I'm not going to show you how to do it in this video because it's a lot more intensive and it takes a lot of time and it's kind of hard to explain but if you are interested in grabbing those already ready to go they will be down in the description box but just to show you this updates in real time so if this score disappears if I click delete the cell automatically turns yellow and if I type A score in it will change back to that gray or that white depending on my alternating colors now in order to do the color coding based on where the score Falls once again I'm going to select all of those cells that I want it to apply to click add another Rule and this time instead of formatting it for is empty or is not empty I could choose less than and I can type in the value so for example less than 50 and I can change the color to let's do this kind of red color there we go and I would just repeat that for each of those different values so if it's between 50 and 75 or greater than 75. hacket number eight is to add a filter this is a great way to keep your data but be able to look at only certain parts of it at a time in order to add a filter I'm going to first click and drag or I can use that keyboard shortcut to select not only the data but also the column headings for it as well with all of those cells selected I'm going to come up to data and I'm going to choose create a filter now you will notice that these columns and these rows have turned green because that is the area the filter is applying to and I also have this little drop down next to each of these column headings which is why I put them with the heading versus not because it automatically applies it to the first row now these have bolded and got a little hard to read so I can select these and maybe make them like size 10 or even size nine just so it's easier to read okay much better from here I can click that filter and I can have it filter by different parameters so for example I added those colors in I could have it filter by color so I could have it show me only the fill color with light red that way I could look at only the students that received a score less than 50. so notice it is showing me only for this column now maybe I want to show the students that got read on all of the assessments if I look here the second column they're all already read but if I come to this third one I'm going to select that same filter but choose light red it got rid of that one that was the yellow and same thing here filter by color fill color red and it's just kind of filtering out out until I'm left with only that one student that has gotten red on every single assignment now in order to undo these you just have to kind of come back in and undo each of the steps but you can easily tell which columns have the filter because you get that kind of funnel look instead of the lines now a few other ways that you could use this in addition to those fill colors you can select it you can have it sort the data you can have it filter by condition so maybe I want it to show me ones that were less than a 90. I can select that click OK and it will filter those out based on those parameters so this is a great way to be able to analyze data I already showed you that data analysis sheet that is in my Google Sheets data sheet spreadsheet template thing that I have for sale whatever and from here those filters are already applied so you can use them within your data analysis very easily hack number nine is to add notes so you're probably familiar with adding comments within Google whether it's a Google doc Google Slides or Google Sheets where it will appear over on this side but within Google Sheets you can also add notes and they're a little bit more discreet than the comments so for example maybe I want to put a note here for Angela she got a 67 on this test and I might look at that later on and be like wow why did she get a 67 when usually she's like in the 90s well maybe she was sick on the day that she took that assessment and I want to make sure I have a note of that so I remember it's okay Angela so in order to add a note to a cell I'm going to select the cell right click and choose insert note and I can then type the message so for example Angela was sick on the day of the assessment and then once I click off of the note you will notice I have this little black Corner that tells me there is a note on that cell and if I hover my mouse over it that note will appear and I can resize it down here in the corner by clicking and dragging if you are using Google Sheets with students in any way you can also use it as a way to add directions for a particular assignment and finally last but not least hack number 10 is to use keyboard shortcuts now I've already shared a few of the shortcuts especially using the shift key in order to select like a whole set of columns or a whole set of rows but there are a ton of keyboard shortcuts within Google Sheets first of all if you want to view the whole list you can come up to help and then you can select keyboard shortcuts but there also is a keyboard shortcut for the keyboard shortcuts so if you are using a PC you can hold down control and then the slash button or if you're using a Mac it's going to be command and then the slash button button and when I say the slash I mean the forward slash the one that's by the ship button on your keyboard and it will pop up as well personally one of my favorite keyboard shortcuts is if I want to select certain rows or certain columns but they're maybe not next to each other because when I use shift it's going to select all of them in between that area maybe I want to select row five and then also row nine if I select row five so it's full dark gray if I then hold down either control on a PC or command on a Mac and click the other row it will select both of those rows and then any changes I make such as the font if I change this to Oswald it will apply to only those rows that I have selected now as I mentioned at the beginning of this video there are so many more hacks for Google Sheets these are just the ones I thought would be most relevant for teachers but if there is another hack you know of that I didn't mention that you love as a teacher please leave a comment down below I would love to learn from you everyone else watching would love to learn from you I have so much to learn from you if you found this helpful and you did learn something new please give the video a thumbs up while you're at it hit the Subscribe button and notification Bell so you don't miss any future videos as always thank you for watching I love you so much don't forget to put your positive pants on and I will catch you in the next one [Music] thank you foreign

Channel: Pocketful of Primary

Views: 36,255

Keywords: teacher, classroom, vlog, 4th grade, pocketful of primary, tips for new teachers, tips for teachers, new teacher tips, teacher tips, classroom vlog, vlogger, teacher vlog, teacher vlogger, a day in the life of a teacher, a week in the life of a teacher, michelle ferre, michelle emerson, teacher technology, technology tips for teachers, teacher tech tips, google sheets, hacks, google sheets hacks, google sheets tutorial, data, data analysis, how to

Id: Ja_DiEfDeBs

Length: 21min 12sec (1272 seconds)

Published: Fri Oct 14 2022