Spreadsheet Fundamentals

0:00[MUSIC PLAYING] This is Simona here, and we're going to have a lot of fun learning Google Sheets. And this skill is all about the fundamentals that you need to get going with the Google spreadsheet application. So we will start off by getting you logged in, and we'll take a quick tour of the interface. And then we'll jump straight in and create a spreadsheet. And we'll also look in a bit more detail at text and cell and number formatting. And we'll see how you can manipulate rows and columns to get the spreadsheet structure that you need.

0:33Maybe you want to hide or group or freeze a row or column or add extra sheets, for example. We'll see how you rename and move and delete files and see the file version history. And then we'll finish off with some of my favorite shortcuts. But note, there are other skills to follow where we learn in more detail about formulas and functions and charts and lists and pivot tables, all that fine stuff. This is just the fundamentals to get you started. And if you're already using Google Sheets, then you may well already be familiar with much of this. I mean, you could certainly skip this first video, for example.

1:05But if, I don't know, say you've been an Excel user for the past 15 years and you're thinking to yourself, oh my goodness, what is this crazy, weird world of Google, or maybe you've just never really ventured too far into the world of spreadsheets, then this is the skill to get you started with Google Sheets.

0:00[MUSIC PLAYING] Before we get into the detail of creating a spreadsheet and seeing what you could do in the wonderful world of Google Sheets, let's just take a few moments to get you logged in, and then we'll take a quick tour of the interface. Now, throughout all of these videos, I'm going to be using Google Chrome as my browser. But it does work well in other browsers as well. So whichever one you're in, take yourself to sheets.google.com. And because Google Sheets is an online service, which is part of its beauty, the first thing it's going to do is say, ah, well, what account do you want to log in as?

0:36And notice here that I've got two demo Google accounts. This is my demo personal Google account. That's Gmail account. And this is the one that links to the fictitious organization that I use in my demos. So that's the one I'm going to choose right here. As you might expect, it's going to need you to log in. And happily, it's remembered my password. So let me just click Next. Now, that will load the Google Sheets home screen. And if you look at the top right-hand corner, this is where you can see what account you are logged in as. So you'll get sort of quite used to recognizing the little picture there, your profile picture.

1:07Because if you do use multiple accounts, so for example, let me now login with my personal Gmail account. Let me just sign in and click Next and pop in my password. And that has opened up a separate tab logged in with my personal account. You can see it's got a slightly different profile picture there. But the reason why I point this out is because very often when we do have work accounts and personal accounts, our work and home life become a bit blurred, particularly when we are using the same computer, the same device for both work and personal tasks.

1:37And of course, the Sheets environment will be connected to the Google Drive associated with that account. So this is my personal account. I could recognize that from the little profile picture up there. And you can see the recent spreadsheets include the travel planner and the house move checklist. Whereas on that other tab where I was logged in with my demo work account-- you can see the different profile picture there-- you can see that the recent spreadsheets are different. I've got the IT audit and the budget proposal and the regional sales figures. Those are the work documents. So be alert to that. But let me just close down the tab with my personal account because I'm going to be using a work environment for these demos.

2:11And I'm going to choose this button here to create a brand new blank spreadsheet. So you will notice you've got menus across the top here. That obviously is going to lead you to the different functionality of Google Sheets. And wherever you see this little arrow in a menu option, that means there are other options to explore. So these are all the different number formats for example. And also, we've got buttons on a toolbar. So this will provide shortcuts to many of the same options that you will find through the menus. So for example, here are some of those same number formatting options. And again, wherever there's a little arrow on a button, that will lead you to more options.

2:42So in fact, these are the very same options we saw a second ago through the menu. There are some common ones here. So for example, bold and italics. And you'll notice when you hover over a button, it will tell you what that button does. And also if you like your keyboard shortcuts, then it will show you that keyboard shortcut in brackets after the little tool-tip there. Now, depending on what screen resolution you're running at or what size window you're using, you might get a little More button at the end there. So let me just restore this window to being a floating window. You can see there's the More button. That just gives you the extra buttons that couldn't fit because of the window size that you're working on.

3:16But also, if you use this option right at the end of the toolbar, you can hide the menus. And again, there's the keyboard shortcut if you fancy doing that on the keyboard. But that will give you a little bit more space on the screen. And that does give you this little dot, dot, dot More button for the overflow buttons. And that's because it's giving you this option here at the left-hand side to search the menus. And they are like this. I mean, we've hidden those menus now so we don't have them as readily available. But I can still search the menus using this option. So if I think, oh, I want to do a spell check. Where is it?

3:47Just start typing in the function that you want, and you'll be able to actually run that menu option directly from that search. So funnily enough, there are no spelling suggestions in this blank spreadsheet. At any time now you can click that little button again to show the menus. And I think generally that's how most of us will work most of the time. But it's useful to have the option to get a bit more space on the screen. And you'll notice that search option has now disappeared. But you can always find it again from the Help menu. Looking down the right-hand side of the screen, you've got the little side panel here, which you can show or hide and that will give you quick access to your calendar or to your notes and keyboard to your tasks and whatnot.

4:23Down at the bottom right, we've got this explore button, which is quite exciting. And we will look at that in more detail another time. But of course, the main body of your screen is the spreadsheet itself. So all of those rows and columns, so all of the rows are headed up by letters. All of the rows are numbered. And whichever cell you are clicked on, well, you will see that cell reference just over here on this area, which is called the formula bar. Now, that does become quite important when we do formulas. But if it's not showing, or you want to for whatever reason hide it at any time, go to your view menu.

