Google Sheets for Educators: Formatting Basics

1. Course Introduction: Hi there. I'm Allison and I'll be your teacher for Google Sheets Mastery. I have worked in the field of education for 18 years, first as a teacher, and then as an instructional technology coach and also in data governance at the district level. Over all of those years, I have learned just how important spreadsheet experience is for both educators and school administrators. I'm here now to teach you what you need to know to get your school's data organized and ready to use. Now, let's get started. Welcome back to our next course in our series on Google Sheets for educators and administrators. So in this course, we're going to look specifically at formatting. So formatting isn't just about making sure that your spreadsheet is pretty or, you know, visually appealing. It's also you'll see it will make your spreadsheet more functional. So whether you are trying to include data for an upcoming grade level meeting or perhaps assemble some testing data for your school, whatever the case is, we're going to look specifically in this course and how to make your spreadsheet more functional with the right formatting. Let's get started. 2. Formatting Text and Numbers: Remember that formatting isn't just about making your spreadsheet look pretty. It's about making it easier to read and interpret. So let's start with some basic text and number formatting in our practice spreadsheet. When I first open a spreadsheet that I've received from someone else or from, say, a data system, the first thing I'm going to look for are the headers. So those are usually in row one, and I want for them to stand out and I want to be able to read everything that's in them. So the first thing that you can do is you can make your column with Swider, okay? We did talk about this in another video, but let me just show you. You can select all of your columns, and then if you double click on the line in between those two columns, any one of the two, if you double click, it will make all of the columns just the right size. It's called right sizing, and it's going to make it so that anything that's long in that column will show. So let's go back to our headers, and I'm going to select row one again, and I'll make those bold. I could also make the metallics, underline. There's a few other things. We could even change the color of the text. If you'd like, you could change it to something different, or you could change the fill color. So if you wanted it to have, say, a yellow background or some other color. So you have some options there on formatting as well. You could another thing you could do is you could add borders if you wanted to, to some rows. So you could do that, as well. Now, everything that I just found here, oh, one other thing I forgot to mention is the font size. So you can increase the font size here if you wanted. I'm going to go back to 11. And you can change the font, as well. If for some reason you wanted to change the font to something different, you have that option. So all of those things for formatting are also in the format menu under format text and font size. So here's the bold Italics underlined strike through. So there's several different options in here under the format menu. Okay. Next, let's look at Oh, one other thing we haven't done is centering. So I always like to center the data in my headers. That's preference, personal preference. If you don't want to, you don't have to. I just like to do that. And then one more thing. If I have a long spreadsheet and I scroll down, I can't see the headers anymore. So I like to just roll my mouse right over this little bar here and notice a hand shows up and you click the hand and drag down. And you've now frozen row one so that when I scroll down, row one is always going to stay. That helps you so that if you're looking at a long group of theta, you still know what data you're looking at in the appropriate column. Alright, let's look at numbers now. So notice we have these grades here. We could add a decimal place to those if we wanted. I'm just going to click on column D here. And notice these two buttons here, decreased decimal place and increased decimal place. If I click on Increase once, it's going to increase it so I can see the first number after the decimal. Now, let's say one of my students had a 69.9 and then I decrease the decimal there, it is going to round that for you when you decrease the decimal. So you do have that rounding that happens automatically. I'm just going to hit Undo to change that back to a 70. So that is how you format numbers. Now, if for some reason you had dates, you can add formatting to those, as well. So I'm going to click on column H here. And then I can go to format, number. And then there is just a traditional date here and a date and time together. But if you scroll down to where it says custom date and time, here's where the real magic happens. There's all these different possibilities of how you want your date to show up. Do you want the day of the week? Do you want it to be just the month and day without the year? Do you want the year first and then the month and then the day? So you have so many different options here. I'll choose, I guess, this one. I like this one. Now, if for some reason you wanted to add something special, you could do that here in this little section. So like, notice if we wanted it to be 30 instead of 1930, we could do that. So you do have some options and then hit Apply. And now notice that date has been updated, and if I add another day, it will have the same formatting because we applied that formatting to all of column H. Alright, so let me undo that. So that is how you format dates in Google Sheets. So we won't cover this in this particular spreadsheet, but there is a way to format for currency and for percents if you have those types of data in your spreadsheet. And then there's more formats listed here as well. So you have a good group right here in this section that covers most of the different types of numbers you would need in your spreadsheet. So remember that consistent formatting, it's going to help you understand and get a good grasp of your spreadsheet at a quick glance. And even when you're sharing it with others, you want for your spreadsheets to be clear and understandable when you are sharing them with others. 3. Conditional Formatting Basics: In this lesson, we are going to look at conditional formatting. So conditional formatting is a really powerful tool that will automatically apply formatting to your spreadsheet based on rules that you decide. So this feature is going to help you highlight trends. It's going to help you flag data that may need your attention later. So let's go ahead and get started, and we're going to look first at column D that has the grades in it. So we want to highlight any grades that are less than 70. So we want to know who in our class has a grade less than 70. So we'll start by highlighting or selecting column D. So I'll click on the D and then go to format and conditional formatting. So when I do that, it's going to bring up this rules pane on the right hand side, and we're looking for less than 70. So we're going to go to format cells if less than and put in 70. And notice it automatically gave us the results of that rule. Now, I don't want them to be green. I'll change them to kind of a reddish color and then hit done. So now all of our students who have less than a 70 are now highlighted in red. So let's do another rule for any students who have a 90 or higher. So this time we'll say greater than or equal to 90. And we will leave that green. So then let's hit Done, which leaves the students in the middle who have a 70 to 89. So let's make a new rule for them as well. You wouldn't have to do this, but you could if you wanted. So I'm going to say, is between that'll help us. So we want anything from a 70. Now, we don't want to include 90 because 90 is already green. But if we say 89, we would miss any student who has, say, an 89.5. So just to make sure we catch any students who have a little bit of a higher grade, I'm just going to put some nines at the end. And then I'll change that to say this kind of orange color, and then hit done. So now we have three rules that all apply in this column. Any of these rules can be deleted by clicking on the trash can, or you can click on the rule itself if you wanted to go in and edit it. You could do that you could do those things. So that's how you add conditional formatting to numbers. Now, you can also add conditional formatting to text. So let's look at the attendance column. Now, because I didn't close this pane, it stayed here for me. And when I went to a new column, it got rid of all those rules. Now, the rules are still there in column D, right? But when I move over to column F, that column has no rules. So I'm going to select column F, and I'm going to add a rule, and we're going to look for text contains absent. So any student who's absent, I want them to show up as red. So maybe we can call home, see what's going on. And then hit Done. Now, normally you'd be finished here, but I notice that I've got a couple kids who are blank, and that's a concern to me because no one should have no attendance. They should be marked present or absent. So I want to figure out what's going on there. So I'm going to click Add Another rule, and this time, I'm going to say is empty. And I'll change that to, say, I guess, maybe yellow. And then hit Done. So now our kiddos who have nothing, no attendance at all, those are marked as yellow. And that way, we can follow up and find out what's going on there. Another really cool thing to note about conditional formatting is that it updates dynamically. So if I make a change to something in my spreadsheet, let's say we decide the student is absent today, it's going to change and show the conditional formatting. The student becomes red. And then if I undid that change, it goes back away again. So that's a nice feature of it so that if your data is changing, like if someone is editing your spreadsheet or if you are, it's going to then highlight the new data that has been updated recently. So that covers the basics of conditional formatting. If you're done, you can then just close this pane here so that it's gone. But remember, anytime you need to get that back again, you can just select the column that you need and then go to format conditional formatting to bring it back again. 4. Formatting for Printing: For this lesson, we're going to look at formatting our spreadsheets for printing. So you might be printing like printing on a printer to paper, but you may also just want to save your spreadsheet to a PDF so that you could email it to someone or distribute it in some kind of a way. So let's look at the process because whether you're printing it to a piece of paper or printing it to a PDF, you're going to go through the same process at the beginning. So let's go to either file and print, or there's just a print button right here on the menu. Okay, so it's going to have a default setting. And notice right now, it shows me at the top that my data is 33 pages long. So not necessarily my favorite. I feel like there's probably a problem there because I don't have that many students. Well, here it is. So notice that there is this line this long line over and over again coming from our conditional formatting that's highlighting. So let's go in and fix that. I'm going to go down to the end of my data, and I'm just going to delete all of these rows at the very bottom below row 68. Now, the shortcut I do to do that is Control Shift down arrow. So what that does is it goes from the row that you're currently on to the bottom of your spreadsheet, and it selects it, and then I'm going to right click and delete rows. Now, Edith is our last student, and there's no more blank data down there. Okay, let's try to print again. Now we have three pages. That's where we want to be much better. So the first thing we're going to do is we're going to look at some of the options here. I like to start with landscape and portrait. Do I like for my data to be in landscape view or portrait? I think in this case, I like portrait, but notice that comments are on pages 3 and 4. I don't really love that. So let's see if we can fix that. We can go to margins and change those to narrow and see, notice that fixes it. Or if you wanted the margins to be normal, then you can go to scale, and scale has an option called fit to width. So when you do that, it's going to make sure that all of your columns are going to fit. Now, if you wanted to, instead, you could do fit to height so that all of your rows are on one page. But that's a little bit too much for me. There's also one called Fit to page to make sure all of your data is on one single page. Now, you probably wouldn't want to do that here because we just have way too many students, and the font would be really tiny. So I'm going to change it to fit to width. To make sure that the comment column, this last column stays together. Okay, let's see. So we have two pages. That's good. Now, the next thing I like to do is go to formatting, and I always like to turn on the grid lines. It just makes it a little easier to see each row in the column. So that's grid lines. There's some other options down here like alignment, and we're not going to really do anything with those, but if you wanted to, you could align if you wanted to make your data go kind of to the top of the page or to the bottom. So you have some options there. Now let's go to Headers and Footers. So in the Headers and Footers section, there are some basics here that you can turn on. So when I hit that checkmark, you'll notice it puts a page number. You could also add the workbook title, the current date, the time, and so on. But I don't really like to use those. I like to go directly to Edit custom fields because it's going to show me six different places where I can put information on my spreadsheet. So let's say in the top middle, I want to add, what do we have here time date. The title. The workbook title. Yeah, I like that. So I'm going to put that there. And then maybe in the bottom right, I want the page number. Let's see. Is page number this one? Yeah. And I want it to look like this. So now it's got that there. I'll hit Confirm and go see if it looks the way I want it to. And it does. So here is the name of the workbook, and here it says page one, and here's page two. So I like to do it that way because if I wanted to even if I wanted to, like, type something in there, like if I wanted to put student List Quarter two and then hit Confirm, then student list quarter two would now show up on the top of each one. So you can even type something in. You don't have to only use the fields that they've given you in there. So that's under Edit Custom Fields. So from here, we can hit next. And then it's going to ask you, do you want to print to a printer or do you want to print to a PDF? And you have the option to choose whichever one you want, and then you can hit Print. So if I hit Cancel here, though, I want you to know that the changes that we made have been saved. So I'm going to hit Cancel, and then I'm going to go back to print again and notice those changes are still there. So everything that we have added to the footers, the width, the grid lines, all of those changes are saved for us. Another thing to note, if you only want to print a portion of your spreadsheet, you can do that. Like, let's say we just want to print Mr. Gross' class only. So I'm going to select that data and then go to print. And then when it's his current sheet, I'll change that to selected cells. And now we're going to only see his students and nothing else in our spreadsheet. And then from there, we could hit next and then either print it to a PDF or print it to an actual printer, either way. So that covers printing in Google Sheets. Let's move on to our next lesson. 5. Practice What You Learned: Alright. We are back and we're ready to do our project for this class. So the details for the project are listed here on the screen, but you can also find them in a little bit of a bigger font, easier to read in the class itself. So, now is your time to go through and attempt the project on your own. I'd like for you to pause the video, do the best you can, fill out all of the parts of the project. And then when you're ready, resume this video, and I will walk you through all of the steps so that you can check your work and make sure you did it as you were supposed to. So, now's your time to pause, and then we'll resume when you're done. So let's start with our sheet. The first step says, create a new Google Sheet, which I've done here. So now I'll enter the following headers, and sell A one, I'll enter student name, and then the days of the week. Across the top. It says, In Column A, starting in Cell A two, list the names of your students. So I'll do that now. Okay, I've entered in the names of all my students. So what I'll do is all write size column A by double clicking on the line between A and B, just to make column A a little wider to accommodate the length of some of the longer names. And then it says, I columns B through F, record the attendance for each student using Ps for present, A for absent, and leave cells blank if an entry is missing. So I'll just go in and put in Ps and As, just some fake data for all of my students. Okay, I've now entered all of the attendance for my students. I've got a good representation of Ps and As and blanks so that we can test to make sure our conditional formatting works correctly. So let's move on to the third part where we're going to apply conditional formatting. So to do that, we're going to select the range covering your attendance data. So I can do that by highlighting all of the Ps and the As and the blanks and then go to format conditional formatting. Okay. And then from there, I'm going to have to move this over so we can see what they says. It says, set the rule to format cells if the text is exactly A and choose a red fill color. And this will highlight any cell with an A. So I'm going to say the text is exactly A and then make that into a red fill. Okay. And then done. And then the next part says, with the same range selected, add another rule, choose to format if the cells are empty and set a yellow fill color. This will flag any cells where attendance hasn't been recorded. So add another rule, and this one is empty, and we want it to be yellow. Okay, and then done. So now our blanks are highlighted yellow, and our absences are highlighted A. Okay. And then from there, it says, review your sheet to ensure absences and blank entries are clearly highlighted. Experiment by changing some Ps and As or leaving them blank to see how the formatting responds. Okay, so I'm going to close out of this and I'm going to do some checking. So I'll change this to an A, and it does update. I'll change this to a P, and it does update, and I'll change this to an A, and those do update. Perfect. So I think all of our formatting is working well. This point, we are done with the project. Everything looks good. But there are a couple of things I would do just because I like these are things I always do to my spreadsheets. So I always highlight or select column one, and I bold it because those are my column headers, and then I center it. So that is something though it's not in the project, it's something I always do. And then I always freeze the top row. So here at the top, that little bar between the column headers and these column the letters up here at the top. When the little hand shows up, you'll just drag it down one row and let go. And now Row one is frozen. So you might be asking, why do I need to freeze Row one if I have a really short amount of data? I can obviously see all my data. Well, there's a couple reasons why. The first reason is you might be adding more data later, right? So you might get 15 more students, and in that case, it wouldn't all fit on one screen, right? Um, or another thing is, if you decide to print this, you will want for this to be frozen in case you have more pages to Like, if your printout comes on two pages, you will want the first row to be frozen so that it'll show up on both pages. So it's always good to go ahead and freeze the first row, bold it, center it, and, um, yeah, I think that covers it. I think that's everything we need for the project. So I hope you were successful and go ahead and post a screenshot so that we can all see the progress that you're making in Google Sheets. 6. Wrap-Up and Next Steps: Congratulations. We have completed another course in Google Sheets, this one in formatting. So let's do a quick review of everything we learned. We started by looking at text and number formatting. So we learned that it's general good practice to bold and center our headers at the top of our spreadsheet. And we looked at how to resize our columns by double clicking on the letter, the line between the letters to make sure everything was wide enough. We looked at number formatting. Remember, we can decrease and increase the decimal places. And we looked at conditional formatting next. So if you'll remember, we went to format, conditional formatting, and you can add rules here or you can edit or delete rules if you'd like, in either case. We talked about how you can add rules based on text, you can add rules if a cell is empty or not. And then we also talked about adding rules for numbers. So you can have different colors or different fonts or different fonts, colors, fill colors, all sorts of different combinations to indicate something in your data. Now, remember that conditional formatting is dynamic. So if I change someone's absence from present to absent, the conditional formatting will update. So it's a dynamic thing. And then the last thing we did is we did our project. So you looked at the project in the class description, and then we created a list of our own students and placed fake attendance for them. And then we used conditional formatting. Let me do that here, conditional formatting to create rules based on the attendance. And then we made a note that say, if we update something, that it will automatically update the conditional formatting colors. So if you like, you can post a picture of your project in the class description so that others can see your work. Of course, you wouldn't want to show your own students' names. So you might use fake students' names like I've done here, or you could just blur that part out or cut that part out of your screenshot. But we'd love to see your work that you're doing to get better at Google Sheets. Now, remember that Google Sheets is something that builds upon itself. So it's great if you can continue working in these classes one by one and learning all of the essentials, and then we'll get into more intermediate and advanced topics as we go. So thank you so much for joining us in this class today, and I'll look forward to seeing you in another.