Spreadsheet Fundamentals

0:00[MUSIC PLAYING]

0:06This is Simona here, and we're going

0:07to have a lot of fun learning Google Sheets.

0:10And this skill is all about the fundamentals

0:12that you need to get going with the Google spreadsheet

0:15application.

0:17So we will start off by getting you logged in,

0:19and we'll take a quick tour of the interface.

0:22And then we'll jump straight in and create a spreadsheet.

0:24And we'll also look in a bit more detail

0:26at text and cell and number formatting.

0:29And we'll see how you can manipulate rows and columns

0:31to get the spreadsheet structure that you need.

0:33Maybe you want to hide or group or freeze a row or column

0:35or add extra sheets, for example.

0:37We'll see how you rename and move and delete files and see

0:41the file version history.

0:42And then we'll finish off with some of my favorite shortcuts.

0:45But note, there are other skills to follow where

0:48we learn in more detail about formulas and functions

0:50and charts and lists and pivot tables, all that fine stuff.

0:54This is just the fundamentals to get you started.

0:57And if you're already using Google Sheets,

1:00then you may well already be familiar with much of this.

1:02I mean, you could certainly skip this first video, for example.

1:05But if, I don't know, say you've been an Excel

1:07user for the past 15 years and you're

1:09thinking to yourself, oh my goodness, what

1:11is this crazy, weird world of Google,

1:14or maybe you've just never really ventured

1:16too far into the world of spreadsheets,

1:18then this is the skill to get you started with Google Sheets.

0:00[MUSIC PLAYING]

0:06Before we get into the detail of creating a spreadsheet

0:09and seeing what you could do in the wonderful world of Google

0:12Sheets, let's just take a few moments to get you logged in,

0:15and then we'll take a quick tour of the interface.

0:17Now, throughout all of these videos,

0:19I'm going to be using Google Chrome as my browser.

0:22But it does work well in other browsers as well.

0:24So whichever one you're in, take yourself to sheets.google.com.

0:28And because Google Sheets is an online service, which

0:31is part of its beauty, the first thing it's going to do

0:34is say, ah, well, what account do you want to log in as?

0:36And notice here that I've got two demo Google accounts.

0:40This is my demo personal Google account.

0:42That's Gmail account.

0:43And this is the one that links to the fictitious organization

0:45that I use in my demos.

0:46So that's the one I'm going to choose right here.

0:48As you might expect, it's going to need you to log in.

0:51And happily, it's remembered my password.

0:52So let me just click Next.

0:54Now, that will load the Google Sheets home screen.

0:57And if you look at the top right-hand corner,

0:59this is where you can see what account you are logged in as.

1:02So you'll get sort of quite used to recognizing

1:04the little picture there, your profile picture.

1:07Because if you do use multiple accounts, so for example,

1:10let me now login with my personal Gmail account.

1:13Let me just sign in and click Next and pop in my password.

1:17And that has opened up a separate tab logged

1:20in with my personal account.

1:21You can see it's got a slightly different profile

1:23picture there.

1:24But the reason why I point this out

1:25is because very often when we do have

1:27work accounts and personal accounts,

1:29our work and home life become a bit blurred,

1:31particularly when we are using the same computer,

1:34the same device for both work and personal tasks.

1:37And of course, the Sheets environment

1:39will be connected to the Google Drive associated

1:42with that account.

1:43So this is my personal account.

1:44I could recognize that from the little profile picture

1:46up there.

1:46And you can see the recent spreadsheets

1:48include the travel planner and the house move checklist.

1:51Whereas on that other tab where I

1:53was logged in with my demo work account--

1:55you can see the different profile picture there--

1:57you can see that the recent spreadsheets are different.

1:59I've got the IT audit and the budget proposal

2:01and the regional sales figures.

2:02Those are the work documents.

2:04So be alert to that.

2:05But let me just close down the tab with my personal account

2:08because I'm going to be using a work

2:09environment for these demos.

2:11And I'm going to choose this button here

2:12to create a brand new blank spreadsheet.

2:15So you will notice you've got menus across the top here.

2:18That obviously is going to lead you

2:19to the different functionality of Google Sheets.

2:21And wherever you see this little arrow in a menu option,

2:24that means there are other options to explore.

2:26So these are all the different number formats for example.

2:29And also, we've got buttons on a toolbar.

2:31So this will provide shortcuts to many of the same options

2:33that you will find through the menus.

2:35So for example, here are some of those same number formatting

2:37options.

2:38And again, wherever there's a little arrow on a button,

2:41that will lead you to more options.

2:42So in fact, these are the very same options

2:44we saw a second ago through the menu.

2:46There are some common ones here.

2:48So for example, bold and italics.

2:50And you'll notice when you hover over a button,

2:52it will tell you what that button does.

2:54And also if you like your keyboard shortcuts,

2:56then it will show you that keyboard

2:58shortcut in brackets after the little tool-tip there.

3:01Now, depending on what screen resolution you're running at

3:03or what size window you're using,

3:05you might get a little More button at the end there.

3:08So let me just restore this window

3:09to being a floating window.

3:10You can see there's the More button.

3:12That just gives you the extra buttons

3:13that couldn't fit because of the window size

3:15that you're working on.

3:16But also, if you use this option right

3:18at the end of the toolbar, you can hide the menus.

3:21And again, there's the keyboard shortcut

3:23if you fancy doing that on the keyboard.

3:24But that will give you a little bit more space on the screen.

3:27And that does give you this little

3:28dot, dot, dot More button for the overflow buttons.

3:31And that's because it's giving you

3:33this option here at the left-hand side

3:35to search the menus.

3:37And they are like this.

3:38I mean, we've hidden those menus now

3:40so we don't have them as readily available.

3:41But I can still search the menus using this option.

3:44So if I think, oh, I want to do a spell check.

3:46Where is it?

3:47Just start typing in the function that you want,

3:49and you'll be able to actually run that menu option directly

3:52from that search.

3:53So funnily enough, there are no spelling suggestions

3:55in this blank spreadsheet.

3:57At any time now you can click that little button again

3:59to show the menus.

4:01And I think generally that's how most of us

4:03will work most of the time.

4:04But it's useful to have the option

4:06to get a bit more space on the screen.

4:07And you'll notice that search option has now disappeared.

4:10But you can always find it again from the Help menu.

4:13Looking down the right-hand side of the screen,

4:15you've got the little side panel here,

4:16which you can show or hide and that

4:18will give you quick access to your calendar

4:20or to your notes and keyboard to your tasks and whatnot.

4:23Down at the bottom right, we've got this explore button,

4:26which is quite exciting.

4:27And we will look at that in more detail another time.

4:30But of course, the main body of your screen

4:32is the spreadsheet itself.

4:33So all of those rows and columns, so all of the rows

4:36are headed up by letters.

4:37All of the rows are numbered.

4:39And whichever cell you are clicked on,

4:41well, you will see that cell reference just over here

4:45on this area, which is called the formula bar.

4:48Now, that does become quite important when we do formulas.

4:51But if it's not showing, or you want to for whatever reason

4:53hide it at any time, go to your view menu.