4:56That's so you can turn on or off that formula bar. So be aware of that. And did you also notice that you can turn on or off those grid lines as well if you don't what those showing for whatever reason. And then the big green button at the top left-hand corner takes you back to your Google Sheets home screen, which is right back where we started. But I think now we're ready to actually start entering in some data and create a spreadsheet. So stay with me. That's what we'll do in the very next video.

0:00[MUSIC PLAYING] In this video, I'd like to get you started with your first Google Sheet. Think of it as your quick-start video. We're going to jump right in. And we'll input some data, and format the headings, and add rows and columns, and copy some cells. And we'll even sort rows and add a quick chart. Wow. Let's start off by entering in some headings for some data that I'm capturing. So we'll imagine it's some kind of sales figures or something-- something generic like that. It doesn't really matter what it is.

0:31But let's imagine I want to type in a heading into cell B1. So I click it. And you can see that I know I'm clicked in cell B1 because it shows it there in the formula bar. But I can now just start typing. So let's imagine I'm capturing sales data for the different stores that we sell our products in. So the first store is at Manchester Parkside. And when I finish typing, I could press Enter. But that will take the selection down to the next cell underneath-- down into cell B2. But I don't want to go there because I actually have another heading I want to type in the next column.

1:01So instead of pressing Enter, I'm going to press my Tab key. So that's quite efficient when you're using the keyboard and you're entering your headings-- pressing Tab or Enter depending on whether you're going downwards or sideways. But anyway, our next store is in London Oxford Street. So let me type that in. And you'll notice a little typo there. I've got a capital T in the middle of street there. But I haven't noticed. We'll come back to that later. So again, I'm going to press my Tab key to type my last heading, which is going to be London Heathrow Terminal 4. So let me just pop that in. And you may or may not have spotted, I've got a spelling mistake there.

1:32But we'll pretend I haven't noticed. But I have finished typing my headings, so now I am going to press Enter. And what will happen is Google Sheets will think, oh, she wants to type another row of data here. So normally when you press Enter, it takes you down into the next row underneath. But because I had typed a few headings and then pressed Enter, it says, oh, she wants to type in the next row. So it's quite clever in anticipating where I might want to type next. But actually, before I type in any more data, I want to sort out my column widths because we can see that these columns aren't quite wide enough to display the full text that I had typed in there.

2:06So column B isn't wide enough. To make it wider, just move your mouse onto the dividing line between columns and you get the blue line appearing in the double-headed arrow. That means you can click and drag to stretch that column to whatever width you like. But, in fact, if you double-click on that same area, it will autofit it to just the right width. And actually, you can autofit a whole bunch of columns all in one go. So if you select multiple columns by clicking and dragging on that gray heading area and then just double-click on any one of those dividing lines between columns, it will autofit all of those headings.

2:37So that is looking better. But let's sort out this typo. I realize I need to correct that capital T to a lower case t. So I click it. And I think, well, I need to get my cursor just in there to-- and I can't seem to be able to get my cursor in the right place to edit it. So what do I do? Well, there are two ways to edit a cell. You can either double-click on the cell and that then will give you a cursor that you can position to make your edit. Or if you click on the cell, you will see the contents up in that formula bar. And that means you can, with your cursor, go in and make the edit in just the right place.

3:11So that is looking better. But I've also got a spelling mistake-- London Healthrow instead of London Heathrow. So we do have a spell checker. If you have a look in your Tools menu and choose Spelling-- let's run the spell check. And sure enough, it's suggesting that I change Healthrow to Heathrow. And I think, yes, please. Please go ahead and make that change. And there are no other spelling errors spotted. But I suppose before we go too much further, we should talk about saving this file. Now, the name of the file is displayed at the top left-hand corner. And currently, it says Untitled spreadsheet.

3:42And actually, it wouldn't matter if you had forgotten to save, which quite often is the case. You get carried away with your content. You forget to save. This is already saving. And you can tell that if I click this little cloud thing to see the document status, it's telling me that all changes are saved to Drive. But it hasn't yet got a sensible name because it's just got the default Untitled spreadsheet name. So I can click in that to rename it. So let's just call it Store Figures, something like that, and press Enter. And we will talk in more detail later on about where exactly that file is stored and how you would move it into a different location.

4:15But from now on, we can forget about it. That is automatically saving into my Google Drive, which is brilliant. But anyway, now let me add some other headings down the side, maybe for part numbers of the different products that we sell. So maybe we've got a part number, something like this. I'm just entering in a bit of random nonsense here so you get the idea. And I was pressing Enter between all of those entries because I did want the selection to move down to the next cell underneath. And at any time if I want to zoom in on my spreadsheet, well, the default zoom is 100%. But maybe 125% is better to help me focus on this part of the sheet that I'm working on here.

4:51But now let's add some formatting. So no wild surprises for you here. I can just click and drag in the middle of the cell and drag across to select the cells I want to format and, sure enough, click the appropriate buttons up here. But in order to select all of my headings in one go, well, I could hold down my Control key to select these other headings as well. So that pale blue shading tells me they are all selected. They're all highlighted. So let's make them all bold. And maybe let's choose a different color, maybe choose a dark green there. And now that they're bold, well, the text has become a bit bigger.

5:23So I can just use that little trick we saw before to select those columns and then double-click on a dividing line just to autofit those column widths again. Perfect. Right. Let's add some figures now. So I'm going to stick with nice easy numbers so that when we do some adding up, we can quickly tell that we've got it right. So 100, 200, 300 for the Manchester Parkside store. Let me just click right up into the London Oxford Street store and add in some other figures-- so 20, 30, and 40. And then London Heathrow-- 25, 50, and 75. Good.

