(DESCRIPTION) A google Slide presentation. (SPEECH) MELINDA HOLT: Here we go. So if you are ready to rock and roll, here are some links. There are two links here. They're both bit.ly's. There are some uppercase letters, so you need to be aware of that. All right, so here we go. We're on the Sheets. (DESCRIPTION) bit dot l y slash Sheet tips, one word, with a capital S.. This Slides Deck. bit dot l y slash Worksheet Tips, one word, capital W and T., A companion Sheet. Next slide, Sheet Tips. Bend the data. (SPEECH) On the Sheets tips. We're going to bend the data. (DESCRIPTION) What is Sheets. (SPEECH) What is Sheets? Sheets is a spreadsheet, OK? So those of you that have worked with Microsoft, if you're familiar with Excel, same thing. Exactly almost to the minuscule little data points, same thing. Yes, whatever you can do in Excel, you can do in Sheets. Can you do it the same way? Heck no. They're different, OK? They are different. Google Sheets is also an app that you can install on any tablet or any phone. I don't recommend you do that because it is really, really small. You can do all kinds of things with Sheets. (DESCRIPTION) Objective Today. Everyone will have a better understanding on how to use Sheets. Everyone will learn how to simply manipulate data that is collected. (SPEECH) And for those of you that aren't familiar with Forms, everyone look up here or look at me. (DESCRIPTION) Google Search. (SPEECH) Forms is the sidekick to Sheets. (DESCRIPTION) Types in address bar, Forms dot google.com. An untitled Form opens. (SPEECH) Everyone thinks Forms is this great big monster and it's the big brother of Sheets, and it's actually not. Sheets is the big brother to Forms. (DESCRIPTION) Updates title to Delete Do. Clicks Responses on top center. (SPEECH) So when you create a Form, their responses are going into the Form, but all of their answers can also be put into a Sheet. (DESCRIPTION) Circles cursor over the small green Sheet icon at the top right. (SPEECH) So everything-- every quiz that you create, every data set that you have that you're collecting from people, I don't care if it's a poll or it could be what are you bringing to the potluck in six months, what are you doing for this, what are you doing for that, what are your opinions on this, that, or the other thing, upload your files, do all of this stuff-- that's Forms, but it all goes into a Sheet. So all of the information from your Form goes into a Sheet. You have to know how to work with the Sheet so that you can manipulate the data that you're collecting. Don't just look at the form for the data. You're going to get so much more functionality out of the sheet when you collect the data in the form. (DESCRIPTION) Switches tabs back to Slides. (SPEECH) So just remember that Sheets is the big brother. Forms is the little brother. OK, it's the sidekick. (DESCRIPTION) Terms to Know. (SPEECH) So some terms to know. You have to know these terms. For those of you that work with Sheets or you work with Excel, you already know these. Bear with me. We gotta get everybody up to speed. You have to know what a cell is. (DESCRIPTION) Cell, denoted by Column and Row. B3 would be the second column in the third row. (SPEECH) You have to know the cell range, what a cell range is. (DESCRIPTION) Cell Range, A group of cells usually within same row or column denoted with colon, A1 colon A10, includes all cells within that range. (SPEECH) You kind of have to know what a chart is if you're going to be working with charts. (DESCRIPTION) Chart, A graphical representation of data. (SPEECH) And I'm going to open up a new Sheet before I get to the Sheet tips. (DESCRIPTION) In a new tab opens a blank, untitled spreadsheet. (SPEECH) All Sheets have cells. So when you click in a Sheet-- click-- this little square thing that when I click, it becomes a little square, this is a cell. And it's in line with a letter, which is a column, and a row. So this cell right here is at B3. You have to understand that so that you understand the formulas that you're going to be creating. So this right here, this is B3. And I'm going to shade that, so you see it a little better. (DESCRIPTION) Shades it bright teal. (SPEECH) This would be C3 right next to it, D3 right next to that. In the top uppermost corner, we've got A1. So it's really simple, but you need to understand that. OK, so we've got cells. This is a cell. B3 is a cell. The entire row would be the row 3. The entire column would be column B. So we've got cells, rows, and columns. OK, what else do you need to understand? You need to understand that everything that is put into a Sheet is data. Everything that is in a Sheet is data. So even this one little cell right here that has B3 on it, that is data. I don't know what it means, but it's data. Sort-- it's just you can go from A to B-- or A to Z, I'm sorry-- A to Z or Z to A. So sorting is something you need to understand as well. Format-- we all understand format. (DESCRIPTION) Format, to add style or feature. (SPEECH) Even spreadsheets have a format function, so I could take this cell right here and I can make it bold. (DESCRIPTION) Clicks B at the top. B3 bolds. (SPEECH) OK, you can't really see it all that much, but I can make it bigger. (DESCRIPTION) Changes font size to the left of B. (SPEECH) OK, so that's part of the formatting. There's a lot more you can do on a Sheet with format, and we're going to cover this in a little bit. By using the Format menu, you can do all kinds of things to this Sheet that you can't do in a Word doc, that you can't do on a Slides. So there's lots of functionality here with format. So these are just some words that you need to understand. Read over those. Get 'em in your head because I'm going to be saying them. So if you're not quite sure what I just said-- what does she mean by format-- well, just click on slide four and look at that definition. So here we go. We're going to have some fun now. Woo-hoo. (DESCRIPTION) Back to Slides. Tip one. Create New or Create Template. (SPEECH) We're going to open a new Sheet. Don't do it yet. There's two ways to do it. Actually, there's more than two ways. There's many ways. There's two methods, I should say. There's a conventional method and unique methods with an S, OK? The conventional method, most of us know. (DESCRIPTION) Open New Sheet, Conventional. (SPEECH) And if you're new to Drive, you might not know this and that's OK. We're going to-- I'm going to show you this if you want to follow along. Feel free. From the Google window, I know I'm signed in because I see my little avatar right here. There's a little tiny person right here. I see myself. You might just see a letter. And if you just see a letter, that's OK, so long as it's your letter. If your name's Jean, it should be a J. If your name's Anthony, it should be an A, OK? So just make sure it's you. And then right next door is a waffle. (DESCRIPTION) Three rows of three dots to the left of photo. (SPEECH) In the waffle, you're going to see Drive. (DESCRIPTION) A triangle made of green, yellow, and blue sides. (SPEECH) All right, so that's one way to get to Drive by using the waffle. It'll be with you on any Google tool. You click on the waffle, and you can go to Drive or-- I don't know-- I kind of like typing, so I just type drive.google.com and Enter. So go to Drive. If you've never opened a new Sheet before, we're going to click on the New button. This is the conventional way to open a sheet. (DESCRIPTION) Upper left corner. (SPEECH) Going to click on New, and you can see because Google Sheets is one of the top three apps, you can see Google Sheets right here. (DESCRIPTION) Drop down menu. (SPEECH) Now, there's a couple of things you can do, and you might want to look over if you-- ah, I already know to do this. So next to Google Sheets, you're going to see a little arrow. If you click on the word Google Sheets, it's the same as clicking on Blank Spreadsheet-- same, same. But from here, you also get the option from a template. Just wanted to let you know that. I'm not going to do that. You can do it if you wish, but then you're on your own. Don't ask me any questions, OK. I'm just going to click on Google Sheets, and bada bing, bada boom, wait for the magic. The first thing you should do when you open up any new file on Google is to title it, so where it says Untitled Spreadsheet, just type something in there to let you know what it is, OK, because Google doesn't care what you name this. You could leave it Untitled Spreadsheet. You could end up with 30 Untitled Spreadsheets, which when you go and look for that spreadsheet that had all of the quizzes in it, you're going to have a hard time finding it. This up here in the Omnibar, that is actually the name of the file. Google names the file as soon as you hit New Spreadsheet, OK. So you need to make sure that you title it something that you're going to remember, because I can guarantee you, you are never going to remember this big long URL that's up here in the address bar, all right. And even if you're on Safari or Explorer or Firefox, any of the other browsers, that big long link up in the address bar, that is actually the name of your file, OK. So title it something you're going to remember, all right. So this is one way, that conventional way, to create a sheet. There are a bunch of other unique ways. So I'm going to go back to the slides deck, get the next slide. (DESCRIPTION) Open New Sheet, Unique (SPEECH) And this only works if you're using Chrome. If you're going to use Safari, Firefox or Explorer, you have to either use the Waffle, OK-- so you're going to select the Waffle and find select that sheet icon-- or you're going to go to Drive, hit the New button, go to Google sheets, OK. If you're using Chrome, you could, on a new tab, type sheets.new or type sheets.google.com. Either way you do it, you're going to be creating a new sheet. start new is one click. So I'm going to open a new tab, I'm going to type sheets.new. Sheets with an S dot new. Hit my Enter key. And there we go. (DESCRIPTION) New sheet opens. (SPEECH) I've already been signed into Google. Maybe I've seen something really cool that I want to copy, or I want-- oh, this is a great idea for data. Boom. I go sheets.new and I've created a new sheet. Just like that. Because I know I'm signed into Google. I'm always signed into Google. So I just go sheets.new, boom. Done. OK. Now, because I'm not going to use this, I'm going to type Delete here. So I remind myself to delete it. (DESCRIPTION) Changes title to Delete. (SPEECH) Because the other way to open a new form is to click open the new tab, right? And then actually typed sheets.google.com. Now, why would I want to type all of that? Because you're going to see up at the top of the page your template gallery. If you don't see template gallery up at the top of your screen, you probably see something that looks like your district name. Which means you're using your club, which is fine. So you just click on that, and up towards the top of your screen, you're going to have tabs, that I don't see because I'm on the pub. I'm on a public Google. OK. But you'll probably have tabs up here. One will have your district name, and then the other one will say general templates, or something to that effect. OK. So template gallery. Look at all of these templates I could use. And as soon as you click one, it's yours. So don't click. Don't click. Because it will be yours. OK. And you're going to-- oh, what's this look like? Click. Oh, what's that look like? Click. What's this look like? Click. And you can have all these different spreadsheets in your drive. Two weeks from now, you're going to-- what the heck was I thinking? So there's a lot of good sheets here. And after you click in, and then it is yours. Go ahead and click one, if you want to. You're going to be able to change it. OK. So I'm going to click on this one down the bottom. Mine says-- Gradebook. I'm going to click on a gradebook. As soon as I click it, I have to wait for the magic. OK. And somebody has created this template because they think it's a big beautiful thing. And if you look at this template, if you followed along with me, and you've got the gradebook template, you would go look at the bottom and you see different tabs. So here I have grades. I've got students 1 and 2, and they've already put in some data for me. So I can follow along, I can put my teacher name. And so you can make it anything you want. This is yours to do what you want with. OK. You can also change the color. You don't like the color blue, just means select it, you can make it red. The template gallery. Let me go back over that. I opened up a new tab. I went to sheets.google.com, sheets with an S, dot google.com. OK. And up at the top of the screen, you should see the plus sign with a blank. And then on the top right hand corner, you're going to see something that says template gallery. If you don't see that, you might see your district name. So you click on your district name and then you'll see two options. You'll see two at tabs. One will be your district, which has no templates. Or maybe just one, I don't know. And then the other tab will say General Tab or General Templates or something to that effect. By the way, while you're here, maybe you don't ever want to use templates, fine. Click on the skinny snowman and hide your templates. (DESCRIPTION) Three dots stacked vertically. (SPEECH) Don't do that unless you never want to see templates again. OK. Yes, you can get them back. But it's kind of hard to. I found that out. So learn from experience. So this is the template gallery. And that's-- and if you scroll down when you go to sheets.google.com, these are all of your sheets as you scroll down. Now, I'm looking at every one of the sheets that I've ever had, that I've ever touched, or that's ever been shared with me. OK. I'm going to look for just owned by me. Here we go. These are all of my sheets. All of the ones that I've created. Why is this so-- so what? I could just go to my drive and get these. Yeah, you can go to your drive and you'd have to do a search to find all of your sheets to put them in a list like this. Google's already done this. This is exactly what you see when you have the app installed on your tablet, on your phone. You're actually going to the app site. OK. So instead of install-- you don't have to install anything, because you're on a desktop, right? So you open it up in a browser, sheets.google.com. That is the app site. So this is actually what the apps see. OK. In line with each one of these sheets that I've created, there's a skinny snowman way over to the right. And when I click on it, I can remove it. OK. Or I can rename it. Or I can open it up in a new tab. Or I can make it available offline. This is not in the sheet tips. So you better pay attention. OK. So how did I get here? How do I make it offline? Cancel. You go to sheets.google.com. You find the sheet. Don't make them all available offline, that takes up a lot of space when you are working on a tablet or Chromebook, OK, or your phone. So when you make an offline, what it's going to do the next time you open it, is it's going to download it onto your device. So that you're able to make changes on it whether you're near a Starbucks or not. OK. So don't make them all offline. Just the ones that you're going to have-- that you want to manipulate offline. This goes the same with slides and docs. You can do it the same way. You click on the skinny snowman. You click on available offline. Wait for the magic. And then we see this little checkmark next to the name. And that's telling me-- OK, this one, I can work offline if I want to. OK. Also in line with the skinny snowman is the Remove. So I'm going to turn off available offline, and then I'm going to remove it, which basically just moves it to trash. All your files are save to trash for I think it's six months now. And then they will be deleted. They will be deleted in date order. OK. So we've opened up new sheets in a couple of different ways. We've done it the conventional way, through drive. We've done through the waffle. You can go to the waffle, if you're signed into Google. Yeah, Linda, you're going to have to use chrome. You have-- yeah, you have to use chrome in order to make your stuff offline. Sorry. I forgot that one little part. We've also done sheets.new new you've gone to sheets.google.com so that you can see the templates. OK. I have a sheet that's-- it's shared with me that has an extension excel less-- or how do you convert it? OK. If you want to convert-- yes, you can manipulate sheets if they are an Excel spreadsheet. You can keep them in Excel spreadsheet. What you do is you go to drive. OK. So I'm going to drive. (DESCRIPTION) Clicks the waffle and then selects Drive. (SPEECH) And you would find the spreadsheet. First, let's go over uploading. So you would find this spreadsheet and you would upload it. There's a couple of different ways to do it. I'm to do it the way that I can show you right now. You go to New and then File Upload. All right. So you upload that Excel spreadsheet. OK. Before you do that, though, you want to go to the gear. (DESCRIPTION) Upper right corner to the left of the waffle. (SPEECH) So you're going to click on the gear. You're going to go to settings. And then you're converting uploaded files to Google Docs editor format. (DESCRIPTION) Second option. (SPEECH) This includes docs, sheets, slides. So everything that you convert, if you have a word file and you upload it, it will convert to docs. If you had a PPT, it will convert to slides. If you have an XLS, it will convert to sheets. So you have to go to drive. You have to go to drive.google.com. You select the gear up at the top of the page on the right. You go to settings. And then convert uploaded files to docs. OK. All right. So that's how you convert it. If you've already uploaded it and it's already converted, go ahead and click it open. And then up at the top of your screen, you're going to see Open With. This is on docs, sheets, and slides. It works the same way across the board. So you open that PowerPoint, or you open that spreadsheet, or you open up the doc, and up the top a screen you'll see Open With. And it will give you the option to-- whatever it will convert to-- it'll give you the option to open it up in a spreadsheet, PowerPoint-- slides, or a doc. And then you choose the appropriate format. Boom. It's converted. So you have to do one or two more steps that way. But that's how you convert. OK. Let's move on. We want to get to do some stuff. (DESCRIPTION) Tip 2. Simple Sum, Time Too (SPEECH) Some stuff. It does throw off the formatting, but you know, what is the price you pay for being free. Grin. Smile. Laugh through it. OK. Change your formatting. All right. I'm going to go to the sheet tips worksheet in just a minute. Before I do that, I want you to see what we're going to be doing first. (DESCRIPTION) Format. (SPEECH) So we're going to select an empty cell. And then we're going to type in a function. (DESCRIPTION) Always start the function with an equals sign. (SPEECH) And then we're going to have a formula. OK. So we're going to actually format or formulize a sheet here. So this is mine. (DESCRIPTION) Opens a sheet with four rows of data. (SPEECH) And here's the address. (DESCRIPTION) bit dot l y slash Worksheet Tips, one word, capital W and T. (SPEECH) Just-- and there's 41 of you here already. That's cool. So not-- you're not going to be able to write on this. You're not going to be able to write on mine. What you're going to have to do is go to File. (DESCRIPTION) Upper left. (SPEECH) Make a copy. OK. And you're not given any options. So you just make a copy. Rename it if you want to. That's fine. It'll come up as copy of. Right. So you can rename it here if you want. And then you can type it on it. Type it on It, yourself. All right. Down at the bottom of this worksheet tips is a simple sum tab way down at the bottom. And I'm not quite sure it's showing up on you guy's screen. I know this is really small. I apologize. But I can't make it any bigger. All right. So simple sum. I'm going to go to that tab. (DESCRIPTION) Second tab from the left. (SPEECH) Now, before we do any summing, all right, formatting is kind of important. When you're putting in numbers, sometimes for no rhyme or reason, Google will left a line, write a line, or even center a line for no apparent reason at all. So let's line up our numbers. OK. So I'm going to choose A2, that's the cell. And I'm going to hold down my Shift key to A7. And you can see I've selected a range of cells. (DESCRIPTION) The range turns light blue. (SPEECH) I'm starting to use those words. So I've got a range of cells here. OK. I want to center a line, all of them. So I'm going to look at my toolbar, and right next door to the Merge Cells button, there's a horizontal Align tab. So there's a little arrow next to it. OK. And all those little lines right there, I'm going to click on the arrow and then I have three options here. I'm going to central line, the one in the middle. And look at that. All my numbers are centered aligned. That makes it a little easier to view. I might have even included the header if I wanted. So that they're all central lined together. There we go. (DESCRIPTION) Header centers, Sum slash average. (SPEECH) All right. Now, next step. We're going to add all of these numbers right now. Or select A8. So it's the number after the number 87, or the cell after number 87. It's also yellow that a little bit. OK. So we've selected that cell. All right. Now, Google knows things and sometimes it reads our minds. OK. So with this cell selected, I am going to-- I'm just going to start typing. OK. And all formulas must start with equals. Must. So we're going to add all of these cells right here. We're going to add all of the cells. And the formula or the function for that is called sum. Now, I just know that because I've been doing this a long time. If you want to do something else different, you don't want to do a sum, or you want to know where that sum is at, they're right here. This little-- I don't know what that icon is called. (DESCRIPTION) Upper right (SPEECH) I used to. It's the Functions icon right now. But that backwards looking E, or the weird looking E is a Greek letter for something. Anyways, so Functions. There's arrows next to it. And here are all the functions that I could add to the cell that I have selected. And here's Sum. (DESCRIPTION) First item on drop down list. (SPEECH) OK. So I can hit that Sum. And then look, the cursor starts blinking exactly where it should. And it has already put in equals sum for me. Now, what I have to do is I have to figure out which cells I want to sum. Summation. Yes. OK. So I could do A1 and then plus A2, and then I could do plus A3. And for you school folks, you're going-- oh my god, what is she doing? Oh, by the way, I don't want to do A1 because that's the header, right? That doesn't have a number in it. So A2 plus A3, plus A4, that's a lot of typing. OK. You can do it that way. You could do equal sum A2, A3, A4, A5, A6, A7. You can do it that way if you want. There's a much easier way. When you're doing a range of cells, you want to remember the colon. OK. So I'm going to see if I can see Zoom in here. I can't because it's-- there we go. So right here, I've got equals sum. And I could have type that. I didn't have to use the function bar. There, Menu. So I've got A2 colon A7. Then I'm going to hit my Tab. Boom. (DESCRIPTION) Equation switches to 284 (SPEECH) And this range of cells equals 284. If you double click in the cell that you just put the formula in, you will see that the range has been highlighted for you. So if any of those numbers are missing-- expression. Thank you, Jean. OK. If any of the numbers are missing, then you know-- uh-oh, I got to add something. So let's-- let me show you that. Whoops. A5. (DESCRIPTION) In cell A 8, equals sign SUM parenthesis A2 colon A5 close parenthesis (SPEECH) OK. So I see here that I've got these cells are selected. They're highlighted. But I'm missing A6 6 and A7. so I need to go back to my formula. (DESCRIPTION) Sum changes to 175 (SPEECH) All right. You go right back here. And then I can change the numbers as I need to. (DESCRIPTION) Changes A5 to A7. (SPEECH) It's a very simple function, the sum. (DESCRIPTION) Number changes back to 284 (SPEECH) OK. So if you're following along, I want you to do average now. OK. So right now, it equals sum, leave that there. In the cell below it, A9, I want you to do your own average. I want you to average A2 through A7. If you want to look-- to follow along, you may. I'm actually going to type it. (DESCRIPTION) Types equal sign A V G parenthesis A2 colon A7 parenthesis. (SPEECH) You have to have the cell ranges within parentheses. (DESCRIPTION) When she tabs out of the cell the equation disappears and changes to Pound sign Name question mark. (SPEECH) Oh. What happened? (DESCRIPTION) Tabs back over to cell (SPEECH) That's not an-- (DESCRIPTION) Highlights A V G.. Box pops up, Unknown function, A V G. (SPEECH) they changed the function name on me. Oh my god. I haven't used the average in a long time. This happens all the time. (DESCRIPTION) Navigates back to the Expression button to drop down. Points to second selection, Average spelled out completely. (SPEECH) Oh my god. You have to actually type it out now. All right. So I just learned something. The function for average used to be AVG. But now, they make you type it out. (DESCRIPTION) Switches A V G to Average (SPEECH) And there we go. There's the average. (DESCRIPTION) 47.33333333 (SPEECH) This is a really long number. We're going to learn about formatting here in just a sec. I'm going to scroll over a little bit so I can look at the time or duration. You might not have to do that. But I also wanted you to see that you can also-- and this is really important, especially on a form. If you ask students-- how long did it take you to read this? How long did it take you to read that? How long did it take you to do this or that? And they put in the times. They put-- 30 minutes, took me 15 minutes. It took me an hour and 15 minutes. So they put in the time that it took them to do something. We need to make sure that Google understands that this is not a time, but a duration. OK. So right here, this 1 colon 15 colon 00, that could be misunderstood to be 1:15 AM. OK. 45-- this must be a duration. OK. It decided that as I was putting in these numbers, as the form was putting in these numbers. OK. But you can see this one it says 1:33 AM. (DESCRIPTION) In the formula bar at the top left. (SPEECH) So it's understanding that as a time instead of a duration. So I need to make sure all of these are duration. So I have to format them. So I'm going to select the cell range. I'm going to select-- what is this? (DESCRIPTION) Scrolls up to see header. (SPEECH) There we go. This is C. The C column. OK. So I've got C2. I'm holding down my Shift key through C7. OK. So you're going to go to Format. Then down to number. And now, down in that list, you're going to see something that says Duration. So click that. Now, Google knows all of these are duration. And now you notice that the 1:15 that was 1:15 AM is now 1:15:00.00 (DESCRIPTION) In the formula bar. (SPEECH) OK. I want to make all of these right aligned. I'm going to select the cell range again, and I'm going to-- because I've made my screen bigger, I've got to look for the alignment, the horizontal alignment. And I'm going to make them all right aligned. There we go. Now they're all lined up. So OK. I want to show you that you could sum a duration. So we've got-- Google knows that this is a time duration. So I'm going to do exactly the same thing that I did over in the A column. I'm going to just sum. I'm going to go equals. Whoops. Equals sum C2 colon C7, in parentheses. And there we go. 4 hours, 35 minutes, 30 seconds. So you can add up time that your students have spent on something. You can add up scores that your students have spent on something. You can get the average time, the average score. You can do all kinds of stuff. OK. How did I make a duration? So I put-- let's just say the form fed the sheet bunch of times that the students all put in how much time they put in. When it's coming into the sheet, the sheet doesn't really know, is this a time or a date? So you select the cell range. By the way, I know everybody has duration. So that's been pushed out long, long ago. So I selected my entire cell range, I'm going to go ahead and do the equation as well. OK. So I selected that. I'm going to go up to Format. Remember, we're making it look like something. It's a format. I'm going to go to Number. That's what throws people off usually. They're looking for the duration or the time right there. But you need to go to Format, Number and then Duration. Should be right in line underneath Date and Time. OK. All righty. So simple sums. Very cool. You can do all kinds-- there are lots of complicated sums. And I'm sure there's some of you out there-- come on, let's get to the complicated stuff. No. I'm not going to do it. All right. We got some stuff coming up. It's going to be more complicated. So I've got you covered. But simple sum, start simply, learn it, live it, use it, and then go-- OK. Now, I want to learn how to-- go to your expressions. I want to learn how to count. Or I want to learn what a minimum means. OK. If you want to learn what a function means, when you go to the-- Thank you, Jean-- Expressions tool, scroll all the way down and click on Learn More. This is not in the tips slides. You need to remember this. You're going to go to Expressions Learn More. Another website basically is going to open up for you. And here is the entire function list. Some of these, you're going to go-- oh my god. But some of them are really simple. All right. You can also narrow these down by, let's say, you want to do something to text. Or you want to do something mathematical. You want to filter. You can actually choose any one of these and all of those text functions will come up for you. If you want to type in keyword. You could type in a keyword. You didn't find sum? You're kidding me. It's a mathematical function. Learn something new every day. There we go. So I had too many filters here. So I just leave it as neutral by, then I typed in sum and here we go. It's down here somewhere. Boom. OK. So there. And thank you, again. All right. So here I'm going to go ahead and close that sheet. And then I'm going to go back to the slides. You can stay on the sheet if you want. OK. And I've got to make to make it smaller. I can't see. All right. Now, next one. (DESCRIPTION) Tip 3, Split Data (SPEECH) We're going to split data. So how many times have you had students may be on a form and they're selecting, or they're just typing in their full name? And that's probably easiest for your students. OK. Or you have them typing two things in a cell, and you really need them to be in two different cells when it comes to the sheet. The name is a good example. Or a name is good example. So I'm going back to the sheet and I'm clicking on the Split tab. (DESCRIPTION) Third tab from the left. (SPEECH) OK. Now, here are a bunch of names. OK. There's also some date and times. Well, let's do the names first. OK. Here's the thing, when you do a split, you must, must, must, must, must Make sure that the column next door has nothing in it. Because when you do a split, everything that is coming into this column, the next column, it all goes to the right. To the right, to the right. It's splitting to the right. OK. If there's anything in that column, it gets overwritten. So make sure you have a blank column before you split. Let's say that there was something in this column, and I don't want to get rid of that data. (DESCRIPTION) Adds a few random letters in the column. (SPEECH) What I would do is click-- right click on the column, right click on the column, and then I just add one to the left. (DESCRIPTION) Insert 1 left. (SPEECH) Boom. (DESCRIPTION) The column moves to the right and a new B column appears. (SPEECH) I didn't screw up my data. I didn't get anything. I didn't mess up anything. But now I have a blank column to the right of the text that I'm going to separate. Why do you want to separate first and last names? I'm so glad you asked that question. How the heck are we going to sort by last name? Do you have the answer? There is no answer. Because you can't. I can't sort by last name if they're all in the same cell, right? So I need to split that up. I need the last names to go into their own cell. Because right now, if I did a sort, it would alphabetized or in order or reverse order, doesn't matter, but it would be by the first name. So here we go. We're going to select ourselves. So I'm selecting A2. I'm scrolling down to A21. I don't have to do them all. I could just do a couple of them. All right. But I'm going to do the whole set here. So I've got my cell range selected. I'm going to go-- now, we're doing something to the data. We're not formatting it. We're not making it look different. We're actually doing something to the data. So if you can remember that, you will remember where split is. So click on Data, and on here you're going to see-- Split text to columns. OK. I'm going to click that. Now, it's going to give me the option-- there's a little tiny box. Unless you can see it on yours or on mine. It says-- Detect automatically. Google will detect commas, semicolons, periods, and spaces automatically. And it will split on those. Now, let's say, maybe for whatever reason, I want to split some text that has a-- what is that called-- the slash. The right slash. OK. Or maybe it has-- maybe I want to split it based on the parentheses. OK. So I can customize. I can select what I want this text to be split on. You'll know when it happens. All right. Right now, though, the common thing between all of these names is a space, right? So I'm just going to select space. Boom. (DESCRIPTION) Last names move over to column B leaving first names in column A. (SPEECH) Now, Trish, when I go up to my column, on my B column, and I click the arrow on it, and I sort my sheet A to Z. Bada bing, bada boom. We have our last names alphabetized. Yey! (DESCRIPTION) Names rearrange. (SPEECH) So you might want to do that. You might want to do that. I could also put them together. That's a whole other function, it's called concatenate. We're not going to cover that. Look it up. Look it up. Go to the Functions list here. And you might even see it here. No, we don't see it here. So learn more. Learn more by concatenation. All right. Now I'm going to scroll down on this Split tab and on A22, I see there are some dates and times. Now, maybe I want the times to be in their own column. Did the first name stay with the last name when we sorted? absolutely. Oh my goodness. Let me show you something else. Now, oh, it might not have. Oops. You know what I forgot to show you, the freeze. I did forget to show you that. All right. So everybody look back over here because this is kind of-- you've got to-- it's visualizing. When I hover my cursor right above the number 1, it's an arrow until it turns into a hand, right about there. When it's a hand, I can grab it and pull it down. (DESCRIPTION) Pulls it down over the top row that says Name. (SPEECH) OK. What that does is that freezes this column right here. I just made it blue so it stands out a little bit. So this is frozen. OK. Now, I could freeze, I could go even further down to number 2, down to number 3, or whatever. But I want it to stay at number 1, because that's my header row. That's very important. So when you're sorting, you have to freeze the row above what you're going to sort. OK. So let's just break the word last. (DESCRIPTION) Adds Last as header to column B. (SPEECH) All righty. Now, when you sort the first names, it'll stay with the last names. Let's sort by first name first. OK. I'm going to go to A column. I'm hitting the arrow next to-- this in line with the A. And them I'm going to sort sheet A to Z. Boom. And all the numbers came up first. Very cool. All right. And now, I'm going to go to the B column. And I'm going to sort sheet A to Z. Last name. They all stay with their respective cousins next door. OK. So yes, when you sort-- as long as you have that header row frozen, you're OK. All right. Now, let's move down-- because again, we're splitting. OK. I'm going to select the range that has dates. OK. I am going to go to the Data. I will repeat the freeze in just a sec. I'm going to go to Data. And then I'm going to-- Split text to column. And it does it automatically. I didn't go-- I didn't select the separator. All right. I let it do it. OK. So I wanted to show you that. Now, we have the dates and the times in two separate columns. You don't have to go in and select space, or column, or whatever. You can do it as you wish. Now I'm, going to select the 4:00 PM. There's an 8:00 in here as well. And I'm going to separate. Again, Data, SPLIT text to columns. And I want to show you Custom. So the custom separator now is going to be-- oh, it already did it. The son of a guns. They did it already. I wanted to show you this. The AM, PM, it already split it. So Google is so smart, it just went boom. What happens if the name is first, middle, and last? Hang on a sec. We're going to repeat freeze-- that's very important. Because we have Esmi who has-- and I'm going to kick him to the curb. Ryan, if you're here, I love you dearly. Ryan de La Vega. He has four, no, three-- de La Vega, it's three names in his last name. So I'm going to show you what happens when I have to do Ryan. All right. I'm going to get rid of this stuff here. So I can show you Ryan de La Vega. (DESCRIPTION) Deletes the random letters in column C. (SPEECH) And I'm also going to get rid of my dates, because I don't need those. All right. So let's say, we have Ryan de La Vega. OK. So I want to split this. Yeah. I'm going to go to Data. I'm going to-- Split text to columns. And I'm a go-- no, I don't want to do that yet. I don't want to do that yet. Because I need to make sure that there are one, two, three, four spaces, four columns, just for safety's sake, next to his name. Because if there's anything right here, watch what happens when I split Ryan's name. (DESCRIPTION) In column D she adds ddddd. (SPEECH) I'm going to go to Data, Split text to columns. I'm going to tell it the space and whatever I typed right here is gone. (DESCRIPTION) de la Vega spreads over three columns, over writing the D's. (SPEECH) But it did split his name up. Right? And now, because it split his name up, I have to put his name back together. So what I usually do with someone like this-- I'm going to undo what I just did. What I usually do is I look through the names really quick. And I go-- OK, I've got somebody with a space, more than one space between their names. And I'll do something like this, where I take out the spaces between de La Vega OK. (DESCRIPTION) Runs de la Vega together as one word. (SPEECH) And then I'll split. (DESCRIPTION) Data, Split text to columns, Separater, Space. De la Vega appears as one word. (SPEECH) And get rid of that. (DESCRIPTION) Deletes the row of D's. (SPEECH) And then I'll put his spaces back in. So that's what you need to be aware of. If it's first, middle, and last then you're taking up three columns. Just make sure that that third column doesn't have any data in it that you don't want erased. OK. All right. I think I'm coming back to the sheets. We've just split the data. So that's-- again, all of this is on the handout. All right. Or on the slide stack, so you'll be able to see it. If you want a worksheet, then you would make a copy of it and you can go ahead and do it to it. So here's we selected the range. The different-- we can customize. How do you get rid of the extra space? If it's at the end, there's a trim white space on the data. So go to your Data tool. And you could trim white space. If there is an extra space in a name, you might have to actually go in and do it. You might have to look at and-- oh, there's an extra space here. I have to delete that. So every once in a while, Google's good, so is excel. But it can't do everything. Sometimes you're going to do it ourselves. (DESCRIPTION) Next slide, Shortcuts. (SPEECH) Oh, shortcuts. It's really cool. I'm going to go back to the sheets. I'm going to go to the format in Quick Keys tab. (DESCRIPTION) Next tab to the right. (SPEECH) And control semicolon, control shift semicolon, and control shift alt semicolon. Sometimes you want to put the date in. You don't have to want to type, or you don't want to type the darn thing in, right? So there are some quick keys here. There in the blue, the light blue. All right. I already did this yesterday. OK. In the yellow area, I'm going to do it again. I'm going to hold down my control key, and hit the semicolon. (DESCRIPTION) Date appears (SPEECH) Oh, look at that. That's so cool. And then I'm going to go control shift semicolon. (DESCRIPTION) Time appears. (SPEECH) Oh, my god. It's 10:00 already. Done. OK. And then control shift alt semicolon. By the way, the word control is command on a map. OK. (DESCRIPTION) Date and time together appear. (SPEECH) So there we go. Easy way to put in the date-- control shift or control semicolon. I don't know that you'd ever want to put in the exact time like this, but again, if you want the time to be a different format, you would select the cell. Go to format. Go to a number, and then you can have more time formats. You don't have to have it down to the last seconds. So we'd go to more formats. More date and time format semi-- go down, down, down. So I went to format. I went to number. I went to more formats, and then more date and time format. And when you select that, you get this really long list of different time formats. So here's the one that I would want just the minute for the hour in the minute, OK. And then I apply, boom. That is done. It's also 24 hour time, so I'd have to go back, and change it if I wanted to. Google is used throughout the land. OK. And most of the land uses military time. We call it military time. They call it European time. You know, share the love. OK. All right. (DESCRIPTION) Moves down to the next blue highlighted section of the sheet. Add 4 months without typing. (SPEECH) January, February, March. Now the next three or four cells, do I have to type April, May, June, July? No. I'm selecting January. I'm holding down the Shift key. I'm going to select February, and you'll notice that are all being selected. The cell range is being selected. OK. So I've got January, February, March selected by holding down the Shift key. I'm taking my hand off the Shift key. I'm going to take my cursor over to the square at the bottom right hand corner of where March is at. I'm going up. There it is. There's the crosshair. Turns into a little a big plus sign. It's a crosshair. I click on that square, and drag it down. And the months fill in. (DESCRIPTION) Abbreviations for April, May, June, and July appear in the next four cells. (SPEECH) OK. Now do the same thing for 2, 4, 6. I'm going to select 2, then 4, then 6. Do you notice a pattern? 2, 4, 6. You can do it with two data, yes. I just put in three, because it's a safety for me. So I've got 2, 4, 6. What with the next number be? 12? No. Google knows though. Watch. (DESCRIPTION) 8, 10, 12, and 14 appear in the next cells. (SPEECH) There it is. If you have any kind of a pattern setup, you can select that pattern. OK. So if you've got a pattern of four things set up, select the pattern of four, and then drag it down. And it should maintain that pattern for you. We're going to rotate the text now. Most people don't know about this. It adds a little space to your column, but it's really kind of cool. So I'm going to select January through July, because I had those dates. If you only have January through May, that's fine. Just select your months. OK. Up next to the link tool to the right of the linked tool, you're going to see an A that looks like it's out of whack. (DESCRIPTION) To the left of link. (SPEECH) It's text rotation. If you click on the arrow next to it, look at all of these different options you have. So you can make your text go in any direction you want. I'm going to select this one, the one that's kind of a tilt up. Boom. (DESCRIPTION) All of the months angle to the top right. (SPEECH) Now I can readjust my column. It can be a little bit smaller maybe. Yeah. And it looks a little more like I know what I'm doing. Like wow, how did you do that? It's just the texture rotation. There's lots of different rotations that you can try including the up/down, the vertical, rotate up, rotate down. And you can also add a-- if you want your own degree of rotation, you can also select that. So that's pretty cool. (DESCRIPTION) Scrolls down to the next highlighted section. Format the paragraph below. 1. Align right. 2. Align top. 3. Wrap text. 4. Merge three columns. (SPEECH) If you have a long paragraph. If your students have on their form they wrote this really long paragraph. And when they do that, all of the text goes into one cell. (DESCRIPTION) Generic Lorem ipsum Latin paragraph extends over several columns. (SPEECH) This cell right here, lorem ipsum dolor. That's one cell. It's not going across the page. Well it is going across the page, because I'm allowing it to. Right now go up to the top of your menu. You're going to see text wrapping. It's next to that a tool that we just discovered. (DESCRIPTION) To the left of A. (SPEECH) OK. The text wrap. There's an arrow next to the text wrap. OK. And then, there are some options here. One of which is clip, so that will stick everything in its cell without allowing it to wrap or extend past. (DESCRIPTION) Only the first words are visible in the first cell. (SPEECH) If you want it to wrap, and it is a long paragraph, you can do that. But you notice, it becomes a really long cell with its own little scroll bar. (DESCRIPTION) Scrolls up and down through long cell. (SPEECH) OK. So you might want to clip it. You might want to wrap it. It's up to you. But that's how you can make a cell. All of the data stay within a cell, and still be able to read it. Because by letting it wrap, instead of extending past. If you have something in this cell, (DESCRIPTION) Cell to the right. (SPEECH) I'll just type something really quick. And we allowed it to extend, it won't. Because there's something there. It will only extend if the cell next to it is blank. OK. So we've learned, and we've already gone over enlightenment. Right? You also have vertical alignment. So you can select that arrow, and maybe put it in the middle. Sometimes, you want things to line up differently, because one cell is bigger than the other. So there's horizontal alignment, and there's vertical alignment along with the text wrap. OK. Can you enter-- can you put in a picture? Sure. So you go to insert. You go to image, and you can put image in the cell. Or image over cell, so that means you can move it around. OK. They want you to upload, or give it by a Google image search. Or maybe you have an image on your drive. So you decide where the images, and then, yes, you can go to the Insert menu to insert. So we've done the insert already. The cell contains web entries is a way to shorten the URL. If the cell contains website entries is the way to shorten your-- no, Bruno, there's not. You have to allow it to stay. When you have a website listed in a cell, it actually is a formula. And that's why it's so long, and it looks really weird. But you have to leave it that way. OK. All right. I think I'm going to come back to the sheet. OK. So we've done all of these are these right here. You've also got some different quick keys. Use them on your own. So quickly to format as a decimal. Quickly to format as time is control shift 2. Control shift 3 is the date, currency, percentage, and exponent. So all of those are quick keys that you can use. You don't have to go to format, and then fine how to do bla, bla, bla. Can you do mathematical operations in Excel, as to present a math lesson? Well that would be excel, but I'm sure you can do it in sheets. Yes, Maria. OK. Yes, you can. It's a matter of fact, I'm going to be asking one of our SMEs to do a little more in-depth on math that would be very keen, because he's done a workshop like this before. Only he's done it face to face. So we'll see if we can get him in to do something like that for you. (DESCRIPTION) Next slide, Conditional Format, Simple. (SPEECH) OK, conditional formatting. You're going to love this. I'm going to go back to the sheet. OK. I'm going to go to-- oh, we should do charts first. We're going to get conditional formatting. So skip the charts tab, and I'm going to go conditional format. (DESCRIPTION) Data in the first column, Date. Second column, Number. The first and sixth cell in that column are orange, 3 through 5 are green. (SPEECH) OK. These cells up at the top through B6 have already been formatted. OK. They've already been conditionally formatted. All right. Now what we're going to do is first, I'm going to show you how to put conditional formatting on a cell. All right. And then we're going to-- I'm going to show you how to extend it to a range. So we're going to add formatting, or we're going to do conditional formatting to this celled everything, that's dated February. Everything is dated February, so on I'm selecting A7 through B11. A7 through B11, so select those cells. Now we're going to go to conditional formatting is in format. So I'm going to click on format, and then down towards the bottom. I'm going to click on conditional formatting. To my left, a little window is going to open up. And Google thinks it knows what I want. (DESCRIPTION) Box opens on right. (SPEECH) So it puts its own little conditional formatting in there for me. OK. Off to the left, I see that it has decided that since I selected the range A7 through B11, which I can change at any time if I want. They've decided that if the cell is not empty, then it's going to turn green. If the cell is not empty, then it's going to turn green. This is an if then. OK, so you need to remember that. You're applying to these cells if this is true, then this happens. I don't want this. I want something different. I want to follow the instructions here, add formatting to these cells to color all cells read that are blank. OK. Here we go. So it's the if part is not empty right now, I selected. But if I select the arrow next to is not empty, I can select is empty. And then I can change the color to instead of being green, it could be red. OK. And there we go. (DESCRIPTION) Empty cells turn red. (SPEECH) We've just formatted conditional format. So when I go to February 2nd, and I add a number, what do you think's going to happen? (DESCRIPTION) Types 45 (SPEECH) Look at that. (DESCRIPTION) Cell turns white (SPEECH) So it's just the blank cells that are reddish in color. OK. Now here we go. Now what I want you to do is go back up to where you see the orange and the green. OK. So you've got January through January 5th. I'm just going to click in one of those cells. (DESCRIPTION) Closes open conditional format rules box. (SPEECH) First, I'm going to get rid of that. I'm going to click in the cell the top one the 30. OK. And I'm going to go to format, and I'm going to go to conditional formatting. Here are the two rules for this formatting. So I'm going to click on the green one. Value is greater than or equal to 50. So if the value is greater than or equal to 50, then it's green. All right. If the other one-- let me go back. (DESCRIPTION) Clicks Done at the bottom of the box. (SPEECH) Is if it's less than 50, then it's orange. So think of this is as far as maybe scores. OK. You might want the ones that are really having problems. The scores are low. You might want those to pop out at you. All right. So let's-- I'm going to customize this a little more, and I'm going to make it. So we've got the range, B1 to B6. Right? And we want it less than-- no, I want all the-- I want the grades that aren't so good to come out. So my point of no return is let's say 75. OK. And I'm going to make it-- instead of orange, I going to make it red, because I really want to pop. (DESCRIPTION) The orange cells turn red. (SPEECH) And we have a 55 there. There's still green, so I got to figure that one out. B1 to B6. Yeah. All right. We're going to say done, and we're going to go to the 55, and figure out what's going on here. Because that-- oh, here we go. Because the formula on the green one is greater than or equal to. So this is why I want to show you this. So we're layering. We are layering conditional formatting. OK. I want everything that's less than 75 to be red, but the rule that says value is greater than or equal to 50 is first. So this is going to be the overriding rule. So do I have to delete my rules, and make new ones? No. All I have to do is drag this rule to first place. (DESCRIPTION) Drags the second rule above. (SPEECH) And you notice, as soon as I did that, the 55 over here turned red. OK. So again, you got a form. You've got a quiz. You want to be-- I'm a very visual person. So I could look through the numbers, or I could arrange them, or I could sort them I guess. But I want to-- I want boom. I wanted to pop out at me. OK. So I do conditional formatting on forms when I have quizzes submitted. That way I can see-- I need to talk to-- I need to talk to Anthony. All right. Because Anthony is-- man, he's just not getting it for some reason. Anthony, I know that's not the case, but I had to think of a name. All right. So we're going to add-- here's another cool thing that you can do on a form, so maybe you want to know whether or not you've done something. Or you want people to tell you whether or not you've done something. So I'm going to select-- I'm going to put a checkbox in here. Most people don't know that there are checkboxes in Google forms. So I selected a cell. I'm going to go to insert, and I'm going to-- oh, there it is, checkbox. (DESCRIPTION) Scrolls down. (SPEECH) Click. OK. I got a checkbox right here. (DESCRIPTION) Checkbox in center of cell. (SPEECH) So I've selected a cell. This cell has the square. Let me see if I can zoom in a little bit. All right. Here's the square. Right here. And as I can-- If there's a little hand, I don't want a little hand. There is my cursor arrow. No, I don't want that either. I have to find that sweet spot. There it is, you have to find the sweet spot. (DESCRIPTION) Cursor turns to crosshair. (SPEECH) So as soon as you get right in the middle of that square, it's your cursor should turn into a crosshair. If you're on a tablet, good luck with that. And I'm not joking, tablets are really hard to manipulate the cursor into the hand of the crosshair. So if you're on a laptop or a desktop, you shouldn't have any problems with it, especially if you're using a mouse. Trackpad? No. You know, if you're really good with Trackpad, it should be OK. All right. So here's my crosshair. Right there. I'm going to drag this down. Why am I dragging you down? (DESCRIPTION) Checkboxes appear in all the following cells. (SPEECH) So I don't have to go insert, insert, insert. OK. So I'm just dragging down the checkbox. OK. Now here's what's really cool, and I think this is on the handout. It's the next slide here. I've gone back to the handout. Here we go. Yeah. So going back out, so I can see the whole thing. (DESCRIPTION) Conditional Format, Advanced. (SPEECH) A custom rule that you can add is that, if the checkbox is checked-- if there's a checkbox system, you have to click on the cell, so that there is a square. Click in the cell, and then there's a square. OK. Custom rule. If the column is true, if the checkbox is true, then the entire row becomes a color. We have actually done this on a worksheet that I have put together for all of these webinars. Our staff secretary is going in, and she's marking attendance. There is a checkbox for that. Then she marks that the certificates were sent. There a there's a checkbox for that. And then she sends the evaluations to the presenters. There's a checkbox for that. So every time she hits a checkbox, there's a new color introduced into the sheet. OK. Now yours might not get this complicated, and I don't recommend you get this complicated. Just try it out for one or two. Right? Start simple. So let's-- I'm going to go back to the sheet tips. Here are my checkboxes. OK. I'm going to select a couple of them. So as we're creating this, you get the idea of how it actually works. Now I'm going to select my range. (DESCRIPTION) Highlights a section of three columns including check boxes. (SPEECH) I selected a couple of checkboxes. I've selected the range. We've got March through June. I'm going to go to format, conditional formatting. This is where you-- and again, Google thinks it knows what I want. No, it doesn't. What we're going to do is we're going to go-- we're going to click on where it said is empty, and we're going to go all the way down to custom formula. OK. Now we've got the range selected as A12 through C31. That's what this rule is applying itself to. OK. If-- here we go. Now I need to explain something. When you put a dollar sign in front of a row or a column, it's gluing itself. So dollar sign is glue. So I'm going to put a dollar sign, and I'm going to put C, because I want the glue to be stuck on the C column. Because I want the sheet to understand that the C column has the checkbox. This is the if. OK. So if C12 equals true, is it true or true? Yeah. I had to look at something first. OK. So if it is true, then it's going to be orange. (DESCRIPTION) Clicks color fill button below and selects orange. (SPEECH) Now look what happened. (DESCRIPTION) Four rows are colored orange. (SPEECH) All of the ones that were checked followed the rule. OK. All of the ones that were checked follow the rules. So it's equals dollar sign is the glue, because my column C has my checkboxes. So dollar sign C12 equals true, and you have to have those equal signs in the exact right position. Equals dollar sign, I keep on saying a question mark. Equals dollar sign C12 equals true. Done. By the way, you also have the option to make it bold. OK. Or make the font a different color, so it stands out more maybe. Or maybe it's greyed out, because it's already done, right? To test it, you absolutely want to go through, and click a couple of those checkboxes to make sure they follow the rule that you just set. (DESCRIPTION) Clicks and unclicks check boxes and they change as she goes. (SPEECH) And again, if you add more than one rule, then just make sure that they are in the right order. Whatever's on the top leads the pack, that's number one. OK. Why C12 in particular? Because that's where my first checkboxes. Amy, I added a bunch of different tutorials on the same thing, on the same tab I should say. So C12 is actually where this tutorial as far as conditional formatting on a checkbox started. OK. If I wanted to-- I can-- well let's do that. If you followed along, let's double click or-- let me start over, because I know some of you clicked off of it. All right. So I'm going to click in a cell that has the conditional formatting. Set. OK, I'm going to go to format, conditional formatting. Right? Here's my rule for A12 through C31. OK. I'm going to click that open, and then I'm going to change the range. Because maybe you're right, hey, maybe I didn't want 12 to be my start. Maybe I wanted 20 to be my start. A20 through C31. So I'm going to do A20 through C31, and say done. (DESCRIPTION) Higher rows no longer orange. (SPEECH) And now you see that these that are checked are not part of the range. I remember, I selected the range. (DESCRIPTION) Highlights the range. (SPEECH) I selected the range, and then the C12 is the first checkbox in the range that I selected. Right? For the first formula, not right now. I see 20 is, so I could go here, and I could change this C12 to C20. The results will be a little different. Boom. And as I checked anything within the C20 range, you'll see the conditional formatting change. That probably confused some of you. All right. So the formula, it's a custom formula, Maria. So you don't have to stick with is empty, is not empty, text contains, text could not contain. What if you want something that's more of an if? Like if this is checked, then it becomes this. Or if that is this date, it's this. And then I add another rule. If that is that date, then it is this. So just think if then, if then. And if your if then in your head, don't match one of these if in rules that Google is already set for you. Then customize it. Create your own if then. OK. That's why I did that. Let's go back to charts. I'm going-- I'm just going to click on the Charts tab. I'm not even going to go back to the sheets. I'm going to get rid of this, because that was just a test. All right. Now, we had someone ask about math, right? So here is a formula 2x plus 3 equals y. If y equals 6, then here's the answer, right? Now, what if I wanted to chart that out? What if I wanted to graph that out? Wouldn't that be very cool? You can do that. So I'm going to select the range, which is the y-coordinates along with the x-coordinates, right? (DESCRIPTION) B and C columns. (SPEECH) I'm going to go to Insert. And before I hit Chart, I need to explain something. Google is looking at your information-- your range-- your cell ranges right now. And it's going, oh, I know what she wants. Boom! Here you go. When Google does that, don't get angry at it if it got the wrong chart for you, OK? You can change the chart. Google's trying to be helpful. All right, and another way it can be helpful, after you select your range, I'm not even going to go to Insert. I'm going to go way down at the bottom of my screen, and you're going to see some things. (DESCRIPTION) Very bottom lower right. (SPEECH) One of them, for me, says min equals negative 6. If I click on that, Google is giving me some options for formulas right now. (DESCRIPTION) Sum, Average, Min, Max, Count. (SPEECH) I don't want any of those. (DESCRIPTION) Small square with cross in the center. (SPEECH) It's also giving me an Explore option. So this little tool looks like a little plus sign in a comment box. (DESCRIPTION) Clicks explore, box opens on left side of screen. (SPEECH) It's giving me some options as far as formulas are concerned. Not charts, but formulas. So that's-- oh, and look at this. And then it's-- because this looked at the data, and I gave it a little time, my Wi-Fi's a little slow. Here we go. (DESCRIPTION) Several charts appear under explore. (SPEECH) Look at this. It's giving me some chart information now. It's also plotting points. Yeah, that's cool. I didn't have to think about anything. Google did all the thinking for me. All right, so it's giving me a bunch of different ideas here that maybe I didn't even think about. If this is the one I want, I click on it. I click on it. I drag it over. And there it is. There's my chart. If you do that using the Explorer tool, you always have the option to change. You always-- whether you use Explorer tool or not. As soon as you hit an Insert chart, in it becomes this or anything, you can double-click on the chart. And then you have all of the chart options on the right-hand side. (DESCRIPTION) Chart editor opens to the right. (SPEECH) You can get lost in this. And, Miss Trish, I know you're out there. I know you're watching because I helped you do this yesterday. And I bet you spent at least an hour playing with your chart yesterday. [LAUGHS] So if you did, check in the chat. So you've got all kinds of really cool things that you can do here. You can manipulate the series. So right here, if I don't like the color blue, I want it red. I can make it red, OK? (DESCRIPTION) Line on graph changes color. (SPEECH) I can do all kinds of something. Like I said, you can get lost in here. You can spend days on some of these things, especially if you have something like a pie chart. So let's do that. So I'm just going to add 1, 3, 5, and then 2, 4, 6. And then I'm going to see if Google sees a pattern. Because I'm just playing right now. (DESCRIPTION) Pulls down the bottom of the last cell. (SPEECH) Let's see what it does. Oh, look at that. It does see a pattern. (DESCRIPTION) Autofills next several cells. (SPEECH) All right, so I'm going to select these numbers. And if-- hey, I want to do that. I just typed in 1, 3, 5, and then 2, 4, 6. So 135 and then 246. Can the chart be changed to make x on the horizontal? Absolutely, you have to go to the chart. That's what I showing you that you double-click on the chart. And then you would customize it. So you've got Chart Axis, or Axes-- Vertical Axes, Horizontal Axes. You control your own chart. Back to this other one. Oh, by the way, the chart-- if you don't like it that big, you click on it, grab a handle, make a smaller. OK, lots of stuff you can do. (DESCRIPTION) Pushes the right lower corner of the chart in and it shrinks. (SPEECH) Back to the numbers. 135, 246-- I just selected those. And then I dragged it down. And it saw a pattern. So it did it for me. All right, I'm going to select these numbers. I'm going to actually use the Insert tool this time instead of Explorer. I'm going to go to Chart. And, oh, that's a nice chart. But I don't want that one. I don't want this. I wanted something different. OK, so it's going to keep you-- or it should open on the Setup tab. If it doesn't, click Setup, go to the Arrow tool next door. Right now, it's selected Line Chart. I don't want a line chart. I don't like the line chart. OK, fine. We're going to select a pie. Boom. (DESCRIPTION) A colorful line chart appears. (SPEECH) And there we have a pie chart. Woo-hoo. I'm going to move this down just a little bit so I can work with it. At this point, and look, folks, you control this, all right? If the data range that you selected was incorrect, you can change it right now using this, or you can start back from beginning if you want. But I'm going to go to Customize. And notice my Customization options have changed a bit, OK. Here's the chart Style. Here's my chart Border. If I want to make it different, I can. It's 3D right now. Ooh, I don't have to have it 3D. I can make it flat, (DESCRIPTION) Pie chart straightens. (SPEECH) OK? I'm going to click the Arrow tool on Chart Style. I'm going to go to Pie Chart. There's a Donut Hole option. So I'm going to select that, go to 25-- ooh, look at the middle. (DESCRIPTION) A hole appears in the middle of the 3D pie chart. (SPEECH) Isn't that nice? If I don't like 25%, if that's a little too much for me, I can actually make it 12%, OK? (DESCRIPTION) Size of the hole in the center decreases. (SPEECH) So it's up to you. You control this. You don't have to use these preselected percentages that they have decided for you. The Slice Labels-- I could put that in if I wanted. It's up to you-- your chart. The different pie slices-- right now, it's got the one is blue, OK? If I click here, I'm clicking on the pie. And then the colors are changing. If I don't like this blue, I can change it to anything I want. And I can also make it pop out. (DESCRIPTION) Selected slice of pie pops out of the chart. (SPEECH) Ooh, look at that. So if I'm doing some data and my administrator wants to know, OK, how is the GED going? How is the GED testing going, or how is that testing going or their citizenship test, whatever? And you want something to pop out at them. Do a pie chart. Take that chunk out-- really cool. Also, on this sheet, because we're running out of time folks, we have Filter and Pivot. (DESCRIPTION) Moves to the tab to the right of Conditional format. (SPEECH) So I'm going to show you how to do a Filter really quick. Look up Filter on the Google Help. OK, so let's say we have a bunch of scores, OK? And I could sort this. And it's already sorted by Andrew. So I've got Andrew's. But I want to see what people are doing per test maybe, OK. So I'm going to click in the D column, OK. And then I'm going to go over here to this what it looks like a little funnel. It's actually a filter tool. So I'm going to click the arrow next to it. And I'm going to create a new filter. And because I selected the D column that says, Test Title, it's allowing me to filter this column. Right next to Test Title, I do see that little funnel thing again. I'm going to click on it. And I want to know-- I'm going to deselect all of this. Because, right now, it's finding everything within that column, right? And I just want to know how things are going on the Language Arts. So I select that. And I hit OK. (DESCRIPTION) Only Language Arts visible. (SPEECH) And here are the people that took the Language Arts quiz, OK. It's a filter. I didn't have to do any sorting. And if I wanted to, I could actually put a total over here doing the points-- getting an average or some of the points. So let me go over that really quickly again. And I'm going to turn this off. I went to-- any one of these columns, you can filter. I click in the column, I go to the Filter tool, I create a new Filter View, and then right now, it's finding everything that's in that column. So in the Language Arts, Mathematical Reasoning, Science, Social Studies, right? If I wanted to see one or maybe even two-- I want to see Science and Social Studies. I'm going to say OK because those two things are selected. And there we have all the Science and the Social Studies. I can sort from here. (DESCRIPTION) Sort Sheet A to Z.. All the categories reappear. (SPEECH) Oops, no I can't. What happened? Oh, you made a liar of me. [LAUGHS] So we don't want the blanks. We just want Science and Social Studies. We'll say, OK. There we go. (DESCRIPTION) Sorts only Science and Social Studies. (SPEECH) It blipped on me, OK. From here, you can also right now this above the filter, in the black bar, yes, you can save. Yes, Jacqueline, that's what I'm covering now. So this is called Filter 3. So I'm going to call this Test Type Filter, OK. And then that way-- because I do. I have several other filters here. Here's Filter 1. This is on Student Name. So right now, Howard was the last time I filtered. I can go in and select Andrews and say, OK. So now, I just see that test. So if I wanted to name Filter 1 and I should, say, by our student filter Bill Kerr-- not filler, OK. There's also a really cool thing that does even more called the Pivot. (DESCRIPTION) Moves to next tab on the right. (SPEECH) I am not going to show that because we don't have the time. Import Range, though, is important. So on the Pivot-- just look up Pivot Tables. Those of you that know what they are, you rock. I haven't quite fully grasped it myself. But I know you need a lot of data. And it does a lot of stuff, OK. (DESCRIPTION) Moves to last tab on the right, Import Range. (SPEECH) So right here, let's say you have a form. And you have a live form. And data's coming into it daily. You can sort it. But as soon as someone submits something, it blows your sort, right? So what you do is, you have the data from the form, or you actually create another sheet. You create another sheet in the worksheet. Because your form's going into a sheet, right? So let's pretend that where were we at? The four-- here we go-- filter. So I'm going to turn this filter off. Let's pretend that this was a form, all right? All the data's coming in this form, all right? (DESCRIPTION) Switches back to Filter tab. (SPEECH) I want to be able to sort this. And I want to be able to do other things with it as it's live. I'm not going to touch the form. I'm going to add another sheet (DESCRIPTION) Clicks the plus sign and the bottom left corner. (SPEECH) just like that, OK. (DESCRIPTION) A blank sheet opens. The tab is labeled Sheet 8. (SPEECH) And then I'm going to figure out, OK, what do you want from this sheet? I want this row of data. I want A2 through AE2, OK. So what you need to do is, you need to know this sheets name. This sheet's name is called Filter/Pivot, all right. Now, I'm going to go back to that blank sheet that I just created, all right. I'm going to click in the first cell, A1, and I'm going to type equals. Here we go. This is the magic. Equals Filter/Pivot. Oh, I should've capitalized it. Filter/Pivot!A2:B2. And it doesn't look like it's doing it. Why isn't it doing it? Oh, I need to look at my cheat sheet. Hang on. (DESCRIPTION) Switches back to Slide titled Grab Live Data. (SPEECH) Here it is. Equals sheet name-- yeah, so what's going on? I knew I did it right. Oh, it's because of that. Uh, OK, so the name-- don't put mathematical expressions in your name for your sheet. So I'm going to rename the sheet. I should have known better. So we're just going to call it FilterPivot. [LAUGHS] OK, all right, I'm going to go back to sheet 8. Sorry about that-- learning moment for all of us. So here we go. =Filter/Pivot!. That's very important. The equal sign, Filter Pivot is the name of the sheet. And then the cell range-- A2 to E2, OK. I'm going to hit Enter. And there we go. And that should only be A2-- shouldn't be in range. There's A2. Now, crosshairs-- dragging it over. Boom. (DESCRIPTION) Drags it to the right and the rest of the cells appear. (SPEECH) And as I look at the data that's coming in-- it's coming in from B2, C2, D2, and E2. I didn't have to type the formula in again. So we've got =Filter/Pivot. That's the name of the sheet-- !A2, right? Now, I'm going to drag down from that. Because I can get B2, C2, 3, 2, ba, ba, ba, boom-- way down. I can drag down from the B column. There we go. It's coming in. This will match FilterPivot. And if I don't want the Monday, Wednesday, Friday-- if I don't want the class time, or class name, or whatever-- I can take that column out. All I want maybe is the name of the student, the name of the test, and how many points it was. If that's all I want, then I can get rid-- that's all I bring in, right? So I can start my sheet with just =FilterPivot, C2, D2, and E2. (DESCRIPTION) Deletes first two columns. (SPEECH) If I want C2-- this is really going to blow your mind-- out of sync-- if I wanted to start it with D2, and then E2, and C2, you can rearrange this any way you want. You're bringing the data in from another sheet. You're not messing with a sheet. This isn't being changed at all. This sheet-- sheet 8, right now is being messed with. Now, that might have just gone poof-- blown your heads off the top of your-- yeah, blown the top of your heads off. So those of you that are a little more advanced, you're going to understand this, all right? You're going to have to practice, folks. That's what it takes. You need to practice. So just have a sheet with a bunch of junk in it-- numbers, letters, number, whatever you want. If you've copied that sheet that I have, I've kind of messed it up. So wait a little bit. I'll get it back to where it was when we first started, OK. And then make a copy of it. And then you've got some data there that you play with, all right? This import range-- you can actually bring data in from another sheet. So what you need to do is have the URL of the sheet. So on this sheet right here-- here is the URL. (DESCRIPTION) Highlights URL in address bar. (SPEECH) That's the URL of the sheet. Sheets this is really cool. Work your sheets. (DESCRIPTION) Sheet Tips. Bend the Data.