4:56That's so you can turn on or off that formula bar.

4:59So be aware of that.

5:00And did you also notice that you can turn on or off those grid

5:02lines as well if you don't what those

5:04showing for whatever reason.

5:06And then the big green button at the top left-hand corner

5:08takes you back to your Google Sheets home screen, which

5:11is right back where we started.

5:13But I think now we're ready to actually start

5:16entering in some data and create a spreadsheet.

5:18So stay with me.

5:19That's what we'll do in the very next video.

0:00[MUSIC PLAYING]

0:06In this video, I'd like to get you started

0:08with your first Google Sheet.

0:10Think of it as your quick-start video.

0:12We're going to jump right in.

0:13And we'll input some data, and format the headings,

0:15and add rows and columns, and copy some cells.

0:18And we'll even sort rows and add a quick chart.

0:21Wow.

0:22Let's start off by entering in some headings for some data

0:25that I'm capturing.

0:26So we'll imagine it's some kind of sales figures or something--

0:28something generic like that.

0:29It doesn't really matter what it is.

0:31But let's imagine I want to type in a heading into cell B1.

0:34So I click it.

0:35And you can see that I know I'm clicked in cell B1

0:37because it shows it there in the formula bar.

0:39But I can now just start typing.

0:41So let's imagine I'm capturing sales

0:43data for the different stores that we sell our products in.

0:46So the first store is at Manchester Parkside.

0:48And when I finish typing, I could press Enter.

0:52But that will take the selection down to the next cell

0:55underneath-- down into cell B2.

0:57But I don't want to go there because I actually

0:59have another heading I want to type in the next column.

1:01So instead of pressing Enter, I'm going to press my Tab key.

1:05So that's quite efficient when you're using the keyboard

1:07and you're entering your headings--

1:08pressing Tab or Enter depending on whether you're

1:10going downwards or sideways.

1:12But anyway, our next store is in London Oxford Street.

1:15So let me type that in.

1:16And you'll notice a little typo there.

1:18I've got a capital T in the middle of street there.

1:20But I haven't noticed.

1:21We'll come back to that later.

1:22So again, I'm going to press my Tab key to type

1:24my last heading, which is going to be London Heathrow

1:27Terminal 4.

1:28So let me just pop that in.

1:29And you may or may not have spotted,

1:31I've got a spelling mistake there.

1:32But we'll pretend I haven't noticed.

1:33But I have finished typing my headings,

1:36so now I am going to press Enter.

1:38And what will happen is Google Sheets will think,

1:41oh, she wants to type another row of data here.

1:43So normally when you press Enter,

1:45it takes you down into the next row underneath.

1:48But because I had typed a few headings and then pressed

1:51Enter, it says, oh, she wants to type in the next row.

1:53So it's quite clever in anticipating where

1:55I might want to type next.

1:57But actually, before I type in any more data,

1:59I want to sort out my column widths

2:01because we can see that these columns aren't quite

2:03wide enough to display the full text that I had typed in there.

2:06So column B isn't wide enough.

2:07To make it wider, just move your mouse

2:09onto the dividing line between columns

2:11and you get the blue line appearing

2:13in the double-headed arrow.

2:14That means you can click and drag

2:15to stretch that column to whatever width you like.

2:17But, in fact, if you double-click on that same area,

2:20it will autofit it to just the right width.

2:23And actually, you can autofit a whole bunch

2:25of columns all in one go.

2:27So if you select multiple columns

2:28by clicking and dragging on that gray heading area

2:31and then just double-click on any one of those dividing

2:34lines between columns, it will autofit all of those headings.

2:37So that is looking better.

2:39But let's sort out this typo.

2:41I realize I need to correct that capital T to a lower case t.

2:45So I click it.

2:46And I think, well, I need to get my cursor just in there to--

2:49and I can't seem to be able to get my cursor

2:51in the right place to edit it.

2:52So what do I do?

2:54Well, there are two ways to edit a cell.

2:56You can either double-click on the cell

2:58and that then will give you a cursor that you

3:00can position to make your edit.

3:02Or if you click on the cell, you will see the contents up

3:05in that formula bar.

3:07And that means you can, with your cursor,

3:08go in and make the edit in just the right place.

3:11So that is looking better.

3:12But I've also got a spelling mistake--

3:14London Healthrow instead of London Heathrow.

3:17So we do have a spell checker.

3:19If you have a look in your Tools menu

3:21and choose Spelling-- let's run the spell check.

3:23And sure enough, it's suggesting that I

3:25change Healthrow to Heathrow.

3:26And I think, yes, please.

3:28Please go ahead and make that change.

3:29And there are no other spelling errors spotted.

3:32But I suppose before we go too much further,

3:34we should talk about saving this file.

3:37Now, the name of the file is displayed

3:38at the top left-hand corner.

3:40And currently, it says Untitled spreadsheet.

3:42And actually, it wouldn't matter if you had forgotten to save,

3:46which quite often is the case.

3:47You get carried away with your content.

3:48You forget to save.

3:49This is already saving.

3:52And you can tell that if I click this little cloud

3:54thing to see the document status,

3:56it's telling me that all changes are saved to Drive.

3:59But it hasn't yet got a sensible name

4:01because it's just got the default Untitled spreadsheet

4:03name.

4:03So I can click in that to rename it.

4:05So let's just call it Store Figures, something like that,

4:08and press Enter.

4:09And we will talk in more detail later on about where exactly

4:12that file is stored and how you would move it

4:14into a different location.

4:15But from now on, we can forget about it.

4:17That is automatically saving into my Google

4:20Drive, which is brilliant.

4:22But anyway, now let me add some other headings down the side,

4:25maybe for part numbers of the different products

4:27that we sell.

4:27So maybe we've got a part number, something like this.

4:30I'm just entering in a bit of random nonsense

4:31here so you get the idea.

4:33And I was pressing Enter between all of those entries

4:36because I did want the selection to move down to the next cell

4:39underneath.

4:40And at any time if I want to zoom in on my spreadsheet,

4:43well, the default zoom is 100%.

4:45But maybe 125% is better to help me

4:48focus on this part of the sheet that I'm working on here.

4:51But now let's add some formatting.

4:53So no wild surprises for you here.

4:56I can just click and drag in the middle of the cell

4:58and drag across to select the cells I want to format

5:00and, sure enough, click the appropriate buttons up here.

5:03But in order to select all of my headings in one go,

5:06well, I could hold down my Control key

5:08to select these other headings as well.

5:10So that pale blue shading tells me they are all selected.

5:13They're all highlighted.

5:14So let's make them all bold.

5:15And maybe let's choose a different color, maybe

5:18choose a dark green there.

5:20And now that they're bold, well, the text

5:22has become a bit bigger.

5:23So I can just use that little trick

5:24we saw before to select those columns

5:26and then double-click on a dividing line just

5:27to autofit those column widths again.

5:29Perfect.

5:31Right.

5:31Let's add some figures now.

5:33So I'm going to stick with nice easy numbers

5:35so that when we do some adding up,

5:36we can quickly tell that we've got it right.