5:53But then I think, ah, I need to add in another column. We've got another store, the Manchester City Centre store that I want to add a column for in between these two columns here. Well, that's easy to do. Just click in-- it doesn't really matter which column-- one of these two columns. Go to the Insert menu. And you'll see it says, oh, do you want to insert a column to the left or to the right or row above or below where you're clicked? Well, I want a column to the left of where I'm clicked. So as I said, that's why it doesn't really matter which column you had clicked in. You would just choose the appropriate option from the Insert menu there.

6:24So I'm going to copy this heading into this one to save me a bit of typing. Well, you know the keyboard shortcut Control-C will copy. And you can tell you've copied because you've got this little sort of dotted line around what you've just copied. Click where you want to paste it and Control-V to paste. You could right-click to cut, copy, and paste if you prefer. Or in the Edit menu, you'll see the same options. But I think those keyboard shortcuts are pretty universal. And then let's just double-click to make an adjustment to that heading because that's going to be Manchester City Centre store.

6:54And yes, that is how we spell center in the UK. Excellent. Let me pop in some figures. And then I think, ah, well, this is looking good. But I wish that these part numbers were sorted in alphabetical order. I just typed them in as I thought of them. I'd really like the AA45 part number to be first. So that's a bit annoying. But as you might imagine, we can sort rows in Google Sheets. And there's some fantastic things we can do with our lists. And you know that we're going to be looking at all of this in more detail in other videos. But let's say we want to sort this range, this group of cells here, by that first column.

7:30So if you go to the Data menu, we've got quite a few sorting options. Well, it's not the whole sheet because we don't want to include the headings there. I just want to sort the range by that first column. And I want it to go A to Z. So when I choose that-- perfect. Did you see the little movement there? We've now got AA45 at the top of the list. And that is now sorted alphabetically. And so now I think we're ready to put in some totals. So let me just add in a little heading there for total. So I want to see what our total sales were for the Manchester Parkside store. Well, I could do it in my head.

8:02I could get out my calculator and just type a number in here. But, of course, this is a spreadsheet. It can do it for you. And there are a couple of different approaches here. And we're going to talk about all of this in more depth. But what I'm going to do is I'm going to use a SUM function. So I'm going to just select the cells that I want it to sum for me. And then over towards the right-hand side of your toolbar, you've got the Functions button. And when you click that, right at the top of the list there, we've got the SUM function. And when I choose it, it's going to say, ooh, did you want to sum that lot that you have selected?

8:33So it's shaded it in a peachy color. I've got this orange dotted line. That's what it's summing. It's got it right. So I'm just going to press Enter to accept that. And you can see at a glance that is indeed correct. And because it's a formula, if these figures change-- so let's say that's changed to 500-- then, of course, that total will update to show me the correct figure. And I'm so pleased with myself. I'm going to make that bold and bright red while I'm at it. Excellent. But, of course, I want the same function in these other cells here to add up the totals for the other stores. So I'm going to copy that.

9:04So let me just press Control-C to copy that cell. I can see the little blue dotted lines around there to indicate that's what's been copied. And I'm going to select all of these other cells and press Control-V to paste it. And that has done the job. You can see at a glance that that has added up all of those different columns. It's a good job we used easy numbers here. But I do have a few options for my pasting. So I could say, no, I only want to paste the value. That would give me 900 as a value in all of those cells because that was the value of the cell that I had copied.

9:36Or I could just paste the formats, i.e., the red and bold formatting. But I'm just going to press Escape to get rid of those options because what it did first time was exactly what I wanted. And at any time if you make a mistake or something unexpected happens, then don't forget you've got an Undo button. So the very first button on the toolbar, if you click that, it'll undo, undo. You can step back. And if you step back a bit far, then you can redo to step forward in time again as well. And that makes it brilliant for experimenting, especially, I think, with a spreadsheet when you're just trying something out.

10:08You're trying out a new function or something. You're not sure if it's going to give you the result you expect. You can try it and undo if it doesn't give you what you expected. But just to finish off with then, let's just pop in a quick chart. And I only want to put in a chart for my Manchester stores. Now, notice I have not selected the totals because those figures will be too big and they will distort the chart. But I have selected the titles because I want it appropriately labeled on the chart. And I go to the Insert menu, choose Chart. Let me just resize that so I can see what's going on.

10:40I can delete that heading if I don't want it. Superb. So wow. There is more to learn about all of these things. But I hope that's given you a good taster before we dive deeper and explore further.

0:06I think you're going to be surprised at just how much we've got to cover here because it's more than just changing the font style or the color or the alignment of something that you've typed into a cell. That's what I would call text formatting. But I feel obliged to put it in quotes because, of course, you can also apply that kind of formatting to numbers, too. But we can also change the appearance of the cell, you know, it's shading or its border. And also, the appearance of numbers, whether they show with a currency symbol or how many decimal places, things like that. So let's see how many of those formatting examples we can cram into this single spreadsheet here I'm just going to get rid of that chart, so we can focus on the formatting these cells here.

0:43And obviously, you already know that you can apply basic formatting using the buttons up here. So let's just reset that red color back to the default. And you might notice that the underlying button is the only one that's missing from the toolbar, but you can get to it from the Format menu or, of course, the universal people shortcut, Control U. In terms of fonts, well, you've got the dropdown here for fonts. But let me just select the whole sheet by clicking this area at the top left hand corner, the intersection between the row and the column headings, to select all of it because I want the whole sheet to be a particular font style.

