In this video I'll be showing you how to get
significantly better using Google Sheets in under 10 minutes. Let's get started. First I'm going to
go up here to the Search field in Google Chrome and just type 'sheet.new' and that's going to open
a brand new Google Sheet, which is just a faster way to get there. I'm going to go ahead and create
a student data tracker here, so I can show you some tips and tricks. So I'll start by creating
a header with student names in one column, some assessment data I want to track in the other
columns, and then I'll also track whether or not I have had a personal connection with students,
and if I've made a positive call home. Now that I've created my column categories, I want to
change the font, but if I just click one cell and try to change the font, it's only going to
apply the change to that single cell. So instead, what you want to do when you're making edits for
an entire Google Sheet is go up here to select all, and then any edit that you make will apply
to the entire sheet. So when I change the font, it's going to change the font for the whole sheet,
and similarly if I want to make the font bigger, I can change the size from 10 to 12, and that
will apply the change across the entire sheet. Nice. Now I'm noticing that the text doesn't
fit within the columns, and I could make the adjustment by dragging on the edge of one of the
columns and then resizing it that way, or another option would be to click on Format and go down to
Text Wrapping, and now that the text is wrapped you'll see that it fits in that single cell.
But the most efficient way is to go to select all again, and then when you make any adjustment
to the column size, it will make that adjustment across the entire sheet, which will also create
a more uniform effect and improve the overall look of your sheet. Next you'll notice that the
text is currently left aligned in the cells, so to change that I'm going to click on the entire
row and I'll go up to the Text Alignment option, and I'll select Center Align so that the text is
center aligned. Now I'm going to drag this header down because I want it to be a little bit bigger,
and when I do that you're going to notice that the text is going to sit at the bottom of the cell,
and I don't like the way that that looks. So in addition to center aligning it, I also want to
vertically align my text. I'll do that by going up here to the Vertical Align button, and then
I'm going to center align the text in the cells, and once I do that you'll see that the text now
sits in the middle of the cell, rather than at the bottom. Now another option here is that rather
than having your text horizontal, you might want it to be vertically aligned. So to change it
from horizontal alignment to vertical alignment, I'll click on that row again and then I'll go
to Text Rotation and select Rotate Up. Now of course the text is not going to fit anymore, so I
will have to drag that row down so that all of my text shows up. Okay so now that I have my header
set up, I'm going to enter my student names, and now that I have my student names entered,
you'll notice that when I scroll up and down on the sheet that the header goes away when I scroll
to the bottom, and I don't want that. I want the header to stay fixed at the top. So what I'm going
to do here is click on the header row and then I'm going to click on View, Freeze, and I'm going to
freeze one row. And now when I scroll up and down, you'll see that the header stays fixed at the top
so that I always know what data I'm looking at, even if I'm looking at one of my students
that's way down at the bottom of my list. All right now that I have my basic data tracker
set up, I'm going to start to input some assessment scores here. So one option is that
I could just hand write them, so I could write Advanced, Beginner, Intermediate, for example,
but if I were to do that, then that's going to take a lot of time. So a more effective way to do
this is to create a drop down box in each cell, so I can just select the result of the assessment
that I need to input. So to create that drop down box, I'll click on the cell, and then I'm going to
select Data, then I'll go down to Data Validation, then I'll change the data validation from 'list
from a range' to 'list of items.' Then I'll just type in the different assessment results
I want to show up in the drop down menu, then you'll see that that's created a drop down
menu and I can just select the assessment result that I need for that particular student. Now
I don't want to repeat that process over and over again because that would take a lot of time,
so I'm going to drag down the edge of the cell, and that's just going to copy and paste
that formatting across that entire column, and then I can also drag the cell to the right
and down again. That will just copy and paste the formatting across all the cells where I want to
input this data point on my sheet. Okay so let's say that I filled in all of my data here and I'm
trying to look at this sheet to make sense of it, and I'm noticing that because it's all colored
white, it's still a little bit difficult for me to interpret. So what I'm going to do is highlight
all the cells that have the assessment results, and then I'm going to go up to Format. I'll scroll
down to Conditional Formatting. You'll see that that's going to pull up a conditional format rules
menu on the right hand side of the screen. I'll go to Formatting Rules and I'm going to change
this to 'text contains.' Now I'll start by typing "Advanced" and I'm going to change the color to
blue, and now that I've done that you'll see that all the advanced cells are now colored blue. Next
I'll click 'Add new rule' and I'll change the text from "advanced" to "intermediate." This one, I'll
change to green, and then I'll add another rule, type "Beginner," and this one I'll change to
red. And now you'll see that all the cells are color coded based on the assessment result that I
chose from the drop down menu. You'll notice that I didn't create a drop down menu in the next two
columns, and that's because if I've had a one on one connection, or made a phone call home, those
are really just yes or no answers. One option would be to create a yes or no drop down menu, but
what I'm going to do instead is highlight all of the cells in these two columns and I'm going to go
up to Insert, and then scroll down to Checkboxes. There you'll notice that check boxes will show
up in each of the cells that I can check on or off to indicate that I did or did not do that
particular thing. Now even though I've color-coded the assessment results, I'm still finding this
sheet a little bit visually unappealing and kind of difficult to look at. So to fix that, I'll go
up here to Format, and then I'm going to scroll down to Alternating Colors. That's going to pull
up a menu on the right hand side of the screen where you'll be able to choose from some different
color combinations that will change the color of the header, and then will alternate the colors
of each of the rows, and that's just going to help make your sheet even easier to look at. I'll
make some other adjustments here. So I'm going to click on the header row again and make the text
bold so that it stands out a little bit more, and then I'll also add a border by going up to the
Border option. And there I can choose the color of the border, as well as the border thickness. So
I'll go ahead and select a thicker line to really make that border stand out. Then I'll do the same
thing with my Names column, so I'll select the column, go up to Border, but for this one I'll
choose a little bit thinner border line, so that it's more distinguished from the header. I'll also
make the student names bold and then I'll make the font even bigger for the header, just to really
make sure that it stands out. All right, so my Google Sheets Data Tracker is starting to look a
lot better here, but one thing that I'm noticing is that I kind of made a mistake by inputting
my students' first and last names in a single column. So I'm going to split that so that I have
a separate column for the last name and the first name, and the way that I'm going to do that is
by going to that column, and I'm going to right click and click Insert Column to the Right. Now of
course one option would be to just manually write in all my students' first and last names, but
that's not necessary. Instead I'll click on the column with my students' names, go up to Data, and
then I'll scroll down to Split Text to Columns. This separator menu is going to show up, and I'm
going to change it from "detect automatically" to "space." And once I click "space," you'll see that
it's automatically going to detect that there's a space in between the first and last names, and
it's going to split those first and last names into two separate columns. Now although it's
useful for me to be able to see that Roger has advanced in all of his assessments..."Guys,
guys, trying to teach a lesson here. All right let's, let's get back to this, let's get
back to this"...there might be other times where I want to see which groups of students got advanced
on a single assessment. So to rearrange the data, I'll click on the column, and then I'll click on
Sort Column from A to Z. Now you'll see that it's going to group the different assessment results
based on alphabetical order, which makes it a lot easier for me to see which students are grouped
in different assessment color bands. Now let's say that I want to recreate this data tracker
for my colleague, Thom, so that he can use it to input his own data, and I actually want him
to be able to see my data too, but I don't want him to be able to edit it. That's really easy to
set up. I'm just going to go down here to the tab for the sheet I created, I'll change that name
to Mr. Kary's Class, I'll right click on it and then click Duplicate. I'll change the name of the
new tab to Mr. Gibson's Class, and then I'll just highlight the cells that contain my data and click
Delete. When I do that, you'll notice that the formatting that I set up stays the same, so he'll
still be able to access the same drop down menu with the same color formatting. Now to make sure
Mr. Gibson does not accidentally delete my data, what I'll do is right click on my Google Sheet
tab, and then I'll select Protect Sheet. I'll select the green Set Permissions button, and
you'll notice that by default it's restricted, so that only I can edit this range. Once I click
Done, I can go to the green Share button, share my Google Sheet with Thom so he can access it, and
when he gets in he'll only be able to edit his sheet and not mine. And one way that I know that
that's true is that I see this little lock icon on the Google Sheet tab that I set permissions
for. Now that you've learned how to get better at Google Sheets, check out that other video to
learn how to get better at using Google Forms.