5:38So 100, 200, 300 for the Manchester Parkside store.

5:42Let me just click right up into the London Oxford Street store

5:44and add in some other figures-- so 20, 30, and 40.

5:47And then London Heathrow--

5:4925, 50, and 75.

5:52Good.

5:53But then I think, ah, I need to add in another column.

5:56We've got another store, the Manchester City Centre

5:58store that I want to add a column for in between these two

6:01columns here.

6:01Well, that's easy to do.

6:03Just click in-- it doesn't really

6:04matter which column-- one of these two columns.

6:06Go to the Insert menu.

6:08And you'll see it says, oh, do you

6:09want to insert a column to the left or to the right

6:11or row above or below where you're clicked?

6:13Well, I want a column to the left of where I'm clicked.

6:16So as I said, that's why it doesn't really

6:18matter which column you had clicked in.

6:20You would just choose the appropriate option

6:21from the Insert menu there.

6:24So I'm going to copy this heading into this one

6:26to save me a bit of typing.

6:27Well, you know the keyboard shortcut Control-C will copy.

6:31And you can tell you've copied because you've

6:32got this little sort of dotted line

6:34around what you've just copied.

6:35Click where you want to paste it and Control-V to paste.

6:39You could right-click to cut, copy, and paste if you prefer.

6:43Or in the Edit menu, you'll see the same options.

6:45But I think those keyboard shortcuts are pretty universal.

6:48And then let's just double-click to make an adjustment to that

6:51heading because that's going to be Manchester City Centre

6:54store.

6:54And yes, that is how we spell center in the UK.

6:57Excellent.

6:58Let me pop in some figures.

7:00And then I think, ah, well, this is looking good.

7:03But I wish that these part numbers were

7:05sorted in alphabetical order.

7:07I just typed them in as I thought of them.

7:09I'd really like the AA45 part number to be first.

7:12So that's a bit annoying.

7:14But as you might imagine, we can sort rows in Google Sheets.

7:17And there's some fantastic things

7:19we can do with our lists.

7:20And you know that we're going to be looking at all of this

7:23in more detail in other videos.

7:24But let's say we want to sort this range, this group of cells

7:28here, by that first column.

7:30So if you go to the Data menu, we've

7:32got quite a few sorting options.

7:34Well, it's not the whole sheet because we don't want

7:35to include the headings there.

7:37I just want to sort the range by that first column.

7:39And I want it to go A to Z. So when I choose that--

7:42perfect.

7:43Did you see the little movement there?

7:45We've now got AA45 at the top of the list.

7:47And that is now sorted alphabetically.

7:49And so now I think we're ready to put in some totals.

7:52So let me just add in a little heading there for total.

7:55So I want to see what our total sales were for the Manchester

7:58Parkside store.

7:59Well, I could do it in my head.

8:02I could get out my calculator and just type a number in here.

8:05But, of course, this is a spreadsheet.

8:06It can do it for you.

8:08And there are a couple of different approaches here.

8:10And we're going to talk about all of this in more depth.

8:13But what I'm going to do is I'm going to use a SUM function.

8:16So I'm going to just select the cells

8:18that I want it to sum for me.

8:19And then over towards the right-hand side

8:21of your toolbar, you've got the Functions button.

8:23And when you click that, right at the top of the list there,

8:26we've got the SUM function.

8:27And when I choose it, it's going to say, ooh,

8:30did you want to sum that lot that you have selected?

8:33So it's shaded it in a peachy color.

8:35I've got this orange dotted line.

8:37That's what it's summing.

8:38It's got it right.

8:39So I'm just going to press Enter to accept that.

8:41And you can see at a glance that is indeed correct.

8:44And because it's a formula, if these figures change--

8:46so let's say that's changed to 500--

8:48then, of course, that total will update

8:51to show me the correct figure.

8:52And I'm so pleased with myself.

8:54I'm going to make that bold and bright red while I'm at it.

8:57Excellent.

8:58But, of course, I want the same function in these other cells

9:00here to add up the totals for the other stores.

9:03So I'm going to copy that.

9:04So let me just press Control-C to copy that cell.

9:07I can see the little blue dotted lines around there

9:09to indicate that's what's been copied.

9:11And I'm going to select all of these other cells

9:14and press Control-V to paste it.

9:16And that has done the job.

9:18You can see at a glance that that has added up all

9:20of those different columns.

9:22It's a good job we used easy numbers here.

9:24But I do have a few options for my pasting.

9:27So I could say, no, I only want to paste the value.

9:30That would give me 900 as a value in all of those cells

9:34because that was the value of the cell that I had copied.

9:36Or I could just paste the formats, i.e., the red

9:39and bold formatting.

9:40But I'm just going to press Escape

9:41to get rid of those options because what

9:43it did first time was exactly what I wanted.

9:46And at any time if you make a mistake or something

9:48unexpected happens, then don't forget

9:51you've got an Undo button.

9:52So the very first button on the toolbar, if you

9:55click that, it'll undo, undo.

9:57You can step back.

9:58And if you step back a bit far, then you

10:00can redo to step forward in time again as well.

10:03And that makes it brilliant for experimenting,

10:05especially, I think, with a spreadsheet

10:07when you're just trying something out.

10:08You're trying out a new function or something.

10:10You're not sure if it's going to give you the result you expect.

10:12You can try it and undo if it doesn't give you

10:14what you expected.

10:16But just to finish off with then,

10:17let's just pop in a quick chart.

10:19And I only want to put in a chart for my Manchester stores.

10:23Now, notice I have not selected the totals

10:26because those figures will be too big

10:27and they will distort the chart.

10:29But I have selected the titles because I want it appropriately

10:32labeled on the chart.

10:33And I go to the Insert menu, choose Chart.

10:36Let me just resize that so I can see what's going on.

10:40I can delete that heading if I don't want it.

10:42Superb.

10:43So wow.

10:44There is more to learn about all of these things.

10:47But I hope that's given you a good taster before we dive

10:50deeper and explore further.

0:06I think you're going to be surprised at just how

0:08much we've got to cover here because it's

0:10more than just changing the font style or the color

0:13or the alignment of something that you've typed into a cell.

0:16That's what I would call text formatting.

0:18But I feel obliged to put it in quotes because, of course,

0:20you can also apply that kind of formatting to numbers, too.

0:23But we can also change the appearance of the cell,

0:25you know, it's shading or its border.

0:27And also, the appearance of numbers,

0:29whether they show with a currency symbol

0:31or how many decimal places, things like that.

0:34So let's see how many of those formatting examples

0:36we can cram into this single spreadsheet here

0:39I'm just going to get rid of that chart,

0:40so we can focus on the formatting these cells here.

0:43And obviously, you already know that you

0:45can apply basic formatting using the buttons up here.

0:47So let's just reset that red color back to the default.

0:50And you might notice that the underlying button is

0:53the only one that's missing from the toolbar,

0:55but you can get to it from the Format menu or, of course,

0:57the universal people shortcut, Control U.

1:00In terms of fonts, well, you've got

1:01the dropdown here for fonts.

1:03But let me just select the whole sheet

1:04by clicking this area at the top left hand