1:13And you can see that the default is from the Theme. Now we're going to talk about themes another time. But if I choose more fonts, this is where I can pick from the vast array of Google Fonts. So, let's imagine in my organization, we always tend to use Raleway. So when I choose that, that's now going to appear as one of My fonts. You can filter the list here. You could show particular types of fonts. So, just Handwriting fonts, for example. So, I do like Architects Daughter. So I'm going to add that as My fonts as well. Let me tick OK to that. And now, what you'll see on the Font dropdown list, those fonts that I've just chosen are available.

1:47So let me choose Raleway. There it is. And if I want to make a cell match another cell in its appearance, in its formatting, well, we can Paint formats. So you click on the thing that's got the formatting that you like. And then, you click the Paint roller button just here, the Paint format button. That sucks up the formatting of what you're clicked on. And then, you click where you want to spit out that formatting so onto the total cell there. So that's a great way of making sure that things match. But let's talk a little bit about Alignment and Text wrapping of these headings here. So, if I adjust the column width here and decide that I really want this text to wrap in this cell, well, I do have some text wrapping options.

2:23So, take yourself over towards the right hand side of the toolbar. There's my Text wrapping. And I can choose that middle option there to wrap. And I can also make this row a bit deeper. And that means, I might want to consider my alignment. So, the usual alignment, the horizontal alignment, kind of does the side-to-side centering. But if I want it vertically aligned within that cell, well, I can do that, too. You can see that it's currently sitting at the bottom of the cell, but let's put it in the middle of the cell. Perfect. And then again, I could use that little Paint format's button to apply that same formatting to these other cells.

2:56And if I select all of those columns and just adjust the widths, I know that they're all going to be the same width as each other and the wrapping has been applied to all of those cells. Now, I didn't do it for this last one because there's just one more option I want to show you with regards to text wrappings. Let me just make this column a bit narrower. And you can see that the default is that the text kind of overflows out of the cell if it can't quite fit. But the other option that we've got is this one. Clip. And when I choose that, it means that it doesn't overflow into the next cell.

3:26But it is still there. If you look up in the formula bar, it's still there. It's just not kind of hanging out. But let me just use the Paint format button to make it match all of the others. But another thing to mention here is sometimes when you do text wrap, sometimes the text doesn't wrap the way you want it to. It doesn't break at the point where you'd like it to have broken. So for example, for this cell here, I really want it to wrap where Oxford starts. So, if I go into that cell to edit it, then I can press Control Enter. Or if you're an Excel user, then you might be familiar with using Alt Enter. Well, that would work, too, but that will force a break for the text wrapping.

3:58And let's imagine, with these headings here, well, maybe I want them to tip jaunty angle. So you can see just past the Text wrapping options, I've got Text rotation. So I could choose-- oh, let's choose the Tilt up option. Very nice. And this is where I really do love the Undo button because you can play around with all sorts of different options and just undo if it wasn't quite what you had in mind. I'm just going to add another row here. So let me go to the Insert menu and choose Row above because let's imagine, I want to add a title. So these are my January figures, something like that. I'm going to make this bold, and I'm going to make it a bigger font size.

4:31So, let's choose 18, or that's not quite big enough at all. But 24 is a bit too big. So you might think, well, how do I get a number in between 18 and 24. Well, you can just type in a particular number. So let's say, I want it point size 20. That's not on the list, but I can type it in. And that's perfect. If I want that headings centered across these cells here, well, I have to merge these cells first. So I can select all of those cells and then choose this option to merge them. And when I choose that, that means I can now center it across all of those cells that I've now merged into one great big cell.

5:03And again, let's use the Vertical alignment pattern to make sure that's in the middle of that cell. Oh, very smart. I want to apply some borders and shading to these cells now. But for maximum effect, I am going to add in some blank rows. So, I'm just right clicking on the row and column headings here to just insert some blank space around the edge. You'll see why when we have it all finished with its borders and shading in all its glory. And I'm going to make those extra ones I've added a bit narrower. This is going to give me a bit of white space around the edge when I've done my borders and shading. So let's start off with the background shading.

5:34So obviously, select the cells and the button you're after is the little paint can here. You've got a million colors to choose from, even custom ones. These are the theme colors. As I said, we'll talk more about themes another time. But I'm going to choose-- oh, let's choose this pale green here. For borders, well, that's the next button along. Let's choose my border color. I'm going to choose a dark green. The Border style, I'm going to choose a slightly thicker border. And then, where I want to put it. And I want it around the outside of what I have selected. So let me choose that. Click away. Oh, that's looking good. But I now want a heavy line to separate out the totals from the other figures.

6:08So, let me select just those cells this time. Back to the Borders button. And this time say, what I'd like a top border of what I have selected. And very often, when you add your own borders and shading, that might mean you don't need the default grid lines anymore. Particularly, if you want to take a little screen snip of this for some other use or maybe you want to present your spreadsheet in a Google Hangout or something, well, you can turn off your grid lines, remember. So let me just turn them off from the View menu, so you can see that beautifully formatted spreadsheet in all its glory. But now let's talk about Number formatting.

6:40So these cells here are obviously numbers. How do I want those figures displayed? Well, they're all sales. So, it is a currency. And if you look up at this area of your toolbar, that's where you'll see your Number formatting shortcuts including this one, Format as currency. And you'll notice that that's got the UK pound sign there. That's because in file spreadsheet settings down here-- that's because I've got the UK set as the locale for this spreadsheet. But obviously, you could change that if you want the default to be something else. But you can override it and use different currencies at any time if you need to.

