Google Sheets in 2019 | Flow Speeches | Skillshare

Google Sheets in 2019

Flow Speeches, Freelancer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
70 Lessons (2h 56m)
    • 1. Setup

      4:51
    • 2. Toolbar and Application Menu

      3:06
    • 3. Basic Vocabulary

      2:32
    • 4. Entering Data Into Cells

      1:20
    • 5. Selecting Multiple Cells

      3:15
    • 6. Selecting Rows and Columns

      1:46
    • 7. Adding Rows and Columns

      4:56
    • 8. Resizing Columns and Rows

      2:04
    • 9. Moving and Manipulating

      2:42
    • 10. Deleting and Clearing

      2:50
    • 11. Freezing Rows and Columns

      2:12
    • 12. Grouping

      2:11
    • 13. Grouping The other Way

      1:55
    • 14. Hiding

      1:08
    • 15. Merging Cells

      2:45
    • 16. Setup Part 2

      4:23
    • 17. Protecting Ranges

      3:49
    • 18. Range Select Syntax

      2:44
    • 19. Selecting Rows and Columns

      2:35
    • 20. Permissions and Protection

      1:37
    • 21. Special Paste and Two Sheets

      2:30
    • 22. Filling The 2nd Sheet

      1:49
    • 23. Renaming Sheets

      1:20
    • 24. Copying Duplicate Sheets

      4:02
    • 25. Formatting Numbeers

      5:05
    • 26. Custom Date and Time Formats

      3:02
    • 27. Custom Currencies

      1:28
    • 28. Right To Left Readings

      2:32
    • 29. Spreadsheet Settings

      1:28
    • 30. Inserting Images

      3:21
    • 31. Alt Text

      1:23
    • 32. Images Over Cells

      1:31
    • 33. Basic Math Functions

      6:13
    • 34. Refining Cells In Functions

      1:53
    • 35. Nesting Functions

      3:07
    • 36. Function Bar Sizing

      0:43
    • 37. Averages

      2:33
    • 38. Using Means or Medians

      1:24
    • 39. If Statements

      3:18
    • 40. Adding Ifs to Profiless

      1:35
    • 41. Comparing apples to Apples

      0:31
    • 42. Using The Function Button

      2:31
    • 43. Copy and Paste

      1:06
    • 44. Comparing Strings (intro)

      3:57
    • 45. NOT (the inverter)

      1:58
    • 46. Sorting

      4:08
    • 47. Filtering By Values

      2:17
    • 48. Saving Filter Views

      3:40
    • 49. Resorting Things Out

      0:47
    • 50. Dropdown List

      3:18
    • 51. Conditional Format

      3:18
    • 52. 7

      2:12
    • 53. Deleting Conditional Formats

      1:25
    • 54. Split Columns

      2:31
    • 55. Removing Duplicates

      2:40
    • 56. Trimming White Space

      1:12
    • 57. Checkboxes

      3:28
    • 58. Setup

      3:19
    • 59. Referencing Across Sheets

      3:08
    • 60. Inserting Objects

      3:29
    • 61. Styles

      1:47
    • 62. Titles

      2:01
    • 63. Gridlines

      1:21
    • 64. Column Colors

      1:09
    • 65. Loading Or P&L

      1:31
    • 66. Adding A Trendline

      5:22
    • 67. Reversing The Axis

      1:41
    • 68. MinMax and Gridlines

      1:27
    • 69. Adding Horizontal Axis

      1:25
    • 70. Publishing Charts

      2:20
17 students are watching this class

About This Class

In this course, you will learn how both the basics of using spreadsheets with Google Sheets and more advanced features such as using functions to create dynamic spreadsheets that reference data from across multiple sources, and generate visual representations of what you have with pie charts and trend lines.

By the time you're done with this course, you'll have full confidence in most things that require you to use Google Sheets. In situations that require something beyond your current skill level, you will have the tools needed to acquire theĀ  knowledge necessary without feeling overwhelmed.

Transcripts