1:06corner, the intersection between the row and the column

1:09headings, to select all of it because I want the whole sheet

1:11to be a particular font style.

1:13And you can see that the default is from the Theme.

1:16Now we're going to talk about themes another time.

1:19But if I choose more fonts, this is

1:21where I can pick from the vast array of Google Fonts.

1:24So, let's imagine in my organization,

1:26we always tend to use Raleway.

1:27So when I choose that, that's now

1:29going to appear as one of My fonts.

1:32You can filter the list here.

1:33You could show particular types of fonts.

1:35So, just Handwriting fonts, for example.

1:37So, I do like Architects Daughter.

1:38So I'm going to add that as My fonts as well.

1:40Let me tick OK to that.

1:42And now, what you'll see on the Font dropdown list,

1:45those fonts that I've just chosen are available.

1:47So let me choose Raleway.

1:49There it is.

1:50And if I want to make a cell match

1:52another cell in its appearance, in its formatting,

1:55well, we can Paint formats.

1:56So you click on the thing that's got

1:58the formatting that you like.

1:59And then, you click the Paint roller button just

2:01here, the Paint format button.

2:02That sucks up the formatting of what you're clicked on.

2:05And then, you click where you want

2:06to spit out that formatting so onto the total cell there.

2:09So that's a great way of making sure that things match.

2:11But let's talk a little bit about Alignment and Text

2:14wrapping of these headings here.

2:15So, if I adjust the column width here and decide that I really

2:18want this text to wrap in this cell,

2:21well, I do have some text wrapping options.

2:23So, take yourself over towards the right hand

2:25side of the toolbar.

2:26There's my Text wrapping.

2:27And I can choose that middle option there to wrap.

2:30And I can also make this row a bit deeper.

2:33And that means, I might want to consider my alignment.

2:36So, the usual alignment, the horizontal alignment,

2:38kind of does the side-to-side centering.

2:41But if I want it vertically aligned within that cell, well,

2:44I can do that, too.

2:45You can see that it's currently sitting

2:46at the bottom of the cell, but let's put it

2:48in the middle of the cell.

2:49Perfect.

2:50And then again, I could use that little Paint format's button

2:53to apply that same formatting to these other cells.

2:56And if I select all of those columns

2:58and just adjust the widths, I know

3:00that they're all going to be the same width as each other

3:02and the wrapping has been applied to all of those cells.

3:05Now, I didn't do it for this last one

3:07because there's just one more option I want to show you

3:09with regards to text wrappings.

3:10Let me just make this column a bit narrower.

3:13And you can see that the default is

3:14that the text kind of overflows out of the cell

3:17if it can't quite fit.

3:18But the other option that we've got is this one.

3:21Clip.

3:22And when I choose that, it means that it doesn't

3:24overflow into the next cell.

3:26But it is still there.

3:27If you look up in the formula bar, it's still there.

3:29It's just not kind of hanging out.

3:30But let me just use the Paint format button

3:32to make it match all of the others.

3:34But another thing to mention here

3:35is sometimes when you do text wrap,

3:37sometimes the text doesn't wrap the way you want it to.

3:39It doesn't break at the point where

3:40you'd like it to have broken.

3:41So for example, for this cell here, I really

3:44want it to wrap where Oxford starts.

3:46So, if I go into that cell to edit it,

3:48then I can press Control Enter.

3:50Or if you're an Excel user, then you

3:52might be familiar with using Alt Enter.

3:54Well, that would work, too, but that will force

3:56a break for the text wrapping.

3:58And let's imagine, with these headings here, well,

4:00maybe I want them to tip jaunty angle.

4:02So you can see just past the Text wrapping options,

4:04I've got Text rotation.

4:06So I could choose-- oh, let's choose the Tilt up option.

4:09Very nice.

4:10And this is where I really do love the Undo button

4:12because you can play around with all sorts of different options

4:14and just undo if it wasn't quite what you had in mind.

4:17I'm just going to add another row here.

4:18So let me go to the Insert menu and choose Row

4:20above because let's imagine, I want to add a title.

4:23So these are my January figures, something like that.

4:27I'm going to make this bold, and I'm

4:29going to make it a bigger font size.

4:31So, let's choose 18, or that's not quite big enough at all.

4:33But 24 is a bit too big.

4:35So you might think, well, how do I get

4:37a number in between 18 and 24.

4:38Well, you can just type in a particular number.

4:41So let's say, I want it point size 20.

4:43That's not on the list, but I can type it in.

4:45And that's perfect.

4:46If I want that headings centered across these cells here, well,

4:50I have to merge these cells first.

4:53So I can select all of those cells

4:54and then choose this option to merge them.

4:57And when I choose that, that means

4:58I can now center it across all of those cells

5:01that I've now merged into one great big cell.

5:03And again, let's use the Vertical alignment pattern

5:06to make sure that's in the middle of that cell.

5:08Oh, very smart.

5:10I want to apply some borders and shading to these cells now.

5:13But for maximum effect, I am going

5:15to add in some blank rows.

5:17So, I'm just right clicking on the row and column headings

5:19here to just insert some blank space around the edge.

5:22You'll see why when we have it all finished with its borders

5:25and shading in all its glory.

5:26And I'm going to make those extra ones

5:27I've added a bit narrower.

5:28This is going to give me a bit of white space

5:30around the edge when I've done my borders and shading.

5:33So let's start off with the background shading.

5:34So obviously, select the cells and the button

5:36you're after is the little paint can here.

5:38You've got a million colors to choose from, even custom ones.

5:42These are the theme colors.

5:43As I said, we'll talk more about themes another time.

5:45But I'm going to choose-- oh, let's

5:46choose this pale green here.

5:48For borders, well, that's the next button along.

5:51Let's choose my border color.

5:52I'm going to choose a dark green.

5:54The Border style, I'm going to choose

5:56a slightly thicker border.

5:57And then, where I want to put it.

5:58And I want it around the outside of what I have selected.

6:01So let me choose that.

6:02Click away.

6:03Oh, that's looking good.

6:04But I now want a heavy line to separate out the totals

6:07from the other figures.

6:08So, let me select just those cells this time.

6:11Back to the Borders button.

6:12And this time say, what I'd like a top border of what

6:15I have selected.

6:16And very often, when you add your own borders and shading,

6:20that might mean you don't need the default grid lines anymore.

6:23Particularly, if you want to take a little screen

6:25snip of this for some other use or maybe you

6:27want to present your spreadsheet in a Google

6:29Hangout or something, well, you can turn off your grid lines,

6:31remember.

6:32So let me just turn them off from the View menu,

6:34so you can see that beautifully formatted spreadsheet in all

6:36its glory.

6:38But now let's talk about Number formatting.

6:40So these cells here are obviously numbers.

6:43How do I want those figures displayed?

6:44Well, they're all sales.

6:46So, it is a currency.

6:47And if you look up at this area of your toolbar,

6:50that's where you'll see your Number formatting

6:52shortcuts including this one, Format as currency.

6:54And you'll notice that that's got the UK pound sign there.

6:58That's because in file spreadsheet settings down