7:12I'll show you that in a second. But let's imagine, that's great. Let's put the little UK pound signs on. But I don't want the two decimal places because I've rounded everything up to the nearest five pounds anyway. So look at these buttons here. I can decrease or increase decimal places. Let's just decrease the decimal places. That's perfect. But now, let's imagine, I don't want the little pound signs anymore. Now let's imagine, I think, oh, no, I don't want that currency formatting anymore. So, you might think to press that button again to turn it off. But actually, what that does it just reapplies the currency formatting number style.

7:43So these buttons are not on and off, like Bold would be, for example. So bear that in mind. So if you wanted to make a change and get rid of a currency or a percentage format or whatever that you had applied, click this button here to see more formats. And then say, please just give me automatic formatting. And that puts it back to how it was. But you will have also noticed there were all sorts of other exciting options to explore here. So let me choose Financial. I quite like this because it shows your negative numbers in brackets. And again, I can get rid of the decimal places if I want to.

8:13But let's just put a negative number in here just to check that that works. So let's make that minus 100. And when I press Enter, you can see that that 100 is displayed as 100 in brackets. But up in the Formula bar, it's still actually showing as minus 100 as the value. But even more excitingly than that, we can create Custom number formats. So a common question that I get asked when I teach people how to use spreadsheets, whether it's Google Sheets or Microsoft Excel, is OK, well, I really want that negative number. Yeah, great, it's in brackets.

8:43That's what I want. But I want it to show in red as well. And one way of handling that is with a Custom number format. So there are all sorts of other formats to choose from. But right down the bottom, if you choose More formats, that's what you can choose a different currency other than the default that appeared on your button as per your locale set for your spreadsheet environment. But what I want is a custom number format to say, well, I want my negative numbers to show in red. Now, there's all sorts of different things to play with here. But what I tend to do is just fiddle up here to change what it's currently set out with my own little tweak.

9:19And the way these codes work is the first bit there, that's how a positive number is going to be displayed. Then after the semicolon, that's how a negative number is going to be displayed. So at the moment, it's going to be displayed in brackets with no decimal places. Well, just at the beginning, I can just type in in square brackets the color I want it to appear as. So if I just pop that in there, you can see the little preview. That's how it's going to appear. Fantastic. And if you are interested in these Custom number formats, then I do urge you to check out the article here, the Help article which explains what all these funny little codes mean.

9:52But let me just apply that as an example. You can now see that's in red. And of course, that's dynamic. So if I make another of these numbers negative, then, of course, that formatting is going to apply where appropriate. And notice, if I go back to those Number formatting options, that custom format that I just created is appearing on the list there for quick use again another time. And then finally, while we are talking about formatting, if I click that little button at the top left again to select the whole spreadsheet and go back to my Format menu, but this time choose Clear formatting, well, that has cleared all of my formatting except for the Number formatting, you will notice.

10:30But oh, that's rather disappointing. So let me just go back to undo, to put it back to that beautifully formatted spreadsheet that I had created. So there we have it. Examples of Text formatting, Cell formatting, and Number formatting. Your spreadsheets will never look the same again.

0:00[MUSIC PLAYING] We already know how to add rows and columns, but there are a few other things we might want to do with regards to the structure of our spreadsheet such as moving rows or columns around or hiding or grouping or freezing rows and columns, adding new tabs for extra sheets, all of that kind of thing. Now I'm a big fan of using the right click menu to insert extra rows and columns and whatnot, and that's what we've done before. But actually look, there's a little dropdown here, which you can click which gives you the same menu.

0:32And I think it's worth just a quick mention about the difference between clearing a column and deleting a column. So when you clear a column, if it's not obvious to you, that just deletes the contents of the column and you could have done that just by pressing the Delete key on your keyboard. Whereas if you delete the column, that will take out the whole actual column, including the contents. So, again, let me just undo to get that back again. But did you also notice that there was a resize column on this menu as well? Now when we resized columns before, what we did, we collect and drag to select these three columns and then just adjusted it with the little dividing line there and that adjusted all three of those selected columns to make them the same.

1:07And we just did it by eye, didn't we? So we already know that those three columns are the same width. But what if I want all four to be the same? Well, I could do that little trick or if I knew a particular measurement that I wanted those columns to be, then I can choose this Resize columns C to F because those are the columns I've got selected, and it measures it in pixels. So let's say I happen to know that I want those columns to be 110 pixels wide, I can click OK. And you probably saw a bit of movement there as they all jiggled to their new size. Another option that's quite useful is this one, Hide the column.

1:39So when you choose that, obviously, the column is hidden. So it's not properly deleted. It's just hidden. And you can see this little arrow thing here. If you click that, it will just spring back again. And if you want to hide it again, you would have to call up that menu again or right click to hide it again. So this can be useful. I mean, let's imagine you're going to present your spreadsheet over a Google Hangout, or a Google Meet or something and you just want to tidy it up a bit to hide some irrelevant data before you show it to your colleagues when you're sharing your screen. But if you find yourself regularly hiding and then unhiding again, then consider using grouping.

2:13So if I choose to group that column, what that does it, gives me this little button here. I can click that to effectively hide it and do what I need to do. And at any time I can show it again and then hide it again. So that's a much better ON/OFF type of setup which might be useful. But anyway, let me just ungroup that column to put it back to how it was. But let's have a quick word about moving things around. Now you have to be quite alert to the shape of your mouse point to here. So if I click on a cell or click on a column heading or click anywhere, notice that my mouse pointer looks like a white arrow.

