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.