Google Sheets: Blue Belt Intermediate | Miguel Echeverria | Skillshare

Google Sheets: Blue Belt Intermediate

Miguel Echeverria

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
7 Lessons (44m)
    • 1. Introduction

      2:22
    • 2. Headers, New Features

      8:12
    • 3. Formatting Headers not Cells

      10:32
    • 4. Keep it Simple

      5:16
    • 5. Formulas

      8:51
    • 6. Charts, Explore Button

      4:47
    • 7. Sharing

      3:57

About This Class

This spreadsheet class will teach you intermediate google sheet skills. I would consider this a Blue Belt Class - White, Blue, Purple, Brown, Black Belt. You will learn how to organize data in a spreadsheet, how to keep it simple, dive deeper into formulas, the sharing feature and so much more. By the time you complete this course you will feel confident in your spreadsheet skills. You will find that there are many uses for them in your life.

Transcripts

1. Introduction: shaved ice have to do with stick around. Hi, my medicine to go and I'm a text teacher. I've been teaching for several years and I really enjoy teaching, um, technology to students, especially enjoyed taking something that could be kind of difficult to understand and making it easier for everybody on. So that's that's why I created this course. So in today's class, you're going to be learning about spreadsheets. So I've done another video in the past where I called the White Belt beginner course. So that was kind of introduction. Especially. This would be the next level, right, a blue belt or intermediate and spend sheets. So this is a little more advanced. We're gonna cover a lot more stuff. We're diving to headers. It is especially how to keep your spreadsheets very organized and on covers and new stuff. That's in spreadsheets as well as sharing so a lot to cover this one. Hopefully, by the time you're done, you'll feel confident and spread. She's you'll be it a blue belt on working in a way towards becoming a black belt of spreadsheets. So I really hope you enjoy this course and please follow along whether you're starting your own business. My mind is an example of a snow cone business. Um, so you can actually five this to your own business, Or you can simply just learn about spreadsheets. But I would encourage you to do a split screen. So you're following along with me on one screen and on the other screen you're going to be I'm watching the video, So anyway, take a look. 2. Headers, New Features: Let's go ahead and get started. So typically, when I get into Google Sheets, I open up my Web browser, Chrome and I open a new tab. If you have one open, you click here and I go to Google Drive. Okay, I found that this is the best way to organize your files because you couldn't do this. You could have gone over here and gone to, um, Google Sheets right here. Right. Which takes you to a screen. It looks like this, which has awesome templates and stuff. They're great, and it's going to show you a lot of stuff, but it's not gonna organize all of your stuff into nice folders. And so I create a folder here, and I created good. We'll see here. And it all just a more organized. So anyway, just preference. So I'm gonna go ahead and go to new Google sheets. And now I have a new Google seat. Get the first thing you want to do is give it a name, right? It's called untitled presentation right now. Not a very good name. So make sure you name everything, so I'm gonna name this thing. Um, Snow, sir. Shaved ice hut. Okay, Once you give the name and click away, it's it saved in there now, and you'll notice that it's always updating you with information right here Is telling you it saved to your drive. If you want to change with the Fuller is from here, you can and you can start here. Um, so just a quick reminder. I showed you this in my other video. I'm about making ah ah expenses. She, um but just a quick recap. Um, these are called cells, right? All these air cells. So this is so a one. And when I click on it, you get a cursor. If I want to type something, take a loo. Right? I just start typing. Um, you can also go inside of the cell by double clicking, and you can get inside the cell. Okay, if I want to delete that word Hello. I could double click in it and press backspace, or I can just click on it and press delete on the keyboard or backspace on the keyboard and it's gone. So let's go and start plugging in our information. So whether you're making a shaved ice hut business or you have your own business or you want to start a business you want to start with. Okay. What? My supplies That I need to make this thing. Okay, so we're gonna plug in what? It called headers, right? Anything comes across this top, they'll have information that's similar down there called headers. So what other headers we add in here? Well, about price. So we have priced. And if you press tab, it will actually just jump right over to the next, um, next column and gonna type the word quantity. Go ahead and do that press tab and measurement. Um, no used Teoh and last one total. And it's gonna press return, so it's got You're gonna return back to the spot. I started having it anyway, so I got all these these headers here? No. Before we jump too far into this, I do want to point out a couple things about Google Sheets is that this is amazing program because it automatically updates there's new stuff all of the time. Okay, I like to kind of look through these little files sometimes these little, uh, these tabs sometime because they'll actually highlight things that air new. For example, right here. Images you see as little green Mark. Oh, this is new. You can add an image inside of a cell, okay? Or an image over the cell that's new, um, slicer, um, keyboard shortcuts. And there's some other things would have been around for a little bit, but it new to me that I haven't really messed with them. They're awesome. Now are things like removing duplicates and splitting text to columns. So there's so many cool things that are in here. All right, now that you have these headers, let's spice it up a little bit. And when I want to do is select from a one to F one, and you should be pretty familiar with this now from my other video. But you simply click inside of the cell and you drag over. Okay, Make sure you don't do this. Which a lot of people will do is they click on here and I'll see this and then they would drag overthinking. That's OK, but what that would do is just mess everything up. So when you mess something up, get used to undoing a redoing. So I like to look at the little shortcut for on the Macintosh. Its its command Z, isn't it the same Macintosh gonna say it's command Z under you. Okay, so remember, you click and you drag in the cell and I want you to go all the big trough across the F one . Okay, with this selected, let's come up here to, um Oh, and by the way, sometimes if your screen is squished over too much, it will show these little like dots here. It just means there's more information under here. There's more tools available to you, you know how to click on that. So if you're watching along with the video and you're doing as I'm doing like you do like a split screen, then you'll probably get his three dots unless you have a good side screen. But I'm gonna go and stretch My now here makes life easier for me. Okay. With that selected, let's go ahead and click this little fill color paint bucket tool. And if you look down here, there's a thing called alternating colors. Go and click that. Okay. Good. And pick a color. Do you like person that already, um, preset for you? Go and find one or you can make your own custom header. And these are the debt represented. Different roads. This is a row and this is a row. I'm gonna go ahead and is best done. I like this one. That's fine. And you'll see it just did this top row, your friend wondering. OK, did it work? Where were the other colors? Well, you'll notice as we go along these air actually gonna fill in so at least they should. So it's safe. For example. I'm just gonna throw in some words here and press return and throw in some words and president turn letters and you'll see it's automatically it's automatically doing white, green, white green. Okay, I'm gonna go ahead and undo that Command Z, Okay, so that's honestly will just make it a lot easier to read, especially when you're reading a lot of information across the screen. It's kind of hard of heavy. I follow along, So having this up separate colors really helps. Okay, and you have it all set up, ready to go for our next step 3. Formatting Headers not Cells: All right, let's go and keep going. So whether you're making the shaved ice hut business with me or you have your own business or you want to start your own, you're gonna want to list the supplies. No, really, What I'm making this four is to figure out how much is it costing me to make one regular snow cone for a customer? Right. So if that information is useful to your business, Like how I count much, is it costing me to make a sucker, or how much is it costing me to make this necklace or whatever it is? Um, breaking it down, listing all of the supplies needed for it And the price and quantity All that will help you and your business figure out how much I'm to charge a customer. So that's what we're doing here. Um, so if you want to just follow along with me, you will be learning a spreadsheet. But you also be learning kind of Ah, how to get a price. So I'm gonna go ahead and plug in my stuff here, and you will as well only syrup cups in spoons and ice. Well, just limit it to that. Okay. And you'll notice this is filling in as I go. These alternating colors and we can always add more. And and you can go ahead and do that if you want. All right, The price. So I just went online and found how much it costs for, Like it. I could thing of, ah syrup 16 95. And it gave me 120 ounces. So I'm gonna write 16 95 128 and I'm just going to write the word announces. Okay. And, um, I figure out how much it would actually take to get one snow cone. You'll need to two ounces of it. Okay, so it's just like this in for now. Um, you'll notice that this is a price, right? 16 95. But it's not representative here represented here. Let's fix that. No. In the past video I showed you Well, you could click here and you could change it to a dollar amount. Okay, so now it shows a dollar. I showed you how you could increase and decrease decimals she wanted. See it rounded it up to 17 but I want to keep that change. Won't get precise. Here's the problem, though. Let's say I want to type in something here. Let's be $45.0. It's not gonna automatically do the dollar sign right now. You're president. You Well, you could do this. You could highlight this and change it to dollar. So now whatever I type in here will be dollar. That's true. That's one way of doing it. Um, but thinking about this your list may change over time. And may you may add stuff more and more items you didn't think of all. What about what about napkins? What about this night? Um, so that's not really an efficient way of putting in a dollar sign or putting in that Ah format. A more efficient way is to click on the column. Okay. So rather than use the cells quick the column and say, Hey, this whole column, when you will, you form as a dollar sign and it will now. So, Mr So that's 45. And go ahead and plug this in, by the way. 45 there, um, spoons, or $3 in any effect cents an ice is $10 even. And let's say I'm just gonna throw in some numbers here. Don't put anymore in here, but you'll see It's gonna just keep going, Okay? Isn't that so much better? That's way more efficient. That's what this, um, next level of learning spreadsheets is all about is being more efficient. Okay. And making it way easier to organize your data. Super valuable. So Okay, let's go and plug in the rest of the information here, and you feel free. Deposit any point and play this info And 300 200 1 92 and clicking interest have here. So this will give me, um, a six ounce cup. Six ounce, huh? This is a spoon. And this is ounces. The amount that I will need The snow cone as we 16 ounce cup, one spoon and 10 ounces of ice. Okay, here we go. So go ahead and positive for you to make sure your information looks ok here so that when we move on, um, it'll look like mine. Something I want to point out real quick and notice the it moved my my dollars, the numbers to the right side of this. So right. And my words and honest. It's on the left side, which is totally fine. We can keep it that way. You can change it if you want. Um, if you wanted them to all be on the left side, for example, this just preference. If you had problems making things look nice and tidy like myself, then you may want them on the left. I know how if you want. So to change the alignment, if you remember it, is this button right here. I can lend it to the left, and once they press it, you're gonna start all now to the left. Okay, Now, I'm gonna undo that. I'm just gonna keep it like this. I don't know why. I'm just gonna go ahead and just keep it going. How they how automatically did it. And that's That's fine. Okay, let's move on to totals. I remember, like I said before, we could plug in a function here cause that's we'll need. But then that means I'll have to apply it here and anything else that comes along with this entire column. So it's a little more efficient to to make our formula using comb. So let me show you what I mean. All right, so just to remind her to begin a function. I'm gonna press the equals sign on my keyboard. Okay, um, I could also get functions over here, but the one that I'm making is a little more custom that I could just find in there, so I'm not sure what I mean. So I'm gonna say equals and you're gonna do this as well. But put in a a apprentice E and lexical sad face it is realized, and I'm going to say this divided by this. So rather than click here, I'm gonna click up on the com this divided by on my keyboard This I'm gonna go and close that Prentice e and then I'm gonna multiply But the times fun sometimes it buy it. And now that I need, I need to balances some of procure whom So I'm gonna multiply by the amount used. So rather than click here, I'm gonna click here and now I'm gonna press return 26 cents. Okay, now, I could come over here and simply click on this auto fill handle and drag down as far as I need to right there, you know, automatically filled in the formula. So if you if you look at it, look, shows right here. Be divided by C times E. That's the same thing throughout this whole thing. Okay, much more efficient to make your formulas like that. Especially if you're just keeping things nice and organized with columns. Okay, so now that we have a total one more quick thing before we in this part of it is, let's go ahead and make these the width of these lighter. So if you remember, you can click and drag on any of these, um, column letter headings. So again, you simply not in between. No, I don't make sure that makes the hands out there that actually move. You can actually move a calling my accident. It's not really working, but yeah, you don't look like that because you've actually moved that and you'll get this problem. I go, Oops. If you ever accidentally do that, this press undo what you do is you simply click and drag. You just dragged across and it I won't do that. So with that said, you have that selected. I'm just gonna anywhere in between any of these columns. I'm just going to click and drag the arrow you'll turn into a narrow over. Maybe is a little whiter. Okay. All right. There we go. It didn't technically have to make those wider. So if you don't want to, you don't have to. It was just preference. Make that little bit whiner. So anyway, Okay. Good job. 4. Keep it Simple: Now that we have this information, you gotta plugged in. It's great. We can use it, kids. Valuable now. So let me think about, for example, you could figure out all magic cost per snow cone. You could figure out, um, how much money you're making off of each snow cone? Um, he could figure out how many snow cones you have to sell in order to to get your money back with all the purchasing you've made. So information is valuable, right? No. Before I jump into that, I made a copy of this one. Don't don't do this. But I went over here and I said file, make a copy and I mess with this just a little bit to demonstrate why making it look more simple with headers is way more efficient than designing it all up and adding a much acute see stuff to it. Okay. Almost like the more boring this looks the more efficient and well run. So, for example, um, I went over here and I just put it a simple shaved ice. I emerged all these cells together and I wrote saved ice, huh? And I even went over here, and I wrote the word total, and I figured that put the formula in the formula and the added all this up. Okay, this might look OK, but its not efficient anymore, cause what would happen if I wanted to add war things to it? Okay, it's gonna mess this up. This all this up here. Um, but where it gets become, really a problem is when you need to sort it. Okay. So for example, let's say I want to sort all of this to be, um, nice where? Maybe it's on an alphabetical order or something. So I'm gonna highlight all this, and I'm going to go to data sort range, and it brings up missile window Says data has hetero Well, I do have Header road. Like all these things are my headers, right? Supplies, price quantity. So yeah, I've got that and says shaved ice. Okay. And then it you realize Oh, wait. It's thinking this is my header. And now I've kind of related problem. And this, Like I said before, using headers has way more efficient than thinking about the design. So I'm gonna get rid of this. I don't want this. Okay. With this, I can work. I can sort things. So this is just another thing I want to show you have a sort. So there's a drop down area and I could press sore a through Z, but it will have a little problem doing that. So watch my absolute press were a through Z. It sorted everything right? Not just this column, and it also sorted my header. It put in alphabetical order. See, I access as so That's a problem. We're gonna undo that. So typically, when I sort something I like to highlight my columns, they wanna sort And then I go to data data, data, sort range data has hetero. Yes. Look, here's all my headers. This looks way better. This is This is right. I want to sort that eight easy. I could even at another sorting column. I'm not gonna do that now, but there's a lot of times like, let's say you want to sort enough, but 1/4 than you wanted to sort like the price and lowest to highest. You could do that here. So I'm gonna press sore and look so it all looks nice. So learning to keep it simple in order to sort. It is really valuable. Okay, so where you would want to make it a little like It's prettier. Fans here wouldn't be on this sheet. Great. It would be on another. She This is called she want Let's add another sheet in. So I'm gonna come over here to the plus at a sheet. And I have two sheets. You notice there's little drop down arrow? You could rename it like I want to name that something You could name it, rename it. You can change the color. You can even copy it to another spread. She duplicate it, hide it like this. Someone you could protect it. Like maybe you're sharing this with someone so many cool things in here. But I'm gonna go seat to It's just a blank. She right? Nothing here. This is setting us up for the next step. Good job 5. Formulas: All right, let's go and continue honored. Um, if you turned away for a minute, let's just review Riel Quick what we've done. We started making this this Ah, she with a lot of good information, right? We were trying to keep it nice and simple is weaken. Sort it. And now we're gonna try to take this information and use it to learn a lot about our business here. Right? Figure out some good numbers to know, Like, how much does it cost? Personal grown. We could put that all in here, but it will be much more efficient if we put on another sheet. Right. So we had she won, and she too. So we're working on sheet to now. And I did not rename these, but you could. So this is again this reminder. This is all still within the shaved ice spreadsheet. This is just another sheet within it. So let's type in some stuff right here. Good type with me. Retail price, uh, cost. Uh uh, shaved ice, um, profit per shaved ice by the prophet. Margin supplies on total and break even. Point right. Some key pieces of information to have a successful business here. Right. Um, I did pull this over a second ago. So you just did overlap and to be one. So if you take this and is pull this over just a hair and I won't overlap in there. Okay? So, retail price, I'm not going to format the entire column like we did with the other one to be a currency. I'm just going to do them individually, per so? Because that are gonna be a little different. So this 1st 1 is a currency. Someone impress here, and I'm just gonna type in $2. Gonna keep my decimals right there. That changed. Now you would plug in. How much does it cost for your product? For deciding the one item that's return to figure out. Now the cost perceive guys, um, that is gonna come from the total oath from the sheet one. So let's go back to it for sex. I'm gonna come over here, Sheet one. I want to figure out what's the total of all of this and put it over there. Well, how did I do that? Well, as we do, you simply go back to sheet to start to jump back and forth. And just like you've done before, you start a formula with what good job starts with E equals and to add something up. Is this some? So I'm gonna tape some. We talked about this in the other video, and I like to start my little parentheses that those air called. I feel like I'm saying that wrong for some recent parentheses, E. Okay, now that I've typed that, I'm gonna come over to sheet one. Don't click anything except for what I tell you. Click sheet one and click. You'll see. It looks like something's happening. Your just ignore that click right here, right. If I click any of these, it'll click that sell. But if I click, this will do the whole calm Something quick. So look, now it's wanting to the whole column and I can go back to sheet to and press return. She is saying she won, um, is coming from the f calm. So I'm gonna press return, but very, very good. Okay. If you messed up on any of that, let's say they used we're putting it in. You do this one more time. If you pressed, equals and you're taping some and then you actually click somewhere And, uh, I remember you can always press escape, right? Just press escape, get out of it. So again I'm gonna press equals some started. She won And quick care. Go back to sheet too. And I could put the closing bracket we're gonna suppress. We turn over. Good. Put it in because we turned. Now it's saying it's cost 95 cents with always all these desperate. That's way to me, This one. So this is a currency. Let's go in his press currency. 90 around it. Up to 96 cents. Okay. All right. Next one. So we have the retail price. We got a cost per shame dies. The next one is profit per shaved ice. Well, that's simply this minus this. So I don't need to go to another sheet to tell me that information that can just goat equals this clicking in on this cell minus. Sign this and it tells me it's gonna be a dollar. Oh, for and go and press return. Here we go. Get some good information here. Now I know how much my I still comes costs. I know how much making off every single snow cone. And, um, my profit margin is going to be Chris equals screaming this divided by this and press return. So be three divided by B one. I could type that as well. I prefer just to click easier. And this is actually a percentage. So I'm gonna come up here and use this form as a percent, and I could lower or raise the decimal point. I'll go ahead and keep it there. 52%. So that's some That's my profit margin making 52 presented profit. So hey, not bad, right? I don't know. You're better business people, and I am here. That's you don't find a way to get lower prices. Okay, so I'm gonna go ahead and go do the next one equals the sum of all my supplies. So I'm gonna go back to sheet one, and I'm going to click on this column and I'm just gonna go and just press return this time . You see, this is here. So it kind of reminds you What are you typing again? So I could go back to sheet to and it would see this hair, but it's also giving me a preview here, So I'm just gonna go ahead, close that press return. Okay, There we go. So that's how much my supplies are. $75.90. Last one. How many snow cones do I have to make to break even to make my money back? My $75 I put into my business. So let's figure that out That is simply equals this divided by, um, this profit for shaved ice. So I have to sell 72. I don't like all the decimals there. I'm gonna get rid of him. So again, I could come right here and say Decrease the Dessena, keep pressing it after so 73. Then they go around it up 73 to make my money back. Okay, all have. Having all this information over here is way more efficient than the having on the other page. It is kind of annoying that you have to see on a separate she But we were more efficiently and I promise you, so you can never have jump back and forth. You have this, but that's OK. It was good. A lot of good information here. So great job 6. Charts, Explore Button: All right, You've done awesome. So now we've got a sheet. One sheet to she one has over data. She too has some, um, ways of calculating some information from our data. So no, let's let's do something. Let's add in a Pitre, okay? It always looks good. So hi shirts, Airfone. So you simply can you could do it a lot of different ways, but what I want you to do is select a becomes these two columns and come over here to insert chart and a little pop up When will appear. So what do you want? This chart. But I don't really want that one. There's a lot of different ones you could do. I want a Pitre. So I'm gonna come over here and say I want know any of these, But I want this one, but not any pie chart like the three D picture. Okay, And here's my picture, and I can click away from that now, when I when I click away, my little screen here goes away. Well, if I want it back, well, you can just double click back on your pie chart and it will appear again. Let's go click away double quick things it back. Okay, um, I want to move this over. I don't like coats out of the West. I'm just good little hand. So she I did that and click on it. What are you doing? Oh, yeah. You just started dragging and click any drag, and I could resize it, make a little bigger. Maybe I'll make it kind of the width of this. What's when we come over here? Great. Seven days, painter. This is showing me how much Um, I spent on each product cups, man, they're expensive. That's what that's what's killing my margin. Right? So I gotta bring this cup price down 59% my of my costs are going to my cups. Yeah. Um, let's double click back on this and look at some of the things we can do in here. All right? We can I'm gonna press customize church style. You could have a background. You can order. Color can change the text. Look right. You can change however you want close That we know. What about tight are the actual pie. It's so we could add in a donut hole you wanted? Um yeah, the the pie slices We could change the color of the pain Which when you clicked on to see if I click on the green shows dream blue, red, yellow, blue You could change those colors. Um, we could add a chart title right now. There's not a turnstile. We could we could put something on air like supplies versus the A price. Um, of the supplies. I don't something here so you can always customize this stuff. Okay, you click away. Never get a picture. Something else I want to point out is down here. There's a button. You may have noticed it. It's explore urban canticle. I'm gonna click it Great. Click away. So when you click it, it wants to pull it some information. It it's a way of figure automatically figures things out for you. Right? Um, depending on what you have it selected. So if I If I was to select these two columns, it was so guess what? There's some of all that is this averages this minimum. Is that Max? Here's a pie chart for you. Look, it looks exactly our pie chart. You could add it in right? So I just wanted to point this explore, but it may be useful to you. You can kind of ask questions, too. Like how much? What's my most expensive product? Things like? I don't know. And it will give its best answer. So if you find it useful, there you go. Okay. And onto our last step. 7. Sharing: All right. Last step. Super amazing. Part of Google Sheets. Probably like one of the most revolutionary things that has happened and all of computing this one single button right here, This share button. So amazing. Okay, All other, um, organizations, companies that make stuff events. So Well, that's another big reasons. I love Google sheets. Bring a person share. Let me share this, um, brings up a little window. So do you want to share it with? Well, if you know their email address, and I'm gonna play one in here now, when you plug it in and says, Hey, what What kind of what kind of share permission you want to give him? You want to make them someone who edits along with you and editor? Or maybe there's someone that can view it. They can't added, taken this view and or a commenter so they could just make comments. So that's kind of viewing and being able to make a comment. So this one, they're limited doing nothing but viewing it. Editor. You giving them lots of permission here, you could make it. They can delete things, they can add things. So, being careful who you're giving your permission to, but this is so cool. So I'm gonna present, um, editor on this and I could write him a message. Shave. This is the thing. I'm, um, sharing, and I'm gonna press send now. Okay. So when you share it with someone, it goes to their Google drive so they could simply go to the email, which it would probably send him an email. I think it sends an email, or you could make you go to their Google drives. Hey, I share something with you. Go share with me. Um, so I shared with me. Okay, so this is it right here. Awesome. Look, I'm gonna have I'm gonna open it two screens here so you can see his. Here's the person I shared it with on one side. And here's myself from this side. It looks exactly the same. But not only that, let's say they just said Hey, you forgot to add You forgot to add something here. You forgot to add, um, napkins, napkins. And they felt this out. Look, I can see live. Is there typing? Ah, so cool. Love it. It even shows you. Look, this is where they're actually clicked on this cell right now. We're going to bury a just myself is clicked right here. If I click here cooking over here so cool alive, you could both be working on at the same time. Amazing meaning at any point I could say. And you know what? I'm cutting you from the share permission. I'm going to get rid of you. I could do that. I can send a little message, could chat circle. And anyway, I hope you kept. And I hope you enjoy this. Um, please feel free to write a comment or review, and them would come. You are now officially blue belt in spreadsheets. So proud of you. Okay, Stay tuned for more to come. You're just gonna get higher and higher belts and one day be a black belt, So great job.