2:43Now that white arrow is your selection arrow. That's what you need when you're clicking and dragging to select something. But as soon as that mouse pointer changes to a little hand, if I move to the edge of the cell, can you see it changes to hand and then I click and it goes to a clenched fist. It's got hold of that cell, then I can click and drag to move that cell around. So let me again just undo that. But this is interesting when it comes to columns. So let's imagine you select that column and then realize actually you want to select all three columns and then you click and drag and realize, I've got a little hand.

3:14I'm moving it around. So if you want to select multiple columns, you have to it all in one go while the mouse pointer is that white arrow. But if you do want to move a row or column then click once, let go. And then when you get the little hand, you can click and drag to move that column around. And it's the same with rows. So I could click and drag to move this row into a different position. Or this row click to select and then click and drag, and you can see a heavy gray line which indicates where that rows going to end up when you let go of the mouse. But notice I didn't actually do it for the column. But if I show you what happens if I try and move this column, it then says, Oh, sorry, you can't do that because it's got its knickers in a twist, because of that merged cell that I had that spanned those columns.

3:55So this does sometimes causes a problem with these merged cells, so I would recommend if you are using merge cells to make a nice heading like this that should be the last thing you do. While you're still manipulating or structure the spreadsheet while you're still working with your data, then it's probably best to avoid merge cells. Now let me just scroll to the very end of this spreadsheet. And you will see, if I go right down to the very end here, it's got 1,002 rows. Now the default is for new sheets, it'll give you 1,000 rows and you can see I can add extra rows if I need to.

4:25This particular sheet got 1,002 because I've been fiddling with it. But actually the number of rows that you can add is unlimited. The limit is actually at the time of recording in the number of cells that you can have is limited to five million cells in a Google Sheet. And there's an 18,000 and something limit in the number of columns that you can have. So for most us, for most day to day use of spreadsheets, that is probably enough. But anyway, I am going to add some more rows. So I'm going to click and drag to select those three rows and I'm going to right click because that's my favorite.

4:55And look, because I selected three rows, I get the opportunity to insert three more rows. So I'm going to add quite a few. And in fact, I'm going to use the redo button here just to do that. again, and again. Because let's imagine I'm going to add in lots more part numbers here and lots more figures. So that means as I scroll through this particular list, I'm going to want these headings here to be frozen. I want them to stay fixed on the screen, so I know which store I'm looking at when I'm filling in figures here or here. I don't have to just remember what those headings were. So the trick is click on the cell that you want everything up to that point to be frozen or down to that point to be frozen.

5:32So I basically want the top three rows to stay fixed on the screen, don't I? So if you go to the View menu and choose Freeze, well it's already got a default option of freeze the top row or the top two rows or because I'm clicked on row three, it says OK how about up to the current row, which is row three. So that's what I want. So if I click that, and now I'm just rolling the wheel on my mouse. If I scroll down, you'll see those headings stay fixed so I can enter in the different figures for the different stores for those other part numbers. To unfreeze, well, go back to the Freeze option again and choose No rows.

6:06But again if I wanted to freeze, for example, the first two columns maybe I was adding lots of extra stores and I wanted to scroll sideways and keep columns A and B fixed. Well, look what happens this time. If to go View and choose Freeze and choose well the first two columns please, it says sorry, can't do it because of that wretched merge cell again. So another good reason not to include merge cells while you're still manipulating and working with your data and the worksheet structure. But also notice down the bottom here, this is just sheet1 of my Google Sheets file.

6:36I can add extra sheets. So just to the left there, I can click on that plus to add a new sheet. So it's a new blank sheet, and I can switch between them. To delete a sheet, well, you can either right click or use a little arrow there to display the menu and delete it because actually what I want to do for this particular example, I want to create a copy of that sheet because it's already got a structure and the formatting that I like and then I'm going to over type it with February's figures. So on that menu, you might be drawn to the Copy to option, copy to a new spreadsheet or an existing spreadsheet.

7:06But to create a copy within the same file is actually the duplicate option that you want. Let me choose that, and it says Copy of Sheet1. That's exactly the same. I've got two the same. So before I confuse myself, let me just change this to February, press Enter and just delete January's figures. So that's already to overtype with the new figures and then, of course, I can rename these sheets, so that copy needs to be called Feb. And the original one, of course, we'll call Jan. You get the idea. We can change the order of these sheets. So I can just click and drag the little tab to change the order, to put Feb at the beginning but that will feel a bit weird.

7:40So I'm going to put it back to how it was. And you can see you've got a few other options here with regards to hiding the sheet for example. And again, this can be useful if you've got quite a complex spreadsheet maybe with one of these tabs showing lots of background data that you don't want other people meddling with and it's quite useful to hide a sheet, so it makes it a bit harder for people to go and explore and break things. And I doubt if you saw the message that popped up briefly at the bottom right hand corner that said your hidden cheat is now viewable from the View menu. So if you go back to View, you can see there is one hidden sheet in this particular file.

8:12And that's how we can display it again. And also you'll see I can change the color of the sheets. So let's make this, for example, blue that gives a little colored line there. And also we can protect the sheet. And we'll talk more about protecting sheets and setting permissions for who can edit what when we cover collaboration. But for now, we've covered quite a few options when it comes to working with the structure of your spreadsheet file.