7:01here--

7:02that's because I've got the UK set as the locale

7:04for this spreadsheet.

7:05But obviously, you could change that if you want the default

7:08to be something else.

7:09But you can override it and use different currencies

7:11at any time if you need to.

7:12I'll show you that in a second.

7:13But let's imagine, that's great.

7:14Let's put the little UK pound signs on.

7:16But I don't want the two decimal places

7:18because I've rounded everything up to the nearest five pounds

7:21anyway.

7:21So look at these buttons here.

7:23I can decrease or increase decimal places.

7:25Let's just decrease the decimal places.

7:27That's perfect.

7:28But now, let's imagine, I don't want the little pound signs

7:31anymore.

7:32Now let's imagine, I think, oh, no,

7:33I don't want that currency formatting anymore.

7:35So, you might think to press that button again

7:37to turn it off.

7:39But actually, what that does it just

7:40reapplies the currency formatting number style.

7:43So these buttons are not on and off, like Bold would be,

7:46for example.

7:47So bear that in mind.

7:48So if you wanted to make a change

7:50and get rid of a currency or a percentage format

7:52or whatever that you had applied, click this button here

7:55to see more formats.

7:57And then say, please just give me automatic formatting.

7:59And that puts it back to how it was.

8:01But you will have also noticed there

8:03were all sorts of other exciting options to explore here.

8:05So let me choose Financial.

8:07I quite like this because it shows your negative numbers

8:09in brackets.

8:10And again, I can get rid of the decimal places if I want to.

8:13But let's just put a negative number in here

8:15just to check that that works.

8:16So let's make that minus 100.

8:19And when I press Enter, you can see

8:21that that 100 is displayed as 100 in brackets.

8:23But up in the Formula bar, it's still actually showing

8:26as minus 100 as the value.

8:28But even more excitingly than that,

8:30we can create Custom number formats.

8:33So a common question that I get asked

8:35when I teach people how to use spreadsheets, whether it's

8:37Google Sheets or Microsoft Excel, is OK,

8:40well, I really want that negative number.

8:42Yeah, great, it's in brackets.

8:43That's what I want.

8:44But I want it to show in red as well.

8:46And one way of handling that is with a Custom number format.

8:49So there are all sorts of other formats to choose from.

8:52But right down the bottom, if you choose More formats,

8:55that's what you can choose a different currency

8:57other than the default that appeared on your button

8:59as per your locale set for your spreadsheet environment.

9:03But what I want is a custom number format to say,

9:06well, I want my negative numbers to show in red.

9:08Now, there's all sorts of different things

9:11to play with here.

9:12But what I tend to do is just fiddle up here

9:16to change what it's currently set out

9:18with my own little tweak.

9:19And the way these codes work is the first bit there,

9:22that's how a positive number is going to be displayed.

9:25Then after the semicolon, that's how a negative number

9:27is going to be displayed.

9:28So at the moment, it's going to be displayed in brackets

9:30with no decimal places.

9:32Well, just at the beginning, I can just

9:34type in in square brackets the color I want it to appear as.

9:37So if I just pop that in there, you can see the little preview.

9:41That's how it's going to appear.

9:42Fantastic.

9:43And if you are interested in these Custom number formats,

9:46then I do urge you to check out the article

9:48here, the Help article which explains what all

9:50these funny little codes mean.

9:52But let me just apply that as an example.

9:54You can now see that's in red.

9:56And of course, that's dynamic.

9:58So if I make another of these numbers negative,

10:00then, of course, that formatting is going

10:02to apply where appropriate.

10:04And notice, if I go back to those Number formatting

10:07options, that custom format that I just created

10:10is appearing on the list there for quick use

10:12again another time.

10:13And then finally, while we are talking about formatting, if I

10:17click that little button at the top left again

10:18to select the whole spreadsheet and go back to my Format menu,

10:22but this time choose Clear formatting, well,

10:24that has cleared all of my formatting except

10:27for the Number formatting, you will notice.

10:30But oh, that's rather disappointing.

10:32So let me just go back to undo, to put it back

10:34to that beautifully formatted spreadsheet that I had created.

10:37So there we have it.

10:38Examples of Text formatting, Cell formatting,

10:41and Number formatting.

10:43Your spreadsheets will never look the same again.

0:00[MUSIC PLAYING]

0:06We already know how to add rows and columns,

0:08but there are a few other things we

0:10might want to do with regards to the structure

0:12of our spreadsheet such as moving rows or columns around

0:15or hiding or grouping or freezing rows and columns,

0:18adding new tabs for extra sheets,

0:20all of that kind of thing.

0:22Now I'm a big fan of using the right click

0:24menu to insert extra rows and columns and whatnot,

0:26and that's what we've done before.

0:27But actually look, there's a little dropdown here,

0:29which you can click which gives you the same menu.

0:32And I think it's worth just a quick mention

0:33about the difference between clearing a column

0:35and deleting a column.

0:36So when you clear a column, if it's not obvious to you,

0:38that just deletes the contents of the column

0:40and you could have done that just

0:42by pressing the Delete key on your keyboard.

0:44Whereas if you delete the column,

0:45that will take out the whole actual column,

0:47including the contents.

0:49So, again, let me just undo to get that back again.

0:51But did you also notice that there was a resize column

0:55on this menu as well?

0:56Now when we resized columns before, what we did,

0:59we collect and drag to select these three columns and then

1:02just adjusted it with the little dividing line there

1:04and that adjusted all three of those selected columns

1:07to make them the same.

1:07And we just did it by eye, didn't we?

1:09So we already know that those three columns

1:11are the same width.

1:12But what if I want all four to be the same?

1:14Well, I could do that little trick

1:16or if I knew a particular measurement

1:18that I wanted those columns to be,

1:20then I can choose this Resize columns C to F

1:23because those are the columns I've got selected,

1:25and it measures it in pixels.

1:26So let's say I happen to know that I

1:28want those columns to be 110 pixels wide, I can click OK.

1:31And you probably saw a bit of movement

1:33there as they all jiggled to their new size.

1:36Another option that's quite useful

1:37is this one, Hide the column.

1:39So when you choose that, obviously, the column

1:41is hidden.

1:41So it's not properly deleted.

1:42It's just hidden.

1:44And you can see this little arrow thing here.

1:46If you click that, it will just spring back again.

1:48And if you want to hide it again,

1:50you would have to call up that menu again or right click

1:53to hide it again.

1:54So this can be useful.

1:55I mean, let's imagine you're going

1:56to present your spreadsheet over a Google Hangout,

1:59or a Google Meet or something and you just

2:01want to tidy it up a bit to hide some irrelevant data before you

2:04show it to your colleagues when you're sharing your screen.

2:07But if you find yourself regularly hiding and then

2:09unhiding again, then consider using grouping.

2:13So if I choose to group that column, what that does it,

2:16gives me this little button here.

2:17I can click that to effectively hide

2:19it and do what I need to do.

2:20And at any time I can show it again and then hide it again.

2:22So that's a much better ON/OFF type of setup

2:25which might be useful.

2:25But anyway, let me just ungroup that column

