Getting Started with Spreadsheets - Google Sheets | Simon Ireson | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Getting Started with Spreadsheets - Google Sheets

teacher avatar Simon Ireson, One Man, One Piano

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

14 Lessons (2h 26m)
    • 1. Introduction

    • 2. Setup

    • 3. Basic Entry

    • 4. Formatting

    • 5. Basic Operations

    • 6. Copy and Paste

    • 7. Basic Functions

    • 8. Long Calculations

    • 9. Profit Margin Sheet

    • 10. Data Validation

    • 11. VLookUp Function

    • 12. Final Spreadsheet

    • 13. Charts

    • 14. Printing and Sharing

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

Welcome to my course on spreadsheets, specifically Google Sheets. Most spreadsheet applications behave in the same way so everything you learn on this course will apply wherever you choose to create your numerical masterpiece.

My teaching style is very relaxed and I gently introduce each topic in a separate video. After years of working with and training on different software I have found that the go slowly and have a go method is by far the best. With this in mind I have recorded the videos in such a way that you can generally follow along, learning as you go.

By the end of the course you will have covered the following:

  • Basic data entry
  • Formatting
  • Simple formulas
  • Copy and paste
  • Data validation
  • VLookUp function
  • Layout
  • Charts
  • Printing and sharing

In order to complete this course you simply need a PC or Mac to work, an internet connection and a Google account.

Meet Your Teacher

Teacher Profile Image

Simon Ireson

One Man, One Piano


Hello, I'm Simon. Pianist, Teacher, Streamer and IT Professional.

With over 35 years of playing and teaching behind me, as well as a career in software development, I am now enjoying the restful life in Orkney. With performance Diplomas from the Associated Board and the London College of Music, as well as a Masters Degree in Piano Performance, I continue to work hard at improving my playing.