1. Setup: we're going to start by getting access to Google sheets. Now you can access Google sheets both online and offline. In this case, I'm gonna be working with Google Sheets online, and I will have a section specifically dedicated to getting access to Google sheets while you're offline. Maybe you're gonna be out in the boonies, Who knows? But in that case, you may need it. But for now, there are two different ways you can get to Google Sheets. The first way is the freeway, and that is, if you have a new account, a Google account, then you can just get access to Google sheets. And the second way is to have a G suite account. Now, in this case, it happens that I have both, but I'm gonna walk you through getting access to Google sheets either way. First someone to start off with just a Google Sheets account, all you would have to do is make an account with Google, and if you don't already have one, they will walk you through the set up. But all I do is Google Google sheets. That's really all there is to it. And then from there it should be the first result you'll be brought to the page that I just showed you before. And then you'll be taken directly to Google sheets. Now, once again, if you don't have a Google account, you will need to make one. But it is a very easy set up. And then from there you get access to Google sheets, docks, slides and forms for free. So it's quite nice and you get a Gmail account to so a pretty sweet deal there, Um, in this case, I'm using Mozilla browser. You can use chrome, or you can use Mozilla, but in this case, all you have to do is click go to Google sheets and then you will be taken to our set up screen, which we're gonna be talking about here in a second. The second way that you can get access to your Google sheets is with a G suite account. Now, if you already have a G suite account, then all you will have to do is log in. Then I'll walk you through. We're not gonna walk you through signing up for a G suite account. It does cost money. Uh, just the lowest price. I believe is about $10. That's what I'm paying right now. But if you add on more users, the price will go up. If you're interested in getting a G suite account, you can I'm not going to convince you to buy. There are a couple of features that are gonna be available to you for Google sheets with a G suite account that are not going to otherwise be available to you. Ah, but these are just a couple of things like sharing and other things like that and getting specific insights. But aside from that, most things that I show you here in this course are gonna be available both via Google Sheets with your G suite account and just Google sheets with your regular Google account. But anyway, I'm going to be signing into my G suite account, so all you have to do is quick Sign in. Now I'm going to skip through the first part of the sign and where I enter my user name and my password and stuff never can be too careful. But you will be brought to your admin dashboard. And then from there, all you would have to do is go to the apse icon and you can see a list of all your different applications right here. Google sheets should be right here at the top. Then you click on it and then Google Sheets will open and a brand new browser. You'll notice that this screen looks identical to the screen that we just saw when we were on Google sheets with our Google account. The only difference is that in the right hand corner here, you can see I have G suite. But either way, now what I'm going to do is just make a brand new spreadsheet. So the way that we do that is, we click this blank button right here. Aside from that, I will make a couple of comments on some of the other features that you see right here. Um, we're gonna be walking through all of them right now, but we will talk about them so you can see we have our little slider right here, and that's just to get access to docks and slides and a couple of settings that will play with later on. But then we have our template gallery. You can see that there are a number of different 100% free templates that you get access to . And you can use these and then base your sheet off of these templates. We're gonna look at those again later on. But you also have some options to hide the templates if you want, and then down below, you can see some of the sheets that I worked with recently specifically yesterday, and you can look at who their own by. You can see also a grid view if you prefer, or a list view. And you can organize them according to when they were last opened, when they were last modified, or their title so you could go ABC if you want to. In this case, I'm gonna go back to the list view. And then this last one right here is to essentially pick a file so you can choose whatever you want a load in. But for now, we're just going to start off with a blank sheet. All I'm going to do is click this plus button right here, and this will load us in with our first Google sheets 2. Toolbar and Application Menu: what we'll do is kind of get our bearings now that we are in our first Google sheet. Like I said before, I'm assume no knowledge in this course that you have no knowledge of Google Sheets. And I promise that as we get deeper, things will get more advanced, Of course, but you can see right up here at the top, we have our title. Now, this is unnamed. So in order to change the title of your spreadsheet, you just quick inside of here and give it a name. So I'm just gonna call this demo sheet one with Spaces and then you hit Enter. And then after that, you can start if you want to consider important In this case, I don't consider this sheet important. Then, just below that, we have our application menu and we're gonna be exploring each of these different parts of the application menu. As we go on, you can see file here is going to allow us to do things like save, download and even import other sheets that it view insert. Like I said, there's a lot of individual things that we're gonna have to explore down below that we have what's called the toolbar, and the toolbar can be broken up into a number of different sections. The first part is going to be a very important part of the toolbar, the undo and redo commands. These commands are gonna be great if you accidentally make a mistake. Remember that your mistake is not set in stone. You can click these buttons and as a side note, if you hover over them, you see that we're getting this little tool tip that tells us what the keyboard shortcut is for these tool bars. Ah, buttons right here. So you could say control Z or command Z on the Mac to undo and control wire command. Why? To redo. We have our print button also a keyboard shortcut, and our paint format will talk about pasting things in. This is essentially going to allow us to specify just how much we want to paste in to our sell. It could be just the content, or it could also be the formatting that was associated with cell that was copied. Ah, the zoom. So if you have a little trouble seeing, you can definitely zoom in. I'm probably gonna zoom in quite a bit throughout this course just to make things clear. Then in this second section, we have essentially are formatting for our numbers and you can also increase and decrease things by decimal points as well. But this is gonna be mostly are formatting mostly number formatting, then here in this section that I'm kind of pacing through right here we have our font formatting. You can change your font style your size bold strike through. And we're going to see a lot of this in action here in a second. And then you have your text alignment right here. And so you can decide how you want your text to wrap. If you wanted to align vertically horizontally, you also have your cell merging and you can set some borders here as well. And then last but not least, these air kind of the other other section the miscellaneous, inserting links, comments, charts, functions, filters, things of the like are all going to go right there. So that is kind of a walk through of the basics of what we see here. Now let's get some new vocabulary 3. Basic Vocabulary: Now that we've taken a brief look at the toolbar and a glance at the application menu, I want to kind of give you some vocabulary. That way you can really understand what I'm saying throughout the course. Now, whenever you hear me talk about rows and columns, you're basically going to know what I mean, Right? We've already kind of learned with those mean so columns are gonna be these vertical things that we see here and you can see that they are organized alphabetically. So they go a through Z. You can see I can just scroll along with this horizontal scroll bar at the bottom here, all the way to Z, and that's gonna be these verticals that we see. And then the rows are gonna be numbered 1 to 1000 I believe for a default sheet, and I'm correct all the way to 1000 and you can click them and see that they stretch all the way across. Now you'll also see that we have these little rectangles right here. And if I click on them, they get highlighted in blue. These air referred to as cells and they're just kind of individual parts of the rows and columns. Now, here's where the new vocabulary comes in. You're also going to hear me talk about a range. Now Arrange is a selection of apart as a group of cells, basically of rows and columns. So if I click and drag my mouse, you can see I'm highlighting this square or this rectangle rather of four cells. This is considered a range, even if I just do vertically. That would also be considered a range of four cells. And if I do it horizontally, that's also considered a range. In this case of four cells, so arranges just kind of any grouping of cells across rows and columns. Lastly, to round this off, you'll hear me talk about both sheets and then spread sheets. If you ever hear me talk about a sheet, we're gonna talk about them in more detail later. But a single sheet, you can think of it as, ah kind of many spreadsheets, and if you ever hear me say spreadsheet, what I'm talking about is the entirety, the totality of all of the different sheets that I have, so you can see if you look at the very bottom of my screen right here. You can see that it says she one, implying that I could and we will have more than one sheet. And so if I'm working with more than one sheet and then I say spreadsheet. In that case, you know, I'm referring to all of the spreads, all of the sheets as a collective. 4. Entering Data Into Cells: that was all well and good, but let's actually start making a spreadsheet. In this case, I'm gonna start off by just showing you how toe insert data and then remove data one bit at a time. So in order to select the cell that you want to add data to you, click on it and you'll notice that it gets highlighted so I could start with a one a referring to the column in one, referring to the row, and all you have to do after you click on it is just start typing so you could say 500. And then, if you wanna move over to the next cell, you can click that cell and say, 200 now A faster way of going across Rose is whenever you're done typing in your data, you just tab over, and now you'll notice I'm traversing this particular row much quicker, much quicker, more quickly. And then after I'm done, another way to traverse your cells and select them is to hit. Enter instead and you'll notice that when I hit enter, I'm moving down columns instead of across Rose, and another way of doing this is to use your directional pad so you can use your arrows and go up down left, right, Just like so to select the given cell that you want to add data to. And then whenever you're done, I'm hitting the right arrow in this case to move along the row. And then I move up at another one and so on and so forth. 5. Selecting Multiple Cells: instead of just selecting one cell at a time, it's very common to want to select more than one cell at a time. For a number of reasons. You may want to format something you may want to delete multiple cells and so on and so forth. So a very simple way to select multiple cells is to just click on cell you want to start at and then drag across, and now you can see all of these cells or highlighted, and this is me creating a range. Now what I could do with this is, for example, at a border to this particular range. So I come up to my tool bar and you'll see this little square right here. I click on that, and then I could add a border. Now the border, that's added is going to depend on the icon I select. So if I add a bottom border, you can see that's exactly what it does. And if I want to clear it away, I just hit this clear button where I could add a border like so we're like, so and delete and then add one Onley vertically, right, just like that, to separate the columns so you can see right there. Now you can also edit your border if you want. You can change the color of the border to something like red, something that really stands out and you can even increase the thickness or change. It's something like a dotted line or a spotted line. In this case, I'll just make it very thick. And then I will make it a complete grid so you can see why it could be useful to select multiple cells, cause now I can make very quick edits to large swaths of my sheep. Now I'm gonna go in, selected these range again and then get rid of that border because it's unnecessary. Something else that you can do whenever you're selecting multiple cells is to delete all the information really quick. You're going to be careful with this, obviously, especially if it's important information. You don't just want to delete things willy nilly, but we can do that. I'm just gonna click and drag across this range right here and hit the delete button, and now you can see that the data is gone. But all of the rows are still in play. you can see I still have rose all the way to Z. And if I scroll down, you can see I still have, um, columns and rows all the way through. So I didn't delete any of the rows or columns. I just deleted the data that was inside of them, but highlighting all of them and then hitting the delete button. Now, another way that you can select large swaths of things is to use the control button on Windows or the command button on the Mac, and this allows you to select multiple cells very specifically so I could select this cell , hold down control or command click again, hold down control or command click again, click again, click again. And now you see I'm Onley, selecting very specific cells within my sheet and lastly, to select multiple cells. What you could also dio is just click on a column or row name. So if I want a highlight or select all of the cells and column B, I simply click the B. And now if I scroll all the way down, you can see that all of those cells are selected. So we have a really long range, and if I want to select all of the cells in Row four, I just click on the number four and you can see that all of them are selected. 6. Selecting Rows and Columns: one common reason to select large sections of your sheet huge ranges is for formatting purposes, because let's say that we were going to make a kind of student report Carter a summary of a student's performance. What we could do is we could come in a one and we could enter the student name and then we could tab over and enter, Let's say their grade and then tab G p a. On and on and on And, um, personality, maybe, and so on and so forth. And then we could come back in later, and then we could format each of these cells individually. So I select a one and then I come up to my text formatting right here and I say bold, which would be control be or command be on the Mac. And then maybe I wanna horizontally aligned this. So I come to my horizontal alignment button inside of this separate section here for text alignment, and I click the center right. But if I had to do this for every single one of my headers right here, that would be very, very tedious. So instead, all I do is I click on the one highlight every single cell in this row, and I do all of that again. So I just hit control be or command be on the Mac to make everything bold. Go and horizontally center everything. And now the coolest part about this is, let's say later on, I decide I add another column like, for example, um, emergency contact. You can see that now this emergency contact column is automatically for matter than the same exact way as all of the other columns that I added before, so that really does save us quite a bit of time. 7. Adding Rows and Columns: in the case of our newly created sheet, Obviously we have little to no data to speak of, so we don't really need to do that much in terms of adding rows or columns. But you can see that this list of let's say, 26 columns can be very short, especially if we're working with a lot of data. So there may come a time indeed, where you want to add a row or at a column, and it's very easy to do that. All you would have to do is select a row or a column. In this case, I'm gonna select personality. And then you could right click it so you can select an entire row once again, or a column or a single cell right click, and then you can see whenever we right click, we get this contextual menu with a lot of different things that weaken Dio. We get inserted row in sort of column where we could insert cells. In this case, I'm just gonna insert a column. You'll notice that when you right click here, there is a column that is added to the left of personality. And then if we go over. You can see that after a Z, there's a 27th column, which is double A. And that's how the sequence will continue. Double a double B that will see and on and on and on until we get to Let's see 26 times to would be 52 until we get to 52. Then it would go to a new set, and then we could add in previous school notice that it's automatically formatted, even though I just added in this new column right here automatically format. And now let's see if Double A is automatically formatted as well, you can see that it is so that's one of the beauties of selecting all the rows or all the columns. Now, you could also add in another row so I can right click this cell here and in order to really show you that I'm adding a new row, I'm gonna add in some student names, so I'm gonna say, John hit, enter to move down to the next line Jessica and then Jill and then Jack and then Joseph. And those are all the J names I'm gonna think of right now. Then, after that, I'm going to right click on any of these cells and you could see it says Insert Row. I'm gonna go ahead an insert the road and you can see that another row was added just before Jessica. And then if we scroll down to the bottom, we originally only had 1000 rows. I say only 1000 but now you can see that we have 1000 and one and then all the way at the bottom, you could see that there's a button that says add and then a number and then more rows at the bottom. So instead of using this right click technique toe, add a row or a column we could actually had, Ah, bunch more rows with single click of a button. So let's say I add five more rose to the bottom. Well, they have to do is select five and it add, and then you can see we have an additional five rose right there. I'm gonna add four more rose because I like even numbers. Now you can see we have 1000 and 10 now. This is only one way of adding multiple rows. You could also add multiple columns and rows in a different way. What you would do is you would highlight the number of cells were number of columns or rows that you want. So let's say that I want to rose or two columns. In that case, I could select one and then two, like so and then if I right click, you'll notice instead of just saying Insert columns. Now it's saying, Insert two columns because I've selected to different columns. The same thing happens with Rose. If I select one row, hold down Controller command, select a second row and then right click. Now you can see that it says Insert two rows, so that would be another way of adding multiple rows and columns. You literally select exactly how many rows or columns you want and then you right click. Now it stands to reason that if you can insert rows and columns, you can also insert individual cells. And indeed you can. All you gotta do is select a given cell. In this case, I select personality right click, and instead of inserting Aurora column, we go to insert cell and you'll notice that we get this little drop down menu where it says shift to the right or down. Basically, what that means is that we're gonna be creating a new cell right here. And so the current cell that's in its place needs to move over or shift in this case, you to the right were down. I'm gonna go ahead and say shift to the right because all of these cells are a part of my headers. And I don't want my headers down on the roads where my data should be. So I'll go insert cell and shifted to the right. And now you can see that I shifted all of the cells to the right and added in this extra space, and those are the main ways that you can add rows, columns and cells. 8. Resizing Columns and Rows: in your journeys of adding rows and columns and inserting data like a madman, you may eventually run into the problem that I ran into here with the emergency contact, which is that Ah, the content is too much for the cell. Or in other words, the cell is too small for the content it contains. So we need to resize this cell. So what we could dio is if you hover over this column name G, you notice that I get this little blue highlight right here in this back and forth era. And that's an indicator that I can resize the column. That's probably most intuitive way to resize it. So I click and I can start dragging, and I could make it is bigger. Small is I want in this case I make it a little bit bigger. That works quite nicely. Now I'm going to undo this by clicking the undo button. That's Control Z or Command Z. And now, instead of doing that, I'm gonna right click on the column name G and then inside of this context menus. You see this little option here? Resize column. I'm gonna click that, and then we're gonna get this did a little dialogue box, and these are the two other ways that we can resize or call him. The first way is by pixels, which you may find to be useful, but in this case, I think it makes sense to just fit data. So this column is going to be just big enough to fit the data inside of it. Now I'm gonna hit, okay, and you can see it worked like a charm. And now my column is the right size. You can use these techniques for the Rosa's well, so you can see if I hover around the border of the row. You can see that if I click and drag, I can make Jack's wrote a little bit bigger. And now that I've made the row a little bit bigger, maybe it would be time to do some formatting for these rows. So I might click on this column and then vertically align it by clicking the vertical line and then plopping everything in the center. But of course I would only have to do that if the row was as big as it is when I go and shrink that row back down to its original size 9. Moving and Manipulating: since we added some new rows and columns, you can see that now are are sheet looks a little off putting. We have this row and column kind of slashing right down and through the middle of it, and so we need to kind of move these out of the way. One way we could do it is to delete them. But then I wouldn't have the example that I have now, which is to show you how to move rows and columns. So what you can do is you can select the rower column that you wanna move, and then after that, you go to the application menu, click edit, and then you'll see that we have these two options. Move column left or move column. Right In this case, I'm gonna move it to the right, and then it will move. It won over. Obviously, this can be time consuming, because I have to perform this operation twice just to move it out of the way. So instead of faster way of doing, it would be to just click on the column and drag it over to the right. Same thing with Roe. I can select the road that I want to move, then go to the edit menu and then move the row down but time consuming. So I'm gonna click on the four and then the drag it and drop it to where I wanted to be. And now you can see that things were looking a lot better. And, of course, if you could move a role in a column, it stands to reason that you can also move a cell. And so what we could do is, for example, move Jack Toh where John is. But I'm gonna show you something that you need to be careful with, something that you may accidentally do sometimes in order to move a self first, you need to select the cell, but there's no edit command that you can run to move the cell. Instead, what you'll need to do is hover right along these lines in here and do not click on this little anchor point right here. Just hover. Ride along these lines, and if you click, you'll notice I get this dashed box, and that's an indicator that I'm moving to so so I could move Jack down below Joseph. And now you see I leave an empty cell behind. But you need to be very careful where you move this cell. Because if I click and I move it again and let's say I put it where John is, you can see I just overrode the data in that cell. And now if I move Jack out of the way again to where it was before, you could see that John's gone. So you got to be very careful with this when you don't want to accidentally delete data. Now we can't undo this Controls your command, Z. I'm gonna go ahead and go back and undo my mistake. But still, even though you can undo your mistake, remember that if you save this sheet and then log out, you're not gonna be able to get access to that same undo that you did before. So just something to keep in mind when you're moving around. Cell data 10. Deleting and Clearing: now. Lastly, since we've talked about adding things and kind of moving things round, we're going to talk a little bit about getting rid of things. Now there's two categories of getting rid of things inside of Google sheets. The first thing you could do is delete something. The second thing that you can do is clear something. So if you want to delete something like, let's say we want to get rid of these rows at the bottom because we have 10 extra rose. What you could do is select the road that you wanted to leave. I'm gonna click the bottom won the 10,000 and 10th 1 Now, if I right click, you'll see. I get this option to delete the row. That's exactly what I'm gonna do just to lead the rope. If you want to delete multiple rows, you can click on each of these. Now I'll show you a trick for selecting multiple rows, select in the first row and then hold down, shift and select the last row, and everything in between will be selected. Now. If I right click, you can see it says Delete those roads. And indeed that's what I will dio now, you may not want to delete your rose. You may just wanna get rid of some data that's inside of the Rose. In that case, you wouldn't delete it, you would just clear it. So I'm gonna on this 1000 and one throw first row, Just add in some numbers and tab over and add in some numbers. Like so Now that I've done that, maybe I want to keep this road, but I just want to get rid of all that stuff inside of it. All that data. So what I can do instead is right. Click and hit. Clear row. You'll notice it doesn't get rid of the road. Just get sort of all the data that's inside of it. But I actually do you want to get rid of this road? So I'm gonna go ahead and delete it anyway. Kind of messed up, but still, um, same thing with your columns if you want to. You can right click your column and you can delete it where you can clear it. The choice is yours now. You could also clear or get rid of individual cells. Very easy to Dio. You can click one cell and then hit the delete button, and that will actually do the trick. Now, if you right click, you'll notice that you also get a delete cell. Now, this will completely get rid of the cell if you just want to clear it. Ironically enough, you select the cell and then hit Delete the cell is still there. It's just gotten rid of the data. We could do that with ah previous school as well. Just click on previous school hit delete. You'll notice the cell is still there. We just got rid of the data that was inside of it. So just unimportant thing to keep in mind. Whenever you're deleting yourselves, you could delete yourself, but you may just want to clear the data that's inside of it. And if you want to clear the data inside of multiple cells, you would use that same control click that I showed you before. Hold down control or command and click on the cells you want and then hit delete to get rid of the data inside of it. But I'm gonna do controls your command Z because I actually do want to hold on to that data 11. Freezing Rows and Columns: next up is another key thing. You'll learn in a lot of the beginner tutorials for Google sheets, which is freezing your rows or columns and freezing is just a real fancy way of saying making it stick, making it stick in place. It will be easier to show you what I mean, rather than to, um, tell you. So if I scroll down right now inside of the sheet, you can see that I'm just going along to new sheets are new parts of my sheet right here. But what I might want to do is as I go down and look at the data here, I might want to remember which value corresponds to which key that is, which header corresponds to which value. And so what I could do is I could select this road right here and make sure that these are always visible. So I select this row. Then I go to the view, button the application menu and hit freeze. And then you can see I have options here, rows columns. In this case, I'm just going to say one row, and that is the road that I have selected. Now what's cool is that if I scroll down, you can see that that first row with my headers sticks, which can be very useful if I have data all the way on the 100th line. I can still remember the order student grade G P A and so on and so forth. I could also do the same thing with this column right here because I may wanna remember you know who is who as I look at their grades and go further and further and deeper into my rose. So I select this column and then I go to view and then freeze. And then I select one column like so and now as I go horizontally, you can see that it sticks. So that way I can match up student with emergency contact very, very easily. Toe. Unfreeze them. It's just a Z z. I could select the column again. Hit view, Freeze! And then I can go to know columns and that will unfreeze it. Now when I scroll horizontally, nothing happens. Same thing with my row here. I'm gonna go and select this row hit view Freeze! No rose! And now when I scroll down this time around no sticking, no freezing whatsoever 12. Grouping: now, as I walk you through some of these other features thes examples where these features may seem somewhat random. But I really am just arming you with some of the basic tools that you'll need for Google sheets as we dive in later on, you'll need them. And so I want you to be aware of them now and then we're going to see them again and again . The next thing that we're gonna see now is the ability to group together rows and columns you'll find a lot of times when you're working with your spreadsheets that all of these different rows and columns can relate in certain ways. So I'm gonna show you how you can group together these pieces of related content. So, for example, previous school and personality or great and G p A are good examples of data that might be related to each other so we can group them together. That way, if you want to see one, it's likely that you want to see the other. And whenever you don't want to see them, you can just kind of put them away and not have toe deal with them too much going on visually. So what I can do is I can select this column and I can right Click were hit this down arrow to get access to the contextual menu and then down below all of our different adding and sizing things. You see, we have group column, and now what this is going to do is create a new group, but we haven't officially created our group yet. We still need to get another member in order for it to be a group. So you can see right away, though, that we have one change, which is that we have this minus sign right here, and if I click it, you can see that the sea column disappears. And when I hit the plus sign, the SEA column reappears. But I'm gonna now add the B column to this group by clicking on it and then clicking. Group column. Now you can see that this line has extended toe where we have two different columns in one group. So now if I hit the minus sign, you can see that both the B and the Sea column disappear in a plus sign to make them reappear. Now it's ungroomed this. What I have to do is click on them and then right clicking could see it, says ungroomed Columns be through, see, So now I'll go and ungroomed them and you can see that it disappears. 13. Grouping The other Way: Now you'll find that there are two other ways to get to the same destination of grouping. Together are different cells or different columns and rows. Another way to do it would be to click on them. And then after that, go to data in the application menu and you can see it says group columns. And then to the right, you could see this little keyboard shortcut. All shift right, Arrow options shift right arrow on the Mac, and that's exactly what I'm gonna do. Options shift, right arrow. You could see it automatically creates a new group. And then if I wanted Teoh, I could continue on and keep hitting all shift right arrow, and that'll make new groups. Now to get rid of a group, I go in the opposite direction. So I go all shift left arrow to get rid of one group Now to make, let's say, D column D a part of this new group. I would go over to the left with my left arrow and then all shift right arrow again. You can see it's asking if I want a group this row with this column. In this case, I'm gonna go ahead and say, Group this row and you can see that Now we have both a row and a column group and the whole shift right there again, I'm gonna group this column together. Lastly gonna go down and then all shift right Arrow group, this row. And now you can see we have a group along the row in the column and I can go ahead and expand those and shrink those as I see fit. And lastly, if I want to undo all of this, I can simply go in the opposite direction. All shift left, Arrow on group row, all shift left, arrow on group call. Um, and that's options shift left arrow on the Mac toe ungroomed this column go down right here on group this row and we are done. So that's just a little bit of a faster way to get to the same place 14. Hiding: Sometimes you don't wanna group together related rows and columns. You just want to get them out of the way so that you can focus on some other aspect of your table, some other aspect of your sheet, and you just want to get some of that other visual stuff going on out of the way. In that case, you could just hide your rows and columns. So what you do is you select the Rower column and I feel like the columns of beginning a lot of love. So I'm gonna select a row this time, and then I'm gonna select another row by holding down controller command and clicking on Row four and then right click and you can see it says Hydro's. So now you can see it's hiding. Both Rose and I get these up and down arrows immediately after that. And that's just so I can go ahead and expand them back out and get them into my line of sight again. So I'll go ahead and hover over and click them, and now you can see that they have both come back into view. If I ever want to hide them again, I can just right. Click on a given row or multiple rows and hit Hide that row and then after that you could see it is out of sight again. Bring it back in the view by clicking those arrows that appear and you can see that we're back in action. 15. Merging Cells: to wrap up this particular section, I'm gonna show you how to merge multiple cells together. This is gonna be a very common operation, especially whenever you're totaling things. Whenever you're summing together Ah, total of all of the different pieces of data in a given rower column. For example, a budget, an annual report. So on and so forth you'll be merging together cells and putting the total in one big sell sometimes. Um so what you can do is select the row or the column with cells that you want. In this case, I'm gonna select previous school and personality, merge them together, even though it typically never makes sense to merge together to headers. But in this case, I want to show you a danger that can happen. So I'm gonna click previous school, hold down control or command click personality. Now, after I have both of them selected, they're two different ways that I could merge these cells together. I could go to format in the application menu and down below it says, merge cells and it gives me my options or the quicker option simply going to your toolbar into the right of where we added our borders. We can now merge ourselves. You could simply click the merge cells button where you could hit this drop down to see all of your options. In this case, we only have one option. Merge horizontally. And now we get the warning. The danger that I was talking about before, which is that if you merge together cells that already have values in them, only one of the values is gonna be preserved. The top left value. In this case, if you had a range with multiple values, then that top part would make more sense. But I'm gonna go and hit, OK? And you will see that indeed on Lee, the value of previous school was preserved. But the main point here is that we only have one. So I could further prove that to you by giving it a border. In this case, I'll give it of border all around, and you can see that it is indeed just one. So could also do this with Rose. I'm gonna select Jessica and Jill, so it's just kind of a subsection and then emerge those vertically ignore the warning once again. And then after that, I'll give them Ah, border around. But clicking on border and just hitting this icon here and maybe even making it a different color like blue Just for fun, you know we're crazy. Why not? And now you can see that we have, ah, merge those two cells together. Obviously, I don't want this. I just want to show you that way as we make more advanced spreadsheets, you know how to do these things. And I'm not just throwing these things out You for the first time. So I'm gonna hit Control Z to go back in time a few times, too. Before we decided that it was a good idea to merge together those two. 16. Setup Part 2: We've been playing around quite a bit with this demo sheet, but now I want to make it slightly more realistic. So there's a couple of things that I'm going to want to do. Ah, First, I'm gonna get rid of a couple of columns, specifically the personality in the previous school. So I'm gonna select both of them by holding down control or command and then right clicking and then hitting delete selected columns Now that I've deleted both of those columns. And next thing that I'm gonna do is I'm gonna modify this in grade column and I'm gonna turn it into, Let's say math and I'm gonna add in a couple of columns after this. So I'm just going to right click, and I'm gonna hit insert one right? And that should give me another column in this case, science. And I'm gonna insert, let's say two more, So I'm gonna go ahead and select two more columns and then right click. And then after that, we're gonna go ahead and hit. Well, two more cell. Excuse me? The right click insert two more columns. And now that I have that, I'm gonna move science over just because I feel like math and science kind of go together. And then after that, I'm gonna add two more subjects history and then tab over English. And look at that. I just see how easy it was just to modify this table. Obviously, the more data you have, the less you can just move things around willy nilly. And then after that, I'm gonna give each of these students here a grade. Now, part of grading is gonna be formatting these different columns right here. Because whenever we're working with data, we want to make sure that these cells can only use a certain format of data in this case percentages. So what we can dio is we can select these columns. I'm gonna click on each column holding down control to select all of them. And then I'm going to get rid of a couple of cells. I'm gonna subtract them. So I'm gonna hold down control and click math to de select it science to de select it History English. And now I have selected pretty much every cell in these columns except for the headers. And now that I've done that, I'm going to format it with the percentage. All this means is that whenever I enter data into these cells, it's automatically going to be turned into a percentage, someone, a click format as percent. And now, when I add in my data, you could see that it automatically becomes a percentage una tab over, and you can enter any value that you want for these fields. It will be okay I'm gonna do. I'm gonna try to give them good grades, at least. But like I said, you can pretty much enter anything that you want for these 2 86 and then. And if you want to center them, you can you know how to do that. But you do not have to and then do 99 if you doing that will in science, but not in math. I don't know how that works, but maybe they don't do a lot of math and their classes. I guess either way, almost done giving everyone at least one bad grade. All right, I think we're gonna be all set on that, and then we'll give them G p A. Now we will look at how to calculate the G p A with functions later on. For now, we're just gonna fill it in with some data here. Based on those grades, I'd call that about it, But maybe like a 3.1, I might be being a little generous year hit, Enter to go down to the next line. And then after that, like I said, doesn't have to be exact data. We're gonna look at how to get very specific with our calculations later on. But for now, give it a 2.2 and I'm gonna give this one a 5.5. And yes, I know that your GP a can't be 5.5. But that's the point. Now I'm gonna go to emergency contact. I'm gonna say, Mom, and then for this one, I'm gonna say, Dad, hit! Enter to go down, Aunt, Enter, Mom. Perfect. Now that we filled out all of our data, we can go ahead and move on 17. Protecting Ranges: Once you've inserted a good bit of data, you may wanna share this spreadsheet with someone, But before we share it, I want to show you how toe make sure that people don't edit it without you wanting them to edit it. In other words, I'm gonna teach you how toe protect different ranges as well as entire sheets if you want. So what you're gonna do is go to the data in the application menu, and then if we scroll down, you can see that we have something called protected sheets and ranges. So I'm gonna go ahead and click on that and you can see, as of right now, we have no protected sheets or arranges so we can click the plus toe, add something, and then we can enter a description if we want. No, there's a couple of things to be said right here. Um, first, you can select either to Onley protect, arrange so anything that we highlight here, or you can choose to protect the entire sheet. I'm gonna start off with the range and introduce to you a syntax that you may not have seen before, so you can see right year that we have what looks like the name of our sheet on exclamation point and then a one. So this is a syntax for specifying your ranges, and I'm gonna kind of show it to you. Whenever you want to specify a sheet name, you just write the sheet name. Now, after you write the sheet name in this case sheet one, you separate it with an exclamation point, and then you specify the cells that you want. In this case, we already had a one highlighted. So a one is what we got. You could also change this to, you know, C five and so on and so forth. But what if we wanted to specify a range of different cells? Well, this in tax for that is gonna be as follows. Let's say I don't want people to be able to change the grades. Okay, so basically I want from B one all the way to be five, So let's see what that looks like. First, I'm going to change this to Capital B one and then to specify the end of this range, I would add a colon and then, after that, say the end of the range in this case, be five like So now, after I do that, I can click set permissions and then it's gonna ask me what I want to give people permission to edit. So in this case, I want to restrict who can edit this range. But instead I may want to show a warning. You do have that option as well, but I want to restrict it. In this case. Now you can select who you would like to give permission to. You can either leave it as Onley you or you can go to custom and then down below where it says, add editors you can add whoever else you think should be allowed to edit this particular range. Aside from that, you can copy permissions from another range. Obviously, this won't work because this is our first time setting this. So in this case, I'm just gonna leave it as only I can edit this range and then I'm going to hit done. It's gonna take a second to save. Now we are good to go. You can see that there's a little green box around this particular range, and that's because it is now protected. So if I come in here? I, of course, can still edit it. But if someone down the road were to later come along and decide Hey, I wanna edit this. They would not be allowed to do so. As a quick side note, This is not a security measure. Ah, people can still copy this spread she they can print it out. They can still do all of those different things. So keep in mind that protecting your ranges is not a security measure. It's just a way of making sure that people don't tweak or manipulate your spreadsheets whenever you try to send them over to someone. So in this case, maybe I have a t A. And I don't want her toe mess around with the grades. 18. Range Select Syntax: I kind of want to expand on that syntax that I showed you before for specifying different ranges cause it's gonna come back into play once we start using functions. Um, which is I'm sure a large part of why some of you are here. So what I'll do is I'll just select, let's say science. And let's say instead of just making sure that they can't change math, I also want to make sure that they can't change the science history or English grades. Well, in that case, I'll go to data. And then after that, I'm going to go to protected sheets and ranges, and I'm gonna add a new she were range to protect in this case, a range. And I'm going to say from sea to and then Colon once again, all the way t E five. Now you'll notice that whenever I do that, all of that is going to be protected. Now you notice we also have a grid here. This would be another way of selecting the data range. You can literally just click, and then I'm gonna drag to select. But you'll notice that we get the same exact syntax. This is just a more intuitive way to do it. If you're not familiar with this syntax for specifying your ranges and don't forget your sheet name is separated by an exclamation point. Now that I'm done with that, I can select. Okay? Now, if you want, you can also add a description to your protections. So, for example, you could say grades from previous years cannot be modified and then set the permission just like so. Gonna take a second, ask us what we want to dio could say custom in this case, I just want it to be me. Or we could show warning Let's go ahead and try a warning. Just toe show what it does. And then after that, we're all good and then you notice it says can edit without warning. Grades from previous years cannot be modified. And then, if I go in here and I try to edit the English grade, let's say I give it a 9.5 hit. Enter. You can see. We do get a warning. You're trying to edit part of this seat. That shouldn't be changed accidentally. Do you want to edit it anyway? Then I can hit OK, so as you can see, it doesn't really protect the range in this case. And it doesn't even use are a little note that we left our little description instead of just lets you know, Hey, you're changing something that you might not want to change. And, of course, just as a kind of closing note, if you want to get rid of your protections, you can just select whatever range that you have here. And then after that, you can just hit delete, and that will remove the protection. I'm gonna click on this one as well. I could modify the permissions if I wanted to. In this case, though, I just want to delete it. 19. Selecting Rows and Columns: This last way of protecting things is to, of course, protect the entire sheet. And this is more of a show and tell. But don't modify kind of protection. So let's say that I just want to show this sheet someone, but I don't want them to modify anything. I'll just hit ad sheet or range, and then it started going a range. I'm gonna go to sheet and you'll notice that we can select whatever sheet we want. In this case, we only have one sheet sheet one as it's named, but on top of that, I can exclude certain cells from this protection. So that way I could still modify things later. Like, for example, I may want to be ableto modify the emergency contact column later on. So what I can dio is click accept certain cells, and now I'm gonna show you a new syntax. Once again, we can select another range. But if you want to specify an entire column, there is a syntax for that. You just select the name of the column. In this case, it's G, and then you do colon. And in the name of the column again, G. And that would be a way of specifying that entire column in this new syntax that we're learning now under The cool thing is I could add another range to this if I wanted to. So we might say that we don't want to protect. Um, let's say the six throw. If you want to specify the entire six throw, you would use its number in the same syntax that you do. Six. Colon six and you can see that that is going to protect the or excluded from the protection on that entire row. Set the permissions. It's gonna ask me what I want to do. I'm just gonna restrict it to where only I can edit the page and then we're all set. Now if I let's say, try to modify the G P a double clicking here, give it a 4.4 something along those lines. You can see that I'm trying to edit this. I really am. There we go. OK, perfect hit Enter. You could see it still works for me, but if someone else were to try to come in and do the same exact thing, it would be a no go. Obviously, I don't want to protect any of these right now, It's only whenever I decided to share this with someone that I might consider who do I want them to be able to modify this, and that's something to think about before you share your documents. So I'm gonna go and click on this protection right here and delete it with the delete button, like so. And like I said, I'm gonna reiterate this right now. Make sure to remember that this is not a security measure. You should really only be sharing sheets with people who you expect will use them for what you want them to be used for. 20. Permissions and Protection: So whenever we started protecting our ranges and are Shia as well, we got kind of into the discussion of permissions and permissions is a huge part of G suite , especially if you're an administrator and permissions just really fancy weight or not even really a fancy way of just saying who is allowed to do what and who is allowed to do those things to what things. So in the example of protecting our data, you can actually decide who is allowed to modify the data and who is also allowed to, let's say, protect data as well. Now, as we talk about permissions, we're really getting more into the G suite administrator territory, which might be a little out of scope for this book. But I'll give you a quick run down of what you can and can't do so if you're the owner of a document of a spreadsheet. In this case, as I am the owner of this particular sheet, ah, then you can protect ranges as much as you want. You can also take away from permission from people as well because you're the owner of the sheet. Now, if you're allowed to, for example, edit this sheet right here. You can also provide protection for it as well. Then you can actually decide who gets to protect other ranges and sheets as well. So if you are allowed, in other words, to protect it, she can also decide who gets to protect a sheet. And lastly, of course, if you can only view a spreadsheet, you only have a view on Lee copy of the sheet. Then, in that case, that's really all you can do, and you're not gonna be able to decide who else is not allowed to make modifications to the data. 21. Special Paste and Two Sheets: one of the beauties of Google sheets is that you can work with multiple sheets inside of a single spreadsheet. So that way, if I wanted to keep all of my student under information under one roof, I could absolutely do that. So let's say that I want to move this emergency contact information out of the current file that were in moved into its own separate sheet. Will like it. Absolutely do that. And I could even put things like address and other stuff inside of that separate sheep. So down below at the very bottom, you can see that we have our sheet right here that we're currently working with and to the left, we have this little plus sign, and that's gonna be how we create a new sheet. So I've been right click that to create a new sheet. And the next thing that we're gonna want to dio is basically, take this data and copy it into our new sheet. So I'm gonna go in select G one, hold down shift and select G five toe. Select everything in between. They're gonna do control, see or command see on the Mac to copy that now that I've copied it. I'm gonna go ahead and click on our newly created she. It should be sheet, too, and I'm going to right click. Now you have two options. Whenever you paste things in, you could paste where you could pay special pay. Special is really cool cause it's going to allow you to paste in a very specific way. You could paste on Lee the values of that given sheet, or you could paste on Lee the format of that given sheet. Or you could do both paste everything except for the borders. So I'm gonna go ahead and paste everything except for the borders. And what you'll see is pretty cool. Not only do we have our emergency contact in a list of everything that we have here, but aside from that, we also have the same exact formatting that we had for the column. In Sheet one, you can see that it has that bold and centered text, so it's pretty cool, and that's going to save you a lot of time, especially every single item. Every single cell is formatted Now. If we right click and hit pay special again, you can see it says Paste column widths on Lee, And that's essentially just going to, as its name implies, paste the exact width of the columns and you can see now have resized. My document automatically pay special comes with a number, a number of different things that you could do paste formula Onley, which we haven't gotten into conditional formatting transposing haven't really gotten into those yet, but you can see how cool it could be to just take data from one row over one column and just automatically place it into another sheet with everything that you had it for matter to be before. 22. Filling The 2nd Sheet: Obviously, this isn't all there is to it. We're gonna want to include the student name as well. So I'm going to right click on this first column emergency contact, and I'm gonna slide it over one to the right, and after I slide it over, it whips. There we go. Any selected? There we are. Click and drag one over just like that. Then after I successfully move over that column, I'm going to go back to my previous sheet and I'm gonna grab that student information. So I'll go ahead and just click the 1st 1 and the bottom one. Control. See your command. See? Then go back to my feet. Four in this case. And then I'm just going to right click paste, special paced, all except for the border. And you can see that everything is matched up very nicely now, if we wanted to, we could also add in a phone number so we could say Ah, phone number. Now we need to format this. So let's go ahead. Just format this entire row. So select row one and then I'll do control be and then all center the text for everything and were firing on all cylinders. Now I'm gonna go back to sell. Ah, see one. And then I'll add in a phone number. And for the phone numbers, you can add whatever you like. In this case, I'll just do 999 Dash 9999 Just like that. Then I'm gonna copy this by doing control Air Command A and then control. See your command. See, then hit. Enter to go the next line control of your command V to paste it in. And I'm just gonna go down the rows like so, adding in this data and hitting enter to move down. So you've seen how we can paste things in and how we can paste things in in a rather special way. 23. Renaming Sheets: As you work with more and more sheets, you're gonna want to make sure that you're giving everything a meaningful name. So first, I'm gonna change this entire spreadsheet to a new name called Student Profile because that's a little more meaningful. A student profiles hit enter, then. After that, you can see that for each of our sheets, they have this little down arrow right here. If I click on that, that's going to feed us a drop down. And in this drop down, I can choose a number of different things first, I'm gonna rename it that way, you know, cause she won and sheet for is not very informative. And if I have, let's say 5 10 sheets, then it can be very hard to keep track of where everything is going to go ahead and rename this sheet right here. And I'm just gonna call it grades and then hit enter. Same thing for sheet forming a right or not right click. I'm gonna hit this down arrow right here. It rename. And in this case, I'm gonna call it contact information. We're just contact info. Now we're all set. You could see it officially changed the names as a side note. We don't really need this emergency contact information any more Soldiers. Ah, select this column and hit delete toe hollow out the data. But you can see that I preserve the column. We still have all the columns that we need. In fact, we have a few too many columns. 24. Copying Duplicate Sheets: now, the next few things I'm gonna show you with sheets are probably things that you could have figured out on your own. But I'm gonna go ahead and talk to you about them anyway, just to be very explicit and cover all my bases. So aside from what we did right here, if you want to kind of move around the order like, let's say we're working with many sheets, I'll go ahead and add another sheet. Let's say you want to move them around. You could see Right now this sheet was automatically inserted in the middle of the grades and the contact info because I had the grades sheet selected. If you want to move it over, you can just right click and drag to kind of reorder your sheets, and this can make it very easy toe. Keep all your ducks in a row, something that you may also find super useful, especially when you're working with a lot of sheets is to change their color, and it can make it a lot easier for you to instead of reading things, just look at what color it is. And so if you have a color system for, like red being super important danger zone information and green being desirable. Then you could do it that way. Whatever you want your color system to be, you can right click on a sheet or not Right click. I keep saying that the down arrow on the sheet and then you could see it says change color right here. You could select whatever color floats your boat. I might use this pure blue right there. And now you can see that it has this pure blue bottom border. I'm gonna do the same thing for contact info, and we're gonna make contact info yellow and lastly for the grades, we will make the grades a purple color like so now it just makes it a lot easier that way. If I have multiple sheets that pertain the grades or multiple sheets that pertain to contact info, I can at least narrow down the search visually. Now, aside from all of this, when you're working with sheets, you may want to copy the spreadsheet and there's really two different ways to skin that cat . The first way is to copy the spread of the sheet within the spreadsheet. So you could essentially duplicate your sheet, for example, with contact info, I might click this down arrow here, and then you can see it says copy, too, or a duplicate. So if I just want to keep this sheet copy within this current spreadsheet, then I would hit duplicate, and it will make an identical copy and keep it in the same place. Notice its has copy of contact info. But let's say you're working with multiple spreadsheets and you want to take this sheet and put it into a distinct different spreadsheet. Well, in that case, you would say copy, too, and you could say new spreadsheet or existing spreadsheet. If you say new spreadsheet, you're gonna open this up in its own brand new spreadsheet. And, of course, your existing spreadsheet would be, ah, set of sheets that you were already working with. Now, in this case, I want to do neither of these. I want to actually delete this copy because I'm not using it anymore. Very easy to delete sheets. All you do is hit that delete button. It's gonna make sure that you want to delete it, and indeed, ideo, and to round this off, you're gonna be working with a lot of sheets in the future, and so sometimes it may make sense to get some of them out of the way. They may be too many for you to look at, and in that case, all you would have to do is hide your sheet. And just so you know, this doesn't mean that it's invisible. Anyone who can get access to the spreadsheet with this software right here or, you know, Excel or something like that is gonna be ableto unhygienic, essentially. So it's not like it's invisible, you know, it's not like some secret agent stuff, but it just gets it out of the way so you don't have to look at it and you can keep everything that you need to look at within sight. So the way that we do that is we just click the down arrow once again and hit hide sheep. Now, to get access to the sheet that you've hidden, all you would have to do is go to view right visual view and then hidden sheets, and you will see a list of all the sheets that you've hidden. Click on that sheet and it will come back into view now. She five is unnecessary at this point. So I'm gonna go and hit the down arrow in this case and then delete it. Make sure I want to delete it. Yes, I do. 25. Formatting Numbeers: Whenever we format our data, you've already kind of seen how we go about it. We do have a set of different buttons that we can select in our toolbar for formatting our data. And we even have a little drop down right here for getting access to the other ones. And I want to show you some of the other ways that you conform at. You're different cells whenever you're working with data. So let's say for our student right here, I also want to include a lunch balance. Okay, Well, I'm gonna go to G right here, and I'm gonna add a lunch balance, just like so. Ah, I don't know if you remember this from school or if in your school they did this. But, um, at a lot of the schools I went to, you had to, like, pay for your lunch. Or if you didn't pay for your lunch, you had to, like, sign a form or something. Either way for this particular column right here, we're gonna be working with money. So what I want to do is set up a format specific way of writing out the money. So I'm gonna select this column. Then I'm gonna hold down control or command, and I'm gonna select the header. That way it's subtracted. So everything but the header. I want a format in a certain way. One way you could do that is to go to the format application menu and then where it says number right here. You can see we get a list of all the different ways I could form at this. For example, financial currency. These are gonna be the ones that we work with right here. This is kind of the money section. You can see we have regular number, which can be useful. Ah, percentages. We've already seen how to do that for the grades and scientific notation. Great. If you're ah, for example, you know, making a spreadsheet about planets. In that case, you're working with very, very large distances. And so scientific notation makes a lot of sense in that case. But for our purposes, we just need currency, and we're not going around it, OK, you might have 25 cents and that 25 cents might get you Amilcar New knows I'm gonna go in select currency now. After that, I have the power to decide how much money these kids have, so that's good. Um, I'm gonna go and say for John. Let's give him 20 bucks, like, so hit. Enter. And you could see it automatically as the dollar sign for me. Even if I don't add the decimal like, let's say, at $15 you can see it'll even automatically include that for me. But if I want to specify my own decimal places, I do need to specify that myself. So ah, for Jill, we're gonna make sure Jill is just balling out of control. $65.72 hit, Enter. And then after that for Jack, we're gonna say that Jack is running low. He's gonna need a refill here soon. So we'll do $3 um, 44 cents hit, enter, and you can see we've automatically formatted that very, very nicely. Now, next thing that we can do is add their year. That is when they start school. So I'm gonna go ahead and go to this next column right here and at a start date, and then we're gonna do the same exact thing. We're gonna select this column, hold down control or command click minus this cell, and they were gonna format it instead of going to the application menu, though honestly, a much faster way to do it is just to go to the toolbar and select mawr formats and down below You can see we have a date right here. Now, if you're watching this and you're not from America, I am aware that in different countries they use different date formats. For example, in Japan they go from largest smallest. That is to say they go from, like 2015 March the fifth. That's how they do it. And in a lot of European countries that I've seen their dates and, uh, they kind of go from smallest to biggest. So they go day, month, year, and in America we go month, day, year. We'll talk about custom date formats here in a second, but by default, mine is set to the American date format. So I'm just going to use that right now. Now, after that, we can add any day that we want. I'm gonna go ahead and say three. When do kids start school? I would say eighth normally in August around August, the 15th and we'll just call it 2018. I'm gonna hit. Enter, watch what happens. You can see that it automatically adds that 20 to the 2018. And we could do that for all these other ones to, um a dash 15 2018 and then eight. Dash 15-2017 and then eight dash. Ah, 15 Dash 20 17. Just like so. So you can get the gist of what we're doing here. Any time you need to format a cell, you can decide how you want to format it. You can work with time, which is Mark aided by its own separate section. Ah, different money. You know, different monetary units in its own separate section. And then just kind of the general number formats right here in its own section at the top. 26. Custom Date and Time Formats: I told you, we're gonna look at how to do our own custom date formats. And so that's exactly what we're gonna do. Now your default date format is gonna be based on your location, or at least where you told Google that you were located. Now, you might not even want to use your own default date format based on your location. You may be doing something like international accounting, So the documents that you're preparing need to be in a certain date format that's different than what you use where you're located. So what I'm gonna do is just use this extra column here. I as an example, I'm gonna select this whole column. Then I'm gonna go to format right here in the number and then down after that, I'm gonna go to more formats and you see, we get the sub menu and we see more date and time formats. Now there's a couple of different ways that we could customise our daytime former. You can see we get a number of different recommended options, right? We can use words. We can even include the time. If we want to weaken, use slashes, we can use hyphens. You know the world is ours. We could use commas. It's it's all kind of stuff. But aside from that, we could also customize it up here at the top where you see this little input box. So if I click on the year, it's gonna ask if I want to do the full year of the two digit year ago, Two digit for the month. I could use the full name, the abbreviation. I'm gonna go with the abbreviation and then for the day I could use an abbreviation as well where the full name, like So now, Aside from doing all of this, I could add things to it. If I click this down arrow right here, you can see him not limited to what I have here. I could also include the hours, the minutes. I could even include a duration if I wanted to. I could say hours elapsed and then maybe set this to a leading zero, like so you can see we have a lot of options. Now let's say that I'm from Europe, like England or Spain, where they do with a different date format. In this case, I'm gonna need the set first the day, the month and then the year. Alright, contrary to what I do in America. So what I would do is I would first delete all of these. It just makes it a lot easier. Instead of trying to insert things and move it around, I just delete everything. Makes it weighs here. So I'm gonna click on this down arrow and hit delete and one by one go through here and just pop all of these off, just like so Now that I've done that, the next thing that I can dio is just adamant. So I'm gonna hit, apply or not apply. Excuse me. I'm gonna hit starting with day and then after day. I'm just gonna go in with month and then after month, I'm gonna go in with year. And just like that, we have the European format nice and easy, lemon squeezy. And then I can choose how I want to format it with the full year and no, I'm gonna stick with numbers on this one. No, leading zero and then apply it just like that. And so now whenever I write this in, it'll be in the European formats in 19 and then we'll do the second month, February and they will do. Ah, 19 97. Thanks. So hit enter and you could see works like a charm. 27. Custom Currencies: Of course, if you conform at dates, you can also format. Currency is the currency that you have may not be the currency you wanna work with for any given reason. And so we conform at that as well. In this case, I'm gonna select the J column and then I'm gonna go to the toolbar and format my currencies . Now, in my particular browser, I'm having a little trouble getting access to the other formats from within the toolbar. That might be a little glitch on their part. But either way, I'm gonna go to the format application menu here and then go down to number and then all the way down it. More formats you could see next to date and time we have currency's gonna click that you'll get a dialogue box. And this dialog box has pretty much every currency that you can imagine. The dean are, um the Gibraltar in what's I called the Gibraltar Ni Always for you. The name of the Gibraltar. Done well. Either way, the world is yours. You can pick any currency that you want. In this case, I will go with the Serbian dinar and then you can click right here, and you can see not only do you get the abbreviation, but you could also get the symbol, or you could get both at the same time. You can choose to round off, or you could choose to include decimals. I'll pick something with a fancy symbol like the one and then I will include the one and decimals as well. Perfect. Now that we have that, I'll go and apply. And now, anytime I add something in he could see, like let's do 41 you can see that automatically adds in the symbols and the decimal. 28. Right To Left Readings: since we're entering the world of kind of national concerns sounds a lot more dramatic than it is. Ah, you may come across the language that's written from right to left. One of the primary examples being Arabic, Arabic is written in right to left. If I'm not mistaken, Uh, if for some reason you're working in Arabic or some other language that goes from right to left, Google Sheets does have the option. I'll let you work in a right to left way in your sheet. So what you can dio is, for example, you don't have to follow along with this. I paste it in some Arabic right here. This is the Arabic word for Arabic. I know, right? Ah, But anyway and then what should happen if you paste in something like that is that these symbols will pop up its right to left or left to right cell. And this is a way of overriding individuals. So So I could set this individual, so to be a right toe left, so or I could set it to be a left to right, So ah, in the case of Arabic, it would make sense for it to be a right toe left, so and lastly, if I want to, I could click right to left sheet, which will allow me to to make the entire sheet a right to left sheet. Now, something to keep in mind with this right toe left sheet button is that it only applies to the sheet that's currently active. My contact info will be completely unaffected if you want to permanently change your settings from, Ah, let's say, on Lee showing these right toe left options whenever you pay something like Arabic into your seat. If you want thes options to always be available, you can change that in your settings. Now, in order to get to your settings, you're gonna have toe go to your home page. So to go to your home page, click this button in the top left corner, the sheet symbol and then from there will be able to access the menu in the menu. Gives us access to you, guessed it the settings. So I'm gonna click on this menu right here and then open up my drawer and down here you can see it, says settings. Once we're inside, a settings were going to go ahead and change this where it says always show right to left controls. We can just check that box hit. Okay, now, once we've hit OK, we can go back to student profiles and you don't even have to worry about saving and Google . I haven't even talked about saving, but one of the cool things I just kind of leapt out of my student profiles. And if I come back, you'll see that everything is exactly where I left it. Everything was automatically saved for me. Now that we understand the right to left controls, I am going to get rid of the Arabic because to be honest with you, I don't even read Arabic and we'll go ahead and move on. 29. Spreadsheet Settings: Now, if you're going to be using a different date format all the time, then what you could do is change your settings. So the way that you change your spreadsheet settings is you go to file the application menu and down at the bottom. You see, it's a spreadsheet settings. Luckily, and Google, she's there aren't that many settings. It's not like photo shop or something, where there's just like a 1,000,000 settings and you have tow learn about the digital theory every time. But either way down here, you can see that we have your location. You also have your time zone. You'll notice that to the right, it tells you exactly what it effects, so it affects formatting details such as functions, dates and currencies. We haven't talked about functions at all, but it also affects those and your time zone as well, which will affect your spreadsheets. History will be recorded in that time zone. Okay, now, the last thing is your display language. If you click this right here, it will open up a new window where your Google account will allow you to change either your default language or add another language that you understand. So you can change that if you want. Teoh. I'm good with English. For now, though. Then, if you over to calculation what we have here isn't gonna really make sense right now. This is just for determining how often the different functions recalculate. And if we should use it. Narrative calculations or not, that's gonna be a bunch of nonsense to us for now. So we don't need to concern ourselves with it, but just the general spreadsheet settings instead. So if you need to change this, go ahead and change it and then hit save settings. 30. Inserting Images: now, another cool thing that you can do in Google sheets is you can insert images into your sheets. So the way that we're gonna do this is we're gonna select the eye right here the I column, and then I'm just gonna clear the data by hitting. Delete could also right click and clear the column that way as well. That's a little faster for me. And after that, I'm going to select this. So where I want to insert my image. Okay, then after that, I'm going to go to where it says, insert and you could see there's a bunch of different things we could insert in This case will go with an image. Now you have two different options. When you insert an image, you can insert it into the cell, were over the cells into the cell, means that you're literally gonna shove that image inside of the cell and then over the cells means that it's going to kind of follow on top of the sheet, if you will. In this case, we're gonna insert it into the cell, and you're gonna have a number of different options as to where you get your image from you could upload it directly from your computer. You could take a snapshot. Weirdly enough, they're using flash the plug in like Adobe Flash. Ah, but I don't have that. You could do it by U R l Now something I want to keep in mind or something I want to tell you is that if you're gonna be linking to an image, make sure it's an image that you own Ah, it's known as hot linking. If you link to an image that you don't really have permission to use and you're gonna be consuming someone else's computational resource is if you're, um, linking to an image that you don't really have permission to use. So keep that in mind because you might be spiking their server costs and they also might replace your image with something a little unsavory toe teach you a lesson. Now you can pull from your albums. If you have a G suite account, for example, you might have a collection of albums on your drive or you could search Now, in this case, I'm just gonna search all these images air free to use commercially or for, um, personal use something to go to stock images. Then after that, I'm just gonna select mountain and hit enter. And then after that, a loaded a few results. I'm gonna just click whatever the first result is. In this case, it's a camel. So click that camel, Camels, air cool, selected, loaded in. Now remember, I said, insert into the cell, which means that it's just gonna fit right inside of the cell right here. And indeed it does, even though it's a rather large emit a couple of things to keep in mind whenever you're inserting an image into the cell first, you can't have both text and images in the same cell, so you couldn't add a caption like Hello there. You can see as soon as I add in Hello. It automatically deletes the image I'm going to control Zito. Undo that and hit enter so that my image pops back. But aside from that, you'll notice that the image automatically shrinks to fit the size of the cell in. If I expand the size of this cell, then it will actually let the image grow a little bit. So what I could do is, for example, merged themselves together that might be another use for merging. So I click on the first cell, gonna hold down shift, and then click on wherever I want to expand it to in this case, all the way to K six. And then I'll just hit the merge sell. But I can choose how I want to merge in this case. Merge all. And you can see that indeed, the image does grow to fit inside by new and much larger cell. 31. Alt Text: you can add all text to your images, just like you would with HTML. So if you right click on the cell, you'll notice in the context many. We have all text. If I right click that, I can provide both a title and a description. This is useful for screen readers, because what the screen reader is gonna do is basically parse through the spreadsheet. And whenever it comes across this photo, remember that if you were blind, you wouldn't be able to see a photo. And I know, but I just had explained that. But anyway, so you can provide all text instead, and the screen reader that reads the screen to them will instead use this. Now, a lot of different, you know, debates on what consists of good all texts or what good all text consists of in this case will just say that the title of this given image is gonna be man riding camel. Now, if the image is meant to portray or meant to kind of convey a particular message, you could put that message as your description. But in this case, let's say it's, ah, a picture of a student. We could say, um, image of John, like so and that would work just as well. Now, obviously, this isn't a image of John, but you get the point and the description. I would try to describe what the image is meant to dio, and then after that, we're done. 32. Images Over Cells: as you saw. Whenever we insert an image, we have the option toe insert it either In the cell were kind of over the cells, kind of letting it float. But I could actually pull this image out, and that is to say, I could make it not in the cell, but rather over the cells. If I right click on it, you'll notice it says, Put image over cells. And that's exactly what I'm gonna dio. And as soon as I do that, the image grows. The reason the image grows is because now it's not no longer contained by the bounds of a simple cell. So if I wanted to shrink this back down, I would have toe click on this image. And you see, we get these little anchor points right here and then resize it. Okay, resize, you'll notice. As I shrink it down from the corner. The aspect ratio is being maintained. If I wanna drag it, I just click, drag it over, shrink it, click, drag it over, shrink it, click, drag it over shrinking and on and on and on, like so. But interestingly enough, whenever I started scrolling around, you'll notice that it stays in place just like that. So pretty cool stuff. Now, whenever you're working with these images, before we had to kind of right click whenever it was inside of a cell to see some of the options we could have for it. But for an image that's placed over cells instead, you get this little options button right here, and you would click that to decide if you want to, For example, provide all text you can, as you can see, reset the size or even replace the image. In this case, though, I'm going to delete the image. 33. Basic Math Functions: I'm really excited for you. Ah, the reason I'm excited because now we're getting into functions. We've looked at all manners and types of ways to select and edit in format are different cells. So we're gonna look at functions now, which is where you really start to see some interesting things that she it's gonna dio. So what I'm gonna do is make a brand new sheet to kind of separate. From what we have going on with our student profile here and inside of here, we're gonna look at functions. If you're, uh, used of programming language before, like, Java script or python or job or C sharp or anything like that, you already know what a function is. And spreadsheets. It's gonna be very similar. But if you've never used a function before, it's kind of hard to wrap your head around first. For all intents and purposes, you can think of a function as a magical command that does things for you, and it's really cool. So here's how we write a function inside of spreadsheets. First you put the equal sign and in the name of your function in this case, it's gonna be all caps for your sheet function. So I'm gonna say add kept and you get the sum of two numbers if you add them together. So then you do a opening parentheses. No notice. I get this little tool tip right here, and it kind of gives me some advice about what this does can. This is the syntax of the function. You can see it says value one comma value to closing parentheses. So basically, what it's telling us is if we put in two numbers separated by a comma, it'll add them together, and it returns to some of those two numbers, Okay. And so these two numbers right here that we pass in the two in the three and the example are considered to be arguments. And so whenever we had a common here, basically what I'm saying is that we're including a list of different arguments, so I could say to And then after that, too, I could say comma. And then after that, I could say to Okay, now, cool thing right here we get this little pop up box right here that says four, which is nice. It kind of gives us a preview of what it's gonna dio and then closing parentheses. Now, if I hit enter, you can see instead of my function right here, I just get the result of the operation, which is four. And if I click back on the cell, you can see in the FX right here. It's kind of showing me what my function was. And the cool thing is that I can come back into this top bar right here and modify it so I could say seven, like, so hit, enter. And now you can see it's modified to be known. If I come back to this cell, I can go back again and again and modify the value and do controls your command. Z undo that little bit that I just did their obsolete there were it enter perfect. Like so there are a number of different functions that you have access to. Its not just ad. You can actually do a bunch of other cool stuff. I'll show you a few of the basic math functions right now, and then we'll get into some of the other cool ones in a second. So I'm gonna select to a is my second cell and I'll go equal sign and then quo Shante, it could see, is already telling you what it does. Opening parentheses just like before. And now we get some of our documentation right here. You passing the dividend and the divisor, or right here, dividend devise. Or so in our example, the dividend is four and two. So that's the equivalent of saying four divided by two So we could say something like nine comma three. Closing parentheses. Enter eggs. Either we get three. Get nine divided by three into you two equals three. Get also multiply. I'll set equal sign. Remember, we're using all caps multiply, and you can only pass into values here for your arguments. So you could say, um, 97 comma five like so, and you'll get 97 times for of whatever that happens to be. Now there's another one very similar to multiply. It's called Product. Now, with product, you'll notice that we get a little bit of a different syntax. Okay, Before, when we looked at what are syntax was, let me do control Z. Undo that if we look at our syntax for these other functions right here, like multiply, for example, you can see it just says argument one, which is the first factor, an argument to which is the second factor. But whenever I do products, the syntax changes. Sold in product, you can see I get factor one. But then, inside of these brackets, I get factor to and then comma and then dot, dot dot. So what the brackets mean is that it's optional. Okay, so if something is in brackets, it means that the argument is optional. And the dot dot dot just means that you can pass in his many arguments, as you want with the other functions, like add quotient and multiply. The arguments were limited. You could Onley pass into, but with this dot, dot dot it means you could pass in as many as you want, so I could do to 756 77 5 close it and then hit Enter and you can see I get that big long number right there. And if I wanted to, I could even format this so I could get like, a percentage or something. Along those lines, the last two kind of basic arithmetic functions I'll show you ar minus so once again we do an equal sign in this case minus, and you can see our syntax. In this case, we have to arguments right here. Neither of them are optional, and we cannot pass in any more than that. So I'll do you 530 to minus 86 and you could see his 446. And lastly, divide. Okay, so do divide. This is different than quotient with quotient. It literally just returns. How many times the number divided and then no remainder. But what? This one, It's a little bit different. So all do you, like, say, nine divided by two. So, Deunan, comma to closing parentheses and you can see we get 4.5. So in this case, it's not just returning the quotient, but it will actually give you the decimal as well. 34. Refining Cells In Functions: a lot of times you're not just passing in scaler values. And if you hear me say scaler value, basically, what I'm saying is just like regular numbers. Okay? Ah, lot of times it's better to just reference a given cell. So let me show you what I mean. In this case, I'm going to say for a seven, I'll give it a total. So do control be your command, be to make it vote bold, and I'll do total. And then after that, in this next column b seven, I'm gonna basically add together to numbers so we'll start with a one in a two. So what Aiken Dio is, I'll do equals ad opening parentheses. And do you remember that syntax that we used when we were protecting ranges and sheets that way of referencing or selecting get different cells or different ranges? We can still do that inside of our functions. So we could say a capital a one you notice a soon as I do that, this a one cell is highlighted and then a comma, and then we might add together a too. So we'll say a to just like that closing parentheses. Now you could see that both of them are selected with these dash borders hit, enter and automatically as them together Walla, we get a 12 just like that. Now the cool part is check out what happens if I were to move these values around? Let's say I change this quotient right here to be 12 instead of three. Okay, you'll notice that not only does the value right here for a quotient change, but also this value right here changes. And the reason is because now we're referencing were not just using straight numbers were referencing different cells. So when the value inside of one cell changes, then the value that were referencing as a consequence changes. And so this cell also changes, and this is how you really start to add dynamism to your sheets. 35. Nesting Functions: where we can really start to spice things up is by nesting are functions. That is to say, you can take the result that one function returns and use that as an argument for your second function. Let me show you what I mean. So and see one ready here. What I'm gonna do is I'm gonna say less than a B s less than a B s. And that's absolute value. If you don't remember that from math class, that's just the absolute value of a number is just how far to the left or right of zero it is on the number line. So negative ones absolute value is one negative 20 seven's absolute values, just 27. So I can show that to you just to really hammer that home a passing like negative 28 and you could see that the absolute value is gonna be 28. So that's your absolute value now and see two, I'm gonna combine two functions together, Okay, I'm gonna say less than or equal to sign. Excuse me. A B s for absolute value opening parentheses and inside of here I'm gonna use another function. Specifically, I'm going to use product and then another opening Princz cause we're still working with a function here. So I'm gonna do to negative 54 negative seven that will automatically give you an even number. So adding another negative and negative? None. So because we have three negative numbers, it'll return Negative. Now a minute. Close my function with a closing parentheses and I'll close my second function with a closing parentheses. Now you can see that our result is going to be 2520. But if I get rid of this absolute function right here, you can see their value now changes. Now we're getting negative 2000 520. So we're literally just taking the results of the first functions calculation, multiplying all these numbers together. And then we're using it as the argument that we pass in for our second function. So now if I hit enter, you can see we get 2520 we can use this in conjunction with what we learned about referencing cells so we could say less or equal to sign a B s. And then we'll do. Let's do the product and then opening parentheses will reference the cell A four. Whoops, a four. And then, well, multiply it by a six. So do a six. And then if we wanted to, we could actually use another function. If we wanted to, we could say something like, Ah, ad. And then we'll do a one Oops, a one comma A to just like that. Now we need to close all of these. So I'll do three closing parentheses and hit Enter and you can see we get a crazy number. But hopefully this is showing you how all of these different bits and pieces are gonna play off of each other and kind of work together. 36. Function Bar Sizing: Now you've seen this ah function bar that we have right here. And any time you select a cell that uses ah function, you can go ahead and click inside of this function bar to kind of modify it. Make the tweaks that you need and you can see whenever you're working inside of this function bar. You're getting access to all the documentation for a given function inside of this bar. But it may be a little small for your taste. And if it is, then you can actually resize that there are two main ways that you can do that. You can hover on this in between line right here and then go up and down just like you would for your rows and columns. Or you could use a keyboard shortcut, control down and control up. That's command up in command, down on the Mac to kind of resize your function bar 37. Averages: Oh, yeah. We have a much firmer understanding of functions now, So I'm gonna go ahead and click on this sheet and slide it over to the right. We may need it in the future if, um, we start needing to experiment with our functions again. But now I'm gonna go back two grades and I'm gonna show you a new function average. Okay, so we can calculate the average G p a as an example. So what we'll do is down at the bottom right here. I'm gonna do control, be your command, be to set this too bold, and then I'm gonna just say average average just like that. And then I could center it if I want to. So I'll go in center right there. Perfect. Looks great. Now what I'll do is I'll start with the g p A. And the way we find our average was we do equal sign average in this case. I want to find out the average G p A of all of the students, so you can see we have a suggestion right here, and it's showing us the same syntax that we use for protecting our ranges and are sheets, which is that we can specify a range. Now. The way we do that is with an opening print season will say F two and then colon two separate F five. Now that's the end of our range. Closing parentheses. And then if I hit enter, You can see we do indeed get on average G P A of 3.4 to 5. Now, when you're working with GPS, it's not normal toe. Have it go all the way to the 1000th decimal place. Most of the time, you only go to the 10th decimal place. So what we can do is select this cell, go into our function bar right here, and then we can round our answer the way that will round. Our answer is by using the round function. That's right. So I'm gonna add round, and I'm just gonna wrap this function inside of these parentheses right here. Now, if I click on round, you can see our documentation rounds a number to a certain number of decimal places according to standard rules. So the first argument that we pass in is our value, which will be the result of our average function and the second optional value that we pass in as an argument is going to be the number of decimal places that we round Teoh. So in this case, I want it to round two, the first decimal place. So I will say comma and then one just like that. Hit. Enter. Let's see if it works. Indeed it does. It rounds it off to the first decimal place, so we get 3.4. 38. Using Means or Medians: There's another function called median that you'll run into, and you may ask yourself whether you should use the mean that is, measure the mean or the median. And whenever we're calculating the mean, we're adding up all the different scores and dividing them by the total number of scores. That's your average, and that's what the average function does. When would you use the mean and when would you use the median? Well, it does depend Ah, lot of times people are going to use the median. If they're measuring tendencies, that is. You know, let's say you're measuring someone's height. You know, most people tend to be in the five foot 10 range, but let's say you're looking at test scores, and most people score 85% on this test. But a couple of people scored perfect, and a couple of people scored 12%. Well, in that case, you have some unusually higher, unusually low scores that air distorting the mean. And so whenever you're getting these super high, super low scores that distort the mean a lot of times it's better to use the median. And this is partly why, uh, or how statistics can mislead you is basically the way that people choose to measure if they choose to use the median or they choose to use the mean it can actually distort whether or not, um given statistic is representative of the finding. 39. If Statements: now there is a problem with our sheet right here, which is that I'm calculating the average. And, you know, I may want to calculate the average for, you know, math, science, history, all that stuff. But at the end of the day, if I'm gonna be extracting a lot of tendencies and trying to pull different information out of this sheet, it might be a good idea to put all of the, um, measures of these different tendencies on its own separate sheet and then reference this data from within that sheet. We'll talk about how to do that later. But for now, I'm just gonna select Rose six and clear the data by hitting the delete button. Now that we have that, I'm going to show you something really cool. Which is if the if now I'm gonna go to sheet six. And my example is just our scrap paper right here where we're kind of playing around with functions. Now, if you're familiar with programming languages, you know what a conditional statement is, right? An if statement. But if not, then I'll kind of walk you through it. So we're gonna be creating a conditional statement here and you have conditional zor conditions in your life all the time. For example, if something is true, you might do something. And if something is not true, you don't do it. An example would be, You know, you really want a book and it costs $10. Well, if you have $10 or more, you will do it. And if you do not have $10 or more, that is, if you have less than $10 you will not by the book. And so that's an example of a conditional in your life. You can actually represent that programmatically. Here's how we do it in my cell here. I'm just picking a random. So I'm gonna say equals if because this is, ah, function here and then right after that we do opening Prince sees. Okay. And here is where we put our, um our kind of conditional logic and are conditional logic is gonna be, Let's say if five is greater than two now, this is true Now, comma, this is where I put what I want the value of this cell to be. If this condition is true in this case, I'm gonna do double quote. Hello, double quote. I'll talk about that. And second matter of fact, yeah, we'll do. Hello and then I'll do comma. And then I'll say colon or comma. Double quote. We failed. Double quote, closing parentheses. Now if I enter, you can see that the value here is hello because five is greater than to the value was Hello. Now, if I modify this and if I say one is greater than two and hit enter now you can see the value is we failed. So in other words, according to this condition, it determines what value we use. If this is true, we use the first value. If this is false, we use the second value. Now, you may also be wondering about these double quotes right here. Um, basically, whenever we're working inside of these, if functions Google sheets, the computer will say, is not sure if we're specifying, let's say, a function name or if we're specifying text that we want to put inside the cell. So to tell Google sheets Hey, this isn't the name of some function. This is text like I would put a name here like John. It's just regular text just like that in order to tell Google Sheets. That's what we mean. We wrap the text in double quotes just like that. And if you're just working with regular numbers, thes scaler types right here, you can just use the number as it is. 40. Adding Ifs to Profiless: Now we can see the if function in action, we're gonna go back to our grades sheet by clicking on this bottom bar right here. And I'm gonna add a new column. Can I call him? And in this case, I'm just gonna say passing and what we'll do is we'll reference the value in, let's say, English or better effect. We're gonna do signs. So where do our math will be passing meth, Okay. And then for each of these cells, we're gonna do it if we're gonna say equals. If opening parentheses in, Here's the condition. Okay, if in this case, be too okay, be to the value and be too is greater than 64. I think that's a passing grade. Or better yet, is equal to or greater than or equal to. We'll do equal to we'll do greater than 65. I think that's a passing grade in school. It's been Well, then I'll do colon. And then after that, I'm gonna do yes. And then colon, no. Okay, close it. Closing parentheses. And just like that, you can see that we get to know which is kind of strange if you think about it. because if we look at her function, B two is the value that we're referencing. And it just so happens that B two is 99. So you have to ask yourself, why is this failing? And 99 is supposed to be greater than 65. 41. Comparing apples to Apples: Hopefully you got it. The reason that this is failing or that it's evaluating to falous is because in Google sheets, eyes were comparing apples to oranges were comparing percentages to regular numbers. A whole number in this case. So if we want to compare a percentage to a percentage, we need to actually format the 65 the number we're comparing it to as a percentage, and you can see as soon as I add that percentage sign it evaluates the yes, I hit enter and now you can see Johnny Boy is indeed passing math. 42. Using The Function Button: Now you may wonder if there's, ah faster way of typing in all these functions because we are about to have toe use this if function for every single one of these rows right here. So you may wonder if there's a faster way instead of hand typing everything. And indeed there is. Ah, First, there's a faster way to add functions inside yourselves. Select the cell that you want And then after you select that cell in this case, Jessica on I three go to this little ah, Greek letter right here. That's the symbol for functions. Then after that, hit your drop down menu and you can see that we have all these different functions some average count Max men and then a bunch of other functions that are categorized In this case , they will need logical anything that has to do with conditional if and or that type of thing is gonna be considered logical. Okay, We also have math, so you'll come across things like rounding So on and so forth quotient. A lot of the other things we played with now, this case, I'm just gonna do if and that might be a little faster and a little easier on your hands than typing something over and over again. Now, once you've loaded in your function, you'll need toe make selections as to what values you want to use. Whether you're using average or some right, you need to reference a value or references cell rather, so what you could do is type it out as we did before. But honestly, there's a faster way to do it. You can just click on the cell that you want to use. In this case. As soon as I click B three, you can see it works like a charm. And if I wanted to select a range of values, of course I could hold down control or command. But you see that doesn't select a range of values. Instead, that adds a list of values, and that's important to keep in mind. I do control Z or command Z to undo that. So what would you dio if you wanted to select a range of values? Well, make sure that your in a range selection mode, which is gonna be controlled E or command E and then you can go and drag it across just like that, and you can see that I'm selecting an entire range. So that makes things a lot faster for you. Now, after that, I wanna add Compare this. So I'm gonna say if b three greater than 65 they will do groups. We almost forgot our same mistake percentage. I will say double quotes. Yes. And then close it off. Double quotes? No. And then we're going to go. Let's see what this evaluates too. And, yes, Jessica is passing math. 43. Copy and Paste: lastly, to really help you work a little bit faster with functions, you can see that a pattern is emerging, which is that we have this I column right here where we select and work with a value from the B column. So you see that little relationship going on right there? The beautiful part is that Google Sheets is automatically picking up on that pattern for us . And so we can copy this function now and paste it in in the subsequent cells and it will automatically replace be three with before. Let me show you. I'm gonna select this cell hit control C, your command seat. A copy. Go down to my new cell hit Control V or Command V, and you can see we automatically get no. One. If we look in our function bar, you can see it's changed a B four greater than 65. I could do the same exact thing for Jack. And now you could see before is replaced with B five and on and on and on, and so you could see that's gonna save you a lot of time instead of having to write out all of these functions by hand, you can insert them with the functions button on the toolbar, and you can copy and paste them once you see this pattern emerging. 44. Comparing Strings (intro): Okay, You've got a really firm grip on the if function right, you learn you can pass in these different conditions. And if these conditions are true, you can use a string right, the text or a number as the value of that evaluates to true in a different alternative value. If it evaluates toe false. Now I want to show you different types of comparisons that you can make. So I'm gonna go to my, ah, scratch sheet right here and then what we're gonna do is just go to a random cell and we're gonna set up a conditional. So we're gonna dio if and then I'll just hit enter to get that filler for me. And then I'm going to start off by comparing strengths. Can not only can you compare numbers, but you can also compare strings. Now, I'm just gonna say Fu in all caps equals Fu with all lower case comma And then we'll do a one which equals true in a zero which equals false, just like in computers, one is on zeros off. Okay, now, if I hit enter, you can see it evaluates to one. Now I'm gonna re click on this cell and then I'm just gonna play around with this inside of the function bar. And that's one of the really great things about the function bars. You can make different tweaks and play around with your functions and get this evaluation right here as a little pop up kind of flash message, and you don't have toe keep hitting, enter over and over again. So what have we learned from this? First we learned that there is an equal sign so we can check if things are equal. But aside from that, we learned that we can compare strings. Not only that, we learn that lower case words or letters or equal to uppercase words or letters, and you can see if I have modify this and make this a capital F it's still evaluates to true. I make this old lower case. Doesn't matter, still evaluates to true. It is only when I actually changed the string to something different, like foe instead of food that we started seeing an error so they have to be identical to each other, the strings dio, but it doesn't matter if they're lower case or capitalized. It's all the same now something else that we can dio is we can create comparison operators like this, for example greater than all right. We've seen this before. Now you see that this evaluates to true, which is interesting, right? So what are we learning here? Well, let's try a different word. Let's try toe instead of Fu. And then after that, let's set it equal to each other. Okay, so we know they're not equal to each other now. If I say like greater than you'll notice that toe is greater than food, that doesn't really make a lot of sense. So what's going on here? Well, if we count the characters that count the letters, we see that there is the exact same number of letters in tow as in Fu. Let's try to make it lower case. Let's see what happens there. Foo you see it's still evaluates like so So what's going on? Well, it was actually happening. Behind the scenes is we're not comparing the length of the strings or anything like that. Rather, we're comparing, um, the numbers that they add up to because at the end of the day, these strings these characters are representative or represented as numbers inside of the computer. You don't really need to understand this for right now. Um, we'll come back to this later. Let's go ahead and explore some other options that we have for if okay, we'll start working with numbers, so that's a lot easier. So I'm gonna say if five is greater than two, we know that's true. But now let's do. If five is greater than five. False five is equal to five. True, and some other things that we can do is if five is greater than or equal to five, which is also true. We can also switch this around too, less than or equal to five. So those are the main operators that you have access to. You have equal to less Stan greater than less than or equal to and greater than or equal to for your if function 45. NOT (the inverter): There are a lot more things that we can do to make our conditional is more complex and more specific. For now, I'm just going to show you one more logical function that you'll might need, which is the not function you can think of not as sort of the invert. So whatever you put inside of a not function, it's gonna tell you the opposite of that. Let's look at an example I'm going to say equal to not and this example I'm gonna say five is greater than two. Okay, now five is greater than to remember. This function is like an inverter, so whatever is true is now false, and whatever is false is now true. While that was deep, let me hit. Enter, You can see now it's false indeed. Indeed it is now, if I'd put something that would typically be false in here, like I'll say, not five is less than two now. That's obviously not true, but you can see it inverts it. So now it is true, and you can use this alongside your if function so we could say something like, You know, if let's say if five is not greater than two. Okay, if five is not greater than two, which is false, then we'll do one. And if it is greater than two, will do zero. Okay, Now you can see we're all set. And what does this evaluate too? Zero, Because it's false. Now, if I flip this around and I do, if five is not less than two, then it becomes true. So just remember you can use this with your riffs to kind of get the logical opposite of what you normally would. But you could just as easily get this logic by getting rid of this, not operator right here or this, not function in the context of sheets and just flip around the symbol. In other words, change the sense. And in a technical term, just flip the symbol, and that would give you the logical opposites. So sometimes working with a not function is not even necessary. 46. Sorting: All right, So we're gonna move away from the world of functions in formulas, at least for the time being, and we're going to start looking at how we can sort through our data. Now, whenever we're sorting through our data, we're gonna need to go to the data menu in the application menu, and you can see we have a couple of options here you can see sort right at the top source sheet by column J and then a through Z. Now, the reason it says Jay, is because that happens to be what I've selected. Now you can click any cell you want, but whenever you're sorting your selecting by column, so as long as you select the cell that's in the column that you wish to organize, then you're gonna be good to go. So with our example of, let's say, organizing our names alphabetically. Before we tried to do that by manually kind of moving Jack around to the top, and we saw that was a bit of a pain. So instead, we're going to go. Select column may go to data, and you see sort sheet by a through Z where Z through a So in this case, I'm gonna do a through Z. It's gonna say, Hey, you're trying to edit part of this pays that shouldn't be changed accidentally, and that comes from probably our protections. So in this case, I'm gonna go ahead and hit. OK, After I do that, it should start to flip things around. Now you can see we've run into a bit of a problem, which is that the header was included as part of the data that we sorted, and we don't want to do that. So next time what we're gonna do controls your command z toe, undo that. And instead I'm going to select this column and we're going to do it a little differently. We're going to go to data, and then you could see it says instead of sort sheet by column. We're going to sort range right here just below that. So I'm gonna go ahead and sit, sort range, and then we get this dialog box instead. And so this gives us a little more control. Now you can see there's a little check box of data has header wrote, and this is how we avoid the problem that we just saw. So I go ahead and click dater data as head a row and then you can see it says, sort by student like so and that's really the only option that we have. So that's all we have toe concern ourselves with Now we could say at another sort column, but in this case, we're just sorting one column. So I'm gonna go ahead and hit sort. It will take a second to load, but you can see as soon as it does we're good to go. But we have another problem. Oh, my gosh. Andrew, how many problems do we have? What we have few. So before John had the score of 99% on his math and we can see that if we do controls your command, Z, flip that back. So what we want is we want to soar this column, but we want all of the other columns to kind of flip around to match that. The trick to all of this is to think about what all your sorting. So in the case of our students here, yes, you want to sort them alphabetically. But we also want these different rose right here to match up so that we are rows and columns matchup. We don't wanna lose data and mismatch. That would be a disaster. So the trick here is to select all of the columns. I'm gonna hold down shift in select column. I as well, and everything in between gets selected. Then I'm gonna go back to data. Then I'm gonna go to sort range. Then Data has a hetero and then sort by student alphabetically. And then what will happen is that all of them are going to get sorted alphabetically, so everything stays as it should. You can see John doesn't lose this score. Jill still has 42 then 99. And I can see that if I go back and forth. Jill still has 42 in 99. Jessica 84 99 87. Then I flipped back against He just gets 84 99 87. Go back. 84 99 87. Same thing with John. Same thing with Jack. So none of the data got flipped around or anything like that. So just keep that in mind whenever you're sorting your columns. If you want them all to be sorted at the same time, you have to select all of them. If you only select one column, that's the only column that's gonna get sorted. And if you have these keys like we have right here, then that can lead to some undesired consequences. 47. Filtering By Values: aside from sorting through our data, we could also filter through our data, and I'll show you how to do that Right now. Let's say, for example, I only want to see people who are failing math kind of want to single some people out. What you'll do is you'll select the data that you want to filter through in this case, whether another passing math. So I'm gonna select the I column. And then after that, I'm just gonna go to data and create a filter. Now, after I create a filter, you can see that this whole entire column lights up in green. And that's how you know you have a filter active. Now I can click on this filter icon at the top, and then I could filter according to what I want Now. You could filter alphabetically. You could have filter according to a condition, and if I click down, you can see I can filter according to the conditions that they've set here, if the text is empty, if the date is this or if it's greater than that so I could work with numbers day, it's text where I could set my own custom formula which will look at later. But in this case, all I want to do is work with some values. I want to filter according to the value. Now, I could clear all of these out by hitting clear, I could select all of these values. Now if I had clear that is I select none of them. Then none of the data appears. And if I hit OK, you can see now none of the data appears to go and click the filter again and select all of them. Now instead, what I want to do is just see who's failing. So it turned off these two values right here and hit. OK. And now I can see the only student who is failing is Jill or if I want to see people who are just passing, congratulate them. I can select Yes and turn off. No hit. OK, and now I only see the people who are passing. Now keep in mind that anybody who has access to your documents and can edit the document can see this filter and they could modify the filter if they want to. If they have permission, edit the document. So just keep that in mind. Lastly, you might want to turn off your filter. The way you do that is you go to data again with your filter active, and you just say, turn off filter right here. Keep in mind whenever you turn off your filter, though, that it's gone forever. None of the settings, right. The filter parameters that you set are going to be saved. And so, if you want that filter back, you're gonna have to go back, make a new filter and specify those parameters again. 48. Saving Filter Views: Now, if you want to save your filters than what you can do is save them as a filter view on the way that we do that is quite straightforward. Very similar process are almost identical to what we did for making a filter. First, I'm going to select the column where I want to get a filter app, and then I'm gonna go to data and just below where it says create filter. You see, it says filter views. And then I can create a new filter view. And just like before, we need to specify our criteria. So what I can do is go to right here and you can see it says Update, range. If I want, I can rename it, duplicate it and so on. Or I could go to the filter views button to the left of the Functions button. And then right here you can see filter view options, and I get the same exact options as I had before. Right now, another easy way to change things is, for example, to go at this top bar right here. You can see we have black columns and black rose. I'm gonna change the name to failing students and then to set the criteria. All I have to do is go to the filter button, just as I did before. And this time we're gonna only select the failing students. Make sure to give your filters a meaningful name. Otherwise they can be quite confusing, especially if you have several fills their views. And now you can see we're only seeing the failing students. And then if you want to turn off the visibility of your filter, you get it X, and that will get rid of it. Or you can go to the filter view button, and that will also turn it on and off. Now you can see we have are failing students inside of this ah little contextual menu here for this little pop up menu. Now, we're going to see if this filter still applies. Whenever I had new rows of data. So, general down below, John, I'm gonna go ahead and add a new person in this case will call her Jennifer, and I need to unformed at this. So I'm gonna do control, be or command be. And then I'm gonna align this flushed that left. Then we'll add in a few things. First, we're gonna make sure that she's failing math, mediocre it Science Pretty good at history and just stellar English. So what do 92? And then after that, we'll give her a 3.2 for the G P. A. Running low on funds will give her a dollar 20. And then her start date is the same as the other students the 15th of August 2018. And then we could just copy and paste this function that we wrote before, So it'll have the same values in this case. I'm just going to control see your command. See, now, whenever you pasted and you could do control of your command, you to pace it or you could right click right here. And then if you look at your pace special, you can see it has paced formula on Lee, and that is very useful so you can just paste in the formula. Make sure, though, that the cell that you're referencing for this conditional statement right here is the correct cell in this case. Ah, B six, which is indeed the correct self. Now we're gonna turn back on our filter view by going to filter views, and we're going to see if this new data is included in our filter view. And indeed it is. So we've learned how to create a filter view. We also learn how to rename it by going to our filter view options right here. You can also update it and even duplicated if you want to do something similar. But most importantly, we've seen that if we add new data that's included in one of the rows or columns were filtering through that is still going to be included in the filter view. Now I'm gonna go ahead and turn this off by going back to filter view and then hitting none . 49. Resorting Things Out: something that you may have noticed here is that whenever I added in Jennifer now, this particular table is not exactly an alphabetical order, because obviously, Jennifer would be somewhere between Jack and Jessica. So this is an important lesson. Remember, if you're adding in new data and you sort of the data before, if you add a new data that throws that sorting out of whack, you're gonna have to re sort it. So we can just select all of these columns again, just like we did before. Holding down shift, Go to data source the range. And then from here, let it know that data has ahead of row, and then we'll sort by student a to Z, and then we'll go and hit OK on that. And now you can see that Jennifer is right where she's supposed to be between Jack and Jessica. 50. Dropdown List: next, we're going to look at how we can add a kind of drop down list of values. What you can think of this as is a way of providing a list of default values that you would want someone to include inside of here. So what you could dio is you could say, for example, select any cell that you want, and then after that, you can go to data and then data validation. And then I'm gonna move this over a little bit so you could see now you'll notice we can select a cell range for where we don't want to add this drop down list. In this case, I just select one So but if you want to add more, you can. Using the format that we already know first, the sheet name separated by an exclamation point than the cell or the cell range that you want. Then after that, we select where we want to get our list of values from, and that's gonna be the criteria. So you can see we have a large number of sources we could get our values from the two most common are gonna be list from a range or list of items. So if you say list of items, it's gonna be a comma separated list of different data values camp. Now I'm gonna do one comma, two commas, three comma four just like that and you can show a drop down list and sell. You could turn the owner off. Um, and then you could either show a warning or reject the input if they enter invalid data. So, in other words, if they enter data that isn't in this list, we could tell them that's not gonna fly and reject the input were Just give them a warning . In this case, we're just gonna give them a warning and I'm gonna hit. Okay, then if I go to this drop down, you can see these values are listed. And if I click any one of them, that will be the data that's entered. But the second I try to get slick and enter a five and hit enter, like so you can see I get this little red mark right here, and that's the, um, Google sheets way of letting me know. Hey, this input isn't really valid. It's letting me do it. It's just giving me a warning that I probably shouldn't do it. Now, if I want to turn this off, I can. I'm gonna go in, click on the cell. I want to get rid of the drop down for go back to data data validation, and then remove the validation. And now you can see that error goes away. And I could also clear out that data if I want to. Now, another common way to do this to get that list of values or valid values is to pull it from a range. So we're gonna do the same thing we did before go to data data validation. And then I'm just gonna get my list from a range. I could type it in by hand where I could click this grid and then start dragging wherever I want to get my range from in this case from math hit, okay. And then reject the input If it's not in this list and then hit safe, Okay. Now, if I go back to my drop down, you could see all the potential value 77 for example. But if I try to get slick and do something like 89% hit. Enter. You can see. It says there was a problem. I've entered some invalid data, according to the rules. And then it changes it back to the original value, which was 77. Now, obviously, in this case, I have no reason to do it to arraign themselves. So I'm gonna go back to data one more time, hit data validation, and then remove that validation. That way we get rid of our drop down list. 51. Conditional Format: something else. Very cool that you can do is conditional formatting. And what that means is, well, we've already learned formatting is right. Changing the color, the font style, all that different stuff. Well, we can actually change that according to a certain condition, just like we did with our if statement. So in other words, weaken style a given cell or given range according to the criteria that it means, or whether or not it meets a condition. So what we can dio is select these different cells that we want to style. So in this case, I'm gonna select all of these right here with shift. And then after that, we can go to format in the application menu and then scroll down to the bottom where it says conditional formatting. Okay, Whenever I click this, you're going to get a little drawer menu that pops out on the side right here. And you can see there's two different tabs single color or color skill. We're gonna stick with single color for now. You notice you can set your range where you want to apply this conditionally and then the rules. Okay, so we can set this, According to whatever we want. Really? Ah, but in this case, what I'm gonna do is I'm gonna set this according to the value that is inside of it. So I'm gonna say, if the text is exactly and then I'm going to say yes. Then I'm gonna set my style. So in this case, I'm gonna set the background color to be black and the font color to be white. This is gonna make it easier to go between who's failing and who's not. Then I'm gonna hit, done, and you can see we're all set. Look at that. Now everybody who's passing gets a Yes. And the beautiful part about this is this function has the word Yes, hard coded. So we shouldn't run into any problems unless somebody tries to mess with it. Now we're gonna add another rule. In this case, What we're going to say is we add another rule and we're gonna select our range so we could select it manually. In this case, I will by saying from my to Colin Teoh I six and that'll be my range, and I'm going to say text is exactly no. And then I'm gonna set that background color to be pure red and the text color to be white , just like so, obviously could tone it down, even selected custom color if I want to you. But I think this will be good. I'm gonna hit done now. If it's yes, then it's green. If it's no, then it's red so you could see it makes it a lot easier to kind of go through the data. But whenever at a filter view like let's say, I turn on my failing students filter view, it's the right here. I just see all of the nose. And another cool thing that I could Dio is, Let's say, for example, I could turn off my filter view and then I could freeze this column so I'll select this column right here and then I'm going to go to five. You freeze one column just like that, and now I could just grab their names and match up whether or not they're passing or failing, just by sliding it across and taking advantage of the freeze right there. Just like so. I'm gonna go and turn off that freeze. I'm just showing you how different things could be combined together. Teoh kind of make some interesting things happen 52. 7: the other way toe conditionally format your cells actually pretty interesting. And it's gonna be a way of really kind of ferreting out the details of the data and where everything's at. So what you could dio is, for example, let's say for G p A. We want to add a color system to indicate if their G p a is low or high. Well, this is a little different than a pass or a fail. We don't really need green and red. We just wanna have color indicators that show us if they're doing okay. If they're doing well, kind of like if you've ever looked at ah, population density map, right. They have a very bright color, for example, for very dense areas or a very dark color and very light areas for the sparsely populated areas. We could do something very similar for G p A. So I'm gonna select this given range right here for gp a goto format. Conditional formatting. Then after that, I'm gonna go to color scale. Okay. Now, for color scale, you select your range first and you have your format rules and we'll just go with the default for now. So we can appreciate what it is. Now we're going to set a minimum point in a maximum point, okay? And the minimum point is gonna be what the smallest value is and you can sleep. Numbers or percentages in this case will go with a number. In this case, the lowest value happens to be 2.2. And you could set a number as the highest value 5.5 and then we can select a color. So I might do, you know, very, very dark green for low values. And you can see I'm changing it now toe where the light values are considered to be higher until it's pure white, which is the highest value. And then I could set, let's say, very bright green for the higher values. So now you see our coloring system for the darker values. It's going to be a lower G p A. And for the lighter values, we have a higher GPS. We can also specify a midpoint. If we really wanted Teoh in this case, though, I would just select somewhere between the two colors that you see here. Um but honestly, I'm just gonna leave. It is none and you can see the very logical system, and you can immediately identify who's doing well who's doing average and who is tanking. 53. Deleting Conditional Formats: If for some reason you want to get rid of these, then you can very easily do so in the way that you would get rid of these is by selecting them like so I'm doing now and then go back to your conditional formatting and then you'll see the format rule just going to hit the trashcan right there and that will immediately get rid of it. Now, there's one more conditional format that I want to add. And it is for the lunch balance. I'm gonna selected this column. We got a format conditional formatting. And then after that, what I want to do is I just want it to be read if they owe some money. So in this case, I'm gonna say, go to the format rules and I'm going to say if the number is less than zero, then make the color maybe like a pink, and then we'll do white for the text. And nobody is less than zero right now, so you can see that that's not gonna fly. And then I'll hit, done. But now I'm gonna change one of their values. So I will change Jennifer's to be zero so double click inside of here and I'll set that to be negative 1.5. So she owes a little bit of money, and now you can see it immediately turns pink, so hopefully you can see how these different color systems can really help you parse through the day. 54. Split Columns: now, Obviously, if we're gonna have a student profile that makes sense to have their last name because the second that there's more than one John at this school, then we're gonna be very confused. So what we could do is simply go over to this column here, hit the down arrow or right click other way and insert a column to the right. That would be easy way to do it. I want to show you another cool way that you could generate another column, which is to basically split one column into two. So the way that you do this is let's say it instead of Neymar student. We have first name comma last name. Well, it just so happens that inside of Google sheets, if you provide any form of demarcation Ah, comma, hyphen, something like that. Then you can actually split up this cell into two columns. According to whatever symbol you have, all you have to do is select the symbol or the select the cell. Excuse me, then go to data and hit split text to columns. Now, you'll get this little, um, little pop up right here, and that's the separator. And you can click what you want to separate this cell based on now automatically, it detected the comma, but you could use semicolons, even just a space where you could customise it and use your own symbol. In this case, it automatically detected that I had a comma. And so it separated based on that. But one of the problems that we have here is that it completely ran over my math header. It's now completely gone, as you can see here. And that's not exactly what we want to do. Controls your commands, you to undo that. And instead, I'm just gonna rename this header row. Are this header right here? First name. And then I'm gonna insert a column to the right just like so, just like we learned before. And then I'm gonna name this one last name and we'll come in real quick and adds in the last name. So hit Enter and I'll say Boudreau. And I'll say Jennifer Lobo. Um I guess that's how you would write that. And then we'll do Jessica Since Simpson Stein. I'm not sure. Uh, no, the Jill ah Smith will be lazy Now hit, Enter and then we'll do John Remington. That's cool name, right? So we're all set. I just wanted to make you aware that you could indeed split up your columns if you wanted to. But remember, if you do decide to split your columns according to some kind of separator that it might run over any of the subsequent columns that you do have. 55. Removing Duplicates: As your data grows and grows and grows, you will eventually come across duplicates. And it's pretty nice to be able to have a quick way to just get rid of all the duplicates. So what I'm gonna do is I'm just gonna copy a few of these different rose right here. So I'm gonna copy Jessica by right clicking, copying and then down below on Rose seven. I'm gonna paste special. All except Borders. Same thing for let's do John. When a copy, John and I'm gonna pace the Monroe eight pay special, all except Borders. And then we will do one more. Let's do, Jack. Why not? And I will do copy, paste, pay special, all values except for the borders. So now you can see we have quite a bit of duplicate data. Now, we might want to get rid of this. So what will need to do is select the data where we want to remove the duplication. So in this case, we want to select pretty much everything. So I'm gonna start at row to hold down shift and click on road nine to select pretty much everything. Then I'm gonna hit data and then After that, you could see remove duplicates. Now, this is a relatively new feature and one of the nice things about Google sheets as they include these little new buttons right here any time something is considered new. Now, as a whenever you're watching this video, it might not be a newer feature. And so whenever that time comes, you know, ignore it. But whenever something new is added to Google Sheets, you can check out that it's new and then kind of investigated. If you want to, on your own time. Now, I'm gonna hit remove duplicates. Now it's gonna ask if data has ahead of row, it doesn't. And in this case, we're gonna be checking through. Ah, pretty much all of these columns. We can get rid of a few of them. We don't need to get, actually do most of them Just a good number. So I'm gonna unclip Let's a ah, we'll do call him T. And actually, you know what? We'll check them all. Why not? Because we're crazy. So I'll go ahead and check that back on. There we go. OK, and remove duplication. It's asking if I I'm sure about this. I am pretty sure about this. Three duplicate rose found and removed. Five unique rose remain as probably the coolest thing about it is not only will you find duplicate ranges, but it'll identify and pick up on the fact that hey, these entire rows have been duplicated. And now that we remove them, you can see that everything is back just the way that I left it. 56. Trimming White Space: now, I'm not sure if you're programmer, but one of the things that you could do with your sheets is you could actually put them into a database, which is really cool. So you could load this into something like Mongo DB or my SQL Or And and those are just databases containers for information. Ah, but whenever you do that, you might need to make a few changes. Before you put that data into a database, One of the things that you might want to do is make sure that you've removed any kind of trailing white spaces. So what you can dio is, we'll select the headers in this case. Then we'll go to data and then you can see right here it says, trim white space. All this is going to do is get rid of any trailing or leading whitespace. But something important to keep in mind is that any kind of line breaks or non breaking spaces that you have are not gonna count. But let's say I add a space here at the end, and then I go away. You can see that now that space is kind of stuck in that cell, but if I go back to data and then trim the whitespace. Whoops. There we are. Trimmed the whitespace. You can see Walla. It has been removed. 57. Checkboxes: No. I'm gonna show you how to insert check boxes. And aside from that, I really want to highlight that. You can combine a lot of the things that I've taught you up to this point and use them to create some pretty interesting things. So what I'm gonna do is show you how to make a check box. So in this case, I'm gonna make a new column called Graduating. And then, just below that, I'm gonna just select where I wanna put my check box in this case, K one for K two and I'm gonna hit Insert down below. I'm gonna say check box. Or now, if they're graduating, it hits check. And if they're not graduating, I hit Uncheck. Now that's a very, very manual, so I'm not gonna do it that way. When I select this and clear the data, you can actually set your own custom check boxes, and I'm gonna show you why we might want to do that. So in this case, I'm gonna select the cell underneath, graduating, and I'm gonna go to data and then data validation. Now, before we use this to create a drop down of values valid values with list from a ranger. A list of items book down below. We also have a check box. Then we can use custom values if we want to. And the one value that's required if you're gonna use custom values is checked. So we're gonna say graduating and then unchecked is optional. But I'm gonna go ahead and set it to feeling. And then after that, we're gonna be good to go, someone to hit save. Now it may not seem very useful, but it's gonna allow me to make my check box automated. In other words, I don't have to check and uncheck it. You can see, though, that if I uncheck it, it's equal to failing. That's the value and checked is equal to graduating. Now I can take advantage of that and put an if inside of here. So right now, the value if I double click inside of this box is failing. But what I can do is I could say equal to and I'll say if I'm gonna wrap this in parentheses, then I'm gonna go and I'm going to say if ah, where is the g p A. Right here? Let's say if your G P a is less than two. You're failing. So we're gonna say if G two is greater than two, then you are going to be Let's see if G two is greater than two. And you're gonna be graduating. Whoops. Graduating. Excuse me. And then otherwise you're failing. Now you can see that this box is automatically checked. And if I try to uncheck it with all my might doesn't matter. I can uncheck it. The only way that this would be unchecked is if I change this to, ah, 1.5, for example. And then you could see it automatically unchecked. Obviously, I don't want to take that away from Jack. He worked very hard for that. G p A. But I'm gonna go and copy this control. So your command, See? Then I'm gonna select all of the cells underneath and control of your command V, and you can see I'm copying the logic. But unfortunately, they are not referencing the same cell right here. So if I double click, you can see I'm still referencing. Ah, well, no, actually. Excuse me. They're all passing. Never mind. I didn't think they would all be graduating sigh. Um, let's just double check our work, though. And make sure they are indeed referencing the correct cell. Yep, G four, this one is referencing G five, and this one is referencing G six. So they are all indeed referencing the same thing. One of the beautiful parts about copying pasting functions. I was just a little surprised by that, and I thought something went wrong because they were a whole graduating. 58. Setup: Now what I'm going to show you is how we can extract data from this she and kind of pull it into another sheet. So what I'm gonna dio is I'm adding new sheet, and we're gonna call this sheet insights. So I'm gonna click it, drag it over to the right, and then I'm gonna hit this down arrow, rename it, and I'm gonna call it insights. And you could color coordinate. If you want to click the arrow again, change the color maybe to something else. Like a like a blue. Maybe kind of a chill blue. There were. Ah, but either way, now that we're all good to go on that what I'm gonna do is I'm going to add some headers. So first we need to format this header right here. So I'm gonna set it to be bold, and then I'm gonna set to be center, and then that will be good for now. The next thing that I'm gonna do is I'm gonna get some averages, so I'm gonna say average math grade. Kind of a long name, but I'll copy and paste it tab over. Tab over. Tab over. Just like that Now you'll notice whenever I tabbed over and pasted all this data in that it didn't format it, which is quite strange. One option that I could do is pace the format and that would work. But instead, honestly, an easier way to do this would just be come in after the fact and then just format everything. So I'm just gonna go ahead and form at this again, center everything. And the last thing that will need to do is to resize thes columns right here. So that way they actually fit the data that's inside of them. So we'll go ahead and click on all of them. Then we'll right click and we will go down to resize columns a through D. We'll get a little pop up and we're just gonna make it fit the data that's inside of it. Now, after that, I'm gonna change this to science average science grade, and I might need to resize these again. But every science create average history grade whips. There we are. History grade. An average English grade. Perfect old. Next, we're gonna need to add a g p A. So I'll go here and I'll add average G p a. Now, given how Maney averages there are, you may think it unnecessary to say average, but I may put some other types of data in here over time. And generally speaking, it's better to be explicit with your data than implicit. You don't want people have to do too much guessing. And so I'd rather have a little bit of a lengthy header than a little bit of a too short of a header. I want people to know exactly what it's about. I might need to resize these again, so I'll go back. And all right, click and resize. Where are you? Okay. I have to select the whole column. Scuse me. There we go. Resize columns be through. Ito fit the data. That's inside of them. Perfect. Now we're all set. Okay, So now what we need to do is figure out how we're gonna take the grades right here, calculate the average, and then put that inside of this ship 59. Referencing Across Sheets: The best part is it's actually surprisingly easy. So what you can do is you can just go into, let's say, average math grade right here and just create your average function, right? Just like so And then after that recon G 0/2 grades and then we can, as you can see, get direct access to our average functioning from inside of here. Pretty slick. Right? So in this case, I'm just gonna select from here to here, and now you can see we're using that same exact syntax. Were specifying the name of the sheet grades separating it with an exclamation point. And then we're specifying a range. Now I can go ahead and close this while I'm inside of grades. So I will do that and hit. Enter. Now we'll go back to our average math grade, and you could see we've easily plugged that data in. So I would ask you to go ahead and do the same thing yourself for all of the other grades as well as the G p A. I'm gonna do that real quick. Make sure the pause a video, take your time, work your way through it and then get back to me now. I'm not sure how long that took you, but it took me about five seconds to do that. And ah, you may wonder how it could take me five seconds to do it. Well, I'm gonna go ahead and undo everything. Hopefully, you've got a very similar results. What I have here, assuming that you copied the data that I copied as well into grades. But either way, I'm gonna dio controls your command. Zita, undo a lot of what I just did. So chances are you did something, like, you know, average on you. You specify the sheet name, which is grades and the you at, you know, exclamation point. All that jazz. Well, there's a faster way to do it. Instead, what you can do is copy the function. That's right. You could still copy that function. Ain't that cool? Whoops. Ah, there we go. Get rid of that. Reload. Apologies about that I had to reload my document. But what I was saying is that what you can dio is you can take this function, copy it and then paste it in just like that. And you could see that whenever I paste this in right year, move onto the next one repast. Move on to the next one repast, and on and on and on, you'll notice that these functions are not referencing the same cell range. In this case, I'm referencing E two through E six in this case and referencing D to three D six and on and on and on, and even for the G p. A. I can copy and paste this function in, and you'll see that I'm not getting a percentage. I'm getting a regular number. So in other words, what this did is whenever I pasted it in Google, Sheets automatically picked up on the pattern and started pasting things in. It realized that the pattern was that I was getting averages for math and then science and history and in English and then the g p. A. And on and on and on. And so that's Ah, one of the really cool things that really save you. Time with Google Sheets is remembering that it picks up on a lot of these patterns that you set up in your data, especially when you're being very explicit with what you're doing 60. Inserting Objects: now there's a lot more information that we could include for our students contact information on and we could start to draw lots of cool relations. You know, like if it's, ah single family home or, ah, does he have both parents at home and how that influences grades? And, ah, there's actually been research already done on that. But either way, you could start to draw a lot of correlations with more data that you have, of course, and measuring certain things that actually matter. But in this case, I think we are going to be good to go. And I'm gonna show you how to insert a chart or a graph based on the data that you have. So if I made a chart based on what we see here, it would be pretty useful. But really, where charts and stuff coming to play a lot of times is showing, you know, tendencies over time and things with like so what you're gonna do if you want to create a chart based on your data here, is you want to select all the data that you want to include in your chart, then go to insert in this case, I'm selecting a two through D to and then you go to insert and then chart just like that. Now you're gonna get this little drawer menu on the left or on the right, Rather, and you could see that we got a nice, colorful little chart right here. Now notice in this little drawer menu on the right that you can first will change the type of chart that you want to work with. Um, for example, you could do a line chart just like this and that in order a combo chart, which is a combination of both a column and a line chart you could do bar, did you pie on and on and on. You can even do graphs of, like, maps if you're showing like, you know, population densities and things like so it really depends on the type of data that you're representing. I would probably only use a line chart if I was showing, for example, changes in the grades or the G p A. Over time. In this case, I just want to see how well everyone's doing. So I'm just going to use a regular column chart now. The next option that you have is something called stacking. That's just gonna change the appearance of it. If you use standard, you can see that now it's kind of vertical or going horizontally instead of vertically and then 100% you can see now what we're doing with this stacking is basically taking advantage off all 100% of this chart all the way up the Y axis. But we're gonna leave this as none. For now. You can change the data range that you include. But given how smaller data ranges, we don't really need to play around with it too much. And then there's other things we could do to label and move these different bars around or these different columns rather. But we're gonna call this good. So I'm gonna go ahead and close this now. The next thing that you can do is you can move it around if you want, so you can just click on it. You'll notice that we get a blue bounding box around our column and you can just move it around wherever you see fit. So if I wanted to, I could squeeze this right here and even resize it a bit, but I think that's gonna be good now. You also noticed that whenever I click on this column chart right here, I get this options right here. And this is going to allow me to go back into that drawer menu that we saw before and make some modifications to it. Not sure if edification is a word, but it would allow us to do it, come back in once again and change the chart type or any of the other things that I just showed. 61. Styles: now, in our case, what we might want to do is kind of change the colors of this chart right here, because has of right now it's Ah, I don't know. It's well, it's it's I'm not a big fan of this eso what we can do instead. So it's kind of funny, though, because these are the Google colors. But I'm gonna go to customize right here and then I'm gonna go to chart style and you can see there's a couple of different things we could change right off the bat. We could change the background color, so if you wanted something like black, you could do that. But notice that because my text, my percentages, Aaron Black it makes it very hard to see that if you go with something like a light blue. But notice that one of the columns is also a kind of light blue, so it makes it a little harder to see that. So make sure whenever you're selecting your colors that you're not kind of hiding. Any data are making it harder to look at the chart. So in this case, white is gonna be the easiest choice. Could change your font to something like wide. You notice that it makes our our text here a lot more bold, and then you could add a border around it like a solid black is always a good call. And now if I click away and look, you can see that it's got this nice, solid border around it, really separating it from the group. I'm gonna hit options again. Go back to the editing of the chart customized chart styles. Now you can see there's a number of other options you have maximized right there, and you can see all it does is basically get rid of the margins that are kind of around him . So, in other words, just makes it as big as possible three D, which is a cool one because you can see it just gives it, um these, you know, stretching cubes. I guess you would call them, But I'm gonna go and close that. And lastly, you have compare mode compare modes, not really gonna do anything for us until we have some other charts toe work 62. Titles: Now, if, for example, later on I wanted toe print out this chart or something like that, it might be useful to give it a title. That way I know what it's about. So what I'm gonna do now is I'm going to go to customize and in chart and access titles and the agency for chart title right here. I can basically just Adam whatever I want. So I could say average grades and I could select whatever fall in Taiwan or anything like that. But you can see that it adds it in just like so. And then you notice it says, major grid line count underneath that. We're gonna come back to that here in a second. But, uh, where was a chart and access titles there? Well, now I can make it bold if I want to. I can even change the font size if I want to. Honestly, I'm really happy with what it is right now. Uh, and I'm gonna also change the title color to black. That way, it's just a little bit easier to read, But that is how you can add a title. And if you wanted to, you could add a subtitle toe. Give some context to the data so I could say chart, subtitle And then I could say for 2015. Then after that, I could align it to the center, and I'll actually added some parentheses to it. You could see were just kind of giving some more context to the data. And then after that, I'll change the color to Black Perfect for 2015. I think we're good to go, and then we get at a horizontal access title or a vertical axis title. In this case, I would say it's unnecessary, but I'll go ahead and show you what it looks like. So we'll just say Horizontal axis title Click away. And now you can see it just added it. So if you were, you know, specifying, like this is representative of time and this is representative of growth, and you could add those titles very easily. In this case, I think it's very unnecessary because we're gonna be adding labels for the columns in a second 63. Gridlines: You can also customize the grid lines for your chart and you could see as of right now, we have about four different grid lines. But if I wanted to, I could add more. I could add less to really kind of show the difference, because all these scores a really similar. So what I could Dio is go and just go to edit, chart, customize and then down below you can see we have grid lines right here. Now if I had an X and A or a horizontal and a vertical axis than I could edit both of them . But in this case, we're really only working with a vertical axis. And so first thing I could do is change the color so I could go to major grid line color change that something like blue, for example. You could see it edits it like so it makes it a little bit easier to see that the kind of the faint grey that we were working with before it also add different grid counts. So you have a major grid line in a minor grid line right now, really working with a major grid line which is in other words, the big one. Right? But if we wanted to, we could add some small ones as well. So we could say maybe four of those. You could see that as soon as I add those in, you get four minor grid lines for everyone. Major grid line, which doesn't help things out too much. I makes it a little too much to see. I'm gonna go in and add one for every major grid line. We have one minor grid line, and so you can think See that things were looking a lot more chart like 64. Column Colors: a very easy way to discover other parts of a chart that you can modify is to right click on it. And you can see that whenever I right click on any given area of this chart, I might get something different. Like if I click on the bar right here, you can see it says format data point Now. I could also get there by going to customize and then from there, going to Siri's. But you might not have guessed that now the cool part about Syriza's that allows me to change these colors that I'm using for my columns because I might not be in love with the blue, red, green, yellow so I can click on that column. And then for that column, I could change it to something else, maybe a purple, for example. Now you noticed that the legend automatically changes as well, and if we click, we could see that we can also customize the legend. Right now, I'm worried about the Siri's, though, and then I can click on this next one right here. And I could also change the color to something like, We'll go with blue and you could see it automatically changes that as well. And then you can go on and on and on. Actually, I'm probably gonna be happy with these colors just the way they are. But you get how you could change the individual data points if you wanted to. 65. Loading Or P&L: I'm sure at this point you're tired of working with student information. So we're gonna move on and start working with financial data. Specifically, we're gonna get the P NL for Amazon. That's profit and loss for Amazon for the past three years. So you can go ahead and get access to this P and l. I've included a attachment for you to download Were I could share it with you if you really need me to. I would much prefer for you to download it as an Excel spreadsheet and then loaded in. But once you have successfully loaded this and you can see it's a very, very simple she and there's nothing much to it, you could easily replicate this if you wanted Teoh using everything that I've taught you change the background, color and size of a couple of rose to get this cool separation right here. Give a couple of borders for these individual cells and format the text. And you pretty much have what I have right here. Pretty neat right now, what I want to do is make a chart showing their net income over time specifically from 2015 to 2018 so What I'm gonna do is highlight this first cell hold down shift and highlight the last cell. So we're going from C to D o C. 22 F 20. And then after that, I'm gonna go insert and then chart. Okay, Now, once we do this, it'll insert the chart and will automatically get that drawer menu where we can start editing things. And we're just going to go through all of the different things that weaken due to customize and modify this chart. 66. Adding A Trendline: One of the cool things about this is that it's automatically populated the legend and the vertical access with the data that we were using as a header in this particular column right here. So we have net income now you can see we get are set up right here, and a column chart is gonna be the ideal we could do stacking if we wanted toe summarize the data with label A topper, something like that. But I'm gonna go ahead and leave that as none are Data Rangers, a 20 toe F 20 it would seem, and that's gonna be fun for our purposes. Now, aside from that, we could play around and switch the rows and columns, though that's not really going to give us what we want. For the most part, I'm actually very happy with this right the way it is. So we're gonna go ahead and go to customize, and we're gonna start seeing what, weaken Dio. We'll start with charts style. I'm pretty happy with the background color. I'm gonna give it a new border and we could make it three d if we want to. Um, most of these aren't really gonna help us out, do anything like maximizes good. Like I said, it's gonna get rid of that margin. But it kind of makes everything overlap, which is not what I want. Now we can go to access and chart titles, and in this case, I'm happy with net income as the chart title. Ah, we could add a subtitle if we want Teoh and we could say, for example, let's see, What did I put at the top here? Values in 100 thousands that would work. So I'll go ahead and add that as my subtitle, though you don't have to do this and then I'll go back, Click on my chart at it and then after I go to edit, customize Here we go. Subtitle paste that in values in 100 thousands. I could make it bold if I wanted to make it a little easier to read where I could simply make the text darker. Both would work fine. Um, and I think that's gonna be okay. I'm gonna go ahead and close this. I could change the vertical axis title if I wanted to, but I'm actually happy with net income as the access title and then for the Siri's. If I wanted to, I could change these bar colors like we learned before, and I can go from one to the next by simply clicking on them. But honestly, I'm pretty happy with this. Could change it from the left, access to a right access. And I will need to do that in a second. But for now, we're gonna leave it. The next thing that we could do is add a trend line, and that's exactly what we're gonna do. So inside a Siri's, we have these three different check boxes that we haven't really talked about before, except for data labels for data labels you already know. It just adds a label for the data. That way you don't have to hover to get the exact value, and that's gonna be good the way it is. I like that quite a bit, but aside from that, what you could also do is at a trend line. Now, a trendline is basically going to be the tool that you used to represent how things were changing across time. In other words, you can use it to reveal patterns in your data. Now, if you just check the trend line. You see, we get this line just going straight through eso. The trend here is that income is going up. Net income is going up over time. You can modify it if you'd like to. For example, Blue might be not the best color because our individual columns were already blue. So I could change this to a, um appear red. Opacity is essentially how see through it is. So if I change it to zero opacity, it's invisible, which is not what I want and 100% opacity is solid, but I'll probably keep it somewhere in the 60 to 80 range right about there. And I can change the thickness if I'd like to as well, so all pretty straightforward right there. Now, aside from that, I could label it now I could use a custom label if I want Teoh, you can see it just comes up with Trend line for net income. But I could modify that and lastly, you can see when I use a custom label. Then it says, show our team. So what is Artie? Well, I'm gonna go and click it right here. Basically what rt shows us is how closely the trend line models, that data and all you really need to know about this. It's it's pretty Matthey. Pretty statistic math. But all you really need to know about this is that the closer that this value is to the whole number one ah, the more closely are trendline models that data. So you could see where at 0.8, which isn't horrible. Not great, but not horrible, either. Now, whenever you're working with trend lines, it's kind of a subject in of itself. There's a lot of different fancy shmancy math equations that you could use to generate a trendline. But for now, all we need to know is that we can indeed show a trend line. You can use the equation toe actually show the equation that's being you. Like I said, Ah, it's some fancy shmancy math. It looks crazier than it is, though, Um, really, we just have some exponential notation or looks like scientific notation, actually. Ah, but I'm gonna go ahead and go with my custom label right here, and I think we're gonna be good for modifying or customizing the Siri's 67. Reversing The Axis: the next thing we can do is go to the legend. And as you know, the legend is kind of like the key, so we can customize that if we want first the position we could put it inside of the chart , which gives us a little more space, actually. Ah, but kind of looks ugly to me, so I'm just gonna put it at the top, and that looks fine. Change the fallen, changed the color. Anything that you want. Really? You could change to pure black, which is what it is by default were something wacky, like green doesn't make a lot of sense. Trying to be too cool with your charts of the goal at the end of the day is toe express the data or transmit the data to someone in a format that they can understand. And if you being fancy with a chart somehow takes away from that, then it defeats the purpose. But if you could make the chart easier to understand as you style it, then you should probably go for it. So now the horizontal axis. This is something that we don't really have yet. But what we can do is check this box right here and you can see now we've reversed the data . So where now it's going from 2015 to 2018 so you can see that's the first part of the horizontal axis. Everything else is going to be pretty much the same. You can change the font and the color and all those different things. But we don't really have a horizontal axis yet, so there's nothing to really be said about that. We could, however, see it. With the vertical axis, you can see we can change the font, the color, and I'll go and show that to you right now. If I change the color to something like green now, you can see all of the labels for my vertical access become green. Uh, but I'll keep it a solid black 68. MinMax and Gridlines: you could also set a minimum and a maximum value for your vertical axis here. So, by the fault, your minimums gonna be zero. In the case of this financial NATO and the maximum, it just so happens to be 12,000,500. But you could head to that off. You could set the max to be 10 million, though that would be deceptive because the final value exceeds 10 million. So it would be 123123 10 million. There we go. And now you can see I've set my maximum to be 10 million. But with the data label at the top blooding, you know that it's 10,073,000. They can still get access to the data, but any who go and close the vertical axis. And we'll go lastly to grid lines, which we've seen before. We can keep everything as it is, or we can add grid lines. Now, given how large these numbers are, you're probably not gonna want to add too many grid lines. It's not gonna help out that much, but you could add maybe one grid minor grid line between these major grid lines and then, aside from that, you could make the major grid lines pure black and then really make those minor grid lines kind of fade into a very light gray. So that way you don't subtract too much. But now you can see makes everything look a lot clear. So we pretty much went through, customize and change everything that we could. And that's one of the cool things about charts in sheets that you really can customize quite a lot. 69. Adding Horizontal Axis: the last thing that we could do here is add in the dates for the period ending. So the way we'll do that as well go back to set up and now we're not gonna add another data , Siri's. Instead, we're gonna add an X axis so we can just click this range button right here and specify the range for our data. And in this case, I'm gonna start a to go all the way to F two like so and then hit. OK, and now you can see that we have successfully added an X axis for our chart, which is pretty cool. Now you can see exactly when they got that net income. Now, if we go back to customize, you can indeed edit this horizontal axis however you see fit. Now, instead of just reversing the access order, you could treat the labels as text. Right there. You can see exactly what that's doing right there, which is not exactly what you want. What I mean whenever I say they're treating the labels as text is, as you can see, it automatically defaults it to the first of every month, meaning that it's not treating it as a number anymore. It's just kind of defaulting it, which is not what we want. We'll come back to this one later. But for now, that is how you add a horizontal axis. And if for some reason your chart ended up by default having a horizontal axis but not a Y axis of vertical axis, then you could add a vertical access to your chart in the same exact way. 70. Publishing Charts: Once you're done making your chart, chances are you're either you don't want to keep it in your sheet or you're gonna want to distribute it somehow. So the first way you could distribute it is just to download it. And the way we would do that is we would click on the chart and then click on the options button. You could see it says Download. Now you can just download it as an image that's gonna take away the interactivity that you originally had, like being able to hover over certain columns Or you could download as a PdF. That's another option or an SPG Not really gonna talk about the difference between, um, paying in an SPG. Basically, a ping is gonna be a bit map image, and an SPG is gonna be a scalable vector graphic. And all that means to you is that, um whenever you drawn image with SPG basically to create the image, they put these little dots or these little vectors anchor points onto the page, and then they connect them together with these lines. And so, in other words, they're creating the image with math instead of with little squares of color, like I said, kind of out of scope for this course, But, um, these can be, Ah, a little bit smaller, especially if it's something as simple as a chart. But another way to do this to distribute this chart would be to publish it. Now, publishing can be done in either one of two ways you could either a link to it or you can embed it. Okay, if you link, then you can determine who you're sharing it with, whether or not they have to sign into your domain, your G suite account or anything like that. But the other way to do it would be to embed it. And you have to be familiar with HTML at least somewhat in order to use this option. Ah, but if you are familiar with HTML, all you have to do is just copy this. I frame and then I have a little HTML document right here. I'm gonna load it in right here, just paste it into my HTML. And one thing to keep in mind with the I frames, of course, is that there are security issues associated with the using I frame. So you might want to sandbox it or something like that. But if you don't understand HTML, don't worry about anything. I'm saying I'm just showing you. Ah, for those who are familiar with HTML and now you could see the inside of my document. I have my chart. It's included in my quote unquote website, and it even has the interactivity that it originally had inside of Google sheets.