0:00[MUSIC PLAYING] We've already seen that your files are automatically saved into your Google Drive, which is brilliant. You really don't have to think about it. But in this video, let's take a look at things like moving files, copying or deleting or renaming them, even the version history-- all of that kind of thing. I'm just going to start off by making a quick change to this file because that is going to become interesting when we do look at the version history at the end of this video. So let me just delete some rows there. And you will remember that this has always been saved to our drive.

0:33And we gave it a name right back towards the beginning when we created this document of Store Figures. But we don't have to have given it a name. It would have still been saving, just under the default name of Untitled spreadsheet. But at any time, you'll see it's prompting we can rename it. So I can change it to something else. So let's call it UK Store Figures, something like that, and just press Enter. But then I think, well, whereabouts is it stored? And this button here will invite me to look at my different folders I've got on my drive and put it into a particular folder if I want to. So I can see all of my drive folders there.

1:04And I look at those and I think, hmm, I haven't really got anything for my sales data. But I can create a new folder on Drive from here. So if I do that and say, well, yes, let's have a special folder full of my sales stuff. So sales data, yes, please. And let's move this file to here. Perfect. And there are quite a few interesting options on the File menu regarding, funnily enough, file management. So, for example, I could go to New and ask for a brand new spreadsheet. That would open up in a brand new tab. Or I could click Open. That'll give me the file picker here.

1:34And, again, that would open up in a new tab. Back to File, what else have we got? We'll look at importing and downloading to different file formats when we talk about working with other people. But look at this-- Make a copy. So this is quite nice. And I think if you're a Microsoft Office user, you'll be used to seeing Save As here. So this is if I want to create another copy of this file. So the default name is Copy of UK Store Figures. But I think, well, no, I want to use this structure to capture the sales figures for France. So I'm just going to give that a more sensible name, choose the location. Yes, I'll keep it in that same place.

2:06And click OK. That opens up on a separate tab. So, of course, I could just overtype all of that information with the relevant data. But let me just go back to the UK Store Figures because that's the one I'm working on. You'll see that I can also star the file. So if I just choose that, that'll give me quick access to it from my drive. In fact, let me just pop into Google Drive, and we'll see that. So if I go to Starred items down the left-hand side here, that's where you can see that UK Store Figures that I had just starred, but also that folder we created. There we go-- Sales Data. And if I have a look in there, those are the two files that we would expect to see in there.

2:38So-- perfect. All works really nicely. If you go back to the Google Sheets home page, though, you'll remember this gives you access to your recently used files and your templates and whatnot. But also, if you're anything like me, you will have quite a lot of these Untitled spreadsheets floating around. And if you click the More button, you can deal with these. So you could rename it if you knew what it was, and you thought, well, I should've given it a more sensible name. Or if you know you were just messing around and you didn't need to keep it, you could remove it. So let me get rid of that one. But sometimes there might be an Untitled spreadsheet.

3:08You think, well, what on Earth was that? So let me open up this particular one. And I'm looking at it-- I realize, ah, no, I don't need that. So you can delete files from here. So if I choose Move to bin, that'll get rid of it and take me back to the Sheets home screen. There are a few other options on here as well, for example, making available offline. We'll talk about that another time. And notice that you can control how these recent files are displayed, either as a list, how they're sorted, or go into the file picker. But my favorite thing of all-- let's go back into this UK Store Figures-- is the version history.

3:41So you remember we were working on this particular file over the last couple of videos. And I did indeed make a change right at the beginning of this video. And look up the top here. It's telling us that the last edits to this file was made five minutes ago. And if I click that, it opens up the version history down the right-hand side here. And this is brilliant. I love this. So let's just click, for example, on yesterday's version of this file. Sure enough, you can see all of those extra rows that we added that we deleted at the beginning of this video. And if you're wondering why the shading looks a bit peculiar-- you're thinking, well, I'm sure that should have been green shading-- it's because changes are shown shaded.

4:15So down the bottom here where it says Show changes, if you uncheck that, then the file will probably look more as you expected. That becomes really useful when you're looking at changes made by other people. But for us in this spreadsheet where we did have cell shading, it did look a bit peculiar. But anyway, if I just scroll through some of these other different versions here, you can see the different iterations of this document. And if you click the little triangle here, it'll expand to show the detailed versions. So while we were playing around, learning about how to use Google Sheets, we had all these different versions. They were all collapsed into these more major versions. But you can drill down and see any of these.

4:47And you'll notice you can also name any version. So let's say I look at this one. I think, ah, yes, now that was the original data. That's going to be useful. So I'm going to rename that version. And that's going to make it easy to identify another time. But also, you can restore a version. So let's say I look at this particular version, and I think, ah, yes, I want that to be the most recent version. So I've got this little More actions button here. And if I choose Restore this version and say, yes, I'm happy to do that, that basically puts that version right at the top of the list.

5:18That becomes the current version. But it does not mean that all of those other later versions are lost. So if I just go back into that version history, you can see at the top of the list here, this is a version restored from a particular date. But if I click this version, that's where you will recognize the version from immediately after that change I made right at the beginning of this video. So it is brilliantly useful. But it really comes into its own, I think, when multiple people are working on this spreadsheet, because it means we can see who made what changes when. And so we will see this again when we talk about collaboration.

0:00[MUSIC PLAYING] Just to finish off this introductory skill on Google Sheets, I'd like to share with you some of my favorite quick tips. Now you already know that we can use the Paint format button to copy formatting from one place to another. And I know you already know that. So that's not the tip, but it's setting me up for something that I want to show you later. But let's just move to the next sheet along. So you can see down the bottom here I've got three sheets, January, February, March. Of course, I can click.