2:28to put it back to how it was.

2:29But let's have a quick word about moving things around.

2:32Now you have to be quite alert to the shape of your mouse

2:35point to here.

2:36So if I click on a cell or click on a column heading

2:38or click anywhere, notice that my mouse pointer

2:41looks like a white arrow.

2:43Now that white arrow is your selection arrow.

2:46That's what you need when you're clicking and dragging

2:48to select something.

2:50But as soon as that mouse pointer changes

2:52to a little hand, if I move to the edge of the cell, can

2:54you see it changes to hand and then

2:56I click and it goes to a clenched fist.

2:59It's got hold of that cell, then I

3:00can click and drag to move that cell around.

3:03So let me again just undo that.

3:04But this is interesting when it comes to columns.

3:06So let's imagine you select that column and then realize

3:09actually you want to select all three columns

3:11and then you click and drag and realize,

3:13I've got a little hand.

3:14I'm moving it around.

3:15So if you want to select multiple columns,

3:17you have to it all in one go while the mouse

3:19pointer is that white arrow.

3:21But if you do want to move a row or column then click once,

3:25let go.

3:25And then when you get the little hand,

3:27you can click and drag to move that column around.

3:29And it's the same with rows.

3:30So I could click and drag to move this row

3:31into a different position.

3:33Or this row click to select and then click and drag,

3:35and you can see a heavy gray line which

3:37indicates where that rows going to end up when you let

3:39go of the mouse.

3:41But notice I didn't actually do it for the column.

3:44But if I show you what happens if I try and move this column,

3:47it then says, Oh, sorry, you can't

3:49do that because it's got its knickers in a twist,

3:51because of that merged cell that I

3:53had that spanned those columns.

3:55So this does sometimes causes a problem with these merged

3:58cells, so I would recommend if you are using merge cells

4:01to make a nice heading like this that should

4:03be the last thing you do.

4:05While you're still manipulating or structure the spreadsheet

4:08while you're still working with your data,

4:09then it's probably best to avoid merge cells.

4:12Now let me just scroll to the very end of this spreadsheet.

4:15And you will see, if I go right down to the very end here,

4:18it's got 1,002 rows.

4:19Now the default is for new sheets,

4:21it'll give you 1,000 rows and you

4:23can see I can add extra rows if I need to.

4:25This particular sheet got 1,002 because I've

4:27been fiddling with it.

4:28But actually the number of rows that you can add is unlimited.

4:32The limit is actually at the time

4:33of recording in the number of cells

4:35that you can have is limited to five million cells

4:38in a Google Sheet.

4:40And there's an 18,000 and something

4:42limit in the number of columns that you can have.

4:44So for most us, for most day to day use of spreadsheets,

4:46that is probably enough.

4:48But anyway, I am going to add some more rows.

4:51So I'm going to click and drag to select those three rows

4:53and I'm going to right click because that's my favorite.

4:55And look, because I selected three rows,

4:58I get the opportunity to insert three more rows.

5:01So I'm going to add quite a few.

5:02And in fact, I'm going to use the redo button here just

5:05to do that.

5:06again, and again.

5:07Because let's imagine I'm going to add

5:08in lots more part numbers here and lots more figures.

5:11So that means as I scroll through this particular list,

5:15I'm going to want these headings here to be frozen.

5:17I want them to stay fixed on the screen,

5:19so I know which store I'm looking

5:20at when I'm filling in figures here or here.

5:23I don't have to just remember what those headings were.

5:25So the trick is click on the cell

5:28that you want everything up to that point to be frozen

5:30or down to that point to be frozen.

5:32So I basically want the top three rows to stay

5:35fixed on the screen, don't I?

5:37So if you go to the View menu and choose Freeze,

5:40well it's already got a default option of freeze

5:42the top row or the top two rows or because I'm

5:45clicked on row three, it says OK how about up

5:47to the current row, which is row three.

5:49So that's what I want.

5:50So if I click that, and now I'm just

5:52rolling the wheel on my mouse.

5:54If I scroll down, you'll see those headings

5:56stay fixed so I can enter in the different figures

5:58for the different stores for those other part numbers.

6:00To unfreeze, well, go back to the Freeze option

6:03again and choose No rows.

6:06But again if I wanted to freeze, for example, the first two

6:08columns maybe I was adding lots of extra stores

6:11and I wanted to scroll sideways and keep columns A and B fixed.

6:15Well, look what happens this time.

6:16If to go View and choose Freeze and choose well the first two

6:19columns please, it says sorry, can't do it

6:22because of that wretched merge cell again.

6:24So another good reason not to include merge cells

6:26while you're still manipulating and working

6:28with your data and the worksheet structure.

6:30But also notice down the bottom here,

6:33this is just sheet1 of my Google Sheets file.

6:36I can add extra sheets.

6:37So just to the left there, I can click on that plus

6:39to add a new sheet.

6:41So it's a new blank sheet, and I can switch between them.

6:43To delete a sheet, well, you can either right click or use

6:46a little arrow there to display the menu

6:48and delete it because actually what

6:50I want to do for this particular example,

6:53I want to create a copy of that sheet

6:54because it's already got a structure and the formatting

6:57that I like and then I'm going to over type

6:58it with February's figures.

7:00So on that menu, you might be drawn to the Copy

7:03to option, copy to a new spreadsheet or an existing

7:06spreadsheet.

7:06But to create a copy within the same file

7:09is actually the duplicate option that you want.

7:11Let me choose that, and it says Copy of Sheet1.

7:14That's exactly the same.

7:15I've got two the same.

7:16So before I confuse myself, let me just

7:18change this to February, press Enter and just delete

7:21January's figures.

7:22So that's already to overtype with the new figures and then,

7:25of course, I can rename these sheets,

7:27so that copy needs to be called Feb.

7:30And the original one, of course, we'll call Jan.

7:32You get the idea.

7:34We can change the order of these sheets.

7:35So I can just click and drag the little tab

7:37to change the order, to put Feb at the beginning

7:39but that will feel a bit weird.

7:40So I'm going to put it back to how it was.

7:42And you can see you've got a few other options here

7:44with regards to hiding the sheet for example.

7:47And again, this can be useful if you've

7:48got quite a complex spreadsheet maybe

7:50with one of these tabs showing lots of background data

7:53that you don't want other people meddling with

7:55and it's quite useful to hide a sheet,

7:57so it makes it a bit harder for people

7:59to go and explore and break things.

8:00And I doubt if you saw the message that

8:02popped up briefly at the bottom right hand

8:04corner that said your hidden cheat is now

8:06viewable from the View menu.

8:07So if you go back to View, you can

8:09see there is one hidden sheet in this particular file.

8:12And that's how we can display it again.

8:14And also you'll see I can change the color of the sheets.

8:16So let's make this, for example, blue

8:18that gives a little colored line there.

8:21And also we can protect the sheet.

8:23And we'll talk more about protecting sheets and setting

8:25permissions for who can edit what

8:27when we cover collaboration.

8:29But for now, we've covered quite a few options

8:32when it comes to working with the structure

8:33of your spreadsheet file.

