Google Sheets Basics – Create, Design, and Share | Adam Steinfurth | Skillshare

Google Sheets Basics – Create, Design, and Share

Adam Steinfurth, Teaching Modern Tech

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
10 Lessons (1h 8m)
    • 1. Welcome to the Course!

      1:56
    • 2. Create a Spreadsheet

      4:50
    • 3. Format Your Spreadsheet

      8:19
    • 4. Use Cell References

      7:21
    • 5. Create Formulas

      6:14
    • 6. Sort and Filter

      7:56
    • 7. Share and Protect your Spreadsheet

      9:38
    • 8. Pivot Tables

      5:45
    • 9. Top Functions

      8:53
    • 10. Protect, Hide, and Edit Sheets

      6:56

About This Class

Learn to create powerful spreadsheets with Google Sheets. This immersive course will walk you through

  • Creating your first spreadsheet
  • Enhancing the style to provide clearer insights
  • Using different types of cell references
  • Creating flexible formulas
  • Sorting and filtering your data
  • Sharing and protecting your sheet
  • Summarizing your data with pivot tables
  • The top 10 most popular functions
  • Protecting, hiding, and editing sheets within a spreadsheet

Transcripts

1. Welcome to the Course!: Okay, thanks for checking out this course. And what this course is going to do for you is it's going to give you the ability to create a spreadsheet in Google sheets. And then we're gonna work on some basics of what that data means and how you can work with it. Inside of the spreadsheet, we'll spend some time styling the spreadsheet. So it's one thing to have just a plain table of data. It's another thing to apply some styles to it, so that it's obvious where it starts and stops, what the totals are and what the different types of data are in the spreadsheet. Then we'll move on to a little bit more advanced topics where you can learn how to use cell references and with those cell references, will move into creating some formulas so formulas can be functions. They can be arithmetic, and they almost always have cell references in them. You combine all this knowledge together, and then you could make some basic formulas on your own. It's spreadsheets. A lot of times, when you using a spreadsheet, you have to sort the data or you can filter it in certain ways will spend some time going over that. And then, since this Google sheets, there are different options to share your spreadsheet and different options to protect it. And we'll walk through how to do that in a secure manner. We'll talk about how to create pivot tables so a pivot table can take a large table of data and summarize it in a way that's much easier to understand. Without having to write any formulas, I will go over the most popular functions, so we've broken that down into the top 10 functions. And then if you get a larger spreadsheet with lots of different sheets and site of it will go over how to protect each one of those, you can even hide them, or you can color them or change their names, so there's lots of different things we're going to do. There is the length spreadsheets for all of these videos, so you can follow along, and by the end, I think you're going to have enough of an understanding Teoh work with a spreadsheet and understand what you're doing. So we'll see you inside the course. Thanks 2. Create a Spreadsheet: there's a couple different ways to create a Google spreadsheet. You can either do it from scratch, which is what we'll do first. Or you can great from a template where you may have a spreadsheet already, that you want to get into Google drive and then open it in sheets, and we'll go over that as well. So the way to just create a blank Google sheet is to start out by going to dr dot google dot com. This is Google Drive. If you don't have an account, a Google account already, then you'll have to create one. But let's assume that you have on and go to a blank area that's in where that your files and folders are I'm going to right click and Google Sheets is one of the options. If you go over to the right, you'll see that you can do it from a blank spreadsheet or a template. So if you wanted to do something common, like maybe a budget or create an invoice for a customer, there'll be a template for that that will get you started. But right now we're just going to do a blank spreadsheet so you'll see it opened a new window with your Google Sheets spreadsheet in it. Google Drive is still open in the background, and it'll look exactly like this. If you're using Google, chrome is your browser. But if you're using another one, it'll still do the multiple tabs. It'll be similar. The first thing is you probably want to do is name it, something that's meaningful to what it's going to be so well, let's just assume that you're doing inventory. Name an inventory, you're ready to go. There's a few basics about how to do this. Who? You're only in one cell at a time if you're entering data and it starts out in a one, you reference these by saying the column first, and then the row and type what you want in here. Maybe this will be the amount. And to go down, you either hit enter or you hit down arrow. Typically, you would just hit enter, which always bring you down. But sometimes you may want to go left right up, down, and just zero keys. Right now I'm gonna hit the down arrow and thats done. You've entered your first data in a goal less stretchy So let's send her a few numbers in here just to show you what you might want to do. Next, we'll do 34 and three and then and then, If you want to sum them, go to answer function, and it's gonna have the most common functions here. This is going to be what you're using a lot of the time and just choose some left. Click on it, and it wants the range of the values. So we're working in a to all the way through a four, and you've also could have just used your mouths left, clicked in a two and dragged it down. A four and now hit Enter and you done your first spreadsheet function sad ing up 34 and three. So that's how you create a blank spreadsheet. It's pretty easy. The key is just remember to go to your Google drive created from there. Give it a good name, then you can find it later. I'm going to show you this steps on how to upload an existing spreadsheet. Someone in little tricks that you want to do is you want to go to your settings and make sure convert upload has checked. If that's not checked, it's going to upload it. Say, in this case in ah Excel format, it's going to sit on your Google drive in a non native format. So if you put on Google drive to use in sheets, just have this checked inhale converted for you automatically. Now that you want to upload your files, go to new and file upload, and this is gonna look at the hard drive on your computer. I happen to have put mine here. So this is a sample data. This is an Excel spreadsheet selected Click Open and you see in the lower right hand corner . It's uploading one item. It has this X here, which is saying that it's in Excel, but if you see by the time it gets into my drive, it's in Google sheets. If you didn't click that check mark that I showed you a minute ago, this would still be in X, and you could view it, but it wouldn't be in the native format. If I open up sample data, it will open up in Google sheets, and it's ready to go si, but it doesn't always upload perfectly in this example, the header just looks black. If I click in, say example, see one, I can see there's a value in there. But what it did was it turned my phone's black in the background black. So let's just go and turn the text color toe white, and we can see what's happening here. So that's your three ways to create a new sheets, make a blank she great one from a template or upload existing data. 3. Format Your Spreadsheet: So if you're using Google sheets and you have some data that looks like this on the left, and you want to make it apparent that it's a table, use of nice colors make it, um, obvious what? The headers in the title. Or that's what we're going to do in this tutorial. So we're gonna start with unformed, matted and work our way over to something that looks like this. So here we go. If we do one cell, let's say we want to do a one. You hover over with the mouse and you left Click one time that selects it. Don't double click if you double click, it brings you inside the cell, and it thinks that you want to change the contents of it the second way that we're going to select eight as you're gonna either select the entire column, and I did that by left clicking on the A or you can do the entire row, so let's left click on the to. And while that's highlighted, if you hold down the shift key, you can go down and it selects more than one at a time, and you can also select the entire sheet by clicking of here in the upper left hand corner . That's everything. So when you're looking at this data, the first thing that you want to dio is make it apparent that these are dollars when a highlight all of those. So I just left, clicked in F three. I held down the shift key, and I used the right and down arrows to select this entire range. And I'm going to go up and just collect the dollar sign in the toolbar that formats at its currency. But then I want to go to column F because I don't really find it to be material what the sense are on a particular item. I kind of wanted on the totals because I want to see everything down to the penny. But here, let's just go ahead and decrease the decimal places. But I want you to see, for example, this is dollar 99 but decrease it by two. Perhaps wrongly. If I decrease it by two, it rounds for me. Google Sheets Dough uses a standard rounding convention in this turns 1999 into $20. Now the value in here still 1999. If I double click. It's in there, but we've changed just how it's displayed. Now let's take this sample data. This is the title of the entire table. It's not a header for a particular column, so we should probably merge Thes rose. So, like left collecting anyone, I held down the shift key and I press the right arrow. Or you can just take your mouths left. Click, select all these left, click and drag, and let's merge cells so I can hear is merging them. So you merge them together, but I want it centered horizontally as well. So this icon gives you three options center. It left Senator the middle senator to the right. Let's put it in the middle, and then let's make it bold. That would be here, and we're going to make a 24 point. So you really see a this table with sample data actually gonna make a little smaller? It's kind of annoying. Here we go. Now. What I want to do is in really handy shortcut that was added just a few months ago to Google Sheets. I'm going to select what I'm considering to be the table here, but if you see, I also selected the header, which is wrote to in the Footer, which is rose. Seven. You'll see why here, Go to format. I'm going to say alternating colors. Why did Doesn't just say format as a table, I don't know, but alternating colors and then on the right hand side, it brings up these options. Now I have a header and doesn't know that I have a photo. You have to tell it that. And I say of a footer as well. And you see the rose seven there became, ah, darker when own click and click it again. And then you just you choose the color and hit done. Now, when you look at this, it looks more like a table. It's apparent that these are, um, the types of units that we're looking at. This is the content the actual data in. Um, let's make it more parent, though, that these down here or totals I selected e six. I'm gonna hold down my control key in select G six, so that enables you to select to individual cells at once two or more. And what I'm gonna dio here is put a bottom border on it. I'm not doing an underline doing a bottom border. That would explain the difference. First, let you see it. There's a to bottom borders didn't do an underlying because if you re sort this data for some reason, the underlines gonna move. But if you do a bottom border, it should stay with Roast six. Now, I also don't like this font. So let's use to trick where you select the entire worksheet by clicking in the upper left hand corner and we'll drop down the font and we'll change it to calibrate. And I think that it would be better if the header the content of the cells in the header, was center aligned. So let's just click on the to to select that entire row and then go to the vertical alignment, which is here, Lina Vertically. Now let's say, Look, I want to zoom in 200% on this, but when I do, things are too wide. So I'm gonna make the unit costs. Don't put a space in here. You'll see why, in a little bit I'm gonna make the unit cost a lot skinnier like this. And I don't make the units skinnier, and then maybe I could see everything. Let's make it a little bit more skinny, little bit more. All right. What happened here was I made it so skinny that the units isn't fitting. Well, so what I have to do is, or one of the options that I can do is to rotate it. You can do this with all your headers. If you want, you could select row two and rotate it. The other option this unit cost is also running over a little bit longer. Shrink its, um, warsi, comptel and what I'm doing to shrink this and just changing the column with by hovering over the line that separates the two holding down the left mouse button and moving left and right, right to my other option to make this fit. Why could you shrink the font? But a fancier option is to wrap the text, which is this middle option. You see that? Put one over the other. No. If you don't like how the others are all down at the bottom of the row because now the row is taller. Let's change the horizontal alignment. That would be where would that be? Vertical limit. It's easy. Let's change the vertical alignment to put him in the middle, another all lined up, so that should pretty much cover the basic things that you can do with your data. Format it into an N, make it look more like a table. Draw the users I down to the important parts or up to the important parts like the header were down to the footer. Let's go ahead and make these bold that way. It's more apparent that there some, and that's about all for the basic, so I hope that's helpful to you. 4. Use Cell References: So when you're using Google Sheets, very basic concept that you use all the time when you're working with data is sell references, and we will go through four different types of cell references, start with the most basic and work our way through to reverend immune cells and other files . So the most basic type of cell reverence is called a relative cell reference. But it's just it's the type that you, um, just type right in. In this case, it's gonna be C five and let's use upper case. That's the standards in tax that you're supposed to use by Lower Case were explained to. In this case, we're gonna dio C five minus d five and a few things to keep in mind here. So you always type the column letter first and then the road number so we wanted See column c Row five and then we're just going to subtract d five, which is the expenses. So we're going to try to calculate operating income, and you would say these air to relative cell references, and I'm going to press enter. You will see why their relative in this next step So that did a straight subtraction. But if you copy it down to these next to Rose and to do that, I'm going to go in the lower right hand corner where you see a little blue square and my cursor turns into a plus sign. I'm gonna hold my left mouse key down and then drag, and that's gonna copy it down. You could also double click on that square, and it will copy it down as faras. The data on the left goes, so it's kind of smart in that way when I let go. And here's what I mean by these being relative references. When when you look back and sell e five. It was C five minus t five when you copied it down and became C six minus t six without those changing spreadsheets woodwork way differently. So those being smart enough to increment makes spreadsheets far easier to work with, and that's relative cell references in their most basic form. Now this next type of cell reference is called a fixed cell reference, and we'll show you where you might want something like that in this next example. So in this case, to calculate the tax you want sell E five and then you want to multiply it by D two, which is where the tax rate is, and it works fine there. That's the right amount. I know it's a negative tax, but you had a loss. But if I copy that formula down to F six n F seven, it stops working and even gets worse first against zero and then it gets an error. What is happening here is that it thinks these air both relative cell references, because that's the way you type them in. So if I look at F six, it incremental did the two down one because you move the formula down. One. You have to tell Google Sheets I d d to its backspace Changing d to that, too, shouldn't move. And how you specify that as you put a little dollar sign before the to. That's a fixed cell reverence. If the column we're moving. So if you were copying these formulas from left right, you could fix the column as well. We could fix the column here, too, but it doesn't matter cause the columns on changing. So let's take that out for now. Believe it clean that works, So let's copy that formula down. And just so we're consistent, let's go back into F five and make that fixed as well, even though that's not causing a problem until you go down. Now we have the same formula in all those cells. And let's just go ahead and fill in the net income because we need it for the next step. I'll do that. Copy that down with the plus sign, and I'm gonna double click copies it down. But it stops here because there's no numbers who left, and we have a total income of $29,000. So in this example, I have other income. But it's on this other sheet, and I want to pull in the income from there, though, so I could do a cell reference on what you do started within equals like you always deal and then the easiest ways. Just take your mouths, go over to the other income and select the cell. So I'm going to tap see six and over here on the right, it shows you what's in the cell. On the other sheet. It's a little preview window, and that's what you want. So hit Enter, and it brings over that 29,000. If you were to want to automate this or type it in, how you do it is you have these little accent marks there like a single quote and then the name of the sheet, and then you close it off with another accent, and then you have to put an explanation point and then the cell reference. He did all that in there and you're good. And the last type of reference that we're going to do is a reference to another file. Nelson's were using Google Sheets. It's all online to find the file. We're actually going to use an address or you are l to it, and I'll show you how to get that. But first you're gonna start it with an equal sign. And And what? In the function that uses import range, Even though we're just picking up one cell, it's It's called a range. And then, if you look at this syntax that Google Sheets is telling you an important thing here, don't forget the quotes. You don't have a quote, someone work, so we're gonna put in a quote and then I'm going Teoh go out of full screen on my browser and I have this other file open to get the girl, go to the file that you're linking two clicks share button in the upper right hand corner and use this year l that it gives you. I just set this so that anyone in my organization can edit should probably work with it. Whatever setting you have there as long as it you bone both piles. So I copied that euro. Let's look quicksight. Ah, the file of the cell that we want is in detail here. Remember that. Go back to the link. We're gonna go into the formula bar right now to pace this. Close it off with a quote to do a comma. Now, the next input that at once is the cell reference. As long as it's on the first sheet. In that file, you don't need the name of the sheet as well. If you do need names, she just do it the way that we did the last cell reference you see here moving my mouth. But the other cork here is that it needs quotes. Usually don't need a quote on cell reference. so it's easy to forget it when you're doing this, but let's surrounded and quotes Now remember, we wanted D 10 so let's go d 10. Close it off with quotes in the parentheses. Now, this is the first time doing this. When you hit inner, it's going to ask for your permission. But I've done this before when I was trying to get this video together. So it's not gonna ask me. It's going to take a second. Think about it and then it pulled it in. We can change that format if you want you last time. You can't tell the formats gonna come over and take see it. Let's get that dollar sign off. Let's take the decimal points away and now everything looks real nice. So that's it. You have relative. You have fixed, you have to another sheet and then you have to another file 5. Create Formulas: Okay, We're gonna use this just little simple table of data here to show you the basic parts of formulas in spreadsheets, what the different types are called and how to combine them together to do most anything you want. So the first formula that we're going to do is we're just going to calculate the order total, and that's gonna be the quantity times the price. If you want to see, use your spirit, she just like an old calculator. All you do is just type the values directly and write 177 times dollar 72 that's going to get you to 304.44. But the problem with doing it this way is that you can't copy this formula down. And if these values change, this formula doesn't automatically update because it's just hard coded in. This is probably the most basic type of formula that you can dio, but it's not very useful, but you don't want it hard coded. So let's delete that and let's start over. Let's type and equal sign that starts every formula, anything formula or function, or whatever you do in Google sheets, that's not a value need to start with an equal sign, and then it gives you this gray bracket underneath. And what that saying is your formulas just started? I need some data. It's not finished yet, so it's waiting for input. The first cell that we want to give it is B six. So left click your mouse and B six, and it's already calculating and say, Now I got a value is 177 but you're not done yet, right? You want to multiply that by C six. Let's grab that value and you're getting three or 4 44 which is what you got when you hard Kate it. But this one's more flexible. So if I hit, enter and I go back and I changed. Dollar 72 $2.89 This formula updates, but one if you arcade it now. Another big advantage of using formulas is that you can copy them down. So I selected this cell, and in the lower right hand corner you get a little square. If you hover over that, your mouse turns into a plus sign left click and drag it down, or you can double click to and stop where you want your formulas to stop and it copies them all down, so it saves you a ton of time. That's your most basic type. But formulas, cell references, which is the first line here, combined with operators just multiplying to sell references together. The second thing that you can dio after you hit the equal sign is use a function. I'm gonna show you that talent function first and again I had equal. It's waiting for something. So that's what the great bracket is gonna typing count and I'm gonna do in the caps lock on because you're supposed to do these things in upper case, we really want to be proper, but you can do it in lower case to one. So I take 10 count. This is a listing all of the functions that are available that start with count. Did you see it was filtering as I type. So if you just have a C in here, it's going to show you everything that started to see another way to input. This function is to go toe insert function, and this will show you the most common functions actually counts on here because you will use count all the time, so let's left click on that and this. Put in the two parentheses. For me, every function has toe. Have an opening and a closing parentheses, and those tell the spreadsheet when you're starting to give it input. And when you're done, did you start to look at the helper text down here, which always pops up after you type in the name of the formula and you hit the first parentheses? It can take a value, but it can take other values, too, if you want. So this is in brackets, which is saying it's optional, but we're on value one right now, and that's highlighted because she's is telling you that's where you are if you go down. It also has a little gray triangle, which is giving you an explanation for what value one is, and it's just saying, Give me the range of data that you want me to count. I'm going to do it with my mouth. You can also just type it in or use the arrow keys. In this case, I want D six through D nine, left click in D six. Hold down the left mouse key and drag down the D nine. Let off the mouse key and you see a put the range into the function. This function is done if you wanted to be now there's no little gray bracket here waiting for more data, and it's showing you a value that it's calculated. Let's go ahead and press enter. So I've counted for We're also going to do a total. This is going to be another very common function. It's gonna work the same way. And I just did that one with my arrow keys. You see, if you do, these alike get pretty fast at it now. The next concept that I want to show you is stringing functions together for a longer formula, and you can string them together with other operators. So in this case, we're going to calculate the average well, you calculate the average by dividing the total by the count, right? So although I just did these in thes two cells, you really could do D 12 divided by the 13 and that's the average. You could also kind of home brew a function hearing so you could put the sum in the numerator and then you could divide it by the count as the denominator in close off the parentheses in a type enter. It's the same thing, So you strong two functions together. You could also just use the average function. If you knew that existed an average these cells close off. The parentheses had escaped. Close out the parentheses hit, enter same value. And let's give this a date, right? So if you want to give it today's state, just use ah, popular function called today. It doesn't need any inputs, but it still needs parentheses because it's a function type in er. There you go, and that's the basics of how to use formulas and Google sheets. 6. Sort and Filter: Okay, so I have an example spreadsheet here that has some pretty typical data in it, I think. And when you have data like this, a lot of times you just want to put it in a different order, which would be sword where he only wanted show part of it based on some sort of criteria, and that would be filter. I'll show you sort first, and then we'll get into filtering a few items and then keep in mind, though, you can do this two main ways, so we're going to sort filter from the menus first. But then I'll show you a new concept that didn't really exist in previous spreadsheets is that there's functions that conduce shortened filter and to do those will go down below because they produce a new list that's based on the data on this list. All right, what you typically want to dio is select the table that's going to make this easier, and then you know the data menu and then do sort range. There's shortcuts up here. If you just want to sort the entire sheet, that's not gonna know where your header is. Those that's going to sort the head or to Maybe that's okay. You can also just sort of range. We already selected the range. So let's just go down to the sort range option. It's gonna give you a pop up. I think it's easier to use. There's a couple options on here that we can talk through that you're gonna want change from time to time. And they're not in those shortcuts if you do the other options on the menu. So it's selling you. Look, we're looking at sorting a 10 3 g 10 which is what you highlighted back here behind this box. But we did have a head around. I don't want that sorted. So put the check mark there and smart enough to take the names from the hetero and put them in this drop down list. Now that told that it has a header. And in this case, we're just gonna want to sort to spy region because we want to see what happened in every region region, keep it from a dizzy and within those regions were gonna want to sort of by rep name. So you collect sort. It did not delete any data. It showing you the exact same data, and you don't have to unsorted to do anything. You can leave it like this if you notice it, does it by region alphabetically and them within that region. It did it alphabetically by the name of the Rep. That may be all you want to do, in which case all you need to sort and you're done. But if you want to take this a step further by doing some more analysis, let's select the table again. Go to data and let's try filter. So I turned the filter on and you can see at the top of every column of data you got this upside down triangle that's letting you know that you can filter that column. But there is no filter selected yet. If you want to see just the pencils that were sold, you want to clear all of these. Just collect, unclear and just highlight the pencils. Click OK, and that filtered out everything else in the list. Data is still there. You can see it goes from 3 to 9, so those rose still exists. But they're hidden from view in one concept here that's important to know is that if I some these If I just do e to two e three, then you'll get 21 which is these two being added together. But if you do the whole range and again, you can see this goes toe talento Row 10. It's just heading. It still picks up those values 327 so there underneath and your formulas can pick them up if you're doing simple formulas like some account. But I digress, So that's one type of filter. So reset your filter disc elect select. Also, all of them are in play and then do text. Contains P E N that will pick up pencils, pen set and pen. And then once you have a filter on, you can see that changes from an image of the upside down triangle to filter. So you know that there's an active filter on here, so that tells you it's probably not showing everything right now. And that's true. So it still goes. 6798 is hidden in 11 or hidden and why you have a filter on. You could still do another sort so or another filter. But let's just sort this by rap. The filter stays. It's still showing only the things that have the letters pen in them. But this is sorted, so it's not filtered, so don't turn into a funnel. But you could also do another filter if he wanted. Let's clear all of them. And just so show Jones and Morgan. They're togethers. Two filters applied. You imply as many filters as you want. And that's the basics of how do you sort and filter through the menu? So we're gonna turn that off a couple different ways to turn it off, and I'm just gonna do turn off filter. Imagine that this is the data that we started with. I want to get rid of this road 11 that's highlighted like it's part of the table like that and right click delete row. Okay, now I want to show you the second main way to sort and filter, which is using functions. There's three different functions that we're gonna go over. The 1st 1 is sort So hitcher equal Sign S O. R T open it up with an opening parentheses, which goes on every function, and then it knows you do the parentheses and it's ready to rock So let's give it a range. And in this case, let's do a 12 g 10 Mere fact. Let's do a two so we don't pick up the header. A 21 win. Only that are there to G 10. Let's look at the helper text again here. The second thing it wants is the sore column. So saying, What do you want me to sort it by? Let's do it by the reps. So that sort column three Once a number, not the name of the Hatter. And we wanted to be a sending sure, Sali, that is true. You see the next thing in the helper Texas and brackets that saying It's not necessary, It's optional. So let's end this here and watch. When I hit Enter, it's going to write this out to the right and down below. It so started where it has room to work and they're sorted. List, you say got right before mounting. That's right, you didn't need it. And this is all dynamics. If you change, which in the original less, it'll change. If you change what's up here? It will change what was out. Put it down here. And if you click in one of these columns. Hit, delete, delete, delete. It doesn't go anywhere because it's all being driven by the sort function. In a 12 if you would go in a 12 and delete, it gets rid of everything. The second function that you can do is a filter, so you can guess how this is gonna work. We don't have to go through it in much depth because it's the same concept. It's going to output a new, lest that's filtered based on how you want it to be. And it's dynamic. So if you change what's above, it'll change. What's output filter may take some time to study this one. It's a little bit trickier to use filter through a function, I think, but the last one that's added is it is a new, um, type of function. It's called sort in, and this one kind of deserves its own mill tutorial, I think, but what it can do is let's give it the range. 10 is in plain English. It's saying, Hey, I want the top 10 sales people from here. I want the last five dates so it can soar in return in number of values. From your data set, we'll try one here. I won't get into a too much. Let's try a three. So the top three and that's all you have to do. The other parameters are optional. There we go. That's the 1st 3 sales. 7. Share and Protect your Spreadsheet: all right. So if you're using Google Sheets and let's just say that we've created this spreadsheet and we're ready to share it with other people now, whether or not maybe we're done and we want to share with other people to see it, Or maybe we just want some other people to be able to log into it and work alongside us. We'll go over those different permission levels to enable you to do that right now. And you start by going into the upper right hand corner and left clicking on the green rectangle. It says Share in. If I do that, I get the sharing dialogue that comes up and you have two main options. You can share it with people in groups, or you can work with just getting a link. So for now we're going Teoh talk about what sharing it with people in groups means and then later won't go into getting a link. Let's just say that you created this bridge. She and you know who you want to share it with. So it's a person named the Michael. You just type that in and you get a drop down here showing you your recent contacts and suggesting people who it thinks you might be talking about. I'm going to left click on the one that I do want to share it with and for this particular person. And if I typed anyone else into this empty space and added them, you can select from three different levels of permissions. So these g o from the least amount of permissions down Teoh, the most pervasive setting, which is editor. So if you just want them to be able to look at this preachy and not change it you had left . Look on viewer. If you wanted them to be able to use some commenting functions, but not actually added anything, make them a commenter, or you give them full privileges, which would be editor. So we'll just leave it at editor for now. In the second thing to keep in mind is this Michael, he's not gonna know you shared this with him unless you notify him. Maybe he knows. Could you just talk to him? Always. Case you could just remove this check, Mark, but I'm gonna put that check mark back in. I'm going to say here is the spreadsheet we just talked about, okay? And then I'm going to left click on Send. This is going Teoh, send Michael and email, and it's also just going to show up in his Google drive. So I'm going to take what I'm working on. I'm going to put it on the left hand side of the screen. So this is a spreadsheet that we started with, and then I'm going to left click on this drive, and I have a different person logged in here. So if I left click on here, you can see this is the account of Michael. I'm going to take it. I'm going to move it onto the right hand side. So on the left uses original spreadsheet in my account. On the right is Michael. So if we go down and Google drive to shared with me, this spreadsheet is going to show up now. He also would have received an email. If I have Gmail open, you would see it. But this is a spreadsheet show all double left. Click on it to open it up. And because I gave Michael editing privileges what you could do, Michael could go in here so we'll look at 309 38. Change it the 4 11 It changes on the right hand side and on the left hand side. So you got full edit privileges with the way that we shared it right now, and we can see each other through these icons, and you can also chat with each other if you want to. So that's the most basic type of sharing is just to share it with one other person. But let's look at some of the other types of left click on share again in another option C , it says Share with people and groups, so you may have some groups may not, but I've set some up and I'll show you what that means. Must maximize this one. No, again, let's get rid of the sharing dialogue. For now, go to Google Drive where we can go in the upper right hand corner and see these other Google APS. There's one called Groups Left. Click on that, and you can just see that I have two groups, so we're acting like this is a company. There's a marketing group in a production group, and these would have a pre defined set of people in them. So if I go back to Google Sheets and I left click on share and I say marketing it will bring up that group as an option, I left click on it. And this. I have the same options here to control the different levels of permissions. And this is a way that I can share this file with an entire group of people so it can be a department at a company or can be a group of friends. Whatever you want, we'll go and send that one to. It's a popular file, and that mostly covers sharing with people in groups who have a few more things to talk about here. If you left click on editor here, you can give temporary access and you get a dialogue where you can expire the access in 30 days. Or you could just change that. That's what it defaulted to, and you can specify when it will expire. If you don't see the ability to add that expiration, that's probably because you're not using a G suite account. So if you want to have extra options, you want to use Google haps inside of a company you need to be using the business lamb, which is called GI Sweet. So check that out if you want those additional options. And let's just close that dialogue for now and look at one more thing before we move on to sharing with a link. And that's this gear icon in the upper right hand corner. And after you have everything set up, you can choose whether or not the people that you gave editor permissions to can change other people's permissions and whether or not viewers and commoners can download print and copy. All right, let's just leave those away. They are. We'll go back, and that covers sharing with other people and groups. And so let's go down now to getting a link. This is the link that people can use to get your spreadsheet, and you can just left click on copy Link to get a copy of it. But you can control how this link behaves. So right now it is selected to just be restricted, which means only the people that you just added can open it with this link. So if you go up to look at who you added, if you want to give that link to the marketing group again. They'll say, Hey, Adam, you know, how do we find this? And they will know how to use Google. Drive very well. You want to get them the link? Um, it will still stay protected because only people that added can open it with that link. All right, But you do have other options here. There's three other options. One of them is anyone at your company. And again, this is a G suite option. It won't be here if you're using a personal account, but if you are using G suite, you left click on this and then anyone that your company convert you and you have options to limit how they view it. Third option, which you will have in a personal account, is just anyone with the link. So now this fundamentally changes how your spreadsheet behaves on the Internet because anyone that gets this link convert you it. Now it is a long, pretty random link, so no one's going to be able to just guess that. But if someone gets a hold of it that you haven't specified, can see the sheet, they'll be able to see the sheet, so just keep that in mind in anyone with the link can be any one of these same three privilege categories. So you could put this on the Internet and say anyone with the link can be an editor. Ah, but just be careful with how you share the link, that's all. So if we copy this link and we said anyone can use it if we go up into Google. Chrome and I opened up a new window, but I opened up in incognito window, meaning the Internet doesn't know who this is. This window is not logged into any Google account. If I put that Lincoln here and I hit enter, it will still bring me to the spreadsheet and instead of 18 06 I can. Still I can changes to 19 and it updates. So this is the Wild West. If you share this link with the wrong people, anyone can change it. Let's go back to my main account and you'll know that someone else is in here because one you shared it with Michael and it knows who Michael is because he's logged into his Google account. So you see the M, but then you have someone here who just gets kind of a silly cartoon assigned to them. So the anonymous hippo that someone else is in this spreadsheet. But Google doesn't know who they are. They're not logged in, so you can always see who's in the spreadsheet and using it up here in the upper right hand corner. You can also see a few statistics so you can ah, see the viewers that you shared it with. You can see all the viewers, and if you've been sharing this for a while, you can see the trend of the viewers. This is what I just did. Well, I was making this video. So this is a history of who I added and how I did it, so that could be helpful. If you find that spreadsheet to share with someone, you didn't mean Teoh and you're wondering how it happened. You can look through here and you can also turn off these settings if you don't want someone seen who shared it with, so let's it cancelled back out of there. Close that. So that should cover all the basics for what you need to know to share your sheet, so that should cover everything that you need to know to share your spreadsheet and make sure that it remains secure and shared in the way that you want it to be shared. 8. Pivot Tables: Okay, So if you're using Google sheets and you have a long list of data a very easy way to extract information out of it issues. Pivot Table already made one on this worksheet, and this is where we're gonna end up. So you have the sales rep on the left. You have the ship mode up top, and then you have it by year. I've already filtered this year. We're gonna do that at the end, and you can see how it set up over here. So I'm gonna delete this. So this list here I have I think it's a good example of what makes good data for a pivot table. And what that means is that one. It has headers, and the headers obviously pertained to the data that's underneath it. The sales rap describes what's in this column. There are no breaks in this data. So if I scroll down, there's no blank lines, and it's so much data that you can't just look at it and get the information right. Okay, So make sure that you selected a cell within the table of data that you want. This spreadsheet that I have on Lee has a table, so that's not hard to dio. And you go to the menu, select data and pivot table, and that gives you a blank slate that you're gonna work with. It will suggest different pivot tables. Gonna use, um, artificial intelligence to kind of figure out what we want unless you have very simple data and you just want to have it summarized by one dimension. These aren't gonna guess what you want because there's so many different combinations. But if you do, just try one of these and click on it. It's gonna build a table for you, which can be really helpful if you have simple needs, right. But just to show you how it's constructed, I'm going to take that off. And what we had said we decide we want to do is we wanted to analyze it by sales were at first, and I imagine that going down the left hand column. But this is where it's a little bit confusing, right? So you want it in this column, but you actually want each sales person to be in a row. So that's why it says Rose, here you want to ad sales rap for rose, even though it's gonna be in a column. So you want each row for an ad. We wanted to be a sales rep, and it's build that portion out for me, so it's put them in an alphabetical order. It assumed that we want to sort by sales, right? Right now we can't sort anything else, so that's fine. And on the columns you want the ship mode and again, this is a little bit confusing, right? This is a row is going to be your row of headers, but it's going to be. Each column is gonna have the date, and it's It's called columns and let's add the ship mode and you'll notice on each of these . It's asking if you want to share the totals, we're not gonna be grand total for the sales rep and it grand total for the ship mode. Now in here. Let's look at the count of sales, so I don't want to see a dollar amount on one see the number of sales. So for values, values is going to be what it shows you in the middle of your pivot table. It's the actual meat of the table, so you have your headers in here now you need to put something in the middle. Let's go to values and add Our sales column is what had the dollar amounts in it. So let's add the sales and it's summing them right now. I want to add each one of them as one someone sale. So we're just going to count, and we're most of the way there. So we have sales rep and we have a count by each ship mode. And the last thing we want to do is we wanted to look at it by year, and I'm envisioning those being on the left hand side here under each sales rep. So you remember this is called Rose, even though it's a column. So let's go over to the Rose. Let's add another column of Rose, if you will, and we're gonna make it the date now. This isn't what you want, right? We wanted by year. But in order to do that, you had to add the dates. So it's a value in here and now what you can do is right. Click on any of the dates and create a pivot data group in select year. This is going to summarize it by year because Google Sheets recognizes that those air dates and it can extract the year and summarize by just that. And let's say you're just looking for 2018. So let's go back to the right scroll down to the filters. We're gonna add a filter for the date, and this will be a little bit turkey. How we're gonna do this, Let's do a drop down it said, showing all items were going to select clear and then we're gonna type in 2018. Now we're gonna hit select. Also. What we're doing here is we're saying un select everything, show no dates. But then, if you type in 2018 it'll show only the 2018 dates that air in the original table. Now, if you collect select all, it's going to select all that it's showing for 2018. Click OK, and then you have this filter by 2018. One last thing. Let's say Phil left the company so you want to take him out? Just add another filter. Go to sales RAB. Right now it's a showing All items that were going to come here and unchecked poor pill cause fills no longer with the company. Click OK, And they're your really easy to understand pivot table with just the data that you need. And if you want to change anything, this is always live. Just go back here and customize it however you want. So that's all taken a good solid list of data that has columns with consistent data types in it, no blanks. And we've created this pivot table that gave you the exact information that you want to see . 9. Top Functions: Okay, We're going to start with the average function, and we're going to want to get the average of the order amount. And since I took my mouse and I already selected the amounts of we want average, I could cheat a little bit and go in the lower right hand corner in a select this, and it will tell me that the average is about $340 so we're going to know for right if we come up with that number. So if we type in the function, you can do it. Lower case or upper case doesn't matter. And once you typed the beginning parentheses, this box of instructions comes up in. The highlight is on the section that amount on right now. So since I put in the starting parentheses, there's a little gray line underneath, and it says that that's waiting for input in the input that it's waiting for is what's highlighted here. So it wants the first value, and the value is going to be the range of F three, and then you do a colon two F eight, and now you see that little gray line is not here anymore. so you can be done with the values. It doesn't need anything to complete the function. And it's already telling you in the blue Font what the answer is going to be if you end it right now. So I'm gonna put in a closing parentheses, a type enter. There it is, $339. That's what we saw in the lower right hand corner. So we know that's probably right. The next three functions that you're going to use all the time are some functions. There's different types of some functions, and we're gonna use them all on the Amount column. The 1st 1 is just a straight up regular. Some function, it's already in hero, double click on it. It's just some and then it takes the range. So that is just going to add up all the orders, no matter what, and it comes up to $2037. But you can also some them according to criteria that you say. So you can say some of these if some, if then you select the range with the criteria in it. So I'm not summing the sales rep range. I'm using this range to decide whether or not I want to sum this range. This function goes one road a time, and it looks to see if column C has a word wrecks in it. And if so, it's gonna add the corresponding value in column F for Row three. So it'll add to 61 96. It'll skip thes. It'll come down and pick up 48 86 and then you'll be done. So if I hit inner, that's how it comes up with 3 10 82 and the next one is called Some Ifs. And that's just a compound. Some if statements. So it's saying some these. If a couple things are happening in this case, it's seen some it. If the sales rep is Rex in, Rex sold those items in Henderson that happens only be this first line because the other Rex was sold in Hemsworth, Pennsylvania. So that should just sum up 2 to 61 96 three different types of some formulas, and they can get you completely different answers it. We've gone through average, and we've gone through some another one that you probably use Alatas account function. We're gonna use it on the extra column because it can act differently when there's text in just with a straight count function. All it does his accounts, the occurrences of numbers. I look in the helper text here. It returns a count of the number of numeric values in the data set. There's only one here, 22 use count. A. It will count all of the values in here. The only thing won't count is blanks. Right now it's getting four, which looks like what it should be getting. But you have to be careful with the county, because if I g o and sell K five and a hit the space key, which is a way of putting some information in here. But you can actually see it. You'll see the count goes from 4 to 5. Maybe that's still what you want it right, because that is data in there. It's just a space, and you can't see it. But be careful. If you're using count A in your getting a value that you don't expect. You may have to go about this differently and then count if can be used in any way you want . In this case, we're saying, Look, count it if it's has the letters v I p in it that your criteria, that happens up here twice. And so it's two now again, If I put a space before the V I P, it's not gonna count. So it only found one of them. If there's a space now, you can get into the trim function if you need to clean up your data. But that didn't make the top 10. Next functions are today and now, and these can be used to figure out the amount of time that's elapsed. They're very similar functions, but one of them has a little bit extra compared to the others. So if you do today, that takes just the value of the day today, and this works a little bit differently in that it takes nothing in the parentheses, but it still needs him because all functions need parentheses. So just close this one off. That's today. And then if you subtract the time at which this sale was made, you Condell was done 258 days ago. Now function like I said, works very similar to it, and it's going to return the day and the time. Certainly in the morning. Right now, I want to find the exact amount of time that has elapsed from these orders. Do now and then I can subtract one of these dates and so add them together. One of the times do this attraction. So it was almost 258 days ago that that sale was made. Um, and you tell the Times come out, Justus. Fractions of a Day and Google sheets. So if one of these dates was noon, it be, ah, half. And then the last function that you'll probably find yourself using a lot of Katyn eight and can Captain eight joins values together. So we could go through this table and we conjoined these. Make it look like an address. And how you would do that, you would just start out with the equal sign and then you type in Katyn. Eight she eats is guessing what I'm going to type here. I'll select the 2nd 1 left. Click on it. It says, Hey, give me the first thing that can captain eight. So I'll give it Henderson. Then I'm gonna want a common a space. So add those has the exact value. Just type it in a string. Make sure you put quotes around that. That's what tells sheets that you're typing in a string and not a number. Then you wants the value of Arizona and I three. I'm grabbing those just by left clicking with my mouth. Another way to get them. Let me put a space in here is again. You see the little grey bar at the bottom. That's shows it's waiting for input. Take your arrow key and just start moving cell selector around and go over to the postal cut and you see it's filled in J three and the blue Texas, telling you what it's gonna look like if you just hit in or now. And that's right. So just close it off with parentheses. Type inner that's gone, Captain eight. So if you want to do that for the line below it, just like any function after you interact, you have a blue rectangle, and in the lower right hand corner of the blue rectangle, you have a little square. Hover over that till your mouse becomes a plus. Hold down your left mouse key and just drag it down. I'll go further so you'll see it stops working. But these air relatives sell references. So the next one picked up this line when after that picked up third line. So that should do it. That covers the top 10 functions that you're going to use working with spreadsheets and almost all the time. These are gonna be all that you need to get done, What you need to get done. If they're not what you need, you can find all the others if you go up to insert in select function. This is some of the more common ones we Damon hit on Max and men you doom or this is going to list all the functions so you can look them all up here to find what you want. 10. Protect, Hide, and Edit Sheets: Okay, So you may find yourself in a situation where you're working with a spreadsheet with lots of other sheets inside of it, and you want to work with all of those other sheets in a multi user environment. We're gonna use this set up to show how that's done. So in this example, we're saying that I'm managing New York, my coworkers managing L. A. And then we're going to bring in a boss who's overseeing all three of them. So since I created this mirchi, I'm going to be able to protect these different sheets and have different actions, depending on who's using the spreadsheet. But before I do that, let's just back up a little bit and look at all the sheets that are in here. So there's a summary she, with all of the locations, is summarised in the table. Then there's summary sheets for each of the locations. But then you also notice that there's Mawr. Details of New York has four other sheets, but you're not seeing them down here right now. So that's the first thing that will talk about. And if you want to follow along with your own copy of this file. You can check out the link to the course and description this video. You can hide sheets from view, but everyone's going to be able to see that they're hidden. You just won't see them in available sheets at the bottom. So this isn't a security measure. This is more just in operational consideration to keep this sheet looking clean so you don't have to visually pick through 12 different tabs at the bottom. If we want to look at one of these hidden cheese, so we have to do is left. Click on it to select it, and it will bring it up. And there's a couple of things that we should talk about at this point. So if I just double left click on this worksheet, I can rename it whatever I want. So I have given these all descriptive names instead of just leaving them at the default. She won she, too, and the next thing that I did was color coded them, so they were all showing. You could just tell which branch a belonged to. Buy the different colors, and you can change those colors just by going to change. Color will go back to New York, You one. We're going to hide it from view again. But remember, that's not any layer of protection. It's just visual. So let's hop over to this other account. For now. This is one that I created old Michael. So let's just call him Mike. He can also go to this list of the sheets and see all the ones that I've hit him from. Few. So next we're going to talk about actually protecting these sheets. And what this protection is going to do is stop the other person from reading it. Or later, we're going to show how to just give a warning message before someone edits it. Just saying, Hey, you might not want do that. And similar to hiding sheets, this is somewhat of a security measure because it stopped someone from editing it. But if they really want this information that can just copy it into their own spreadsheet, so I wouldn't use this to protect confidential data, they're still going to be able to see it. But I would use it for an operational consideration. Just protect who can change what? So I'm New York, and I don't want Mike to be able to edit my sheets, so we're going to right click into protect sheet. You would also have to do this on all the details sheets, but we're just going to do it here for now. We're going to say New York summary. So we're just describing the range because if you end up with a list of 20 different protected ranges and protected sheets, you want to leave yourself hence as to what it is that you're protecting. I don't want him to edit anything in here. If there was a little column that he may want to edit, you can just left click on, accept certain cells, carve out an area that he could manipulate. But we're going to leave that off. And when you left click on set permissions, you get this detail here asking you exactly what you want to dio. If you only wanted yourself to edit it, you just do that. I'm the owner of the sheet, so I created it. It will let me do that, but let's do customs. I'm going to leave myself on. I put this person in here as my boss, but we don't want Michael to be able to change things. Let's left like that. Take it off and click done. So to show you what this did, we're going to switch over to Michael. And you can tell this is Michael. If you go into the upper right hand corner, you have two hands here. One is the big M that you over over and the other one is. You see, my face is kind of behind. Let's go back to mine and you see that Michael's icon is behind. So if the lot of users were logged into the spreadsheet, you would see them up here matter. Fact, I'll go log the third person in right now. We'll be right back. Hold on. All right. They've arrived, the bosses in the room. So this third spreadsheet is the third person that will be talking about it a little bit. I just want to show you that you'll see all the users up here, and you can chat with them if you want. And this tutorial already assumes that you shared this sheet with these people. If you want more information on sharing a sheet, you can check out the lesson on sharing and protecting. But we're going to move forward with protecting these cells. So I have a lock down New York. You see this lock icon is showing on it. All the users, we're going to see that it's just showing that protections applied. It doesn't indicate who is applied to go back to Michael. If he comes into New York and he tries to say lovely, blah, nothing happens. You type, and just literally nothing happens at all. So we have the New York sheet locked down so that Mike can edit it. Now that we have that protection on, I'll show you how the just applying a warning works. Go back to my account, go to the all locations sheet. Will, right, click and protect sheet. We're going. Teoh, get this a name, and when we set permissions, we're going to choose to show a warning when edit in the range. So when you choose to show a warning, it doesn't give you any other controls, and you can't customize the warning message. But let's click done, and it's pretty simple. Every single user that tries to edit this sheet after they press center, they're going to get this warning. It's just giving them a heads out to be careful to make sure that they want to make that change. And I did that here because I've already created this. Everything's on Michaeli linked. You really shouldn't have to change it. So it makes the users think about it before they dio. So let's go back to pour Mike's account. He's been locked out in New York, but he really wants something changed. He's seeing this eight in here. It's bothering him. He thinks it really should be seven. So even though Mike's locked out of these cells, what he could do is leave a comment. So if he really thinks that this eight should be six, my could right click left. Look on comment. Tag me in the comment by using the at sign and just starting to type my email address and say you could say, Shoot me a message. I think this may be a six. Let's talk. And if you want to see how these comments working a little bit more depth and how these action items can work as well, lift like put a check mark here, check out one of the lessons that's coming up soon