Since moving to Orkney I have begun live-streaming my piano as well as teaching piano and software online. I still have a strong passion for encouraging personal and professional development in others as well as keeping current with the latest technology.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Introduction: Hello and welcome to my course on getting started with Google sheets. This is a nice simple conversational course. None of your hard hitting two-minute videos here. I'm just going to ramble and give you a nice little tour of Google Sheets. And together we'll start from the complete beginning. So if you've never used a spreadsheet before, this is ideal for you. All you'll need for this is a computer which I assume you've got because you're looking at me now, as well as a Google account. You do need a Google account, otherwise you can't use Google Sheets. 2. Setup: So this course is going to concern itself with getting started with Google Sheets. Other spreadsheet applications are available, the most common or popular if you like his exile and pretty much 90% of what you learn in this course, you can open Excel and do exactly the same thing, probably with exactly the same terminology inside XL. However, we're gonna specifically used Google sheets. And this short video is just to get you started in Google sheets and getting a spreadsheet up on screen before we actually do anything else. So let's flip over to the screen. This is Google Chrome, which is where Google Sheets runs. And if you're at any point in logged into Google, then you should find your logo here, not my face, but your face. And next to it is this little array of nine buttons. And if I click on that, then sheets becomes available. I can click on sheets. And that's going to open this screen here. I have zoomed into Chrome a little bit, just so it's nice and clear for you. It's best for recording that wave. I'm also then going to just make it full screen. So we don't have to look at the address bar anymore. Once we're here, there are templates, but we're not gonna be using templates for this course because in some ways when you're getting use to using a new application, you don't want a load of things on the screen that you haven't set up yourself. You need to learn from scratch. So we're gonna go for a nice blank spreadsheet. Once I get that, I'll get a nice What can initially look a little bit intimidating screen got all this lovely grid down here and some menus and some toolbars, all sorts of things. What we're going to concern ourselves with Just for this video, here is a brief overview. So I'm gonna give this spreadsheet or name. I'm going to give it a course introduction. If I could spell, press return. And the lovely thing about working in Chrome and on Google Sheets is the fact that it saves all the time. So every single time I do anything, it's saving it. So if anything should happen to my laptop, then I my work is safe, safely stored on Google. So that's got me into the sheet. I've now given it a name. If I then come back out again, I can click on sheets home with this green icon again, click on that and it will take me straight back to this page it was before. And by default, these files are listed in order of use. So if I then click on Course introduction again, that will take me back into course introduction. There it is. And then I can come back out again. And should I wish to delete it, then I can go over to the three dots and remove or rename opened new to what I'm offering here. But for now we'll just go back into the course introduction. And here is my sheet, and we're ready to go. And in the next lesson, we will actually start entering information in anger and start learning how to use the spreadsheet. 3. Basic Entry: All righty, then let's get some basic information into a spreadsheet and stop doing what we had to do. So let's start with a new spreadsheet. I'm going to start with a blank. And I'm gonna name this basic if I can spell in formation. And off we go. So here we are presented with a list or a grid of possible places to put information in. This is where you store absolutely everything in the spreadsheet. Anything within this grid is where all the data is concerned. We have columns and we have rows and every cell is uniquely identified as if you were playing battleships. So this cell here would be E 12, and this cell here would be C six. You essentially have an unlimited number of columns and an unlimited number of rows. If I scroll right to the bottom here, I get 1000 rows by can add another 1000. If I wanted. I can scroll back up. Likewise, I can go to the right and I get as far as zed or z depending on where you're from. However, this isn't the end. If I were to add a bunch more columns, then we'd get a and then a, b, a, c, a, d, a, and you can have as many as you like. It's convinced it Google Sheets doesn't mind whether you go to the right or down. But it's convention that if you were to have a lot of records, for example, for example, you were doing how many products you'd sold and each sale, for example. Then you would have your columns of information along the top, and then each sale would go down the rows. So it's just traditional to try and keep it as thin as possible, but then it can be as tall as you like. By no means you don't have to do that, but it's just easier for us humans to read. If we scroll up and down through records and the column stay the same. But I have seen it the other way around is entirely, entirely up to preference. Google Sheets doesn't care. It's just the US humans. Looking at the information that can. I find it a little tricky? I have zoomed in on Chrome slightly to make the whole spreadsheet a little bit bigger. But you can, with this zoom button here, I can make the cells at a 150% larger. So you can really see what's going on or back to a 100%. Nothing you do in here as far as viewing, it will affect if you were to print this spreadsheet out, font sizes do. And what you do with the typefaces would change things. But actually how you view the spreadsheet, that's just for comfort and ease of use. So what can we put in ASL? Well, you can put anything you like, any anything alphanumeric, so you can type anything you like symbols. You can even put pictures in if you so desire. But for this course we're going to concentrate on letters and numbers and do calculations within cells. So Google Sheets makes some assumptions as soon as you type something in. And we'll try and convert everything to a particular what we call a data type. We don't wanna get too worried about that. But essentially, all we care about at the moment is, is it numbers or is it letters? So let's do something really simple, really simple. I'm going to type the number five and press return in this cell. So now in C For I have the number five. We've got the number five appearing in two places. We've got to actually in the cell which is where we care about. And also it appeared in this function by here, number five. Now at the moment that seems a bit pointless because it's showing the same thing in both places. But later we're going to have some reasonably complicated functions that we'll add different cells together or some things together. And so there'll be two different things. There'll be the information of heart as far as the formulas are concerned in here. And then the result will appear down the bottom here. But for now, when you're editing a cell, so I can click on there. If I wanted to change this five, I can double-click here and change it to six. Or I can click on the cell, go up to here, and click there instead and change it. Seven. Doesn't really care. And you can edit it in either places, often once the cell gets a bit more complicated and there's formulas in here, it's easier to edit it up here and say You're not fiddling around in the little boxes. But again, it doesn't matter. You can edit it with user preference. So that's seven, That's great. Alternatively, I can type my name in and there's Simon. And you'll notice the Google Sheets didn't shift this text over to the right. So it's just a little nod on the part of Google sheets letting you know that it's recognized that this was just a number. There was nothing else but numbers that I entered into that cell. So it's shoved it immediately to the right just to let you know that it's recognized that it's a number as well as nothing else. We can obviously have combinations. So I can have 7's based Simon. And you'll notice that hasn't shifted it to the right because it's got text in at the same time. There is a little trick you can do as well. There are occasions when you want to put just numbers in a cell that you want Google sheets to treat it as text. For whatever reason. Let's say you had product code 0873 for example. So if I just type 08703, then in it will remove the 0 because it knows that it's a number and 0 doesn't make any sense in front of a number. So I can go 54 dupe five or eight rather. And it will just remove the zeros. If I wanted to have 0873 as a physical piece of text, then you have to put an apostrophe first. And as soon as you put an apostrophe that's telling Google sheets that everything else that comes after that is going to be text. So then I can go oh, A7 vary. And you'll see it's put it on the left. That's telling me that it's recognized that as text. It's not a number. So therefore, I wouldn't be able to do any calculations 0873 because that's exactly the same as far as Google Sheets as concerned as Simon. But that's how it's important that you need to know that that apostrophe function exists so that you can do things like have 0873. Because product codes often start with funny numbers or there's spaces and all sorts of things that you can do that if you don't put the apostrophe, then Google sheets will attempt to reduce it to a number as quickly as it can. So we've got some numbers and we've got some text. But of course there's more than that in the world. So for example, there are things like dates. So I'm here in the UK and we usually think of the dates as day, month, year. So I will say the 13th of February, 20-20. Press return. And it's happily shoved that e1 as the 13th second 20-20. It hasn't really arranged it too much, but you'll notice it has put it on the right. So it knows that it's not text. And it can't be a number because it's got the slashes in it. So it must have recognized it as a date. But what I can do is I could go up here and there's only a little, There's toolbar here. We've got all sorts of formats that we can use. So I'm gonna go to this one on the right where it says More formats. And I'm gonna go down here and select Date. Now if you're in the US or somewhere else where the you have month, day, year, then this will pick up on whatever your regional settings are. If I click that, then it's going to nicely format that as a specific date. I could then go say to my number seven and I can go to here again. And I can say number, which is already done. But now because I've said I want to force it to be a number. It's now added some decimal points. I can then go to the little decimal point buttons and move things around and say I want that specifically to a few decimal points. And if the number was something more complicated like 7.585, this, it will do mathematical rounding on it straight away as you reduce the decimal points. So we'll end up with eight if I keep moving the decimal point. Likewise, I can press percent, and likewise I can press currency. This will default to wherever your standard currency is. So again, if you're in America, that would be US dollars. But here it's a pound sterling. And it's just going to say everything according to the best it can do. If I try and do that on Simon, I'm pressing currency. You can hear my most being clicked and see it flashing slightly, but it can't do anything with that. But do be aware that it has actually formatted that sell for currency. So if I were to change it for a number, it would then reformat it as a currency later on. But that's neither here nor there really. So we've got a date, we've got some currency, and we've got all sorts of data types that you can do. Really, they're the ones that we're going to use the most. For now, either a basic number or currency data handy and it's always nice to have them neat and tidy. So actually datatypes. So that's basic information and getting some basic data into a spreadsheet, the best thing to do is just go and have a practice and just type a lot of information in and see what happens to it. Again, if it gets flipped to the right, then Google Sheets has done something with it and recognized it as a datatype it can deal with if it leaves it on the left, is just assuming it's going to be text and it's just going to leave it alone. And that's normally a quick identify identifying factor if you like, as to whether you've typed something incorrectly. Because Google sheets can't do anything with these ones. But as soon as it's flipped over to the right, then it can start to work on those figures if you like. Well, the information that's in that cell, you'll be not surprised to know that obviously later on, I could move the alignment of this text over to the left if I so desired. But for now, we're just going to leave it where it is. Alright? In the next video, we're going to look at Formatting. And so we can make our spreadsheet look as pretty as possible. And then we'll move from their marvelous. 4. Formatting: Okay, so here we are roughly where I left off last time. I've just zoomed out ever-so-slightly, just so I can say a little bit more of the dual bar. So that we've got a few more than ETL tools that we can work with. So all we're going to do in this video is to have a little play with high weekend change and pretty up any spreadsheet we have. So we can effect one cell at a time by clicking on the cell. Or I can drag over lots of cells and affect many of them at the same time. So let's have a look at say my name, their assignment. I can then go up to here and I click Bold, and it will make it bold or italic, and it will make it italic. Or I can click a strike three if I so desire. Or I can change the text color. All of these things. I'm rushing through these pretty quickly. Because if you've used any other application before, you'll be used to these buttons. And you can literally just click and have a little play the text color you can change. You can have your own random one if you like. So there or I can have that bold. I can then change the size of the text so I can make that 14. I can change the particular font that's available within Google Sheets. And so I can know that I can then change the alignment. So this is where it would then change and look slightly different to how Google sheets would automatically do it. So like I mentioned previously, if it recognizes a datatype and something that you can work with, it will shift it to the right. If it thinks it's text, it will leave on the left. So I'd normally suggest is worry about preteen up your spread sheet properly if you're going to worry about printing it. Otherwise, only do enough formatting to make it clear to yourself to separate section. So like having a title or a headings. So it's easy to draw your eye across the spreadsheet. You don't need to spend hours and hours formatting beautiful things if you're never going to print out just for looking at them for typing numbers in. That's not what you in the spreadsheet for. We can also fill the background so I can make that red if you really want to, horrible and garish and unpleasant. And finally, we can also add borders. So if I go over here, I can click the borders. This can get a little bit fiddly. I would caution you against getting too excited about using boarders because the cells, but right up against each other. So this cell, this is the right hand side of this cell, but this is also the left-hand side of this cell. It can get a little bit confusing when you do, where do wonderful borders. But if I just highlight a load of these, then I click the borders and then I'll get a nice outline of all these, all these cells. And then up top and bottom, I can also say I just want the top ones and I have to clear them out and then just do the top ones and it'll just give me the top line of the section that I've selected. And then I can select this one here. And again, I can go the left and it'll just give me the left of the section I've selected. I can then also change the color of the border. And I can also change whether it's a dotted and dashed line are. So I end up with this. Bear in mind that when you do print out your spreadsheet, then the gridlines, the actual grey grid lines that represent the grid are generally not printed. So you'll then be left with whatever Borders you've created. But as I mentioned it again, I would caution you against doing too much with borders because they can get a little bit complicated to unpick again. And because of the nature of a spreadsheet, it's automatically lining everything up. And you really just want a few lines if necessary. Again, if you're printing it out, if you're not printing it out, then you shouldn't need too many borders at all. Because you're looking at a grid all the time. Anyway. If, for example, you've got a bunch of text and you like the way this particular cell is formatted and you want to apply it to this cell. Then you can go here and then select your form and select your sized bolts like the colors. But there is a handy format painter. So if I'm on this cell and I want to use that formatting somewhere else. I can grab this sort of roller paintbrush. Click that button once. It's then highlight that wherever I click Next, it's going to paint that Format on top of that cell. And you can do that for all sorts of things. I can do the same thing again, and I can just drag over the whole thing. And it will reformat things accordingly. You'll notice it's done everything. So this was text. So therefore it's taken that weird one, that date and converted it into a weird and wonderful date number, which is usually from the has_many days is since 1979 or something like that, 1970, or even earlier than that. But either way, it does all the formatting, so do bear that in mind because it will change the datatype and everything is turned everything into text. If you do something a bit weird and wonderful, then you can go to Edit and just do undo. And you can do controls ed or Control Z. If you want to do a keyboard shortcut and it will just remove or revert back the last thing you did. And you can keep doing that until you run out of undo, which is a long while he remembers many, many clicks. So that's formatting. You can do formatting to a whole column. So if I highlight this column and I say I want the text to be orange, then everything in that column will now be orange. And if I highlight, Well, I haven't gotten a double thing. Let's do this row. If I do this row and say I would like impact, then ev, every row in this, every cell in this row will now have the font of impact. And I can set it to nine and it will be nine. I can make it all bold or not bold. And it will be applied, that change will apply across the entire row and no prizes for guessing that if I were to select and drag across two columns, I can then change the font for those two columns and everything. And it goes beyond the, off the bottom of the screen. So if I scroll down, you'll see that all the columns have been highlighted. All the rows have been highlighted for those columns. Likewise, if I click this row or glick, who moved and dragged, if I click and drag across all these rows like this, then you can see it's highlighted all the rows across the screen. Simple as that, that's basic formatting. And next, we'll start doing some adding up and taking away. 5. Basic Operations: Alright, then let's see if we can get this spreadsheet to actually work for a living and start to do something that we couldn't do with a sheet of paper. Because at the moment what we've done is we've just written some information down, perhaps changed its color or changed its size. And that's not really using a spreadsheet at all. So let's see if we can do some sums in a spreadsheet and work out how to do that. So the very first thing we're going to do is just pick a cell at random. And I want to do a very basic sum. So I want to do four plus five. So I'm going to type four plus five, press Enter, and it's done absolutely nothing. And if I just move up and down with my cursor keys, I can move up and down the spreadsheet like that. But I've ended up with four plus five and it hasn't done anything. It's not added that up at all. And it looks like it's just not working. Well, if we think about what we did initially was when we entered just a number or just a date, then Google Sheets knew what to do with that. It knew how to deal with just a number or just a date. And it moved over to the right and said aha, year, a number or a date or anything else. It's treating as text because this has a plus sign in it. And as far as Google Sheets is concerned, is a plus sign is exactly the same as the letter a or the letter Q. It doesn't know what to do with it. It just treats it as text. In order to force Google Sheets to do something with this information, we have to tell it that we are going to be entering a calculation. So in order to do that, we need to add an equal sign at the start of the information. So a bit like when we put the apostrophe in the apostrophe said anything after this is going to be text, then the equal sign is anything after this is going to be a formula or some sort of calculation. And the way we do that, if we don't edit SL, I can just double-click here. And that'll allow me to bring up the cursor and I can move around in the cell. Or sometimes if I just click on the cell is easier to actually work up here in the function bar because you're not fiddling around. And sometimes this will end up with lots of texts which will go out beyond this L. So it's sometimes easier once you want to start editing a cell to go into the formula bar up here. It doesn't matter. It doesn't care either way, but sometimes just easier to work up here. I'm just gonna zoom us back in so that we don't have to fight with squinting at the screen. Now we're gonna go back up here. And instead of 4x plus five, I'm going to put the equal sign at start. And you'll notice that even though I haven't pressed return yet, Google Sheets is already anticipating that the answer is going to be nine and then a press return. And now we've got two different things. If we look at the cell, we've got the result, which is nine. And if I look in the function bar, I've got equals 4x plus five. And that's great. That's exactly what we want. We want four plus five equals nine. Marvelous. Our work here is done. We now have a calculator. Clearly, this is doing nothing more than what you could do on a calculator. But we're going to gradually work up to some slightly more complicated things as we go. So four plus five equals nine, and 4x plus five times ten equals 54. Now, you may not be expecting to see the number 54 here. You might be expecting to see four plus five is nine times ten is 90. And is a very important thing that matters must sink in and must be acknowledged right away, is that the order of operations follows mathematical order of operations. So multiplication and division happen before anything else. So they happen and then addition and subtraction happens. So whenever Google Sheets sees a formula, it'll look at this formula and go, right. I don't care the order you've written these numbers in. All I care is the ICR multiplication symbol. So I'm gonna do the multiplication first. So I'm going to be five times ten. And then I'll start looking at anything else you've done. Then I will add that to four, which gives me 54. So that's very important to remember that multiplication and division always happened first in a formula. However, I might want four plus five to happen first, regardless of what Google Sheets thinks. So I want the four plus five to happen before it's multiplied by ten. And we can do that. We use brackets. So I'm gonna put an equals sign. And then I'm going to do open brackets, four plus five, close brackets. And then multiplied by ten. And I'm going to press return. And now we're gonna get the 90 I'm after. So everything within a bracket will happen first. And then everything outside the bracket will happen again in the order of mathematical operations. So four plus five is nine times ten gives us 90. So that gives us some basic functions that we can work with. But again, we've not really done anything here that we couldn't do with a calculator because everything that I'm doing is concerned with this particular cell. So I haven't gone outside of D5. D4 doesn't know anything about D5 and everything that's happened in here I could have done on a calculator. So let's try and do something a little bit different and we'll try and do the same sums that we did before, but perhaps using it across themselves. So we've got the numbers four with 510. So let's go over here. I'm going to put four in this cell, five in this cell, and ten in this cell. So now I want to build up the same information that I've got in this cell, but I'm gonna do it using the numbers that are stored in these three cells here. So we'll go to this cell here. And let's say, let's use this one for the sake of it. So what I'm gonna do is I'm going to say equals. Now, remember we're using the kind of battleships notation. I want to find the information in F four with a lovely thing we can do here is we can actually just click on F4 and it will add F4 to my SMS or to my function. I can then press plus, and then I can do that. Click on the F5, I can then do multiply and then click on F6. And I've got some lovely colored cells highlighted. And I can see the F4 plus F5 times F6 and press return. And I get my 54 again. I can do the same thing here. I can go equals open bracket. Click on F4 plus. Click on F5, closed bracket, multiply, and click on F6 and press a button. And I'll get exactly the same thing. So we've got this formula here, which is four plus five times ten with the brackets, right? It, and we've got exactly the same function here with the same result. The only difference is now I can change one of these numbers and these formulas will update automatically. So instead of by ten, I can put 20, press return. And these cells here will automatically update with the new values. As soon as I update the cells, if I don't have one, then multiply it by one. So they're going to be the same for multiple VO2 is gonna start to be a bit different. So everything I change everything every single time I change an item on the spreadsheet, then it recalculates the whole spreadsheet every single time. Okay, so now we've got something we can work with. We've got something where we can start to leverage the power of having this grid of information. So I'm just going to clear all the cells whose going to sweep across here and press backspace, declare all the cells. And I'm gonna just start working on some different information over here. So I'm gonna say width really can't spell today, and height or length rather. And then over here we'll have area. So I've just type some text in there. And here I'm going to say 45. And in this cell here, in, in E three, I would like to multiply the width by the length to give me the area. So I'm going to type equals, and I'm going to click on B3 multiplied by and then S3, and then press return. And I get the area. And that's great. Lovely. But what happens if I wanted to say calculates from different areas? So let's say I'm just going to put some random numbers. I've literally just randomly types for numbers. I've actually missed one nurse can't type. There we go. Got loader numbers. So then I would come here and I could type the same formula in clicking on these cells, same formula in here clicking on these cells. Fortunately, I don't have to do that. Google Sheets knows that it lives in a grid world. And you'll notice that in this cell, when it's got the blue highlight round, it's got this little, little big fat box at the bottom right-hand corner. If I click on that and drag down and let go, it will copy the same formula into the cells. Wherever I've dragged that, we can drag it down or I can drag it to the right as well. It doesn't really care, but it's going to copy the same formula down. But it's clever enough to know that when it copied it down one, it also moved the cell down one. So here it's copying B3 and C3 from here to here. But when I moved down one, it's doing B4 and C4, and it's doing B5 and c5, and then it's doing is B6 and C6. Yeah, that's great. So we can then use that all the time. And I can have as many different widths and lengths records as I like. And I can just copy the formula down. No matter how complicated this formula is, I can just copy it straight down. Let's take this one step further. And instead of area, let's have volume by adding some height. So let's add a height column over here. And then we'll have a volume. That's other. Excellent. And I'm going to again just put some nice random numbers in fairly random height. So in order to get volume, we multiply our area by our height. So over here on them do equals, and I'm going to say area multiplied by height by just clicking on these cells, pressing return. And we've got the same thing. I've got a nice multiply. So two times 40, or, sorry, two times 20 is 40. And I'd like to do the same thing. Just drag this down. And I'll get lots of volume. And that's great. Nothing wrong with that. But let's say just for whatever reason that the height of everything that we're manufacturing is always the same. So this is two. I can then take two and drag that to the bottom. And that will make everything to, yeah, this seems a little bit of a waste of a column because everything is going to be multiplied by two. So yes, in here, instead of multiply by G3, I could put multiplied by two instead. And that would be fine. But it might be that in our manufacturing process later on, we change. And instead of two, it's 2.1. Now, then I've got to go to every formula, or at least I got change one formula and then move it down. It would be nice if we had some way of saying, give, let me reference a cell that represents the height. And there's only one of them. But I'm going to use that in all the formulas. So let us do that. So instead I'm just going to slightly untitled. I'm gonna put height here. And next to it, I'm going to put two. And then I'm going to highlight all these and just delete it. And yet I've called a bit dark. So now my volume calculation here, which was pointing at G3, and I like this one here, or g4 In this case, it's not going to have anything to add up. What I want to do is use this. So instead, I'm going to go back to here and I'm going to just delete that for now. I'm gonna press equals and I'm going to say area multiplied by height and press return, and that's great, it's 40. So then I can copy that formula down. And I've got lots and lots of zeros. Why is that? Well, that's because when I copy this down, it's done exactly what we did before when we scroll down before. Here's this one which is e3 multiplied by G one. Then I'll scroll down or move down a cell. So it's gone e four multiplied by G two because it's moved down. But I want this to still be pointing to g1 so I could correct this formula. I could go into this formula, edit it, changes G1 and press return. And now I'll get my two times eight is 16 and that's great. But again, if I tried copying that formula down, again, still won't work. What we need is a way to say to Google sheets, when you copy a formula, please keep referencing this particular cell without moving. So what I need to do is I need to use the dollar symbol. So I'm going to edit. This formula. And for the G, one part of the formula, I'm gonna put a dollar in front of the G and a dollar in front of the one. What this tells Google Sheets is that I want to lock G as a column and lock one as a row. I can do it individually. So I can say lock G, which won't help us here because we're going to go up and down the rows. Or I could say lock, lock one, like this, which means keep the same row but allow the column to change. But just to be sure, I'm going to put dollar V $1.1 and then press return. Nothing's changed here because this is never was a problem before. But now I'm going to scroll down or fill down and now we get the result we want. Because when I go from here, this is E three, which is here, multiplied by geology $1.1. If I then move down, you'll notice the e4 changed, but the biology $1.1 did not. And it hasn't changed at all. So now I've got all of these formulas easily typed in, because I only had to create this one and I just dragged it down over top of the other ones. And they're all now looking at this one cell. So if I change the value to three, then they will all change at the same time. If I change the value to walk, then they will all change. And no prizes for if I change it like that. So an easier way to see what's going on and how we can use the dollar sign to lock different parts is to build something like a multiplication table. So what I'm gonna do is just and we'll use it another feature of Google Sheets while we're at it. It's nice again to keep you sheets as tidy as possible. And there is, although it's a spread sheet singular application, it actually you can have as many sheets as you like within the application. And I'm just gonna click this ad cheat button down here. And it will give me a blank one. I've previously added one in a whilst I was preparing, so that's why it's gone. Xi3, it doesn't normally leave out sheet do. And that will give me a completely new sheet. And I can go back to the forwards and I can keep my data or having organized so you don't have to hire everything. If you had lots of different things going on, you don't have to have one at the top and then scroll down and scroll down and scroll down. You can have everything nice and clean. Google Sheets doesn't care. And as you'll see later on, we can actually talk between the two sheets or as many sheets as you like, doesn't really care. I shall stop trying to say the word sheets. So I'm going to build myself a little multiplication table. And I'm gonna do it nice and simply I'm going to have 12345. That's a bit boring, isn't it? Why would I do that? So let's have one. And here I'm going to have equals b1 plus one. And I'll give me two. And now I can drag that to the right. And that will calculate everything based on that, which is great. So I can then say start at three instead. So anytime you find yourself wanting to do something in a hurry or do something that seems really obvious, then don't be afraid to just stick a little nice easy formula in. And we could do complicated things like multiply by two or r two. You know, I could do to instead. So that's three and then copy that across. And that'll give me weird odd numbers. It doesn't really care. But for now, we're just gonna do equals that plus one and copy that across. And then I'll do the same thing. I'll put one in this one and then I'll do equals. And I will say that one plus one gives me do. And I'll scroll down to the essay. So I've got something like, I'll end up with this cell being 11 times seven. Now, let see what we're gonna do to how to work this out. So we want in this cell the result of multiplying a two by b one. So let's do that. We'll go equals Russia. Click on a2, and I shall multiply by b1 and think return. And this cell is absolutely right to one times one is one, even. I can cope with that. But if I scroll is done, then we're gonna get some quite weird results. Because if I look at the formula for this is now going to do a three multiplied by b2. So I'll get one times two is two, which is good. But then if I go to the next cell, I'm going to get a four, which is three multiplied by two. Now I'm getting six and it's all a little bit wrong. What I want is this cell to multiply a four by b one. So just for ease to make it look a bit neater, I'm just gonna get rid of all these ones so we can see what we're doing. So why I'd like to do is use that dollar sign so that when I drag down over these cells, it knows what it's doing and it will lock in place. So when I go down, I want to fix the column that I'm in or fix the row and then let the row move around. So let's edit this formula. Yeah, I always want this to look at the a column. So I'm going to lock that in place. And then I always want this to look at the one row. So I'm going to lock that in place. So it's always going to look at a and then it's always going to look at one. But then I can let it change in the other basis. So I will press return. Nothing's changed here if I drag down now, you'll see that I've now got something that looks slightly more right? Because if I go down to here, this is now doing a 11, which is ten multiplied by b, one which is there. And the lovely thing is because I've taken care to lock both sides of the square if you like, I can now drag to the right and let go. And wherever I click in this formula, so here I can see it's a six, which is a six here, multiplied by e one, which is up here. And we've got the correct result. Five times four is 20. Now if we wanted, we could do something really clever. We could highlight Rowen and make that bold. And we could highlight Column a and make that bold as well. We could perhaps make those a little bit larger. Yeah, and I could do the same thing here and make that a little bit larger. Yeah, just to make it more obvious. And we can do all sorts of things that highlight these ones here and make those italic. We could make them a different color if we so desired. Just to make it really, yellow is really nasty. But green, for example. And that gives us a multiplication table. We can do anything with. Now the lovely thing is that because we used b1 plus one as a formula here, I could change, I can change this and make it ten. And I can make this 134 doesn't really care, but now we've got 12 times. 140 is 1680. Hopefully, you can see that this is the power of a spreadsheet. By entering a correct formula in. We can copy that and do some very complicated calculations very, very quickly. And we can replicate that calculation in many different cells. And we shall look at that even further in the next video. 6. Copy and Paste: Alright, and so in this lesson we're going to look at copy and paste. And one of the ways we're gonna do that is by using the spreadsheet multiplication table that we created last time. But we're going to create it from scratch. And please do during these videos, do try and keep up and play along at the same time if you like. So we're going to call this new mult table just for the sake of anything else to call it. And just because it's fun to have something extra to do along the top and the bottom. So along the sides of the grid, we're going to have a horizontal adjust. This will make sense in a minute. And a vertical adjust. If I can spell. Counts bell. Yeah, we go and I'm just going to have this as one and this is one for now. And to make this neat and tidy, I'm gonna highlight both these. And if I just zoom out slightly, then I'm going to use the alignment tool to just align them to the right, just so it looks a little bit neater. Again because they were text then Google Sheets left them on the left to start with. But now we've got something we can use elsewhere. Now let's do our usual multiplication table and we're going to start with one in this direction and one in this direction. And then we're gonna have our multiplication table work down here. So the ones going to be a fixed point. And then what I'd like to do is this one, instead of just being too, I'd like to be this, to be this cell plus whatever the horizontal adjustment is. So what I'm gonna do is I'm gonna do what I did before, which is gonna press equals. And then I'm going to click on this. And then I'm going to add the horizontal adjustment cell, which is this one here, C1. And that's great. That's not going to be a problem. But remember, we need the dollar signs to try and keep this so that we're always looking at cell C1. Now we can go back on here and edit using the cursor keys. Or I can use the mouse to come up here and edit, to change an ad the dollar sign in, and that's absolutely fine. And I said earlier on, I'm not going to be using any keyboard shortcuts for this, but I'm gonna mention it just because you may wish to use it yourself if you use F4. So here I am. I'm going to press F4 once. And the cell that I'm currently referencing will change to have $2 signs. If I then press F4 again, it will change to just fix the row. If I press F4 again, it will change it to just fix the column. And then if I press F4 again, it goes back to her being sort of a flexible reference. In this case, I want it to be dollar c $1.1. And it can be easier if you can remember just F4, then it's the function key for, then it's absolutely fine. I'm on a laptop so I have to press function and then a four. But either way, much simpler than trying to type the $2 signs in, but typing the $2 signs in will work absolutely fine as well. No problem. And I'll press return. And that will give me that. So then I'm going to drag quick field list to the right until we get ten. So we've got a nice load ten. And if I look at any of these formulas, I'll click on this one here. This one tells me it's going to be i four, which is i four plus c1. This one here. So theoretically if I put two in here, it should add two between each of the 55, between each of them. And you can see the numbers changing as I change it. So a public act one for now, I'm going to be exactly the same thing but moving down. So I'm going to type equals. I shall click on a five plus and then I shall get vertical adjust. And then I'm going to press F4 once just to make it so it's due dollar press return. And then we've got to, and I'm gonna take that down to somewhere here, should give us ten. And again, just eat inspecting any of these formulas just to make sure things were right. If I click on any of these 11 plus dollar, $1.2, which gives us this one here. So now if I change that one to two, then it will change the vertical axis. This is beyond the point. The whole exercises a little bit silly, but because it's a multiplication table, but it does teach us a number of things about the, about spreadsheets. So we're going to create that and that's no problem at all. Then what we'll do, let's put the formula in that we actually want to calculate. So we want this to be a five multiplied by before, but I want to be able to copy the formula. So we're going to use the dollar signs again. So I'm gonna type equals and then we're going to click on a five. And I want to fix the a column so we never want to leave the a column. So I'm going to press my F4 button until I just get a dollar in front of the a. And then I'm gonna do multiplied by and then click the fall. I'm going to do the same thing again, press F4 until just the row. He's got the dollar sign. So I'm fixing a and allowing five to be flexible, fixing B, but allowing for or the row to the flexible press return. And we should get one times one is one. And if I drag this down, I shall get this looks perfectly reasonable to me and then I shall drag it all the way to the right and let go. And hopefully we've got 7749, which looks good. What we got here. Six 5s are 30, little bit difficult to see. So why don't we do a little bit of formatting. So I'm gonna highlight this row. And I'm gonna make that bold. And I'm also going to make that 14. I'm going to highlight this column. I wanna make that bold and make that 14. And just for the sake of amusement, I'm going to drag all the way over here and make the text inside a sort of gradual greens are already good. Let's make it a blue eagle. There we go. And now we have our lovely multiplication table with everything we could possibly want in it. And I can make an adjustment. I can say instead make this five goes up in five. So I can make that go up in 5s. And if I put 0 at the start, then I'll get lots of 5s everywhere. If I particularly wanted my 13 by 15 times table, then I can do that. And I can find out that a 117 times a 135 is 15,795. Bit ridiculous. But we get the idea. So then what I thought we'd do is we just have a little look. I'm just gonna make this a little bit more sensible again. So let's make it two. So we can see what's going on. We'll pop it back as profit back to something. I was uneasy about 1010 by ten, adding, adding ten each time. There we go. Then these are things that we can look at nice and easily. So what we haven't talked about is inserting rows and columns. So what I'm going to do is I'm going to click on this column here, column F at the top here. You can then click on this little button or little drop-down arrow next to the F column, which will offer the same thing as right-clicking. And you can right-click anywhere in the spreadsheet to get a context menu to let you do all sorts things or into a cell here, and we'll come to that in a little while. But for now I'm going to click on f, right-click and I'm going to say insert one to the left. If I say one to the left, it's going to put a column to the left where I currently am. If I put one to the right, it's gonna put it right. So if I put one to the left, it should insert a column between E and F. I shall insert one to the left. And you'll notice that f is no longer F. F has moved to G, and G is week two H, but we've still kept the same alphabet long atop. So we don't invent a new set of letters to fit between the two. However, you'll notice the calculations all still work because what Google Sheets has done has realized that you're gonna put new column in here and it's maintained the integrity of all these calculations. So if we look here, we can see the A5, which is still A5, is multiplied by G four and there's nothing left for f. So if I were to highlight this row or column, sorry, and then drag that to the right. I'll end up with exactly the same figures because this is doing exactly what I told it to do. This is moving one to the right and then it's doing the calculations and it's, this one here is also referencing evil because that's what it was doing before, before we start this extra column in. So what I can do if I want to fix this, so it flows nicely to the right like it did before. I can simply take the column that I've just added and just wipe that. Sum all of these. Let's do a couple. And it will then reflux the formulas to then point to each other and then they will adjust to they go along accordingly. And we can just make sure of that by just going right back to the start or this column here where that is. And then dragging that over top of all of them. And then we know that the formulas have been fixed. So this is the first taste of adding a column in. And you should always then go and re-inject your formulas and make sure that they're doing exactly what you want them to do. And they haven't adjusted themselves. Adding rows, deleting rows, adding columns, deleting columns are the most common ways to completely ruin a spreadsheet. The reason it's easy to ruin the spreadsheet is because when you insert the row or column, nothing bad happens as far as you can see, because Google Sheets in this case works out what to do and keeps the formula's working. But sometimes they're working but they're no longer following the plan that you had for the layout of the spreadsheet. So do remember that calculation and design are two separate elements within a spreadsheet. And you need to make sure that if you change the design, you also change the calculations. And if you change the calculations, you also change the design. Google Sheets isn't is it does not have an idea of keeping those two things together. You are separately moving things around. So whereas it might look obvious to you that this column should then add to that column. To that column. Google Sheets has no idea what it's doing, what your vision for the spreadsheet is. It's just purely moving the formulas around. So that's great. So let's do the opposite to what we just did. So I'm gonna click on F And I'm gonna delete a column. And it deletes it. Now this time, it can't help you because this time we've taken information away and it can't work out how to retrospectively move things because when we take things away, it has no idea how to fix a formula. So we're going to simply take the formulas that we know are good and drag them to the right. And they will overwrite again, and we're all back to normal again. We can do exactly the same thing with the row. So I'm going to right-click the row and I'm going to insert one row above. And it will do exactly the same as we did before. So I can add a row in here, drag that down to there. It will all work. And again, I've got the same thing referencing that, um, do places referencing the same cells. So I've got eight n multiplied by before, and I've also got a nine multiplied by before because there's nothing did it Could we haven't adjusted the rotor, keep it in line with every else. So I'll just drag that down. And now I've got a new row with all the correct formulas as I expected to be. Same thing again, I can take any of these rows, delete the row, it's going to ruin all the references. So I'll take the formulas and drag them back over top and away we go. Now we can do the same thing with individual cells. That can get even more entertaining. So if I highlight a bunch of cells, and if I go insert cells, shift right or shift down. So because I've highlighted this block here, I want to insert some new cells where these, where I've highlighted. So Google Sheets needs to know whether to take the ones you've currently got and move them to the right out of the way or move them down out of the way. And what we'll do is we'll move them down for now, it's easier to see. So I'm going to insert cells, shift down. And as you can see, I've got six brand new cells with no formulas in whatsoever and the existing formulas have carried on working as they would do before. So this one has just been adjusted so that it can then still work where it was before. So this cell, or rather the form has been left the same. So this cell is still saying a nine to which is that one multiplied by this F4, so it can't move around. So that's great. Nothing wrong with that. But if we wanted to then put something else in here, it's harder to fix that formula. So what we're gonna do is I'm going to just undo that with Undo. And it will put it back where it came from before. So we can move things around, we can insert things. But I will caution you that every time you insert something or delete something or move things around, you do need to then review your formulas and make sure that nothing terrible has happened. One last thing we can do, we can obviously copy and paste. So if I highlight this lot and say I want to copy it, I can right-click and I can copy. And then I can go somewhere else. And I can right-click and I can paste. I'll do that. Now this time it takes the formulas and moves them relatively, do where it's gone. So this time it's going to have dollar f $1.4. So it's still pointing at this one here. But now it's looking at a 16. So it's trying to do a multiplication here. So if I put 45 in here and let go, then magically all the formulas will spring to life because it's now doing 45 times 5045 times 60 when it's working right? But it hasn't copied, unlike when we insert a row and it adjusts the formulas to the new row. When you copy and paste, it just takes it to the new position and assumes that everything's where you, you need it to be. An everything's working. That's great. I'm going to undo this and keep on doing until I get back to where I was. The last thing that we can do is I can select a whole group. And if you notice we've got the border right? Instead of using the quick filled anybody, I'm just gonna grab with this little hand that appears on the border. And I'm going to just drag that section down. This sort of does the same thing as inserting cells. So you can see that this has retained the exact formula that I had before. So this is now pointing A9 steel or is still pointing at a nine over here, and it's still pointing up F4. So it's exactly the same as inserting some cells here, some blank ones, but it's just moving around. But it is worth noting. If I didn't do this, this is actually quite a safe way to move things around. So if I wanted to move the whole multiplication table around, I can grab the whole table and drag it to here. And it will function correctly and take the formatting everything with it. And it's gonna be absolutely fine wherever I drop it. Because everything that's contained, all the formulas go with the movement. So usually that's a very safe way to move things around. As long as you've got everything you need. If you just take small chunk of it, then you've started to go entertainment. As you move. One last thing while we're in here and we're dragging everything's around is I'm just going to highlight a couple of cells. And if you'll notice down here next to my head, there's this little, some 4 thousand. This is a little summary which is really quite handy, that wherever you've got highlighted, if I click on it, you can see the sum, you can see the average, the minimum number, that maximum number, and the count, and the character different numbers. So count is the number or occupied cells and the count of numbers is the number of occupied cells which have got numbers in them. So this can be quite useful even for just doing basic calculations. So I can just quickly see, oh, that's sorts, that's worth $9 thousand and that's a 150. So if you've got a load of figures you're working with, rather than writing a formula to calculate how much things are you can do quickly drag the mouse over it and go, alright, that law is 5,600 and it'll just add them up, stray away for you. It's just a nice little simple thing to do. Save you. If you're just checking a spreadsheet or working through some figures, then that's a nice, easy way to quickly just check through the whole sheet. Ok, so I highly recommend that you have a go at making your own multiplication table and just playing around with it, make it as complicated as you'd like with all sorts of different things in it. You don't have to stick to multiplication. You can do whatever formula you like in here to try and make it complicated. You could even have three numbers. You could have this number, this number and another number down here and do something with that as well. It doesn't matter, but try doing it yourself. And then in the next lesson, we will start looking at some other formulas. 7. Basic Functions: So in this video, we're going to introduce functions. And functions are the heart and soul of a spreadsheet and are the reason that we both having a spreadsheet at all. Really, we can do everything we can do in a function. You could construct yourself by using sets of cells which all gradually work out very complicated things. But for example, statistical standard deviation. You could work out by linking this cell to this and then adding all sorts of things like that. But rather than handily, people who create spreadsheets or create this pretty engine also create lots of useful functions for us. And if I go to the insert menu and then go down to function, then here is a huge list of all the possible functions that are built in to Google Sheets. And they will do all sorts of complicated adding up and referencing other cells and moving things around and doing all sorts of things. But for now we're just going to assert, as far as this course is concerned, we're going to deal mostly with the ones just at the top here. Because they are the simplest functions and they're the easiest to understand. But just be aware that all these others are here for all doing all sorts of things like finding the day out, particular day of the week out of a date. So you can find out if you put a date in and it will give you whether it's Monday, Tuesday, or Wednesday. For now, we can either insert functions by going into the function into here and then fill in. All the easiest way to do it is to just type equals and then stop typing something. So I would like to generate a random number. And there's a couple of handy functions that help, help me do that. If I start typing r a, then all the functions that possibly begin with letters are a are then presented to me. And this one here it says a random number between 01 neat, inclusively. So I'm going to press return. It will then open the bracket because most functions require parameter to be passed in. In this case, this one doesn't, so I can just press return again and it will finish it for me. And if I look at that, here's what the Google Sheets is actually working with. Its rand with open and closed brackets. And this is the result. It's a number to large, significant figures between 01 Rand that spreadsheet itself only updates when you make a change to it. So in order to get a different random number in here, I go to an empty cell, any empty saddened spreadsheet, you just press backspace. It will recalculate the sheet for you. So every single formula in the spreadsheet will get updated. If you make new entries into a spreadsheet than that happened automatically. But the easiest way to get a spreadsheet to recalculate, I'm just pressing backspace, tap, tap, tap, tap Liggett and no new number each time. Once I've got that, I could then say highlight and get ten of those. And if I wanted, I could go over here and get that many of them. And if I look down here at my thing, I've got some, uh, some of it I'll go on average, minimum and maximum and accounts. So I've got 90 different numbers of which the average is this and the sum of them is this. And that's great. And again, if I go to empty cell and just press backspace, I'll get a new set of numbers every single time. These numbers are a little bit weird to look out. So it's going to highlight all of all of these cells. Press backspace to clear it. And I'm gonna go back up again and its type RA again. And this time there's a function called RANDBETWEEN. I'm gonna click that with the mouse and then I've got an open brackets. And now we've got a nice easy function to which takes a couple of parameters. That is a nice introduction to how functions work. So we've got our weird and wonderful bit of information here where it starts to sound a bit abstract, but just take a breath and you'll be fine. So random between low and high. Here's an example. We're going to have two entries. The first one is a one, then a comma, then a ten. And it says it returns a uniformly random integer between two values increasingly. So essentially if I put the numbers one comma ten, it will randomly produce a number of integer number, so no decimal points between 110 sounds simple enough. So one, comma ten and press return. You don't have to. You could put the close brackets in Google Sheets is kind enough to let you be lazy enough to not have to worry about. Press the EPA and the last bracket in. So now here's the function that's actually in that cell, rand between open brackets one comma ten, and here's the result. And again, I'll go to an empty cell, press backspace, and I'll get a load of different numbers every single time I press it. And then I'll do the same thing again. I'll get ten here will go over to I. And I've got a whole spread of numbers that if I press backspace here and just keep pressing it, then every time I press backspace, I'll get a new set of numbers. Well, that's great. So then we can use these numbers just to demonstrate a few of the other functions that we've got available to us. So what I can do is I can press equals and a very common function is the sum function. So SUM open brackets. Now, you can sum one cell, which seems a bit pointless because you might as well put just but the self-reference in or any number of cells you like there are in contiguous group. So the most obvious is if all I close that and then just click and drag down here, I'll just sum this column up and press return. And that will sum this column. And every time I press backspace, it's adding up and I can check by just highlighting over here, and hopefully that'll say 43 down the bottom, which it does. So the sum of this column is 43. But this particular range, and if I click on the function, it'll highlight it for me. Doesn't have to be one column. I could change this to beat N and press return. And now this is adding up all the numbers in a and B. And it doesn't matter, it doesn't really care, it will just add them up anywhere. So I'm just going to delete this. So I'm gonna type Enter or equal, sorry, sum. Then I'm just going to drag all the way over here and press return. I should note that if I, if I go over here and press equals and some, and open brackets, if I drag from the bottom to the top left, it makes no difference. It's that it doesn't care whether you drive from the top left to the bottom right or the bottom right to the top left or even top right, bottom left, and whatever the other one is, doesn't care. You're still gonna get the same result. So those two sums there exact, exactly the same. So if I look over there, it's a 1D, 9D and these ones are you anti ten. So these two cells are doing exactly the same thing. Another sometimes useful function is max. So if I type equals and then MAX, open brackets, I'll pick the same range, drag that all the way over there. Press return. This will give me the highest number. It all the occupied cells if I no prizes for guessing if it helped, equals and min, open, open brackets. Same thing, same range, and press return, I get one. I'm pleased about this because I asked for the random numbers between 110. So it's nice that the highest number is attended, the lowest numbers are one. The next one we use a lot is count. So if I do count and open brackets, same range. Press return. That is going to count all the occupied cells which have numbers in them. So that's got 90 of those. And lastly, we're gonna do average. So I have lots of different averages. This one here is the most common, which is why hasn't gone anywhere to wonderful extra letters when its average value in the dataset, ignoring text because he didn't want to count text. So if my name was in the middle here, we would just ignore it. And there we go. That's fine. You don't have to worry about blank cells. So this one, for example, the count is there or if I use average. This is currently got this range between here. If I add in an extra row, the average is not going to change. So 5.303 is the new calculation because the numbers change every single dime is difficult to see, but is not including this row because there's no numbers in there, because the average is the sum of the numbers divided by how many there are. You don't have to worry if there's nothing in a cell, then the functions do literally ignore that cell. So your range can be sort of too large to start with and you can gradually expand with it as you so desire. So just to show how this works, if you like, I'm just going to clear all these. And then I'm gonna do some text and then I'm gonna do count. And then I would do average this and I shall highlight those eyes. You'll get my right alignment. And in this cell I'm going to go equals sum. Open brackets, highlight all of them. Press return, and they're going to do the same thing here, equals count. Highlight them all. Press return, and then average. I'm going to work out by going equals and I'm going to do sum divided by the count. And then I'm gonna press return, and that'll give me that. And at the same time, why not? Over here, I'll do equals average and see what Google Sheet thinks of it. And I shall just highlight them all and press return. And hopefully, if I go over here and keep pressing backspace, those numbers will look the same because it's doing exactly the same calculation. But nicely. Instead of having to do it in three steps than Google Sheets has got an average force that works out in one step. And that's much neater because I could write. If I copy this, I can then paste that over here. And that average will is an easier way for the spreadsheet to work out what the averages is much easier for you to read it as well, all nice and clear. So there's no particular confusion. In the next video, we're just going to have a little chat about using multiple cells to work out calculations or buy, collect, sort of collecting together some calculations into one cell and see which is better and which is easier to read and deal with. But in the meantime, have a play with these functions. The sum, the count them in the max and the average and just get used to start including the random number one as well, or any other functions you want to have a look at. If you find a function in there that you'd like to look off, give it a go, see what it does. Again, it's a spreadsheet is digital. You can't do any damage. Have fun exploring functions, and I'll see you in the next video to talk some more. 8. Long Calculations: So this video isn't an extension really to the one before. We're going to carry on with similar functions, but also want to talk about and how you might lay your spreadsheet out and how not to get drawn perhaps into complicated functions. Use the spreadsheet wisely is the answer here. So layout in steps so that you don't have to try and work something out later when you come and view your spreadsheet after a couple of months and you've forgotten how you worked out. If it all laid out in steps for you, then it's easy to fix any problems that might come along. And also, I like I say, when you come back some months or even years later, you'll understand your spreadsheet much better if you've laid out nicely. So let's just show nested functions to start with. So I'm just gonna do this same thing I did last time. If I actually clicked in the right place and then we get rand. Lambda's going to use this because we don't really care what the number is going to be. So we'll just have some ten numbers that are just going to sit there in those cells and we're just going to use them. So used to run rand function and just copied it down ten times. You should be happy enough with that now. And what I'm gonna do is I'm going to do equals and we're going to sum. And then I'm going to select this range and press return. We're going to do equals and count. Select this range, pressure ten. And then I'm gonna do equals, equals a, C1 divided by C2. And that will give me my average. And if I keep pressing backspace, I'll get onto different averages. And that's fine. Nothing wrong with that at all. We know we can use the average method, but that's when we're using this just sort of labor the point really. Now, there's nothing wrong with putting both of these things in the same cell. So let's go over here and we'll do our average again the slow way. And I'm going to say some of this. Then I will glue close bracket, but I'll do divide it by the count open brackets, all of the same range. And parser ten. And that will give me exactly the same average. You can see here, I like keeper. So now the question is, which is easy to read? Is it easier to read this when you come back to view it? Psalm of A1 to eight n divided by count W12 identity. Or is it easier to read? Is the sum. And he has the kind of, especially if we were to rearrange it and arrange it so that these were in columns. So we could do the same thing. We could do it the Sahlman account. So let's do that. I'm gonna delete this and we're going to put some here just as text. I'm going to count that way. And then average. Like this, I'll do the same thing again, equals some. Highlight the sum or the range. And then I'll do count like that. Turn and then average, which is this divided by this. As simple as that. Yes. So now we have an average. Here's the sum, is the current, Here's the average. And it's really obvious how we got the average because it says C2 divided by d2, his C2. And here's D2. Much easier to see what's going on. And we don't have to have a complicated formula. And here we've got these two columns in the way. Now, these two columns to do are working. Now the nice thing about a spreadsheet is that we don't actually have to look at them. They can still exist if I highlight these columns and then click the little arrow or right-click, then I can actually hide columns C and D. Click that button, and now I'm left with an average. So if it bothered me that my I didn't want to show my working and it was all just laid out. I don't want to see all the working. Then I can hide the columns I don't care about. And the average will still happily calculate. If I press backspace on here, it's still working out and those columns are still doing things in the background. And you'll notice that I've got a, b, c, d, e. So c and d are missing. And all I do is I hover over these two arrows, click the button and they'll pop back into existence. And this is important because I believe that we should have more working out columns and then hide them rather than having a complicated single cell with lots and lots of information in it. So what we're gonna do is we're gonna do a slightly more real world example and see the same thing. But we'll show that there's, if we put too much into one cell, it can get a little bit complicated. So I'm going to highlight all these, delete it and will start from here. So what we're gonna do is we're going to imagine that we've got some product that we're selling by a square meter. So I'm going to have cost per meters squared and I'm going to have rice or retail per meter squared. And I shall highlight both those and just make them aligned just so it's easier to see. And in here I'll have, let's have 75 pence and one-pound 12. Again, I'm in the UK, so I make no excuses for using pounds and pens. And then I should go up here and format as currency. So I've highlighted those two cells are going to press the little pound sign here. And it will highlight as currency because it's just nice to have currency. And then because we're selling per meter squared, we're actually going to have, we're going to sell off fabric or carpet or whatever is we've, we're selling, but it's going to be sold by millimeter width and millimeter length. So we'll have width that I'll put millimeters there just to make it obvious and length. If I could spell length, and we go millimeters. And that's great. So we've got a width and we got length and I'll just put something in here like 550 millimeters in, 1200 millimeters in length. At that point. We'll then let's move over to here and we'll say an area in meters squared. And then we'll have a cost. And then we'll have a retail. Yet. These are things that we want to fill in. And lastly, we'll have a profit over here. So these are things we'd like to work out. So the first thing is an area in meters squared. Well that seems reasonable enough. What we'd like is our width by length and also by meters. So if I go equals, I'm going to add a bracket to make it really obvious. And I'm gonna say the width multiplied by 1000, sorry, divided by 1000, because it's in millimeters. And I'm going to multiply that by the length divided by 1000. So this will give me the area in meter square because there was a thousand millimeters in a meter. So therefore divide them up and we're offer, we've got army to squared. I would then like the cost of that. So I shall say is, I shall say equals. And I'm going to say, here's the meter squared. I'd like to multiply that by the cost per meter, which is C1. And I am going to press F4, just a lock in place because I'm imagining that this might have a fresh return. This might have a bunch of different records all flowing down here, but I always want it to look at C. Well, so I've locked in place, so this gives me 50 bits for that. And the retail, I'm gonna do the same thing, which is going to be the area multiplied by the retail full to lock it in place. And that gives me the retail. And then I would like to profit. The profit is simply going to be equal to the retail minus the custom. And there's the profit, and here's my spreadsheet. So I've managed to calculate things out and I've got lovely simple formulas in each cell. So I've gone area which is calculated this and that to doing the area for me. So once I know that's right, I can tick that off and know that that's the area. Then the cost is multiplying the area bound by the cost. And I can look at the cost. And here's the retail and I can look at that by their compare that to the, what I've got before. And then the prophet finally then takes these two values and calculates it. Now it is possible I'm just gonna tied his colleagues. It looks nice and neat. If I just bring that across to that, it would be possible to do this all in one cell and save these three columns. So if I didn't care about these columns, if I didn't really need this information anywhere. And all I was interested in was the profit. Then I can do that all in one cell. So let's attempt to do that, shall we? And we'll do it in a couple of little steps. So we know that we want to essentially get to the point where we did the retail minus the cost. So I think if we work the retail light first and go from there, so I'm gonna go equals. So what is the retail? Well, the retired is the area multiplied by the retail per meter squared. And what is the area? Well, it's this times this multiply by 1000, so we'll start there. So I'm going to do open bracket. I am going to do same thing we did before, a5 divided by one hundred, ten hundred. And then we're gonna multiply that by B5 divided by 100. That gives me the area. I then want to multiply that by the retail per meter squared, which is this. And I'll do the F4 to make sure that solid and press return. At this point, I've done nothing more. I've done no different to what I did to get the retail before, but I've just put it all in one place. So I've done the area which is this little section. Yeah. And I've multiplied it by the retail per meter squared, which is that little section. So that's great. I've got the retail. I've stopped and I were just to check that it's all OK. What I'm gonna do is I'm gonna do the same thing and add or take away the cost. So I'm gonna go into here and in order to make sure things work nicely, I'm gonna put another set of brackets around everything. And then I'm going to copy this. I'm going to highlight everything because it's all virtually the same. Copy that with control C. And they're gonna do minus and then control V. Or I could have done right-click paste. This will give me all of this. And the only difference I want is instead of c2, I would like C1. And that will give me the cost pressure term, and I get the same profit. So let's just go through that, spits lower so I make sure I go through it. I've worked out the area which is this a5 divided by 1000 to B50 thousand. I then multiply that by the retail meter squared. And then I've done the same thing on this side with the cost. So I've marked out the area in the same way. Then I've multiplied it by the cost. And then all of this section, everything that's in a bracket will be worked out first. So that'll be worked out. Then this will be worked out because it works from the inside out. And then this lump is subtracted from this lump, which gives me the same value z. If I change any of these, if I go 4-5-6, then I get 20 events. And if I go 2345, I get 1.4. And these are all absolutely fine. We could call this prophet and with a long calculation. So my, the point I'm trying to make in this rather lame example is that I would much rather have these three columns which are not required anymore. If I had this one here, I could get rid of all this data here. And then that's gone. This calculation worked, but this is still doing the same job. I've still ended up with a profit because the information is all stored within this formula. But I'd much rather just go undo. Much rather look at these three columns to work out what the working out is. And then look at this not complicated formula that says, Oh, take F5 and subtract E phi from it. So F5 is o, F5 is the retail. Oh, that makes sense because I'm going to subtract the cost from the retail and that gives me the Prophet, Great. That's good. If I don't want to look at this information, I can just highlight those three columns. Go to the arrow and hide the columns. And then I've got the nice tidiness that I would like by just having this one cell rather than, but I get the bonus of not having this hideous notice kicking about in the cell. And it's not just for aesthetics either, because this is much harder to debug and work out what's gone wrong with your spreadsheet. If you're, especially if you imagine some really complicated things in here with extra function names, all sorts of things. You see how we get lost in the brackets, all the while. I'd much rather add a column in, then make a formula bigger. So always be like when we're at school, show you're working. These are nice simple formulas dealing with a nice simple idea. Yeah, again, remember you don't have to do in order to Google Sheets doesn't care. You could put these three columns, for example, these three columns could live all the way over here. In fact, if I highlight them and then pick them up with a hand, I can drag them all the way over here and let go. They live over there. But this all still works. So now if we look, W5 minus v5 is still the same profit. And if I look over here, these are all still working because all that's happened is the columns of moved. And they've been shoved over here to the right. And Google Sheets doesn't care. It just it just gets the data from wherever those wherever you tell it to get it. So I can put these three columns absolutely anywhere I like. So I'll pop them back wherever this line is. We'll pop them back in here and they come back to you. So don't be afraid to move things off screen we things away, but certainly use the height. I can hide that column. And if I like, I can hide this column as well. It doesn't matter, it doesn't care. You just get these little extra arrows. That mean you can pop the columns back out whenever you like to view them. So the data doesn't go anywhere. Calculation still happen, but it's much easier to read your spreadsheet from there. Okay? So have a little play, have a little play with all sorts of different hiding and showing columns and trying different calculations. You can even experiment with making your calculation as tricky as possible and replicating it with the different brackets and see if you get lost in the brackets. If you leave a bracket off, then Google sheets will attempt to work out what's going on. You see it added an extra bracket into fix it, but now the brackets in the wrong place. So now we've got, we might have an incorrect calculation because it's filled, put an extra bracket on the end to make it work. So quite quickly, you can lose track of where all your brackets are. So I recommend, for the tenth time, I recommend having more columns and less complicated formulas, so much easier for you and anyone else to read. And then he just hide working. If you don't want to show it, hide you working pied columns and they go away. All right, excellent. Have a play, and I'll see you in the next lesson. 9. Profit Margin Sheet: Okay, so over the next few videos, wage gradually build up a slightly more complicated spreadsheet. Nothing too terrible said is kind of a product sales spreadsheet, which is a tradition amongst spreadsheet training really. We always end up with a title spreadsheets. There's nice and obvious what you're doing. You can have months so that you can have things that you've sold. But we're going to, we're going to build out, we're going to use a couple of other functions as well, which I'll go into in a couple of separate videos. But for now, let's just get a very basic profit margin based cost and retail sheet if you like. So we're gonna call this profit. If I was typing in the right place, profit per item. We'll need some items. So I'm gonna go item on there is going to have a cost for that item, a retail price for the item, and then some profit. Simple as that. Those can be nice. And we're going to have some items that's awesome. Shoes and boots and some gloves. Some sort of clothing manufacturing will make this look a little bit neater, will make that bold, and we'll make it slightly large exists almost the title of the spreadsheet. I'll highlight these ones that make them bold as well. And because these are items in a list, I'll leave them just in regular text. So let's have a cost. So I'm gonna say the shoes are gonna cost five boots go cause sickness and clubs are gonna cost a pair of gloves, gonna cost three. That automatically come up as pounds. Because previously the, I'd already practiced this, come up there if I would have pressed pounds at that point and highlight these and R format that has currently as well. So next, we're going to have a profit margin. So the company is going to decide what profit margin we're gonna make for our or, yeah, what margin we're going to have for each of our items. And so I'm going to say a profit margin here. Well that elegant to align that to the right. And I'm going to say 5%. So I'm going to press the percentage, which isn't going to do a lot because we're not gonna see much and then put five in as 5%. So now we've got the profit margin of 5%. So simply we then want a retail price, which will be the cost multiplied by the profit margin. So let's do that. We're going to go cost, which had before. I'm going to multiply that by the profit margin, and I'm going to press F4 to make that dollar D21 and press return. And it looks like we haven't quite got it right because the retail should be the original cost plus the extra margin that we're going to have here. So that formula needs to be slightly adjusted. So I'm going to open brackets, close brackets, and then add in the cost again before again, which will give me a retail price. So it's the cost. Multiply by the profit margin, which gives us the margin and then the retail price. The profit we could work out in the same way as the cost multiplied by the profit margin. But I always like to do something like profit as simply the retail minus the cost. The main reason for that and not using it doing the same calculation again, is firstly, it provides a double-check later on. And secondly, if I then change how I calculate my retail value or change how I calculate my cost value. This is the base definition of profit, which is the retail minus the cost. So I don't have to worry about this formula including this profit margin figure, as well as this formula including this profit margin figure. This formula just deals with what it says on the team, which is profit. Whereas this is the minimum amount of information that is required to work out the profit margin based on these figures here. So low, I could like be like when I was talking before about adding extra information into each formula, I could go via the cost of the profit margin, but that's not really the definition of profit. So keep before me there's as simple as possible. This is simply c four minus d four. And then if I've done everything right, I should be able to copy that down. I've gone too far, doesn't really matter. Highlight those press backspace. And now I've got the cost and profit on everything or one marvelous and nice little profit. They're item. Next We could say sales and income. And again, I'll highlight those, make them bold. And I shall type in here some sales. These would be manual, let say 50 of those we've sold and we solved for pairs of boots and six pairs of gloves. Income is going to be, in my opinion, to profit income. So it's going to be 50 times the profit. So we're gonna go equals profit times 50. It doesn't really matter if I my accountant, accountancy is up to standard as long as the spreadsheet makes sense. Because we're we're not using this an anger just for training down. We go scroll that down. And now I've got some income. So if I sell 50 pairs of shoes, I'm going to make 12,050. Doesn't seem very reasonable, but and then we'll go on. And then finally, let's have a total. And i'm going to go equals sum. Total is up, press return, and that gives me a total. Google Sheets worked out the pound sign here. It realized that it was adding things which are formatted as bones. So therefore, it's going to put a pounds down here automatically gonna make these a bit bigger. And in fact, because it's, the sales will make it really big. And I will also move that to the right to show that it's a total level. So this nice and quickly, using the information we've had in the previous parts of the course, we've now got a profit per item. And we could add as many items would be like the spreadsheet's going to do all the adding up as we like. We can make this as easy. I can highlight this. I can insert eight rows above. And I could have something else like this. I could have I don't know, T-shirts. Yeah. Which cost 250? Yeah. And if I just format these three as that, I should be able to go on here, drag that down at that and say we've sold ten T-Shirts. Symbols that I've highlighted that list just earn 250. There we go. So we can add a new item as easily. Drag the formulas down and it'll do all the working out for us. And we only need to fill in the bits where we've actually got something to fill in, simple as that. So that's the profit per item cheat. In the next video, we're going to talk about another function or another part of the spreadsheet. And then we'll work our way towards a yearly sales per gene. So I'll see you in the next lesson. 10. Data Validation: So there are a couple of or one function and one part of the spreadsheet sort of engineering the I'd like to use as part of the final spreadsheet we're working towards. And this one is data validation and is extremely useful in spreadsheets, especially when you're going to start referencing cells which have got text in them. Because it would be nice to make sure that you always have the same text. So if you're using, like we saw in the last video where we've got shoes. If we're going to reference something that's gonna list shoes, it would always be nice to use the word shoes. So this might be used for product codes. For example, if you want to use a list of product codes in various different places around your spreadsheet, then it'd be nice if there was one definitive list of product codes that you can use and you don't accidentally mistype the wrong product code. And one way to do that is data validation. So what I'm gonna do is I'm going to type my name Simon. If I was actually clicked in the cell, Simon, I'm going to have Michelle, George, and Frank. There we go. I've got four names. Now let's say over here, I wanted to make sure that I had a list, say of Simon, Simon, George, and Frank, and the price they paid for something. So you know, 12041, bone 4056. And this was I can highlight the whole column and make it currency. There we go. It doesn't matter, it's arbitrary what's here. But I want to then compare the simon things somewhere else. It might be that I got a bit trigger-happy, especially if this list was long and I've ended up with Simon and because it's got too many n's because I've mistyped it and that's easily done, especially if you imagine this list was very, very long. You start to miss this. And sometimes when we're looking at spreadsheets, we might be zoomed out further so you can't quite see as clearly. So what we would like is some way of validating or making sure that you can't type in this box, anything that is not listed in this list. And that's how we're going to use data validation. So I'm gonna take this cell here and I'm going to go up to data, down to data validation and click on that. And it says for this cell range, which for now is the cell wall I've just selected. The criteria is a list from a range or a list of items. Note all sorts of different things that you can choose from. The most simplest is list from a range. And then you click this little select data range. Great. And then you select your range right there. So A1 to A4 and click OK. And it says show drop-down list in cell, which is great. And then you have two options. Show a warning if it's invalid or reject the input. And I'm going to say reject the input and click save. Now I get this little drop-down. And when I click this box, okay, I've dropped down, I get a choice of what to select. And if I try and type anything in here, yeah, it goes, this data was entered, it violates the data validation rules. There's a little bit brutal, but essentially it's saying You can't put anything other than these four in it. It's a fantastic message. And I can do the same thing as before. I can just drag that down. Phil. And now I can choose from this list a specific set of items. And I can't alone. In fact, I'm not allowed to put anything in here that is not part of the data validation. And this then ensures that whatever is listed here is always going to match what my original list was. So this data validation we're going to use just to make sure that the sheet that we're gonna use is gonna reference everything correctly. When we move forward. In the next video, I shall introduce a new function called VLookup. I'll see you then. 11. VLookUp Function: So as promised, here is a short video on the VLookup function. I have found this to be a very useful function. And below, it might initially seem like quite a complicated function. It's actually very, very useful for looking things up. And I think it should be included in a beginner's course because this with combination of data validation allows you to make very, very complicated spreadsheets. And you don't really need any more information. You can use multiple sheets and you can go across all sorts of different things. It's just a really, really useful and it'll function. So here I have a completely ridiculous situation. But I have three salesmen, all selling eggs, bacon and Brent, and this is a unit of eggs, Josiah box of half dozen eggs and some beacons and bright. And these are the prices that I celiac. It really makes no difference to the example, but what the and legitimacy of a is better. This is how I've chosen to set it up. Well, I'm gonna do, is I'm going to create some data validation in this cell, which is going to look at and only allow me to select from eggs, bacon, or bread. So I've selected the cell only go data. I'm gonna go Data Validation. I'm going to just do this in D9, going to select my data range, select egg, bacon inbred. Click OK, reject any other input, show the drop-down and click save. In here, I've got a list of eggs, bacon, and break. If I were to change this to Sausage, is then I'll have eggs, sausages, bread makes no difference. This list is now restricted to that list there, which is all like Arabic. So I'm gonna just pick eggs. Now here, in here, what I'm gonna do is I'm going to use the VLookup function. And I'm going to show you in this context how it works. It doesn't make a great deal of sense, but it's a nice, easy example of how it works. So let's go with mice and sodium will type equals and then gotta go V, look up and it says vertical lookup and press return. And here we see the parameters that have to be passed for VLookup to work. Don't be put off by this nice and easy. The first thing is search key, which is the thing you would like to use to look up in your list. In this case, I would like to use eggs. So click on that and it says D9. Then I'm gonna put a comma, and then the next says range. And this is the list of things that you would like to look through to try and find eggs. But this range also needs to include anything you might like to return. So in this case, I would like to look down this list, find the matching item, which in this case it's eggs, and then return one of these three values, either the George value, the current value, or the hairy value. So I am going to include all of this. In my range. So a2 down to D four is even the colon a2 dot default. I'm going to first comma. I'm gonna type in this index. I'm just gonna put two for now and we'll come back to that in a moment. And then in his sorted, this will be false. The reason for false is because this list isn't sorted alphabetically. If this were sorted alphabetically, you could put instead of false, you can put true, but I'm going to leave it as false. And for most of the time, I would just leave that as false and make a Google sheets do the work, always just leave it as false. Now, here's the formula in it's a neat and tidy form. We're just gonna review that again. So the vertical lookup, the result in this cell is going to look at D9, his D9. It's then going to search the first column of this range, which is a2, d four. And it's going to try and find what it sees in dynein in this list. If it does find what it is looking for in this list, the first one it finds from the top-down, it will stop, and then it will move to the second column of this range and give me the value back. So here I am in this range which is four columns wide. It's going to look for eggs, it's found aches is then going to move to the second column because that's what I've told it to do. And then it's going to return that value, which is one by Duany. And there was one time 20. If I change this formula and make it returned the third column presenter, it's now going to return 125 because again, it's looking in D9 photo, makes his eggs. Then find me the third column. If I change the column 21 and press return, it's just gonna return eggs because this is the column is found and this is the value is found, and this is column number one. Lastly, we can go to column num of all. Let's return and it will find 130, because over here, if I change eggs to sausages, it'll return 270. If I change sausages, bread, it'll return 90. Again, I could change things if I put this here and if I just in this cell type two and press return, instead of this column being a number, instead of this value being a number, I can change that. And I can change that to the cell E eight. Press return and it will still work. So now I don't have to edit that. I can say bring me back the first column, which is bred over nu at the second column, which is George, the third column which is frank, and the fourth column, which is hairy. If I do something that's incorrect, like five, then we'll get this hashtag ref. And if you hover over, just move, you may say that where there is, it'll say v lookup, lookup evaluates to an out of bounds range. Out of bounds usually means that you have selected a number that's outside a range or outside something that's bigger than number is larger than the scope of the range you've chosen. So here we can't have a five, the fifth column of something that's only four columns wide. So I could take this further and have a unique drop-down for George, Frank, and Harry. And then a vertical lookup which returns which column is. And then use that inconvenient combination with a drop-down for bread to then you can see how you could nest these things together to gradually make it more and more complicated, neater to use though, it's still a nice neat function. There's nothing complicated here because you can't get, you can't build this incorrectly because Google sheets won't let you. You still always have to have something to look up, some range to look at, and then a column to return from that range. And for all intents and purposes, I would always leave this as false at the end. And so this is the most advanced function we're going to use in the course. But it's useful because we can go look elsewhere. We can have an item and then this can return something from elsewhere based on what is found in this column here. And we'll see that in use in the next video. So I'll see you then. I would recommend to having a little, just before I go, I'd recommend just creating a spreadsheet like this and then playing around with it until you're comfortable with getting different values as you change this range or is changed the column that you're going to return. Get used to what Vertical Lookup does, just so you're comfortable with it as it. And I'll see you in the next video. 13. Charts: Okay, so we've gone my spreadsheet that we're happy with, benign noise to show it to the boss. And the boss doesn't understand numbers, the boss only understands pretty pictures. So it's always nice to show it. It'll chart. And hopefully you can hide anything naughty in the cracks. But we're gonna show that show the chart. Now, depending on your spreadsheet, application, charts can be quite complicated. And Google Sheets is no exception. Half the battle. In fact, more than half the battle is making a good selection right at the start. And then hopefully, Google sheets can then make best judgment of what you've presented. If you tried to build a chart in little pieces, then that can be quite complicated. But the best thing is to say, Right? I would like a chart of sales. And the chart is going to end up having the months on it. It's going to end up having the title of the item and it's going to have all the sales figures. So let's select everything that we care about. Here is everything I would like on a chart. And then I'm going to press the magic button and cross my fingers. So I'm gonna click Insert Chart. And let's look at the good things. The good things is it's entitled The chart, shoes, boots, clubs and tee-shirts. He's got a legend which got shoes, boots, gloves, and tee-shirts. The months, it's got some values and it's got some lovely lines. That's fantastic. It looks like it's done everything correctly. We look over at the chart editor. On the side. We can see it's got line shot. If I close this or click off it, you lose the Chart Editor. The simplest way to get that back is to just click on the child some way, all the white space. And then you get u three dots and go edit the chop and it will bring the editor back. So that's how we get the editor back in case you lose it. Then from now gonna go instead of line chart, I'm going to scroll down and I'm going to choose a stacked column chart. And this looks very nice. This looks exactly like the sort of thing. I would like to show the boss and show things how they've worked because we've got january we sold t-shirts, most gloves a little bit less. And you get the idea. Now once you've got your chart, you can then just move it around and resize it however you like. It'll try and keep it scaled. And from there, we can then edit this chart to our heart's content. So there's all sorts of things we can do with it. We can go to customize and then you can change the style and you can change the axis titles. In this series, we'll come on to the second legend. You can change whether to show it, not show it, and all the different things. I would invite you to actually play with the chart and see how you get on. Thing that I would say is in vertical axis. One of the things that can be useful. Let say the boss was hoping for 500 sales, then you could say the maximum should be fixed at 500. So then you can see it's always gonna show 500 is going to force the, force the graph to show what charts or a show that at 500. If you leave that off, then it's going to do a nice sort of approximation. It try Google Sheets, tries to be neat and tidy and tries to do it to nice neat numbers obviously depends on your data. You did the same thing that the minimum value and say that 50 and they'll start to lose things off the bottom. And like it says here, allow bounds to high data. If I untick that, it forces it to then show them again. But if not, we'll go minimum again. Most of the time, the automatic version that it finds is good enough. You can change the scale format. You can change all sorts of things, faults and everything you can possibly see here. You can change and make your child look. However you like. Vertical, horizontal axis at exactly the same thing, you can slot the labels. So if you've got some long labels, it's quite nice to be able to slot them at different angles. And sometimes it looks neater and idea. Especially if you've got something like January and then you can do that. So there's my chart. Once I've got it, I can then put it anywhere I like in the spreadsheet so I can move it around, I can make it smaller. So I can move that down here. So now we might like to add a chart which shows the profit because sales of number of items might be different to how much money you actually make off a particular set of sold items. The only problem we've got now is going to be slightly more involved because months are detached from actual grid down here. I could copy these down, paste them into these cells here. But that would defeat the object. We might as well go through a little bit of pain together. And every spreadsheet application works in a very similar, if not slightly different ways. And charts can be a little bit hot to handle. So you'll have to bear with me as we go through this. But it's just about rearranging things for the chart to make it look the same as a chart you've got before. Because there's no point in comparing things if they're not going to look the same. So here is the profit data. This is all the data I can get without including the months at the top, can equip My Chart button. And this gives me Google's best guess for what's going on with the charts. Well, first of all, It's put the shoes as the x-axis descriptions, which should be the months. So first thing we'd like to do is turn shoes into a series, which is a series is just a collection of data, is that in this case is this row of information. And you can have as many series as you'd like on a chart. First thing I wanna do is where it says use row 11 as labels. I don't want to use row 11 as labels. I would like to reuse row to eventually. So we'll untick row 11. And now we've got shoes, boots, gloves, and t-shirts as data in our chart. The next big thing I'm going to do is I'm going to go to the chart that I want. So that when I start working on things, start with our stacked column chart. And this is what I'd like to head towards. Yeah, we're pretty good. We've got T-shirts, gloves, boots, and choose. And this is good. We haven't got our months yet. So I'm gonna move the chart down and we're gonna go from here. And what we're gonna do is we're gonna go where it says x axis in here, and then click on x-axis. Then I'm going to click on select a data range. Then it's looking for a data range now is important to remember that the original data range included this column as well. So it's not just January to December. You also includes. First column as well. So we need to match that here. If I don't, if I select January to December, which makes most sense in my head, when I click OK, I'm gonna get this weird result where I've got February to December and a blank month. That's because the range that I have selected is one column different to the range that was in the original data. So instead, I'm going to go here, going to remove my x-axis and do the same thing again. Go back to my slight day arrange. And this time we're gonna select the range including that first column so it matches the data I've got. Click OK. Now it lines up. So we're close. We just need to change the title and change the X Axis Title. I can double-click on these, but I much prefer to go to customize go chart and axis titles, Horizontal Axis Title says shoes, I'm going to get rid of that so it doesn't say anything. And then on chart title, undergo, put shoes back in. Shoes, boots, clubs and teacher. Let's click out of that. Click in here. And now I've got a chart that's looking at this. I got the same colors and everything's in the same order. And the nice thing about Google Sheets as I can line this up, we'll do a little snap with that little red thing and snap onto that as well. And then I can snap that to there. And I got two jobs that look the same to teach at 12 boots and shoes. Now you might find that when you're fiddling around with the charts, that the series end up in a different order. So I'm gonna click on here, click on Edit chart. And if I go back to the setup, this series here, they might end up in the wrong order. Unfortunately. And this is the same for a lock spreadsheet applications. You can't just drag these around and move the series around and have instead of t-shirts first, have gloves first or boots instead of t-shirts, you can't just drag them around because it's quite complicated what the spreadsheet application is doing in the background. The only way we can move things around is by going to T-shirts, selecting these three dots and removing t-shirts. And then going to add series and add t-shirts back in again. And it puts it at the start. But if I remove gloves and then add gloves back in again, gloves now becomes the first one. So you can probably see that by a process of elimination, we have to gradually remove the thing that we want. So I'm gonna take T-Shirts away, go to here and add teachers back in again. The blessing is that once you've got all your series xyz in, it does remember them, so you don't have to go and pick that range again is already remembered what that range is. You don't have to go and find a new range. Should on that subject, you subsequently add a new row in here. And you want to include that in your chart, then you would simply go to your Chart. Goods your three dots, edit the chart, and then add a new series based on this data range. And it will just add it in. So if we sold a new product, then we could add the new information into our profit item here, fill all this in, then come to our yearly sales, add a new row here, I can click on the row, right-click, insert a new row, and it will add a new row in here. We can copy all the formulas across, copy or leaves formulas across. Everything will work fine. And the only thing we have to do is then go back to our chart. Go to edit the chart and add a new series in here with the little select today arrange. And everything should just work absolutely fine because it's just going to copy what it did before. As long as you've done the hard work to get it right, then all should be Well. So this is the end. Of course, as far as adding new things into the spreadsheet, there's going to be one more video just on printing and, and how we get out of here and actually onto a piece of paper should be wished to. But hopefully you can see that you've got enough information and enough complexity in a spreadsheet to get really, really complicated spreadsheets. If you're essentially adding lots of numbers up, you can do averages. You can do charting. The charts can get as complicated as like there's a huge list of possible charts. If I go to edit the jar there and backup to start college art, you've got a list of all sorts of weird and wonderful charts that you can possibly imagine. And you can do lots of pretty pictures with those. You can lay out as much as you like and get as complicated as you'd like. So hopefully, this course has been helpful in getting you that far. We've got one more step to go, which is just to go for printing. But apart from that, I hope you've enjoyed it so far to go forth and create complicated spreadsheets, but use columns. Don't make complicated formulas. This should be really as nearly as complicated as you ever see. Because if it needs to be more layers than that, then create a column somewhere else. Again, don't forget, you can create a bunch of information over here. If you want to on the same sheet. And then reference that over here, I would actually say, why not create a new sheet and call that calculation. And then add all your calculations in here and then reference them nice and cleanly on your final spreadsheet. It's so much easier to go from one column to the other rather than go through a really complicated single-cell which got lots and lots and lots of data in it. Anyway, during meet in the next video where we'll sort out some printing. 14. Printing and Sharing: So here we are at the end of our little journey. And the last thing we have to do is to present this spreadsheet to someone. We might want to print out for ourselves, stick it on the fridge. Or we just want to show it to someone else to share the information as traditional ways to do that and the slightly more modern ways to do that. The traditional way is to stick it on a piece of paper. So we can quite happily do that, and that's absolutely fine. So There's really one way to do that. I'm within Google Chrome and it utilizes Chrome print. And what I'm gonna do is I'm going to just for now select the cells like this and have a little selection ready just so that we can see what we're doing. And then I'm gonna press the Print button over here. And this will bring up the Print Settings. And it's exactly the same as you'd imagine printing anywhere else. So, but the, the options we've got, we've got current sheet, which will give us everything that's on the current spreadsheet that we've got. So if I just go back to here, that will go all the way down as far as there is information. So when we talked previously, we talked about having some working columns over here on the right hand side when nobody could see them, but they're there for working. They will be included in this. So when I go to print, you'd find that this was all very small and then you'd columns would appear because it's trying to get everything that's on the current sheet. The next option is Workbook. And what that will do will print out every single one of your spreadsheets one at a time, starting with the first one. So when we say spreadsheets, we're talking about this profit per item, then yearly sales, and then any other sheets that we've got. So it will include absolutely everything that you've saved, essentially in this Google Sheets document. And it will spread them out as best it can be. The last one, which is the one I tend to use most often is selected cells. So this will now print only what I've got highlighted. So if I cancel this, so I can do something silly like this and then do selected cells and it will print that out. This gives you the most control. So for example, if I wanted to print just the information like this, then I can select that, press print, go to selected cells. And then this is what we're going to work with. We've got a bunch of options. We've got the usual landscape portray, fit to width, fit to height, fit to page. All this malarkey that you usually have with printing anything we can change the margins. One thing that is important we get a formatting is the show grid lines. So earlier on, I talked about right at the start of the course. In fact, I talked about borders and how I'm not a huge fan of various different borders. Well, one of the nice things about Google Sheets is that you can, and it is by default, showing the grid lines. So you don't have to fiddle around with lots and lots of borders. If you're happy that your sheets gonna look like this with these quite nice grayed out grid lines. If you've got your own borders, they will show up as well. But what we can do is just untick Gridlines and now it'll only show whatever Borders you've highlighted. But personally, I'm doing a spreadsheet. Everybody knows it's a spreadsheet and it, you know, it's debatable whether in such a small set of items here, it's quite obvious that this figure here is gloves for February. Although I would suggest that we might want to just perhaps the titles of each of these columns because it's slightly disconnected, but with the grid lines in, it becomes much more obvious anyway. So I personally am a fan for gorilla. Nobody, despite what some people might think, nobody treasures a spreadsheet printout for the rest of their natural lives. The whole point of a spreadsheet is to produce a function. It's not a glorious, marvelous, wonderful piece of artwork. So in my humble opinion, having printed out many thousands spreadsheets over the years, I'm much happier if it just does what it's supposed to do, so it looks clean. It's got lots of information in this example here, if I take off grid lines is not as clear as with the grid lines on, everyone can read what they're going to they're going to read it for a meeting or however long we're going to be using the spreadsheet for war, it goes into your accounts or it goes into a file somewhere. If we're going to print it out and then it's never looked at really again. I just think it's nice. Just don't worry too much about spending lots and lots of time on pretty, pretty designs and making it look as beautiful as possible. The time for that is if you're putting it into a dissertation nor yet is your doctor or you're producing a scientific paper and then it needs to be clean and crisp, then that's fine. But you're likely either way, keep it as neat and tidy as possible with as little frill as possible. Anyway, that's my comment on that. If there weren't notes, it would show the notes as well. You can put notes within each cell. And then we've got horizontal and vertical alignment on the page as you'd probably imagine everything today. If I go and run that elastic it at the bottom of the page. So that's the formatting. We've got some headers and footers. We can put all sorts of sheet name work. We can take all these and have the garden date and time if you like. Again, it's all available. The unborn thing is once we've got here, we're at the point where we've done everything in here that we want to hear it looks R, then click next. It will have a think about it, and then we're off to the printer. So here this will change depending on the computer you're on. Essentially, we want to know where it's going to be off to a printer or whether say to a PDF, anything else. At this point you're now in your world. The icon help with, because this will change depending on the particular printer that you've got on your network or plugged into your computer. So that's simplest printing. So that's, that's simple as that. If I wanted to include the charts, charts are slightly interesting. You would think that you could just click on one of these press to print button and you'd get a printed chart. That's not quite how it works. So charts are treated exactly the same as on the page. So if I want to include the charts, I have to scroll down and include them inside the the selected cell. So now if I then go to print and I go selected cells, it will include those. Because they are now included. If I go Cancel and do a little bit like this, and then go print and selected cells. You'll get exactly what you expect to get. So when it comes to printing out charts as well as the spreadsheet, you do have to line them up slightly and make them look nice, sort of aligned with the grid. So this would be fine if I, if I highlight this, select everything, press, print, Go to Select ourselves. And then in this case, I might take the grid lines off. Then you've got quite a nice sheet with the two jobs at the bottom. However, why would recommend is actually instead of trying to print, line your charts up nicely, if you go to the chart, click on it and then go download, you can actually download it as its own PDF. So if I go PDF, then it will download that chart separately. And then it's nice and crisp. And then you can use that PDF however you like for, for whatever ever you want to do with it really. So then you can print out separately. I think that often charts and spreadsheets together. And the greed rather spreadsheets together looks a little untidy. So you can do it that way. So that's printing really as simple as that. You highlight what you want. I would generally go highlight what you want. Drag across whatever you want. Do press the Print button, select selected cells, and then do the rest of it to make the page look as pretty as you like. And away you go and off you go to print. These days. In order to save the planet a little, we tend not to print things out so much. And we use it digitally and often, if you're in a presentation, you've got your laptop there anyway, you could show this sheet or if it's someone else's, you could show it via the sharing functionality. So up here is a great big green button that's currently saying share private only to me. So I'm gonna click that button. It will then bring this up. There's my email addresses here, which you can't see because I've figured it out. But we can share this spreadsheet specifically with people who are also Google users. You literally type their email address in. And they will then give them access as either full access or read-only. In addition, even more useful down here is this link option down here. So this works if you've used anything like Dropbox or OneDrive or anything like any of these sort of cloud sharing offerings. We can copy the link and then anyone, anyone with this link can view or you can restrict it, or you can change to comment date on it or full editor. If you give them editor, then anyone with that link will then be able to go to the spreadsheet and change it and save it there and then so leave it as viewer. This is great because you can then copy this link, share it to anyone you like. And they can read your spreadsheet, display your spreadsheet and see all the information in it, but they can't edit it. And this is kind of the modern way of printing things really. If you wanted to share things around, then if you're in a presentation, you're probably going to show on a projector anyway, if you wanted to send it round to meeting attendees, then you can use the link and they can view it that way. Just append. Some people do like the piece of paper. But this is kind of the more modern way to do that. So that's it. That's printing and sharing that you lot, that's everything. So what we've done is completed our spreadsheet course. Hopefully, this course has been useful to you. And if you can produce something that looks very similar to this, then you've got all the skills you need to produce very complicated spreadsheets. I highly recommend if you just, just lay you working out. I know I said this about five or six times, but rather than try and do everything in a complicated way, you've got infinite spreadsheets and infinite cells. So have a calculation, have a, you know, a sheet that you call working. And do all your calculations in that sheet. If you want to have lots and lots of working out. And then you can have a nice clean presentation sheet where you can just lay your, your boxes out. Don't forget the e. If you've got a bit of information here and a bit of information here, and another bit of information here. Then anywhere, even on this sheet here, I can click on here it click equals, then go to profit per item and click that box there and just press Enter. And all this does on this spreadsheet here is just look at the profit per item sheet before and put it here. There's no calculation or anything, but this means I can then have my own clean layout for all the other information that I've gotten, all the other sheets. So you can always reuse the information that you've got. So if you don't like the way this is laid out, you don't have to redo all these calculations again and work out all the formulas. Just grab the information that you want and put it on another sheet in a different way. It doesn't mind, it doesn't care. It just, it just, it'll just lay out however you want. So hopefully this course was of some use to you. If you enjoyed it, please leave a review and I look forward to helping you again soon go forth and to produce fantastic spreadsheets. Thank you.