0:00[MUSIC PLAYING]

0:06We've already seen that your files are automatically

0:08saved into your Google Drive, which is brilliant.

0:11You really don't have to think about it.

0:13But in this video, let's take a look at things

0:15like moving files, copying or deleting or renaming them,

0:18even the version history--

0:20all of that kind of thing.

0:21I'm just going to start off by making a quick change

0:23to this file because that is going to become interesting

0:26when we do look at the version history

0:27at the end of this video.

0:28So let me just delete some rows there.

0:30And you will remember that this has always

0:32been saved to our drive.

0:33And we gave it a name right back towards the beginning

0:35when we created this document of Store Figures.

0:38But we don't have to have given it a name.

0:39It would have still been saving, just under the default

0:42name of Untitled spreadsheet.

0:43But at any time, you'll see it's prompting we can rename it.

0:46So I can change it to something else.

0:48So let's call it UK Store Figures,

0:50something like that, and just press Enter.

0:52But then I think, well, whereabouts is it stored?

0:55And this button here will invite me

0:57to look at my different folders I've got on my drive

0:59and put it into a particular folder if I want to.

1:01So I can see all of my drive folders there.

1:04And I look at those and I think, hmm, I haven't really

1:06got anything for my sales data.

1:08But I can create a new folder on Drive from here.

1:11So if I do that and say, well, yes, let's

1:13have a special folder full of my sales stuff.

1:15So sales data, yes, please.

1:17And let's move this file to here.

1:19Perfect.

1:20And there are quite a few interesting options

1:22on the File menu regarding, funnily enough, file

1:25management.

1:26So, for example, I could go to New

1:27and ask for a brand new spreadsheet.

1:29That would open up in a brand new tab.

1:31Or I could click Open.

1:32That'll give me the file picker here.

1:34And, again, that would open up in a new tab.

1:36Back to File, what else have we got?

1:39We'll look at importing and downloading

1:41to different file formats when we

1:42talk about working with other people.

1:44But look at this--

1:45Make a copy.

1:45So this is quite nice.

1:46And I think if you're a Microsoft Office user,

1:48you'll be used to seeing Save As here.

1:51So this is if I want to create another copy of this file.

1:53So the default name is Copy of UK Store Figures.

1:56But I think, well, no, I want to use this structure to capture

1:59the sales figures for France.

2:00So I'm just going to give that a more sensible name,

2:02choose the location.

2:04Yes, I'll keep it in that same place.

2:06And click OK.

2:07That opens up on a separate tab.

2:09So, of course, I could just overtype

2:10all of that information with the relevant data.

2:12But let me just go back to the UK Store Figures

2:14because that's the one I'm working on.

2:16You'll see that I can also star the file.

2:18So if I just choose that, that'll

2:20give me quick access to it from my drive.

2:22In fact, let me just pop into Google Drive,

2:24and we'll see that.

2:25So if I go to Starred items down the left-hand side here,

2:28that's where you can see that UK Store Figures

2:30that I had just starred, but also that folder we created.

2:33There we go-- Sales Data.

2:34And if I have a look in there, those

2:35are the two files that we would expect to see in there.

2:38So-- perfect.

2:39All works really nicely.

2:40If you go back to the Google Sheets home page, though,

2:43you'll remember this gives you access

2:45to your recently used files and your templates and whatnot.

2:48But also, if you're anything like me,

2:49you will have quite a lot of these Untitled spreadsheets

2:52floating around.

2:53And if you click the More button,

2:55you can deal with these.

2:56So you could rename it if you knew

2:58what it was, and you thought, well, I should've given it

2:59a more sensible name.

3:00Or if you know you were just messing around

3:02and you didn't need to keep it, you could remove it.

3:04So let me get rid of that one.

3:05But sometimes there might be an Untitled spreadsheet.

3:08You think, well, what on Earth was that?

3:10So let me open up this particular one.

3:12And I'm looking at it--

3:13I realize, ah, no, I don't need that.

3:15So you can delete files from here.

3:17So if I choose Move to bin, that'll get rid of it

3:20and take me back to the Sheets home screen.

3:22There are a few other options on here

3:24as well, for example, making available offline.

3:26We'll talk about that another time.

3:28And notice that you can control how these recent files are

3:31displayed, either as a list, how they're sorted,

3:34or go into the file picker.

3:36But my favorite thing of all-- let's go back into this UK

3:39Store Figures--

3:40is the version history.

3:41So you remember we were working on this particular file

3:44over the last couple of videos.

3:45And I did indeed make a change right

3:47at the beginning of this video.

3:48And look up the top here.

3:49It's telling us that the last edits to this file

3:52was made five minutes ago.

3:53And if I click that, it opens up the version history

3:56down the right-hand side here.

3:58And this is brilliant.

3:59I love this.

4:00So let's just click, for example, on yesterday's version

4:02of this file.

4:03Sure enough, you can see all of those extra rows

4:05that we added that we deleted at the beginning of this video.

4:08And if you're wondering why the shading looks a bit peculiar--

4:11you're thinking, well, I'm sure that should have been green

4:12shading--

4:13it's because changes are shown shaded.

4:15So down the bottom here where it says Show changes,

4:18if you uncheck that, then the file

4:20will probably look more as you expected.

4:22That becomes really useful when you're

4:23looking at changes made by other people.

4:25But for us in this spreadsheet where we did have cell shading,

4:27it did look a bit peculiar.

4:28But anyway, if I just scroll through some

4:29of these other different versions here,

4:31you can see the different iterations of this document.

4:33And if you click the little triangle here,

4:35it'll expand to show the detailed versions.

4:37So while we were playing around, learning

4:39about how to use Google Sheets, we

4:41had all these different versions.

4:42They were all collapsed into these more major versions.

4:45But you can drill down and see any of these.

4:47And you'll notice you can also name any version.

4:50So let's say I look at this one.

4:51I think, ah, yes, now that was the original data.

4:54That's going to be useful.

4:55So I'm going to rename that version.

4:57And that's going to make it easy to identify another time.

4:59But also, you can restore a version.

5:02So let's say I look at this particular version,

5:04and I think, ah, yes, I want that to be

5:07the most recent version.

5:08So I've got this little More actions button here.

5:10And if I choose Restore this version and say,

5:13yes, I'm happy to do that, that basically puts that version

5:16right at the top of the list.

5:18That becomes the current version.

5:19But it does not mean that all of those other later versions

5:22are lost.

5:23So if I just go back into that version history,

5:25you can see at the top of the list here,

5:27this is a version restored from a particular date.

5:30But if I click this version, that's

5:32where you will recognize the version from immediately

5:35after that change I made right at the beginning of this video.

5:38So it is brilliantly useful.

5:40But it really comes into its own,

5:42I think, when multiple people are working

5:43on this spreadsheet, because it means

5:45we can see who made what changes when.

5:47And so we will see this again when

5:48we talk about collaboration.

0:00[MUSIC PLAYING]

0:06Just to finish off this introductory skill on Google

0:09Sheets, I'd like to share with you

0:11some of my favorite quick tips.

0:13Now you already know that we can use the Paint format

