Cheat Sheet & Shortcuts

Hi there, my name is Daniel Scott. And this is our Microsoft Excel 2016 cheat sheet. Check out my full Excel course on bringyourownlaptop.com

Before I forget, there's also a printable PDF version that you can print off, and stick next to your desk, and look all awesome. It's on bringyourownlaptop.com/resources

So the first tip from our cheat sheet is to insert a column. I need to add my headers, so I'm going to click  in this top row here, and go 'Control', Shift', and hit '+'. It allows me to put in either the rows or the columns.

Next tip, I'm going to type in 'January', or just 'Jan'. And if I grab the bottom right square, and drag it across, Excel's pretty clever, and knows I mean this string of dates. That works the same for days of the week. If I type in 'Monday', or 'Mon', and I drag it out, it puts in the days of the week as well. Thank you, Excel.

Next cheat is, selecting all these, I want them to be currency because pretty much they're always a currency in Excel. So, 'Control', 'Shift', and hit '4' on your keyboard. And that will put it to your default currency. Mine's set to £s at the moment, yours might be $s.

Next step is, say you want to select the whole bit of data that you've got. Mine's pretty small. Yours might be huge, lots and lots of rows and columns. Just hit 'Control' 'A', and it selects the whole thing. Let's go and set it into a cool little-- under 'Home', along here, where it says 'Format as Table', I'm going to pick one of the pre-defined ones. Click 'OK', and it's going to make it a pretty little table for me.

Next shortcut is 'Control', and then 'Tab' on your keyboard. Normally, just above caps lock, on the left. And I'm going to click that, and it toggles between any open Excel files. So if you've got a couple of thoseyou're copying and pasting from, 'Control' 'Tab' between the two.

Next shortcut is, say the date, you want to enter today's date. Just hit 'Control' ';' and it's put in my exact today's date. So I'm using my 'Control' 'Tab' again to jump to my next open Excel document. We want to do a couple of things. One is, I'm working on this long document, it's pretty long. I can double click thebottom of any cell. It jumps me all the way to the end of that column, but wouldn't it be handy if I had myheader rows along the top here, because I have no idea what maybe this number is while I'm all the way down there. So what we can do is, go to 'View', 'Freeze Panes', and here I'm going to freeze the top row, you could do the first column. I'm going to do the row. This means, when I scroll down, hey, look at that, stuck at the top. You can unfreeze it by going to 'Unfreeze Panes'.

Another handy cheat sheet for working with this type of data. I'm going to select all of it, 'Control A' grabs my whole bit of data. And I'm going to go along to 'Home', and I'm going to go to this one that says 'Sort & Filter'. And I'm going to hit 'Filter'. That's all I need to do to turn on these cool little drop down menus. This means that I'm in need to hunt out the people who haven't paid. So I'm going to drop down this menu, and 'unselect' all, and click 'Unpaid'. And I'm going to find these people, and try and wrest all my money from them. That is the sorting and filtering. So I'm going to 'undo' that to turn my sorting off.

Last thing I want to do is something called Flash Fill. It's going to be the last of our shortcuts. And remember, in this column here, I'm going to hit 'Control’ ‘Shift’ '+' to insert a column for me. Thank you, very much. And Flash Fill does some cool stuff where-- I want to join these two names because at the moment they are separated. And what I can do is, if I type in Gwendo-- Man, I picked the toughest name of them all. And I put it how I want it. That's how I want it, please. And what we can do is, just underneath here, we can head under 'Data'. There's one called 'Flash Fill'. And you see, it goes through,and it knows what I mean now, and starts joining them all up. Flash Fill can be used for all sorts of things. It can be used to pull these names apart, as well as putting them together.

Now I said that was the last of the cheat sheets, but it's not. Go to 'File', 'New', don't be afraid to use the templates. There's so much in here, if you need an invoice, you can follow my course and do it, or, you can cheat. Just go and find an invoice that looks kind of close to what you want. Switch out the logos, and you're away.

Now remember, this cheat sheet's just a little bit from my longer course on learning Excel. Check it out on bringyourownlaptop.com

Also remember, on that same website, there is a resources tab along the top. You can download a PDF with all of these tips we just went through, in a nice printable format where you can stick it up next to your desk.

My name is Dan Scott, and I will see you later.