Cheat Sheet & Shortcuts

Questions

You need to be a member to view comments.

Join today. Cancel any time.

Sign Up

Course info

24 lessons / 2 hours

Overview

Hi there, Welcome to this Microsoft Excel BootCamp. Together we’re going to learn how helpful Excel is in nearly every part of our professional lives.

This course is for beginners. You do not need any previous knowledge of Excel. We will stick closely to the powerful built in features of Excel and will not get bogged down in confusing code & complicated formulae.

This training course is project based. We start with a simple company branded invoice and explain how to calculate totals & tax. Using a complex and messy spreadsheet we will clean it up using Excels automatic features. With our new tidy data you’ll learn how easy pivot tables can turn long and hard to understand information into simple tables & beautiful graphs. Before you’re finished you’ll be making helpful drop down menus to help you fill out & sort your financial data. . You will learn how to turn uninspiring profit & loss statements into a good looking, easy to use documents. 


Class projects:

  • Create a quote & invoicing form.

  • Cleaning & formatting messy imported data.

  • Inventory spreadsheet.

  • Pivot tables

  • Regional Sales Report

  • Profit & loss spreadsheet.

  • GST & Tax calculations

  • Graphs for use in Word, PowerPoint, InDesign & Illustrator

  • Creating spreadsheets that work within Word documents.

Who should attend?

  • This course is designed for people who have little or no previous experience in Microsoft Excel. You will start right at the beginning and cover all the basics.

  • Only basic computing skills are necessary - if you can send emails and surf the internet then you’ve found the right training.

  • By the end of the course, you will be producing real world results with Excel.

What do you need?

  • No previous Microsoft Excel experience necessary.

  • You'll need Excel 2016 installed on your laptop. The standard installation of Excel 2016 or the Microsoft Office 365 version is fine.

Course duration 2 hours

Daniel Scott

Daniel Scott

Founder of Bring Your Own Laptop & Chief Instructor

instructor

I discovered the world of design as an art student when I stumbled upon a lab full of green & blue iMac G3’s. My initial curiosity around using the computer to create ‘art’ developed into a full-blown passion, eventually leading me to become a digital designer and founder of Bring Your Own Laptop.

Sharing and teaching are a huge part of who I am. As a certified Adobe instructor, I've had the honor of winning multiple Adobe teaching awards at their annual MAX conference. I see Bring Your Own Laptop as the supportive community I wished for when I was first starting out and intimidated by design. Through teaching, I hope to bring others along for the ride and empower my students to bring their stories, labors of love, and art into the world.
True to my Kiwi roots, I've lived in many places, and currently, I reside in Ireland with my wife and kids.

Downloads & Exercise files

Download Exercise Files

Transcript

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.

  • Powered by Marvin
  • Terms of use
  • Privacy policy
  • © Bring your Own Laptop Ltd 2024