0:16button to copy formatting from one place to another.

0:19And I know you already know that.

0:20So that's not the tip, but it's setting me up for something

0:23that I want to show you later.

0:24But let's just move to the next sheet along.

0:26So you can see down the bottom here

0:27I've got three sheets, January, February, March.

0:29Of course, I can click.

0:31But if you like a keyboard shortcut,

0:33then you can use the Alt key with the up and down arrows

0:36to navigate between your sheets.

0:37So I do like that one.

0:39But anyway, here we are on the February sheet,

0:41and I need to add in some figures.

0:43Now, of course, you could just type in the numbers and press

0:46Enter and keep going and then when

0:47you get to the end of that column,

0:48go up to the top of the next column with a click

0:50and then carry on typing.

0:52But actually, more efficient is to select the block of cells

0:55first and look what happens.

0:57As I type and press Enter and I just type, type, press Enter.

1:01When I press Enter again at the end of the column,

1:03it automatically takes me up to the top of the next column.

1:06So that is much more efficient.

1:08If you prefer to use your Tab key to Enter in data,

1:10then you can do it that way if you prefer.

1:12So select the data first, type the number then,

1:14Tab, Tab, Tab and that'll take you

1:16through each other at a time and then onto the next row.

1:19But then I notice that this heading here doesn't match.

1:23That's the wrong shade of green.

1:25And, of course, I could use that Paint format button

1:27that we used a second ago on the other sheet.

1:30But actually that formatting will

1:32be remembered as the last format I copied,

1:34and I can use a keyboard shortcut Control-Alt-V

1:38to paste those formats several steps after the event

1:41of originally copying them.

1:43And I love that.

1:43I quite often will have my favorite format already copied,

1:47and then I can just apply it at any time

1:48throughout the rest of our session.

1:50But the format painter is more than just

1:53making things look pretty.

1:54So look at this.

1:55Do you remember, we created this number format

1:57whereby negative numbers showed in red and in brackets.

2:00And, of course, that's dynamic.

2:01So if I change that to a positive number,

2:03then the red goes away and the brackets go away.

2:05And you'd expect the same to happen here as well,

2:07change that to a positive number.

2:09But for some reason, that's not working.

2:12So for some reason, who knows what the reason is?

2:14When I change that to a positive number,

2:16it still seems to think that it's red.

2:18Now rather than trying to work out

2:20what's wrong with the number formatting of that cell, well,

2:23I have to say it is much more efficient just

2:25to click on a cell that is behaving

2:28and use that Paint format button to reset the misbehaving cell.

2:32So now if I make it a minus number,

2:33that's going red and in brackets.

2:35Back to being a positive number, it's behaving as I expect.

2:38So great for troubleshooting.

2:40Let me just dive onto the March sheet here.

2:43And let's imagine that all of these cells,

2:45well, it's just estimates.

2:46I don't have figures yet, so I want the 100 in every cell.

2:49Well, I can just press Control-Enter on my keyboard

2:52to put the value that was already

2:54there into every cell in the selection.

2:56And actually this is really useful for testing

2:58your formulas, just putting in some dummy data

3:01something easy like tens or hundreds

3:03just to quickly see if your formulas are working properly.

3:06But for my next trick, I'm going to take you into this document.

3:09And you can see I've got a list of part numbers.

3:11And they're a bit fiddly to type so I think,

3:13brilliant, I'm going to copy those from this document

3:15and put them into Google Sheets.

3:17It's going to be a bit annoying, isn't it?

3:19Because I really want each part number

3:20to be in a separate cell, but we'll see how it goes.

3:23I'm going to press Control-C on the keyboard to copy those.

3:26And then in this blank worksheet I've got ready to go just here,

3:29I'm going to paste it.

3:30But when I choose Paste, you'll see

3:32it looks like it's just going to put all of those

3:34into a single cell.

3:36That'll need some tidying up.

3:37But look, I've got this button here that will say, Oh,

3:41did you need to split that into columns.

3:43And if I choose that, look at what it has done.

3:46It has put each of those part numbers into a separate cell.

3:50That is fantastic that has saved me a lot of time.

3:53So I got that option from the little button that popped up

3:55when I pasted, but you can also see that down here, Split text

3:59to columns in the Data menu.

4:01But anyway, I'm quite pleased with that.

4:03But then I think, I really wanted

4:04that list of part numbers to go vertically not horizontally.

4:08But we can transpose this.

4:10So if you select the cells and then copy them and then

4:13when you paste, if you go to the Edit menu

4:16and choose Paste special, I've got this option

4:19here to paste what I had just copied transposed.

4:22So that is what I was after, the list apart numbers vertically

4:25in a list there.

4:26Perfect.

4:27So now let's imagine I'm capturing data

4:29for all of these part numbers for the 15th of each month.

4:33So I'm just going to put in 15th of Jan 2021, 15th of Feb 2021.

4:38I don't want to carry that on for the rest of the year.

4:40But these spreadsheet applications,

4:41they are very clever.

4:42They are very good at spotting patterns.

4:45So if you give them the first two steps of a pattern

4:47and then use this little magic blue square here,

4:49this is the fill handle, your mouse pointer

4:51changes to a thin black cross and then just drag,

4:54it will continue the pattern for you.

4:57Again, a fantastic time saver.

5:00So I already shared with you my favorite keyboard shortcut

5:02for hopping between the different sheets

5:04in your spreadsheet file.

5:05But we've got other ones as well.

5:07So Control-Spacebar selects the column or Shift-Spacebar

5:11selects the row.

5:12But if you do like a keyboard shortcut,

5:14then take yourself into the Help menu

5:16and you can see a whole great long list of all

5:18of the keyboard shortcuts.

5:20Because to be honest, if I just share with you my favorite 20

5:23keyboard shortcuts, that's not going

5:24to necessarily be the same 20 that you would pick.

5:27So do have a look through this.

5:29But one more thing to mention about keyboard shortcuts,

5:31so for me with my history of the programs that I've worked on,

5:35I expect the F7 function key to start a spellcheck.

5:40But it doesn't I also expect the F5 function key

5:43to give me the go to dialog box but,

5:45of course, that just refreshes the browser page.

5:48So again if you've got some of these keyboard shortcuts

5:51that you're used to using that don't work,

5:53then back into that same option, check this out,

5:56Enable compatible spreadsheet shortcuts.

5:59Now I'm not saying that every single shortcut

6:01that you used to use will suddenly magically now work.

6:04But those two examples that affected me do now work.

6:08So if I now do F7 is going to run a spell checker on all

6:11of those part numbers, and that has made a big difference

6:13to me.

6:14Brilliant.

6:15And so that concludes this introductory skill

6:17on Google Sheets.

6:18And I'm thinking that you'll be feeling pretty comfortable

6:21finding your way around and entering in data and whatnot.

6:24But stay with me because in other skills,

6:27we're going to roll up our shirt sleeves

6:28and learn about formulas and functions and charts and lists

6:32and pivot tables.

6:33We really have only just begun.

6:35And so for now, I hope this has been informative for you.

6:38And I'd like to thank you for viewing.