0:31But if you like a keyboard shortcut, then you can use the Alt key with the up and down arrows to navigate between your sheets. So I do like that one. But anyway, here we are on the February sheet, and I need to add in some figures. Now, of course, you could just type in the numbers and press Enter and keep going and then when you get to the end of that column, go up to the top of the next column with a click and then carry on typing. But actually, more efficient is to select the block of cells first and look what happens. As I type and press Enter and I just type, type, press Enter. When I press Enter again at the end of the column, it automatically takes me up to the top of the next column.

1:06So that is much more efficient. If you prefer to use your Tab key to Enter in data, then you can do it that way if you prefer. So select the data first, type the number then, Tab, Tab, Tab and that'll take you through each other at a time and then onto the next row. But then I notice that this heading here doesn't match. That's the wrong shade of green. And, of course, I could use that Paint format button that we used a second ago on the other sheet. But actually that formatting will be remembered as the last format I copied, and I can use a keyboard shortcut Control-Alt-V to paste those formats several steps after the event of originally copying them.

1:43And I love that. I quite often will have my favorite format already copied, and then I can just apply it at any time throughout the rest of our session. But the format painter is more than just making things look pretty. So look at this. Do you remember, we created this number format whereby negative numbers showed in red and in brackets. And, of course, that's dynamic. So if I change that to a positive number, then the red goes away and the brackets go away. And you'd expect the same to happen here as well, change that to a positive number. But for some reason, that's not working. So for some reason, who knows what the reason is?

2:14When I change that to a positive number, it still seems to think that it's red. Now rather than trying to work out what's wrong with the number formatting of that cell, well, I have to say it is much more efficient just to click on a cell that is behaving and use that Paint format button to reset the misbehaving cell. So now if I make it a minus number, that's going red and in brackets. Back to being a positive number, it's behaving as I expect. So great for troubleshooting. Let me just dive onto the March sheet here. And let's imagine that all of these cells, well, it's just estimates.

2:46I don't have figures yet, so I want the 100 in every cell. Well, I can just press Control-Enter on my keyboard to put the value that was already there into every cell in the selection. And actually this is really useful for testing your formulas, just putting in some dummy data something easy like tens or hundreds just to quickly see if your formulas are working properly. But for my next trick, I'm going to take you into this document. And you can see I've got a list of part numbers. And they're a bit fiddly to type so I think, brilliant, I'm going to copy those from this document and put them into Google Sheets.

3:17It's going to be a bit annoying, isn't it? Because I really want each part number to be in a separate cell, but we'll see how it goes. I'm going to press Control-C on the keyboard to copy those. And then in this blank worksheet I've got ready to go just here, I'm going to paste it. But when I choose Paste, you'll see it looks like it's just going to put all of those into a single cell. That'll need some tidying up. But look, I've got this button here that will say, Oh, did you need to split that into columns. And if I choose that, look at what it has done. It has put each of those part numbers into a separate cell.

3:50That is fantastic that has saved me a lot of time. So I got that option from the little button that popped up when I pasted, but you can also see that down here, Split text to columns in the Data menu. But anyway, I'm quite pleased with that. But then I think, I really wanted that list of part numbers to go vertically not horizontally. But we can transpose this. So if you select the cells and then copy them and then when you paste, if you go to the Edit menu and choose Paste special, I've got this option here to paste what I had just copied transposed.

4:22So that is what I was after, the list apart numbers vertically in a list there. Perfect. So now let's imagine I'm capturing data for all of these part numbers for the 15th of each month. So I'm just going to put in 15th of Jan 2021, 15th of Feb 2021. I don't want to carry that on for the rest of the year. But these spreadsheet applications, they are very clever. They are very good at spotting patterns. So if you give them the first two steps of a pattern and then use this little magic blue square here, this is the fill handle, your mouse pointer changes to a thin black cross and then just drag, it will continue the pattern for you.

4:57Again, a fantastic time saver. So I already shared with you my favorite keyboard shortcut for hopping between the different sheets in your spreadsheet file. But we've got other ones as well. So Control-Spacebar selects the column or Shift-Spacebar selects the row. But if you do like a keyboard shortcut, then take yourself into the Help menu and you can see a whole great long list of all of the keyboard shortcuts. Because to be honest, if I just share with you my favorite 20 keyboard shortcuts, that's not going to necessarily be the same 20 that you would pick.

5:27So do have a look through this. But one more thing to mention about keyboard shortcuts, so for me with my history of the programs that I've worked on, I expect the F7 function key to start a spellcheck. But it doesn't I also expect the F5 function key to give me the go to dialog box but, of course, that just refreshes the browser page. So again if you've got some of these keyboard shortcuts that you're used to using that don't work, then back into that same option, check this out, Enable compatible spreadsheet shortcuts.

5:59Now I'm not saying that every single shortcut that you used to use will suddenly magically now work. But those two examples that affected me do now work. So if I now do F7 is going to run a spell checker on all of those part numbers, and that has made a big difference to me. Brilliant. And so that concludes this introductory skill on Google Sheets. And I'm thinking that you'll be feeling pretty comfortable finding your way around and entering in data and whatnot. But stay with me because in other skills, we're going to roll up our shirt sleeves and learn about formulas and functions and charts and lists and pivot tables.

6:33We really have only just begun. And so for now, I hope this has been informative for you. And I'd like to thank you for viewing.