Microsoft Excel 2016 Bootcamp - Zero to Hero Training | Daniel Scott | Skillshare

Microsoft Excel 2016 Bootcamp - Zero to Hero Training

Daniel Scott, Adobe Certified Trainer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
24 Lessons (1h 54m)
    • 1. Introduction

      1:43
    • 2. Exercise Files

      1:44
    • 3. Setting up a US letter or A4 sized page in Excel 2016 so it can be printed

      2:37
    • 4. How to add an image or logo to an Microsoft Excel 2016 spreadsheet

      1:39
    • 5. Adding text to our Excel Quote & aligning text & numbers

      7:16
    • 6. How to display the date correctly in Excel for the month first or month second formats

      3:43
    • 7. Adding borders & lines around cells in Excel 2016

      5:12
    • 8. Use these formulas to calculate a quotation or invoice with sales tax GST or VAT in Excel 2016

      5:26
    • 9. Making a quote or invoice template in Excel 2016

      4:48
    • 10. Printing your Excel quote or invoice to one page plus exporting a Excel PDF for emailing

      3:52
    • 11. Using pre made templates

      1:42
    • 12. Cleaning up Tidy up messy spreadsheets in Excel 2016

      4:02
    • 13. Cleaning up date formatting & currency formatting in Excel 2016

      3:56
    • 14. Remove blank line rows columns from Excel 2016 spreadsheets

      2:25
    • 15. Remove duplicates in Excel 2016

      4:27
    • 16. Splitting or seperating names into separate columns in Excel 2016

      4:17
    • 17. Sorting & Reordering a Excel spreadsheet by name price or date

      2:29
    • 18. Repeating formulas in Excel

      2:31
    • 19. Practise exercise

      2:29
    • 20. How to create a graph in Excel 2016

      7:33
    • 21. Create chart in Excel for use in Word & Powerpoint, InDesign & Illustrator documents

      7:22
    • 22. Microsoft Excel Pivot Table Tutorial for Beginners Excel 2016

      11:00
    • 23. How to make a profit & loss spreadsheet in Excel with drop down menus

      17:23
    • 24. Excel 2016 Cheat Sheet & Shortcuts

      4:29
143 students are watching this class

About This Class

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. Click here to grab your awesome Exercise Files. 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.

Download the exercise files

Transcripts

1. Introduction: Hi there. My name is Dan. Welcome to this Microsoft Excel Bootcamp. Now, together we're going to learn how super useful Excel is for pretty much, every part of our professional lives. Now, I made this course for beginners. There is no need for any previous Excel experience. What we'll do is we'll stick to a lot of the pre-made built-in amazing features of Excel to do our work. Rather than getting bogged down in convoluted code or really complicated formulas, we're going to stick to the easy methods. This training course is project-based. We'll start with a simple company branded invoice that calculates both totals and the tax. We'll take our really big, ugly, messy spreadsheet and clean it up using Excel's amazing features. With this new tidy spreadsheet, you'll learn how easy pivot tables can turn long, hard to understand information into simple tables and beautiful graphs. Before you're finished, you'll be making super-helpful drop-down menus to help you fill out and sort your financial data, chaining uninspiring profit-loss statements like this into good-looking dashboards like this. There is also downloadable exercise files and a printable cheat sheet you can use. My friend, it's time to sign up and together we get you from Excel zero to Excel hero. Now I mean to keep looking at the screen for a while afterwards. Never sure how long though. Long enough? 2. Exercise Files: Hi there. Now, as part of this class you'll get exercise files you can download. There'll be a little pop noise and a link down here by Taylor, right there. Okay, and you can go and download the files to play along. What I've also got as part of this course is the completed files. What that means is that at the end of every video that I do, so there's a few videos in this course. At the end of every video, I'll save my file to where it's said that it's working. So if you're doing it and yours doesn't quite work or you're not sure how I actually did something. You can check it against the completed file and yet to see the difference between yours and my one. Now there'll be link on every video to those particular files. The other thing that's quite useful is the Cheat Sheet. I've made both a video vision and a printable PDF version so you can check out this link here and that will get you to the printable vision. We can print it off, stick it next to your disk, be awesome. There's a video version that's one of the last videos in this course. The last thing is something I want from you, and it's a review. Now it's a bit early for reviews, I know, but whenever you feel it's time, okay. Post a review, post a comment, thumbs up, tick shear, whatever the platform it is. They will use interactions like reviews or shares or likes as a way of determining how good a video is. So, the more reviews I get, the better the video does the better I do as a trainer. Whenever you're ready leave a review, be honest, good or bad. Leave a review, actually no, don't leave any bad reviews. That's it, okay. So, here's the files. We've got the Cheat Sheet. Post review. Let's get on with the course. 3. Setting up a US letter or A4 sized page in Excel 2016 so it can be printed: All right. In this video, we're going to take this very boring, plain, old Excel spreadsheet and turn it into this page sized boring Excel spreadsheet. Just so that we can be using actual US letter sizes or A4 sizes, we'll look at changing the measurements, the default inches to millimeters as well, getting ready to build our quote in this tutorial series. Let's go and do that now. First step, let's create this first option here, it says blank workbook, and we get our standard Excel layout. Now we want to change this to be more of a page layout because we're going to be printing this, making PDFs, emailing it, that type of thing. What we need to do is go along the top here to view and switch it along to this third option here called Page Layout. You can toggle back to normal if you prefer, but Page Layout's going to allow us to get our page size so there's no point using the cells that are over here because they're not going to be able to print on one sheet. The other thing to double-check when we get started is the size of our page and maybe these margins from the edges here. I'm based in Europe so I'm going to use A4 or letter. I'll use letter because most of you, my students, are based in the US. But let's go along to Page Layout, and long here is size. We can switch this from A4, which is the default here for me in Ireland, over to US letter. The other thing we might do is I've got mine set to inches, you might be setting yours to metric. You might want centimeters and millimeters. You do that over on file, down to options while you're there, then you go to advanced options and somewhere along here, scroll. There it is. Ruler units. Yours might be set default so it'll pick which country you're in. But say I do stuff for the US all the time and for Europe so I have to switch between these two. You might switch into centimeters or millimeters. I'm going to force my number, inches. Let's click "Okay". Next thing I want to do is, I'm still under Page Layout, we're going to go to margins and I'm just going to make them a little bigger. I'm going to go to wide and it just puts the edges away. But a space margins between the edges of the page just looks bigger. We don't have a lot of detail in a quote so we can center the page a little bit more. That's how to set up your page sizes, change the measurements, get you margins right. Now we're going to go in the next video and look at adding images to our quote. 4. How to add an image or logo to an Microsoft Excel 2016 spreadsheet: Welcome. In this video, we're going to look at bringing in images into Excel, we're going to resize them, and we're going to put them in the top-left of our quotation. Let's go and do that now. It's such super easy, it doesn't really matter where you click because images can be moved around any way you like. Click on the cell reference where you wanted to go, and then go up to Insert, and along towards is Pictures. Go off and find your pictures. If you're working with my course, go and download the Exercise Files. They'll be a link in the description or in the comments on this page, and Exercise Files and we're going to use this, Bring Your Laptop Logo or BYOL logo. Bring it in. It's going to come in at whatever size it was created at. It's quite a big image here. I'm going to select it anyway, and I'm going to use the bottom right corner. We're just going to click hold and drag, we're going to go diagonally up towards the top left corner, and you can resize it. I'm going to get it to more appropriate size for our quote about there. That is how you bring in an image. Now to move it around, you just click anywhere but the edges, and it doesn't really matter. They don't sit in cells like tics does. You can put it any way you like. You can't put it up past the margins that we set up earlier, so if you want to have it a bit further up here, you're going to have to go back into your Page Layout. Go to Margins, and you're going to have to go to Custom Margins. We'll just make narrow margins and bring it close to the edges here depend on how you want to do. I'm going to put my margins back to wide, and still in the top left-hand corner there. Great that's it. Let's go in the next video and start adding some of our text for our quote. 5. Adding text to our Excel Quote & aligning text & numbers: First and most important is let's put in the big word, this is, I am a quotation. Now that's fine and it's fitting. I'm going to show you, let's change the font size. I'm going to sleek through or you an use this little option here or under home, up the top here. I'm going to make it nice and big. You can see here if I hit 28, it's big and it's flowed across to the other page and it causes everything to fall apart. We got to look at aligning things up and aligning them. At the moment this is aligned left so it pushes out that way. We're going to have this cell selected and up the top here under home, there is an option here that says left align and it's just going to make sure that we're on this furthest cell, but it's pushing from the right out left. If I keep adding texts in here, it'll keep going that way and not bunch into another page. The other thing we need to do is looking at the top here, we just need to keep an eye on this because there's going to be times where it does jump out to this other page and you're going to wonder what's wrong. What's happening is, you can see this is white area and then there's this darker gray area. The dark gray is what actually appears on the page this well, lighter gray here are the margins so nothing can appear in here. You can see there's a little bit of gap between this last column. This gap here, this gap here and this little gap here so I can make them a little bit bigger but if I go too far which is if I go past into that white stuff it goes [inaudible] and winds up breaking everything. I'm going to undo that. You can push it all the way out here, just make sure a cell doesn't span across this gap here. Make sure everything that's in it. This is my quotation, fonts you can change easily up here, we can use Calibri, that's fine and you might have a cover font, you can change it to. We're going to add some basic details in here. I'm going to leave this line here free just as a spacer, I'm going to type in our address, our office in New Zealand is at 666 Great South Road, it's a terrible address but it brings good luck for us anyway. We're going to put in our address. Great easy that's our address and we're going to look at putting in some other digits. We're going to put in our date. I'm going to put it over here. There's no specific place, what I want to do is have the date here and underneath that I'm going to have the quote number and I'll just put in a hash and underneath that, it's going to be valid for value. Quotes can be a little bit different depending on the industries. You might have to go through and find some examples of quotations. Just make sure you got all the details you need. We're going to put in all the main basic stuff, so you can add and remove as you need. One thing I want though is, you can see them lining up to the left and if I stop putting details over here, I want them all to line to the right just because they look nicer. I'm going to select all three of these and I'm going to go right align, so all the colons lineup. Over here, we're going to put in, we're going to skip date for the moment. We're going to put in 100 for the quote number. You can put any number you want. It doesn't have to be a number but say we do put a number in here, we're starting out because say it's the first day of business so we want to impress people like we've already got your customer 99 be you're not, your number one though. But you want to make them feel like they're 99. What happens when you're formatting numbers is texts wants to be left aligned by default. If I put texts in, left aligned, if I put in numbers watch, right aligned. This is just a defaults for those two ways of entering data. We can overwrite them. First of all, I want my number to be left aligned and I also want those zeros back in. To do that, we're going to have it selected and we're going to be on home and we're going to use this option here in this little group called number. The drop down at the top here general gets rid of those two zeros and what we're going to do is switch the text. This makes it do less. It just goes in as block text, so now if I add zero zero, it will stay there and not remove. You might need these for your quotation numbering. Yeah. Valid for, and we're going to say 30 days and the other thing we want to do is we'll deal with the date as well. I know we haven't done the date, we'll do that in the next video. But there's not much of a gap between these here, so I'm going to select all of these. What I could do is I could grab these and cut them and paste them over here and then make this one really small. That formatting does work, but what I want to do is select all of these guys and I'm going to use under home, there's this one here called indent. There is a decrease and increase, I want to increase the indent just to push it across a little bit, just there's a bit of gap between the two. Great. Let's throw in the rest of our detail. This is going to be client details, so I'm going to bold this one, control B, or you can use the bold at the top here and this is where you put it in some place here because this is obviously going to change depending on every client. Maybe client name, business name, and this will be replaced for every person's address. Often some businesses, well, a lot of business idea would need these details, they need business name, their limited company name and their address, and potentially a phone number as well, otherwise, they don't accept it as a valid quote. The next thing we're going to do is we're going to add some special instructions now and this might seem simple, so we're just going to add like notes. Might just be some details like special delivery instructions or just any special things you need to add to this quote and to explain it. That's pretty easy but what I want to do is I want a nice big chunk of, I could text field that I can put into this because what happens is I've got some texts that I want to put in. If I go to my exercise files and open up one quote or one quotation copy, there's just a little bit of text that I want to add. I'm going to copy that and paste it into Excel and if I paste it into a cell here, what happens is it just runs on forever and pushes over to this other page and lights a problem. I'm going to undo, there's a couple of things I want to do is I'm going to select all of these guys, so I've got two rows and a bunch of columns. What I'm going to do is I'm going to merge them, span to home and along the top here, there's one called Merge & Center. Merge & Center means it's going to merge all these cells and it means that the text is going to appear in the center of the cells. It's not what I want to, and this little drop-down here is one a simple one just called Merge cells. Now it's one big text block but if I paste inside of it, double-click add paste it still works, pushes the text of, goes there but then it gets cut off. What I want to do is, I've pasted my text in, like I just did there. I've got it selected this one cell and up the top here just above where it says merge, there's one called text wrap. That's what I want, so it means that it's going to get to the end of this box and not just get cut off, but it's going to wrap back around and you can make this as tall as you need to. You might make it three cells just in case there's lots of text in there. That is how to do some basic text input. We've done some cell merging and some aligning. Let's look at putting the data in in the next video. Why is it special? You'll see in the next video. 6. How to display the date correctly in Excel for the month first or month second formats: In this video, we're going to look at formatting the date where the day is at the fronts and the month the second and vice versa. Let's go and do that in Excel now. All right. Date is not hard, but there's some formatting issues that pop up sometimes, so I'm going to put in the date here. I'm just going to [inaudible] type fourth of the fourth and I'm going to put in 17, press '''Return'' and it does some things where you can see it's auto-formatted. It's added the year toward there, 2017 when I just put in 17. You might like that, you might not, but it's easy to go and change. You'll see at the top here where as before, all these cells were just just to General, this one's automatically being changed to Date. Okay. We can drop this down, go down the bottom where it says More Number Options. It's defaulted to Date and you can see these are the different formats. You might want to go back to what you had before, where you were just using the two digits for the last date here. Okay. What else? There's a couple of other ways where it removes the 0, it's up to you, the formatting you want. Down the bottom here, this is the location, so at the moment, mine's giving my day first, then the month, then the year. Okay. If you're based in the US, you do the month first, right? Crazy, but yes, you do. You can change that down here where it says English, United Kingdom and you can switch it out to United States and that'll switch that around. Okay. I'm going to leave mine on United Kingdom. One thing we will do is, if I switch it back to this one here, the format that I first chose, it doesn't matter what I put in there now. Okay. I can go into here and I can double-click it all and I can say, actually I want 4/4/2017, the long version and it will always format it to be consistent. Another thing you can do is, with this selected here, I'm going to date. There's a long version. Okay. Long Date. Okay. Click on this one. Now, you notice that mine it's going to hashes. Okay. All that means is that I can not fit in there. It's showing data that can't fit. Says, "not enough room", so I need to make this wider. If I make it too much wider, it's going to go off and be on this page. Not good. What I'm going do is, with the cell here, I'm going to grab it at the top here, next to H here and just drag it along to give myself a little bit more room so I can drag it out and hopefully all my days fit in there. Okay. You got to leave enough room for the longest date. I don't know what that one is. September? That's a guess. We're going to need to leave a lot more room probably than that. I'm going to go along, move a few of these along just so I've got room for the word September. That's formatting the date. What you could do is you can put in today's day automatically, so instead of having this date here, I'm going to delete that and at the top where it says Formulas, there's one here called Date and Time and I can say today. Click "Okay. " That'll calculate whatever day it is today. The problem is when I open this quote, it's always going to be today's date, so you might have acquired that you open in two years and that date's going to change. It's up to you if you like this automatically filling in. Don't mind the dates changing whenever you reissue a quote That's fine. I'm going to undo mine and go back to how I had it before. Great. There is another way of getting around this. Say you hate the formatting. Okay. The formatting is driving you bananas. You just want it to do what you want to do, is under Home, where it says date, go back to this one that says Text. Text is just as you type it. If I put in 14th of the fourth, 2017, it's not going to change it. It's just going to be exactly how you want it and that might actually just be the easiest way to do it for this quote. All right. That's working with dates in Excel 2016. Let's go into the next video. 7. Adding borders & lines around cells in Excel 2016: Hello, wonderful Excel learners. In this video we're going to look at adding borders around cells, like up the top here where we've got some shading, some lines around the outside. We're going to use premade styles, we're going to make our own for the bottom here, plus this little total looking lines that go around the outside. Let's go and do that now. It's really easy. Let's go and do it. First up, let's put our description and amount headings in. First of all, what we want to do is, I'm going to make it two columns is going to be one column for description and one from amount, you might have another one that might be discounts. It's up to you. First of all, I'm going to merge these guys. I've selected all the ones I wanted to select and up the top on to home and there's this option, this is move, merge, and center and it's going to work perfect for us. We'll merge all cells and the text that inside is going to be centered. Description and this one here is going to be amount and what I'll do is I will move this to the middle as well by going up to here. I might have to click off, click back in, and up here is center. Great. I'm going to make them bold as well. Click on B for bold or control B make some bold. Next thing I want to do is I want to stall the lines around the outside. With them both selected, so I click and drag it across these two. Now you've got some premade styles. If you're under the home tab along here under styles click this last little area here the bottom one gives you a bunch more. If you hover above them, can you see in my document there, they're actually updating. Just find something that works there's no exact for all. This one here is output looks very boring and traditionally the tops of hitting, so I'm going to use that one, okay, perfect. Next thing I want to do is I want to make a nice big box and for, yeah, well, the different description options and I just want to line around the outside. I'm going to select a big chunk now for us because there's nothing really that goes underneath. We're going to select the big chunk of this. It's easy to add rows. We're going to do that little later on, but you might as well make this nice and big here, just in case we get quite with lots in here. Just click this, this wonderful line around the outside. Actually, I want the line around this bit. Come up. I want run around this bit first and what we do is, I don't want any the styles or wanted to manually do it and you can do it by hovering above. Well, dropping this little menu here down. It's this borders is just underneath the font there. If you have to look at the little icons here, but the one I want is the outside border so is just kind of put a line or on the outside. Now, how do you know there's a line around the outside and all these because there's this gray lines, this black lines it's hard to notice. What we're going to do is we're going to turn off these things called grid lines. We're go to up here under home. Actually, it's under view and there's one here that says grid lines. Grid lines if you turn them off. Grid lines are only the as a helpful use for you while you working. When you print this, those grid lines don't appear. It doesn't really matter if you have them on or off, they won't print but it's just helpful if we're doing lines are on the outside, makes life a little easy. Back to home. Same around amount, select this guy. Can you see it's defaulted to the last one I used, because I can just click on this rather than having to go into the dropdown now. Perfect. There's a couple of other things I want to do is, first of all, as I want, there's going to be here, subtotal and is going to be text and then there's going to be total. I'm going to make this total bold because it's the most important one. I'm going to make them all right aligns so I selected them all up here. I'm going to make them right-aligned and in here is going to be my subtotal texts and the total here is going to have a slightly different line around on the outside. It's really common to have this. Under the stylings here drop this down. Where is it here? This one here is total. One line at the top two at the bottom. Here is our total. Great. Those are some of the styles near the lines around on the outside. One of the things that we will have to do and before we move on is, and I'm going to grid lines back on to so you can see. You'll see in here there's actually lots of different columns. I want to join all these so I could go through and select them all. Go to home. Go to merge. Probably not merge and center because it'll center the text. I want it to be left-aligned and I could go to, merge cells. Great. I could keep doing that over and over again. If I try and select them all and try and do it all in one big go just makes one giant box, bad. We're going to look at this other merge technique. What it means is I'm going to take all of these. There's one in here that says merge across. It's going to merge everything are along the rows but not the columns so that becomes really handy. You can see there is exactly what I wanted, all these are the lines. This one here is already perfect and that's going to be it for our borders. Let's get on to the next one where we start adding our totals and calculating them up for our quote. See you next video. 8. Use these formulas to calculate a quotation or invoice with sales tax GST or VAT in Excel 2016: All right, in this video we're going to add our amount. That has the dollar signs Great. Then we're going to make an option where we may add some into our quote. Adding an In Design course and we add some money to it, it's going to add them all up, down the bottom for our subtotal. Then it's going to calculate the texts and give us our total altogether. let's go and do that now in this video. First thing is we're going to put into some dummy text over here, this is going to be coming to an Excel course. I'm going to charge you $300. Now, at the moment it doesn't have the currency symbols, let's change that. Instead of selecting Just this one, I want it to be for all of them. We're going to select all of these by clicking and dragging across all of them and then at the top where it says general under home drop that down. There's a basic one in here called currency and you might get lucky and it might give you the right symbol. Mine has given me pounds, okay. I don't want English pounds.I want the euros or $. You come and change it and there is a drop down here, to these options by a few on the full list, drop this down all the way down the bottom says more number formats beyond currency and you can see here is the symbol that I want. You can drop it down. There is a bunch in here. Depending on where you are in the world, okay, you'll find your option in here. I'm going to find English and I'm going to use English but United States, Great. Let's click OK. states applied a currency formatting hasn't changed the number at all, just knows that the dollar signs. Next thing I'd like to do is I'd like to do the subtitle. Subtitle is really easy. It's just all of these edit together. What we'll do is we'll put in another one. Let's say you wanted to do a word course as well. I've got a word course go can check that out. This one here is cheaper let me show why. One thing we will do is that the currency is right aligned here. I want the multi right aligned so just click that all right-aligned. Just align up with these totals on the bottom here. 'll first put a formula work. You just add everything in this column and put it here in the subtitle will add the text, next. I'm going to use something called auto sum, most common and easiest Excel formula to do. It appears in lots of different panels you can see and I've got the selected on here and he's down the bottom here, less than small. If you've got a smaller screen, you might have to go to formulas and here is right at the beginning and just click auto sum. It's going up and reached up and said, Would you like all of this? It's close. I'm going to say, what you, actually I'm going to drag across them I want all of these and there's no point having that one in there. Just drag of course all the ones you want, you can manually type it in here. You can see is running from column I along the top,18, which is there. I 18 all the way down to I 35, which is this last option here, I'm going to click return on my keyboard. You can see there it's edited it up. It's edit the dollar sign as well, made it the right currency, awesome Excel stuff. Yeah, that's it for adding things up using auto sum. Let's calculate the text next. To calculate texts and clicking my text, you might call it VAT or GST, or sales tax. Whatever you want to call it. We calculating some texts. Click in the cell and up here in our formula bar, we're going to start with an equals. This is just telling, this is really important It tells Excel that we are not typing words we are typing a little formula and what I want to do is, I want to take this subtotal here. I'm just going to click on with my mouse, you can see it edits to the automatically. I can just type it in. They want to times this by my Tax rates. Tax rates can't be typed in as a percentage in formulas, it needs to be a decimal place. It's going to be zero point something and you need to find out what it is for your country or your state in your country. There's so much, where I am at the moment Island K It's 23 percent and if I return, I have that tax. In New Zealand is 15, which I thought was high went up to that from 12.5. K and 23 is little higher. If you're in some States of America, I know it's 7.5 and this is, I guess a little bit interesting. You need to put in 0. 75. Because if you don't and you put in 75, that's going to be 75 of syntax that's pretty high. It needs to be 0.075 and that'll give me your tax. Nice. Let's add these two together for our total. I'm going to click in this total cell and I'm going to, there's a couple of ways I can do it. I can do auto sum, or I can just do equals and I'm going to say, can you [inaudible] plus, and I'm going to click u and then I'm going to hit return. That's going to give me my total. My sub-total plus my text equals this happy days. Its just it works. Let's say we've done this, you're going to now do my, say my Photoshop course I've got one of those two. But that's super awesome and it's super expensive. It's 700. K you can see it's calculated, calculated the tax and has my total happy days. All right, let's get into the next video. We will look at adding and removing these columns in case we have extra stuff that needs to fill it in. Let's go do that now. 9. Making a quote or invoice template in Excel 2016: We're going to look at creating a template for this quotation, or an invoice. It doesn't really matter what it is. You've probably sick of opening the last job, doing a Save As and changing it out and hopefully remembering to change the file name and not saving over the last time. It's probably why you are here because you've done that a few times. So that's the caveman way of doing it. A slightly less caveman way is to use sheet. So this is our workbook, the whole document, and you can have sheets within here. So what I can do is I can double-click sheet 1. Call this one maybe invoice. I'm going to call this the actually BYOL bring your own laptop invoice. I'm gonna put the invoice number at the end here, 0099. You can see it up there. Enter. What I can do is I can right-click this one, say I need a new one, is the next quote that I need to do I can replicate, go to "Copy". Just remember to click "Create a Copy". Click "Okay" and we get this extra option. Okay, so it's going to just appended with the number 2 there. I can double-click that and just change this to 100. So I can keep doing that. It's good. It keeps all my quotations in one book. That might be enough, for you might go, "Job done. That's works enough for me." So you and just talk between obviously the two here and we just go and change these. So let's say that's not what you want to do. You want to make an actual physical template using the Excel stuff. All you need to do is, I'm going to get rid of this option here. I've only got one book. I'm going to get rid of the name 199, just to make it or tidy. All we need to do is do a Save As, as a template. The moment, just a regular old Excel document. We can go to "File", we can go to "Save As" and in here under this drop-down here, where it says Excel. You'll also be defaulting probably to Excel workbook. You want to go down to Excel Template. Just know that it's going to put it in a, not a strange place, but under your documents on the Custom Office Templates. Leave it to go in there. Give it a generic name. Someone's going to be my bringing laptop invoice or commands actually a quote. Yeah, so that's perfect. I might actually call it a template, just so I'm really aware of what it is. Cool. Hit "Save". Now, I have a template. Now using the same plate is quite important because you can overwrite your template accidentally. So the way to use it since we have created a template, what we want to do now is we're going to close this down. If we open up Excel, what we can do in the welcome screen here is we can go from Featured to Personal and you'll see this quote, just here. If I open this up now, great, it's generated a copy of it. You can see it's got a similar name, but it's got one appended to the end. If I try and hit "Save" now, either this or shrunk the top left here or Control S, it's going to save as by default. So when we override that template and I've got to browse and I'm going to put it in my documents. I'm going to call this one quote. Instead of template, this one's going to be 0099. It's just a regular old Excel workbook. Click "Save". So that's how to start it. You have to go to that new window and go to personal and open it up. The trouble with doing it any other way is that let's say I do it slightly differently. I close this down, I opened up Excel. Just be careful. If I go to my "Recent" and open up this thing here, my template, I haven't generated a new document based on that template. I've actually just open the template. If I go and amend this now and change it all, I've changed my template forever. It's just about the way you open the file mainly. There are couple of ways that make this work. If I close this down and I find my template, so he's and documents, he's under custom Office templates there is there, if I try and open him, watch this. If I go to this BYOL quote template, double-click him. If I open him this way, you can see it's appended with the one at the end, and this is perfect. This has created a new page. I try and hit "Save". It saves it as a new name back to where we were. So it depends on how you want to open it. You can go to that option where you do it from this screen here we've got a personal or you can double-click the file. You just can't go to Open. Anything but open or using these recent because that'll update the template. So that's it for creating templates. I feel like I've talked myself around a little bit in circles. In the next video we're going to look at printing this on a US letter or A4 documents and making a PDF that can be emailed out. That was a quite probably more helpful. So let's go and do that in the very next video. 10. Printing your Excel quote or invoice to one page plus exporting a Excel PDF for emailing: All right. In this video we're going to look at printing our quote or invoice onto one page and also maybe creating a PDF that we could maybe e-mail to the client. Do it that way. Printing's is pretty easy. Let's got to File, click on "Print", and pick your printer from this option here. Make sure the page size is correct and hit the big goal print button. The one thing you might find is that, my preview is looking great, but often it can be split across two pages and they're just a bit weird. The first thing to check is that your letter's size is down here. If you are in the US, you're using US later. If you're anywhere else, I'm using the maybe the A4 size. Okay. Just make sure that corresponds with, back in here under Page Layout under Size is what you're using in here. You might have opened up a template, form a line and its set up to our letter, but you're trying to print on a A4 and things aren't lining up perfect. Okay. Just make sure those two match up. The other thing you can do to make sure everything lines up is to set your print area. Okay. You can select all of the cells, everything that's on your page that you want to print and coming down here. This is everything on this page here, and if you can't see it in this format, go up to Home, no, go to View, and it'd be on Page Layout. Then you are going to figure out what's going to go on your page. Okay. Then go to Page Layout and there's option here that says Print Area. Okay. So Print Area and say Set Print Area. That's just telling Excel this is the stuff I want printed, none of those extra junk that I have lying around, just the stuff, please. Then when you go to File Print, often it will be on one page. Next up is to make a PDF that we can e-mail out or send to someone in another way. Okay. You can do it. There's a 100 different ways in Excel as it turns out. Under Print, you might have a PDF printer like I've got. Okay. I can do it that way, and I can go to Save As, and I can go to this drop down here and there's a PDF there. Okay. I can go to share and I can go to e-mail and I can say Send as a PDF. It's going to generate the PDF and add another step by dumping it into your Outlook e-mail system. If you're using Outlook, this is really handy. I don't. I use G-mail, so I have to make a PDF and then send it out separately or attach it separately using G-mail. You might notice on my one, I've got a few extra options that you might not have. This one is Send as PDF, pretty same as this one, right? But I've used the word Adobe in here. It's because I've got something install called Adobe Document Cloud, I think they call it or Acrobat Reader or I've got Acrobat Professional installed. It just adds a few little extras to this. Okay. I could use that one. You probably don't have them though, and that's a little extra one as well, you probably don't have, and this is a little extra one, you probably don't have, so loads of extra PDFs. But don't worry, you've got more to.o Let's go to Export, and we can go to this one here is the one you probably have. Create PDF and we can generate this one here. Okay. All roads lead to here. We've got a PDF, we're going to give it a name. I'm going to call mine, the next invoice is going to be 100. Okay. That's it. I'm going to open mine after publishing, I'm going to hit Publish. Probably It's going to open up in whatever your default reader is. It's probably going to be Acrobat Reader hopefully. Okay. Yeah. There you go. I've got it. I can go find it on my desktop somewhere and I can attach it to my e-mail. There it is there. Okay. That is how to print and how to make a PDF. If you're having problems, drop me a line in the comments and questions because sometimes it's a bit of a pain getting these things all to line up on print properly. Yeah. All right. I'll see you in the next video. 11. Using pre made templates: All right. This is a quick one just to remind you that there are a bunch of templates built into Excel, find them online just to get started. You might be put in charge of, I've got to go off and I've got to make a profit and loss sheet. When you open it up, it comes up into this featured options and you can type in profit, I'll just type in profit and loss, and there are lots of templates ready to go. You might not like the styling, perfect, but I might just have the calculations and nail the formulas that you can rob out of this. Say you just did the [inaudible] adjusted out , then we do our quotes or you want to make an invoice, is you might not like any of these because you're going to style the more perfect, but you can open one up and get started with this. Just maybe the only thing you want out here is to go through and actually just grab the formulas out of the total here. You can just grab the formulas in bits and pieces that you need. Don't be afraid, there are lots of options in here, new. Sometimes you need to use the language, correct? You might be calling something in your country, but it's called generically, something else in the US, so think US. You need to make a schedule instead of making it, type in schedule and pick from the thousands of templates to get started. You'll find something that's close enough and you can go and adjust with your new Excel skills that we're learning. All right, don't forget about templates. I cheat all the time and stopped with them and work backwards. All right, so that's it for this video. On to the next one. 12. Cleaning up Tidy up messy spreadsheets in Excel 2016: Okay. We've been given an Excel document or some export from some database. It might be something online, it might be from some internal systems that you've got at your business. But the data is a bit messy and we need to clean it up before we can do anything really. Okay. We're going to look at the tactics for doing that. We're going to open up a new Exercise Files. If you haven't got the Exercise Files, download them. There'll be a link on the screen. Let's open up Half Marathon Entries. Okay. We've got all the entries for our Half Marathon, and the times they got, the charity events, there is prizes that they won, and times, and dates, and stuff. But there's some messed up formatting. The quickest and easiest is that it's kind of ordered along the top instead of what would probably be nice if it was ordered top to bottom. This is pretty easy. We're going to slick through all the cells, okay? We're going to click on this one here, go along into the end, and hold "Shift", and click the last one. Where is the last one? Just there. Okay. Those are all my cells. Then I can go to Copy, and it selects the whole lot of them, then any way down here, or you can make a new sheet and just paste it onto this. Up to you. I'm not going to use another sheet. I'm just going to paste it just underneath, okay? I'm going to go to Paste, and there's little drop-down underneath Paste. Rather than the shortcut, Control V, use this. One of the little options here, you can see has a little arrow of kind of flipping it across, and you can see it's what's called Transpose, and it's just flipped from left to right, top to bottom. Cool. With this other stuff, I'm going to click cell 1 and delete the list. Now, one thing I should probably remind you is that we've only got one copy of this original data. We might do a Save As so that we're not wrecking the original, okay? I'm okay with wrecking it because I've got lots of copies. I've selected all this and I'm going to right-click any of the columns and hit "Delete". Cool. I've got rid of those. Phase 1, complete. Phase 2 is there's no column headings so we need some headings on the top. This is name, whether they paid or not. I'm going to right-click in where it says 1, in here, and say "Insert", and I've got a new row along the top here. I'm going to call this one, Name. I can tab along to the next one or can just click in the next cell. This is going to be Entry Fee, this is Email, and this one here is the Sponsorship. The next one is the date they entered, so Entry Date, and the last one was the time they ran, Finishing Time. Great. We've got our column headings. We need column headings for lots of reasons. [inaudible] out of this and lots of other things. What we might do for this top 1 here is we might select all of these, okay? Just click in the first one, drag across. I'm going to make mine bold, and that's it for adding columns. You might potentially need to add a column down the left-hand side depending on your data, okay? You can do the exact same thing and right-click A, and go to Insert, and you can have a list down here as well, and start adding titles. Okay. I'm going to hit "Save". Okay. Last thing is I might just drag these columns out here so I can see a little bit better the different areas. Okay. I can see the email addresses, sponsorship, and we got the date of entry. If you see any of these hashes here, it just means that it's only showing part of the data, okay? It generally doesn't do it for text, but will definitely do it for numbers because missing a few zeros off a number could be really dangerous, okay? That's it there. What we'll going to do is do some formatting with the currency and dates next dates as fun ones. We'll save that to the next video. Let's go and do it. 13. Cleaning up date formatting & currency formatting in Excel 2016: Hello, wonderful people. In this video, we're going to clean up our dates. Okay, the moment we've got a shortest version with four digits for the year. There's ones with two digits for the year. There some long words with the full word October in there. We need to clean all that up. The other thing is we'll add our $ signs to this column here because it's our money and it will look like this $ signs all consistent dates. Let's go and do that in this video. Okay, dates can be a bit of a problem, right? We've imported this data from who knows where, and some of the entries are in this format where it's got the written word, May. Some of them have got the days, months, year in four digits. Some of them have got them in just two digits. We need to get some sort of consistency. It doesn't really matter if the month is first, or the day is first, it's the same process. What we going to do is you might be lucky and they might import and all you have to do is select on it and go up to here and say I want to go from long date to short date, and that might work, but then you might have lots of problems. Other problems where you've got other values where I say, yeah, that's just be the long date and it just doesn't change. Okay, so we need to do something slightly more manual. You might have to do the easy way and just go and change it or this option here. I'm going to select this one. Okay, hold Shift, enter on my keyboard and select the last option, and then in here, I'm going to go to data. I'm going to go to the one that says ticks to columns, feasibly easy, leave it as delimited and hit next. It doesn't matter what this is. Click next and you're looking for this one here. I want the dates to be day, month, year. If you are in America, you might go month, day, year. It doesn't really matter, okay. It's just going to force all of these guys to be at least dates that we can adjust have finish, and something kind of changes. You can see nothing really happens except now when I go to home and I go to format and I go to date and I got to short dates. They are all changing. When I go to long dates, they all become the long dates, and so if I scale this out a bit there you go, you can see them all. Okay, so you might have to do with the quick, easy way by just picking up here short or long dates or you might have to do my little trick here. Go to date and then sort, yeah this ticks the columns option, cool. Nobody likes the long dates, back to short dates and do our currency. Back to here, back to general. Short dates, great. Make a little bit smaller again. Okay. The next thing we're going to do is this one here is missing all the currency, so that's 4,100. I'm just going to select this whole column by clicking the day up here, I'm going to pick currency, currency, currency. There we are, cool. It's picked pounds by default. You can go into here and pick actually more number formats, currency and I want not the pound, I want English, doesn't really matter. All the symbols [inaudible] New Zealand, we love New Zealand. Okay, so we just added the $ signs to it [inaudible] One thing you might also do is give seats, added these point zero zero to the end, and they might be useful for you if you've got change, in my case, just adds more data that I don't need, makes it a little bit more confusing. I'm going to select the whole column again, and over here under home, just underneath where I've got currency here. You can decide how many decimal points you want to show. You can show more. Now case we want to show less, okay, just down to the nearest $. Lovely. Let's hit save. That my friends is how to format dates and currency formats and other stuff. Okay, let's look at cleaning up more of our table here in the next video. 14. Remove blank line rows columns from Excel 2016 spreadsheets: All right. In this video, we want to go through and remove any empty cells or in this case, a completely empty row. There's two really quick and easy ways. We need to select the whole data set. I've clicked the top-left of my set, come down to the bottom here. I'm scrolling down, hold it, shift on my keyboard and selected this last column here, the whole thing's selected. A real quick and easy way would be just to re-order these, sort them. Let's say, we sort them by first name. Up here was this for Sort and Filter. I'm going to say I want to go to a to z and it's listed everybody's name from alphabetically all the way through. You'll notice that those kind of three missing blank cells, it just at the bottom here and hey, presto, really just gone. Because they are all lined up at the bottom here. Let's say you don't want to re-order it, another really easy way is to have the whole thing selected. You got it all selected. There's an option over here. Make sure you are on your home tab. Over here, it says find and select. Go down to go to special. There's one in here that says blanks, click " Okay". It's got them selected. Seems got them all selected there. Then an option up here where it says delete. I'm just using this little drop down. I'm going to delete cells. It's going to ask me what I'd like to do once they're deleted, I want to shift the cells up. Great. It just moves them all up and we kind of end up at a very similar place. Blanks are gone and that we haven't re-ordered our list, either way it works. Before we go onto our next video of tidying up these lists, and I just want to kind of do one quick little thing is, let's say that the columns here are in a bit of a bad order. I want to move finishing times right next to the names because let's say that that's the most important for us in terms of this runners' time half marathon thing. I could select B, right-click, insert a column, then cut and paste that across is a quick and easy away. I can click on "F", clicks the whole column here, and I can hold Shift down on my keyboard. What happens is if I grab any of these edges down here like edges here you can see it gets a little crosses and I click and drag it, you can see you get this little I beam that appears and it can go anywhere. I'm going to put it just next to the names. That's quick and super easy way to re-order your columns as well. I hope that was helpful. Let's go into the next video. 15. Remove duplicates in Excel 2016: Hi there. In this video, we're going to look at removing duplicate content. It's a good way to check to make sure you haven't got duplicates. I've definitely got some in here. I can see a couple right there. There's a Leslie Glover, and they're exactly the same. I want to remove them. But this one, it's a big long list so I want to go and automatically check it. These ones are easy to find because they're exactly the same. There's no difference between these two, all the way along. But there's another one down here with Haty Chavez. They're exactly the same, except they got different times. It might be that time they got a timestamp of when they're imported into the list. There's two different ways we're going to do this. What we need to do is select the whole data set, and we can do that now that we've cleared out our blank lines. Before, we had to connect, click this first one, go down here, hold shift. What you can do is click anywhere in your data set and go "Control A", and it's going to pick the whole thing. It won't do that if you've got blank spaces in any of these rows. So I've got the whole thing. The easiest one is just to remove the ones got a data. Let's go to remove duplicates. We're going to leave it on Select All, and we're going to click "OK". It's going to get rid of two of them. I know there's four so it's done it Okay. What I like to do after that is just to highlight anything. That might be all you need and you can finish this video. But let's say we want to just double-check because it's quite important. I do this quite a bit for my subscription service. I want to send this specific e-mail to the people who have signed up for my free stuff, and different e-mail from the people that have paid. I don't want to hassle the people that have already paid to say, "Hey, come pay." I want to make sure the e-mails only go out to people that are yeah, I don't want [inaudible] for this. Anyway. What we're going to do is a cool little thing to do, so slick anywhere in these cells, control A, selects the whole data set, and we're going to go back to Home, and there's one in here called Conditional Formatting. Drop that down. Go highlight cells, and go to this one that says Duplicate Values. What it's going to do is click "Okay". It's going to just highlight anything that there's a duplicate. It's not just the whole row like it was when it was deleting before. Anything that's a duplicate in here, it's going to highlight, and it makes a little bit obvious that I'm not too worried about these times because there's always somebody else has got the same time as them, somebody's also unpaid. It's these lines here, I can see these two Haty Chavez, and then I can compare and go. They're exactly the same except for this one. So what might have happened in this fun run is that maybe Haty and his, I don't know Haty name is, and daughter ran as well and they ended up being class as the same person with different times, I have no idea why are they named twice. You don't need to know. But it's just a really handy way to double-check before you go off and commit to things. I'm going to undo that and get rid of my styling. What you can do to get round that, just to make your delete duplicate content a little bit more robust than our first option. I'm going to slick all again, go back to data, go along here to say Remove Duplicates. Instead of saying I want to compute all of these column headings and find something that mentions across all of them, I'm going to "Unselect" them all and I'm just going to say, I want any rows that have the same e-mail, because I feel like that's a really unique. If you have the same e-mail with somebody else, you read the exact same person. You might have something else, that's a data entry, and here that might be a social security number, the in line revenue document data, something like that. Client ID, that might be a good one. E-mail address, I'm going to click "Okay", and it's found those other two. So it's removed Haty from it and there was another data option in there, so it's removed the entire column. Which one? It's removed the second one. We had two for Haty, so it just removed the second one. Be careful when you are just randomly deleting to make sure you delete the stuff you want to. That is deleting things, duplicates even from your Excel sheet. Let's go into the next video and tidy up this list even more. How much more tidy could it get? A little bit more. 16. Splitting or seperating names into separate columns in Excel 2016: Hey there, my name is Dan. In this video, we're going to split our names here that are bunched together on one cell and presto turn them into a two column thingy. Super easy, let's go and do that now. There's two ways of doing it. There's the regular way, it's called text to columns, pretty easy and there's another one called Flash Fill. I'm going to show you flash fill afterwards because it introduces flash fill to you. I like to say flash fill loads. Flash fill has so many other cool uses, but let's do the first one. So what I want to do is I want to split the first name and last name. So I'm going to select this column here. Actually first of all, I want to separate them into two columns, so I haven't got a second column here. So I'm going to right click B and go to insert. So I've got a nice blank one here. I'm going to select it and I'm going to make sure I'm on my data tab, on top here and there's one that says texts to columns delimited. I click "Next". What spaces, what delineates these two words? My case is a space between them. You might have maybe a comma between them. Mine is a space. You can tell down here that little preview. Pick whatever separates them out. Mine is spaced, I'm going to click "Next", and then I'm going to click "Finish" and magic. You can see it separated my first name and last name. That could be cool if you've got maybe cities and states and post codes, you can separate them all out. Yeah, that have got spaces between them. So that's one thing. It works, it's good. What I want to do though, I'm going to undo that. I'm going to insert another column because I want to show you something cold Flash Fill. I love it. I love saying it. It does awesome things. Think of it as you teach it once what to do and then it goes, "Well, you probably mean to do this over and over again?" Then it learns. It's really cool. So what you do is I've got Cynthia Wolfe here. Let's say I want to separate them out. So I can do a couple of things at the same time though. I can say actually I wanted to be Cynthia, I'm typing this in, but you can see I'm using Title case, so I'm putting Cynthia there, but I'm using uppers and lowers and then her name Wolfe here. Nothing really happens except when I click here, still nothing happens. Make sure you're on data or data and click on Flash Fill. It just kind of guesses what you were doing. You can see that it's pulled the first name and it's changed the case at the same time. Same here with Wolfe. You say Flash Fill, and it goes through and reaches into these ones here. I love Flash Fill. There's lots of uses for this when you're reordering data. Let's say you want it, but you want it to be as still, you want to change the case, but you want it to be like this where they're still together like they are, but they're reordered so that a last name is first because it's easier to sort like that. So I've switched them around and put a comma in there as well. Underneath it, I go Flash Fill. Cool. It's reached in, grab them, sorted them the way I want. You can do that with any data. Just give it a shot. It works most of the time. Make a column next to it, reorder it the way you want. Next row underneath and just click "Flash Fill" and see how it goes. So that's it for Flash Fill. I'm just going to tidy up my columns now you can skip along to the end. So what I want to do is I need them separated. So I'm going to undo, get rid of this one, and I'm going to say I want the last name or first name first. So I'm going to do Cynthia, tab across, Wolfe. You can't do two of these at once. you might just click both of these and go to Flash Fill, but you can't click Flash Fill when there's two of them. So you've got to do this one, that one, it's not too drama. Flash Fill. Cool. What I'll do is this will be first name, last name, and this column here, I'm going to delete. Bold them up to match the rest of them, and that will get us onto the next video. Let's hit "Save". I will see you in the next video and I promise not to sing the would Flash Fill in anymore videos. I've got it out of my system. See you in the next one. 17. Sorting & Reordering a Excel spreadsheet by name price or date: Okay. So we've beaten our list into some submission and now it's looking nice. The formats are the same. Now we're going to do something basic with the data just to, yeah, grab what we need out of it. Okay? The easiest way is just sorting and filtering. So the easy one might be, a nice quick one might be, let's say we want to order by- I'm going to click in where it says Cynthia and I'm going to say sort A to Z and it just goes through and puts it alphabetically. Same in last name. Okay. Spreads across the rows for you automatically. There's going to be more interesting potentially when we get to the finish times, okay? So it can have the person who ran the fastest. In this first one it looks like it was an even tie against three people. I randomly generated this data so three-way tie for first. Sucks for Kelly Ellis, Irvin and Doty. But yeah, you can do this for any of them, okay? Dates into, just click A to Z or you can obviously hit Z to A to get it the other way around. Okay. Sponsorship, same thing. I'm just figuring out who earned the most. Okay? So that ones at the top there. Other cool things you can do is I've got this one here where it's paid and unpaid. So what I want to do is filter it, okay? So it's different than sorting. It's just going to clear away the ones I don't need. So I want to find out, everybody is unpaid so I can chase them. Okay? What we can do is click anywhere in here actually along the top columns here, that's going to filter and these little dropdowns up here. These are really handy because now I can go along to show, I'm going to unselect all and say just the people that are unpaid. Now I can send these guys a group email to say, "Where's my money?" Especially Dety Riley. That was four grand. So this is really handy. Filters, don't worry, it hasn't deleted the data. Just means we intend to filter off. Who comes back to life. Okay? So yeah, you can filter by anything. You can, in terms of timing, these ones here are not going to be as useful. So filtering, depending on your data, you might have different things. Different departments, different product lines, different clients, those types of things are going to be filtering off. All right, so that's sorting and reordering your spreadsheet and making it a little bit more usable. All right, so that's going to be it for this video. Let's get on to the rest of the tutorial series. Okay? Next we do charts and graphs. I love charts and graphs. Let's go do that. 18. Repeating formulas in Excel: [MUSIC] Hi there. In this video we're going to look at repeating formulas. So, from your exercise files, open up repeating formulas and then slowly wait for Excel to open. A simple list here, what I'd like to do is figure out how inventory I have in stock in terms of $ value. I want to times this quantity by the cost per unit. Okay? So all I'm going to do is in here, I'm going to create a formula, and we always start a formula by having the equals at the beginning then we decide which cells we want to work with. I want to work with the cell here, so that is C4 and I want times that, so we use little asterisk key, it's normally hiding under your eight. Okay, I want to times it by this one here, D4. I'm going to click "Enter", so it's times that by that and gives me my value. Now I don't want to have to do this our over and over, I can copy and paste. Okay, my formula I can copy and then down here, undo and click in the cell and paste it. The problem is it's still using C4, D4, which is up there. So if I hit "Enter", it's going to give me the same value even though these are different. So easy way to do it is just select this cell here, this little, and the bottom right corner here, there's this little dot. Click and drag this down kind of like expanding this box and it's clever, thank you Excel. Goes down and switches out. You can see now this one, if I click on it, it's C5 and D5 instead of this C4 and D4 and it's done it for all of them down there. So that's a nice quick way to go and repeat you'll formulas using Excel. What you can also do with it is instead of just repeating formulas, you can repeat pretty much anything. If you are sitting there in an Excel and you're doing something like, I wish I could just do this over and over again, It's probably that feature there, so things like the month, so it's January and if I click hold and drag this down, it's pretty clever and starts working out what you mean. You can do the same left or right. Nice and easy. It works with days of the week as well, so Monday, let us to put the whole thing in and click, and any sort of like numbered list. See this one here. We've got item 11 and say we need a bunch more numbers. Goes through and adjust those. It doesn't work for everything but that just clicking and dragging, give it a try. Super easy for repetitive things. Alright, I will see you in the next video. 19. Practise exercise: All right. It's exam time or just a practice because you can cheat on this exam because I'm not there actually watching you. You can go back to the videos and check and if you can work anything out, you can check out this one here charts and graphs as the finish one. Open up data cleanup project and I want you to go through and do these tasks for me. The first one is to go through and remove any empty rows. Then I want you to go through any duplicate rows, to see if you can find in any of those. I'd like you to center some of the columns you can see over here, we've got showroom, sales and reseller sales. That kind of worked as center. I want you to change that. I would like you to move at the moment the wholesale prices here. I'd like you to move this. It's just after product name, you can see here product name and then this the wholesale price. I want you to drag that column across. I'd also like you to make sure that these are set as currency rather than the wholesale price. You can see, I want you to add the dollar signs to it. I'd also like you to create four formulas. The first two, I go into B, calculating the total number of sales. These are the sales from our showroom and the sales from our resellers. I want you to add those two together and give us the total there. Then I want you to do a second formula, that has the total sales. I want you to do in dollars. I want you to figure out by taking the total sales times it by the unit cost of the wholesale price, and then give us our price here. Just a tip, remember equals is the first thing you need to put in when you're making a formula. The last two are going to be just two totals, so the total sales and the numbers and total sales in the price. Last thing I want you to do is I want you to reorder your list so that the one with the most sales is at the top there. You can see at the moment is this Paw patrol stuff. My son loves Paw patrol. Reorder them this way and those are your tasks. Go through, do them. Once you're finished, send me a screenshot. Just to show that you've done it and you've made a work. All right. That's our practice exercise. Let's get on to the next video. 20. How to create a graph in Excel 2016: All right, in this video we're going to look at making charts, adding different labels and we'll also look at, I'm doing some of these in-cell graphs as well. Let's go and do that now. We're going to start with this spreadsheets called charts and graphs and you can open that from your exercise files. All we're going to do is we're going to create a graph. Now, you'll often be creating graphs from data like this with this more than one field. So what we want to do is to decide what's going to this. I'm going to decide on column B is going to go in. So just clicked on the would be at the top there. Now I need to pick some other parts. So in this case, let's say I just want to pick out total sales. So I need to select them and just this option here. So hold on control on my keyboard. Okay, and I click on the word G at the top here and the column hitting. Okay and it's like both of these columns. So yeah that's how you can decide. Watch data goes into your chat, then we're going to go into insert. There is a zillion different options up the top here. I couldn't way to get started is just this one that says recommended charts, especially if you're unsure you like how am I going to show this thing? Click on that. It gives you often really good advice on working and go into it. Okay, so in this case, I'm going to pick this one here, a clustered column. Okay. You can't go into all charts and then just see what it looks like as a pie chart. It's working but maybe too much detail on that one and it gives you a little preview of your actual data which is really cool and you can work through out. I'm going to go back to recommended charts. I'm going to use this one here. Column click okay. To make it bigger you can grab any of these ages here. Okay, I'm going to make this one less and big here. To move it, be careful not to like drag the inside parts. If I start dragging this inside parts and it works but I can often drag like bits within the chart around, so I am going to undo that. So when you're moving the whole chart grab the outer edge, then ensure you get the whole group the whole chart coming along with us. Okay, next thing we want to do is we're going to decide and it's given us the right titles or I guess everything that I want. I can go and adjust them. I can double-click where it says total sales and I can make this anything I like. So this might be toy sales. So you can just go over the top it's giving you some place order stuff. If it doesn't say you don't have a title up here or it's missing some part that you want. What you can do here I'm going to close it down, is if you click off, click back on, and on the outside you get these three little options here. This first one at the top here chart elements, allows you to turn things on and off. So you might decide that I want a legend, because it's missing it. Say there's my legend that it's appeared and let's say it doesn't excess titles. You can see along the hit this might be profits for this might be May 2018. So you can add all the different elements using this little plus button here. Now to change the styling of this, there's like a generic way and then there's a more specific way. The generic way is and if you have your chart selected grabbed the outside, make sure you're on design. You can see this in pre-made designs along here. I'm just hovering above them not even clicking them, just giving me some different style ideas, and how it might be displayed. Say that you pick one, you can go through that. You can also pick a color change. Now say you want to change the color of the bars or and all the colors okay click on color change. Color changes a little bit with I don't often use this one because it's changes like the group of colors. There's like a whole mix up here. I want to pick specific colors rather than this. So this is the generic changes along the top here aerobics changes, inside this want to make some small changes like I want to change the color of this bar, maybe make it a bit wider. So what I'm going to do is I'm going to select on it, and you end up double-clicking the parts you want to change. I'm going to double-click this bar and this little option on the side here opens up and you've got three parts. Okay, so I've got my fill, I've got a fix, and this last part here. Where can play around with a series of options. So this first one here I'm going to change the fill, its going to automatic. I want to say actually I want to give it a solid fill of and see here it says color drop that down. I can pick some of the theme colors, some of the standard colors for me, say I have got a corporate color that I want to use, and go to more colors and I need to type the RGB value. If you have no idea what your company RGB value is you might have to reach out to your marketing department or to designer or somebody to work out what your RGB value is and you can type it in there. Well, if you just want a pretty color, you can just drag this little toggle around. Okay, pick it. I'm not sure what color that is. Click okay and I've done it for one of them. So what we might do is instead of doing it for just one, I can click off and click on this one of kind cooked on all of them. If you double-click them like I said, you can change them individually. But if I click off and just click on one of these guys and I can go to fill, I can go to solid for. Because of mixed that color already, it's done recent colors. Okay, so I've done to the whole lot. Another thing I might do is with these bars here, okay with them all slight mix there were highlighted here along to hear this is series options and my width gap end up doing this quite a bit. I like the gap between to be smaller, just, I don't know, it's a fully graph. So generic stuff along the top and start double-clicking on things to open up this side window here to make more specific changes. A couple of other things before we leave is with your graph selected and move over to the design and you can go through and change your top type. Say that, yeah, this column's not working and the bar is going to be better for you, click on bar and we got to run it this way. The other thing you must do is you might find that the axes just around their own way be better to have these guys along the top here and extend the bottom. So you can have it selected in this one this is switch rows and columns and just flips it up the other way. So depends on how is looking you might have to play around with this switch rows and columns. Another thing before we go is I'm just going to move this out of the way. Remember grabbing the edge of it is sometimes it's just nice to have not a full other graph but just unless little visual. So what we can do is slick that off. I'm going to select this, hold Shift, grab the bottom one. So I've selected all of this in this column and this little option up here, it's called the quick analysis. So I click on quick analysis, there's some cool stuff it does.You can see bar chart is actually putting like a chart inside the cell as a little bit of a visual aid for the data. Same with the color here you can start to see by far that top one, the pole patrol sales are by far a lot more than the rest of them. There's a different options in here, the top 10 percent and some nice little bits that go inside of here. So I'm going to leave my database on, they can be helpful. So that is eight full creating graphs will look in the next one, I will create some different graphs and we'll export them for Word PowerPoint, InDesign, and Illustrator. So let's go and do that now. 21. Create chart in Excel for use in Word & Powerpoint, InDesign & Illustrator documents: All right, in this video, we're going to look at exporting this chart to lots of different places. Will look at what we got we've got Word. We're going to stick it in PowerPoint. We're going to also put it into Adobe Illustrator and change the colors and make a vector and editable. The same thing for InDesign. If I can find it in design. We'll also make a PDF version that we can share and all other things that we might need it to go. All right, let's get exporting chats. We'll start with putting it into other Microsoft products. It's pretty easy. You click on the edge of your chart and you use your "Copy". We'll Control C and then jump to the product you want to put it into. Let's jump to Word first. If you use the shortcut Control V, it goes in and [inaudible] , there's another way of putting it in up the top here where it says under "Home" under "Paste" this will drop down, there's pretty much two options. You can see the end of that use this illustration theme embed Workbook is the important one. The next one is a very similar. It says embed workbook. This one here says link data. You've got two options. Linking data is what happen to this first one. But if you just use your shortcut to Control V, it's going to link the data. It just means it's still connected to Excel. If you make changes in Excel, it's going to adjust in here. That can be useful sometimes, sometimes though you just want it to be detached and not connected to Excel and use the one that says embedded. I could use this first one here and says embedded. It looks the exact same in PowerPoint, no difference. Let's switch over to PowerPoint. Again, I'm in PowerPoint and I can just use Control V, one option, I can "Drag" a little bit smaller. You can see a lot of the same features appear here from Excel. It doesn't really matter whether you want to add chart elements in Excel or do it in here. I've got this option. Then I'm going to go to the one that says embed cause by default it links and I'm going to use this one here that and embeds it. Two options here. Let's check out what happens when I change them in Excel. In Excel let's make a big change. This guy here, the paw patrol sales and let's say that they were mistaken. We want to go through and it was only six rather than the thousands that we had in there. You can change it in here and let's jump into Word. You can see the one that we pasted, that's linked has adjusted automatically, but the one that we embedded hasn't same in PowerPoint. You can see in here, the one that's linked, the one that's embedded. It's really easy to go between Microsoft products. Obviously. Next we'll look at getting stuff into, say, some other desktop publishing documents. We're going to look at Illustrator and InDesign by Adobe. The technique is very similar. I'm in Excel, I'm going to "Click" on my graph. I'm going to hit "Control-C" or hit this "Copy" button. Then I'm going to jump into, let's say, Illustrator First. If you've never used illustrators, is just a desktop publishing program or graphic design program. You can use it for lots of things. All I'm going to do is just "Paste" in here as a control V or edit paste, and the cool thing about it is that it's still editable and it's still what's called vector, so it's scalable. In illustrator hold "Shift" grab the corner here, and I can scale it up. If you're an illustrator user, this is going to be useful if you are not, just ignore the rest of this video and skip on to the next one. If you are an illustrator user you can select on these bits and you can see, the all grouped together, right-click them, ungroup it. They're actually bits are a little bit hard to play with because this is bits all over the place until you. There's that, but there's, it's not perfect and not fully editable like it is when you going through for two other Microsoft products, but still pretty cool, what you can't do is grab the, say the white area here and click on, say these columns here. You can go through and decide that actually I'm going to use one of my colors. Can you can change these colors here in Illustrator. Very similar if you want to do it in InDesign. Jump into InDesign. In InDesign the same Control V or edit paste. It's slightly different. What you'll notice is that it is still vector and scalable, which is really nice so I can scale this up. Hold "Control and Shift" in InDesign, scale it up. Nice and big you can see still it's Vector. You might be seeing it a little bit blurry. You can get a view and say Display performance and make sure it's in a high-quality and look nice. You can scale it up really big. In trouble is you can't do the adjustments like we could in Illustrator, We can't go through and want to change this to my corporate color pink. It'll fused so ways around that it's pretty easy. You can go into illustrator. You could make your changes in here, then Copy and Paste from illustrator and Design. Even better. You can just have illustrator open. You going, you want to go straight from Excel to InDesign, but you want that control. What you do is you "Paste" in here, then you just "Copy" this version that's ended up in here, and "Copy" this into InDesign. I Pasted in there. I can "Select" on it, "Right-click" and go to "ungroup". You can start to see I'm starting to get the little bits and pieces like I did for an illustrator. They are a little bit hard to work with, but you can start adjusting them. Same thing with the white area in here now, I can "Click" on these guys. All right, you guys, these guys here. I can start adjusting, not him. Yes. Super easy to work with them. I can select on this and pick and new fill color. There is ways of making it vector and adjustable in InDesign. Okay. You just going to "Copy" and "Paste" it into illustrator and then "Copy" that into InDesign. One last thing we'll look at and say it won't is to file formats, you'll want to copy them straight here is like a PDF and to go into. Other products might not be some of these Adobe ones that might be something completely different. The easiest format to do that is a PDF. So let's go and do that. In Excel, what you need to do in pretty much the only thing you need to do is actually have your charts selected the one you want to export. If you've got lots of charts, just pick the one you want and then go to "File", go down to "Export" and "Create PDF", Great. In here, there's actually, it is by default is going to work, but you can't go check in here in the Options and it says selected chart but that's by default what it's going to do and give it a name and save it and you've got a PDF. I'm going to have my profit chart profit even. This is going to be my 2018 one the publish. Now I've got a PDF that I could use. It's scalable, it's vector. You can use a lot of different things now other than the Microsoft and Adobe world. All right, I hope that's helpful. Let's move on to the next video. 22. Microsoft Excel Pivot Table Tutorial for Beginners Excel 2016: All right. It's the one everyone's been waiting for, its pivot table time. What is the pivot table? It is a way to take longer data like this, where there's lots going on, and break it down to usable chunks. I've created one, I just want to show you a bit of a demo. So on this other sheet down the bottom here, I've broken down that data to show me the products by the years, and how much there were sold. I can break it down even further, and I want to see the products that were sold by just Daniel Scott, the salesperson, or maybe Ben Samuals. So it's a way of taking this data, and feeding it into a sheet, and being able to rejig it to prove a point, to extract data, to see it in a different way. Let's go off and build our pivot table. To create a pivot table, open up the exercise files. This is the one they call Pivot Table Data 1, open that up. What we need to do is have our cursor anywhere inside this dataset. You might have to select it if you've got lots of different groups of data you're working with. In my case, it's just one big chunk. So I'm going to have it anywhere in here, and I got to go insert in this first one here called Pivot Table. Everything is fine. This one here, new worksheet is just interesting. So we've got our workbook, which is the Excel file. Within this workbook, we can have worksheets. I've got sheet 1 down here, and you can have multiple ones in here. Its just like pages in a document. So it's going to create a new one of these, I'm going to click "Okay". You'll see down here, here's sheet 1 still, with my data, but I've got this new one sheet 2, which is going to have my pivot table on it. Now, let's just name it. Lets double click Sheet 1, and call this one maybe Raw Data, and Sheet 2 is going to be my Pivot Table. So what we need to do is we're going to use this pivot fields here. This is the most interesting, or important thing when you're building a pivot table. We're going to use these fields down the bottom here. Now, the most important ones are rows, and values. You'll use these the most. We use columns a bit more, and filters even less, but rows, and values. Rows are these. So the ups, and downs, the y-axis, the vertical. So I'm going to put in, say, my products into here. So you click hold, and drag them into rows. You can see here, it has listed them down here, and so, we're cleaning up our data from that nice big raw dataset, and just pulling the bits we want. So I want to know the products, and I want to know how much was ordered, the values. You can see here now, I've pulled from my raw data. I've pulled out the products, and how much was spent on them. I can use a lot of these. So let's say I want to get rid of the products. I can click on it, and there's one that says "Remove Field". Actually, I want to find out for my salespeople, areas there, and the rows. I can see the rows down here, this is their totals that they have earned as salespeople. So I could bin that one as well. I find rows, and values often gives me most of the things that I want. Let's say looking at our customers as well. These are the customers, and this is how much they've ordered. That's rows down the side here, and values. Columns is when you want to break out even further. Say my question is, I want to find out products, and I want to know in which quarter of the year they were sold. So I'm going to bin this customer 1, and I'm going to drag in my products. So that's products. What I want to do is I want to add some columns along here, Q 1 2 3 4 so I can put in quarter, and it breaks down my apples, and when they were sold. So Q1, blueberries were sold but there were no apple sold in Q1. It might be nicer for this one instead of products, am going to remove this one, and I'm going to say salespeople. So the sales for Daniel Scott in this quarter, and all the quarters plus their totals at the end, by far Daniel Scott is the best salesperson on the planet, good work Dan, and poor Tayla, doing badly, but that's all right. So that's a pivot table. When you try to work it out, you'll need a dragging like is it meant to be this here, or that there. Does that look better? Sometimes you end up with the product along the top here, and I'm going to get rid of this one. You're like, oh, that doesn't work, is a strange looking pivot table, so, you end swapping these around. Don't worry, often the values of assignments they price is the quantity, its units sold, its the numbers, it's the digits, and these columns, and rows. Rows mainly, and then columns to break out a bit more detail. Now, the last one here is filtering, and I find I use this, not very often. I'm going to show you two other ways of doing it. It just means I can filter it even further, because I've got my x, and y axis done. What I want to do is I want to break it down. Actually, I just want the results for apples. I don't want all the rest of the data. So instead of filtering it, what I can do is you can see this is row labels, this little dropdown here, and I can say actually I untick to select all. I just want to show apples, and it breaks down that content a little bit more. Apples were only sold in Q3. I can go into here, and say actually not apples I meant, I want to check on how boysenberries are going. You can see boysenberries were sold in all four quarters, and that's their grand total. So we're filtering down. So you can use these little kind of filters. It doesn't matter if you're doing it up here. I'm going to turn all this back on, and you can see that up here I can say I just want to filter by Q1. Let's say Q1, and Q2, because Q3, and Q4 haven't finished yet. So we don't want to add those to our totals. So you can do these filters that are within the labels here. I'm going turn these little back on. So when is a good use to use these filters? Is when you want to add an extra filter that's actually not displayed here, because we're filtering by our quarters, and by our products. Let's say we want to filter by the salesperson now, but we can't, because it's not there. So this is where filters are useful. In here I'm going to add salesperson, and you see that at the top here I got this extra filter. It's a weird place for me. I don't like it that's it at the top here. So drop-down here, and I'm going to say I want to find what Daniel Scott's doing. So it's this third filtering. I've got my quarters, and I've got my products, but I want to filter a little bit more, and I want to break it down by salesperson. Now, I don't really like filters, just because there's a nicer way of working with the same function here. So I'm going to drop this down, and I'm going to remove field, and we're going to use something called a slice or slicer. So click the "My table" here, I'm at analyze, and there's this one here called Insert slicer. So I'm going to pick the exact same thing I had before a salesperson, and it gives me the exact same details, but on a cool little push button version rather than this ugly thing at the top that's insignificant, I feel. So I can say actually I want to go by Daniel. I want to figure out what Tayla is doing, and I want to see what Ben is doing. It's the same thing. It's a filter. They call it a slicer, and it's this cool little box out here. You can click multiple things. I want to find Ben. So this little option here, I want Ben, and Daniel, so it gives me those options, and I can clear it out as well. Clear this filter. To get rid of it, I can click on the edge, hit delete, and then get rid of the slicer. You can have more than one slicer. You can pile them up next to each other, so you can make some cool combinations if you need to. So I want to find out who earns the most as a client. So all I'm going to do is, I'm going to pick my clients. Where are you clients? I can see the customers, and drag him into here, get rid of my products. Actually, I'm just going to get rid of this one as well. So this gives me all my clients and how much they've earned. I can either just look through, and see who the best client is, or I can use my more traditional sorting functions. So I can click in here, and I can say, actually, I want to sort it by the largest to the smallest. So these guys here, Fabrikam, have been the base client for me for that dataset, but say I want to break it out a bit further, I want to know across the quarters who the best is. So I click on quarters, and I'm going to drag that into columns. So I can break it out a little bit. Couple of things to know when you're working with pivot tables is, I don't like these labels. They're cool for resorting things, but once you're done with them, they just confuse the pivot table. So under Analyze, and this option in here that says, a Field Headers. I'll turn this off. The graph's still perfect, except you don't have these little things you can reorder by, and I think it just cleans up that table. So up to you whether you have those on, or off. If you do lose this thing on the side here, this pivot table fields list, this option field list you can turn on, and off. Also, if you lose it, it's probably just because you've got your cursor clicked down here. Click over this, double-check that it's on.The other thing is, and probably the most important, and I like to say this to the end, is that if you update your raw data weirdly, your pivot table doesn't update automatically. So I go into here, and I decide that actually this was wrong, and I've put that to zero, I've copied that across, a lot of them are zero now, even more to make it more obvious. Now I hit save, and I go back to my pivot table. It's not updated. So what I need to do whenever I update my raw data, I click in here, I go to Analyze, and I go to this one that says Refresh. Where is it? There is the refresh, and I will go, and retick it. So that's a pretty big one for the pivot table, a big marker right next to your disc. Raw data does not update the pivot table. So that my friends is how pivot tables work. I'm going to set you a little exam, a little project. I would like you to create a pivot table that shows me who the best sales person of boysenberries were. I want you to go through, and create a quick little pivot table just to show me. There's couple of ways of doing it, and how to display it. So I'd like you to display it, and send me a little screenshot, and stick it in the comments, or links, dropbox, which way you can. I'd love to see it. All right, so, that's it for pivot tables. Let's get on to the next tutorial video. 23. How to make a profit & loss spreadsheet in Excel with drop down menus: [MUSIC] Hi everyone and welcome to this tutorial. We're going to look at making a profit and loss spreadsheet nice simple one. It's going to do cool things like down here I've got a Sales tab and it allows me to do things like Data Validation, which sounds really scary, but it just means if I type in Dan here, it says, "No, don't put in Dan, put in a date please. " When I get across to here, when I'm putting in my items that have sold, I can create a little drop-down list and it's pulling this list from this list over here, so it keeps everything consistent. Same for the cost of sales. Very similar format. There's a drop-down for all my different costs, and when we've finished all of that, we create a dashboard to show it all, using Pivot tables showing my sales, my cost of sales, my net profit, and also a pretty looking graph. Let's now go get started. The first thing we're going to do is create our sales page. We're going to call this one sales. We're going to make it look pretty for no reason other than to satisfy my designer urge. I'm going to make it a blue background and have white ticks and I'll make a bold, I'm going to make this a bit higher. In this cell here, I'm going to tab across a little bit. I'm probably going to get it to center inside this cell. There's my sales. Inside this sales document, there's going to be the date the sales were made. There's going to be the price of the sale and there's going to be the item that was sold. Now what we want to do is couple of things is I'm going to stand these out a little bit. Now you can do them all in one go by selecting all three of these columns and dragging any one of them, and they all get a bit bigger. What we want to do is look at something called Data Validation because, say we're going to create this Excel spreadsheet for somebody else to start working on. We want to make sure that they don't go and wreck it. You're going to give them some boundaries, and that's called Data Validation. What we're going to do in this column here is I'd like to say all the things in this column have to be a date. We do that by going up to date row along the top here, and you're looking for this one that says Data Validation. Click the drop-down and click Data Validation and it'll default to here. What we're going to say at the moment it says, you can enter any value here into these cells, no problem. What we want to say actually, it needs to be a date and you can see it could be a time. We'll look at less than a second needs to be decimal point or a whole number. You can give it a specific value that it has to hit. I want it to be definitely dates. I'm going to say I just want it to be greater than, it can be any date as long as it's greater than the first of the first, 2000. This is a random date, as long as it's above that, and it's a date format and click Okay. What happens is if I put in a day here, the first of the first 2017, great. It works. No problem. But if I put in, say my name, it comes up with what's called a Stop, which just means you are not allowed to do it. You can retry, but you can't go any further. It just won't let you do it. There are times where a Stop isn't as important. If I select all these guys again and go to my Data Validation. The other options in here are, instead of, and this one here, where it goes to stop. There are these other two. Warning is like a Stop, but you can override it, and the last one here which says Information is more like a general like, hey, Frequently Asked Question kind of thing. Let's quickly look at Warning. Let's type the error message. Let's say date invalid. Please, enter date into this field. Spell field right. That's going to be the popup text that appears. Let's see what this one does. If I put in Dan now, it's wrong, but unlike stop where there were no other options, this option you can override by clicking, yes, or you can hit stop by hitting, no. You can see here my date invalid and please enter a date. That's the pop-up that comes to the person that's using it. The last option in here, I'm going to hit, Cancel, on this one. I'm going to select all my cells and I'm going to select the last one Data Validation. Let's just have a little look at it, can't even find it, there you are, and the message in this case is going to be just information same thing, but you'll look at the difference when I pop in Dan. It's more like a helpful reminder, did you know rather than a warning. Up to you how you work it. I'm going to put mine back to a stop because I need it to be dates because I'm going to use a report at the end of this tutorial that's going to show the dates and what prices with money we're going to enter for the different days of the month. They're all selected back to Data Validation, and I'm going to say that no letter goes forward unless the dates put in there properly. The next one, easy one under price, we're just going to select all of our cells here, and we're going to say under Home, we're going to say, I would like you to be my friend, Accounting, but actually I need my defaults to pounds, so I need to change mine to a $ sign. This first one, I'm entering off my bank statement and it was on this date, and it cost this much and the item. Now, the next thing we want to do is we want to create a drop-down menu. Why? Because let's say we sell 200 different things or ten different things. We don't want people just willy-nilly adding the name of it because when it comes to creating graphs or tracking data, and we need to have consistent naming. What we're going to do is create a nice little drop-down lists so that people can just choose from it. Then it's going to be exactly the way we want it displayed. To create a drop down list, it's pretty easy, you create a list somewhere. It could be on an extra Workbook, sorry, extra worksheet. We could create a sheet here, but what we'll do is we'll just create it to the right here. We'll call this one a Product list. We sell courses that are Photoshop courses. We sell InDesign courses. We also help Illustrator courses, and of course Excel courses and Word courses and all the courses. That's going to be my list here. I want people to be able to pick from this little drop-down here. What we going to do is in this list here, I'm going to select them all. We go back to Data Validation like we did for our date. We say you have to pick, where's Data validation, here. You have to pick from this list. We use data a second ago. Pick from this list, I would like the source to be. Click in here and I'm going to say this, Source please. This is where we're going to pick out less from. Click okay, and you can see this little drop-down menu appears. I'm in here and this is for my Photoshop Course. Let's go through and add a little bit of fake data. It's going to be different month and the same year. We've got another one that was another statement for $500, and this one here , is going to be an InDesign course. I'm going to go through and add a bit of data. I'd [inaudible] fast-forward this little bit while I go and do this, boring. Okay, so I've entered it in all my details from my bank statement. We've only got a few details here. Yours will be a lot longer. Okay, next thing I wanted to do is I'd like to create another sheet or another document because this is my sales. I like to keep the cost of my sales on another place and that could be just below here. Cost of the sales just underneath or we're going to keep them on a different worksheet. I would like you to go through and see if you can do this on your own because it's exactly the same, but instead of it being costs in terms of sales coming through, it's going to be the cost of things we've spent money on , okay? So it might be things like printing materials, the venue, the trainer costs, any sort of costs involved. You're going to go to your statements, put that into your cost of sales spreadsheet. Now, I'd like to see if you could do it because it's exactly the same. I don't want you to copy and paste. I want to see if you can build a list of things to pick from. So might be telephone, it might be rent, power, water and I'd like you to create little drop-down that people can pick from. Now you're not going to go and do that are you? Okay, because I wouldn't either, because this thing here is exactly the same thing I want us to go change the list and dropped down. So let's just do that together. Okay, so I'm going to right-click this guy here and go move and copy, even, and I'm going to Create a copy. Okay, and I'm going to move it to the end. Double-click this one, I'm going to call this one the Cost Of Sales. So not much difference. This one here is just the Cost Of Sales and the only thing I need to do in here is over this side, I'm going to have to go through and change my lists. This might be the cost of the venue. This might be the cost for what else? What kind of trainer cost that we have? We have the Trainer, we have the cost for Materials and you might have lots of other ones. Okay, so Telephone, all sorts of different ones and in here we're going to clear these out but leave the data validation. In here the list changes, so this one here is going to be a cost. Let's say my venue cost me a $150 for some reason. That was my venue, and then there was a trainer cost which was 1 million because train is awesome. Okay, so we go to this drop-down, change it to Trainer. This one here, we're going to go to some printing costs, cost us $20 and the Telephone cost us $30 for the month. Cool, and that's the field that we don't need. Great, so that is my costs that come into the business. So down the bottom here, I'm going to rename this one my Sales. I've inputted my sales, I've inputted the cost of those sales and now I would like to build a little dashboard to show me my profit and loss. Okay, so we're going to create a new blank sheet down here by putting this little plus button. This one here is going to be called my Dashboard. What am I do is just click hold and drag that tab to the front. So it's the first thing that opens. To create our beautiful Dashboard, we're going to add a title, and I'm going to fancy it up a little bit. No reason remember. Actually I'll do it to this whole column, so it looks nice and I'll get into center, and I'll get the font to be a bit bigger and I'll end it then. Time of ways to finish. Okay, next thing I want to do is I want to show a table showing the total of all the sales. At the moment, they're all separated in here, I want to group them all together. We're going to use our beautiful pivot tables that we did earlier. I've picked the cell I wanted it to go into. I'm going to go to "Insert", I'm going to go to Pivot Table. Now, I can't see it here, all you need to do is, I want to go to the Sales and grab all of this. Then click "Okay" and it jumps back to here, back to our dashboard and I want to show my items in the rows and my price in the values. I'll do the same for showing the cost of those sales. I'm going to put it where I want it to go, right there and I'm going to go to "Insert", Pivot Table and I'm going to go to Cost Of Sales. Grab these guys. Same thing, I want the item to go in the rows and I want the price to go into values. That is my sales and cost of those sales. Now, we're going to have to play with the labeling a bit. You can see here I'm going to stretch this out. I'm going to call this one Sales instead of row labels. So this is going to be Sales. I can't spell, c'mon, Sales. Sum of Price is probably not what I want, let me do in dollars. I'll do the same for here. Great, I'm going to select this whole column and say actually I'd like you to be the Accounting and I'd like it to be in dollars. Great. Yes, this one here, I want to be Cost Of Sales. I would like to start doing some totals. So at the moment, Grand Total seems a bit too formal for me. These are just Totals not Grand Totals. What I'd like to do is, work out what my Net Profit is, my expenses off my sales. So we're going to go Net Profit, and what will do is, I would like it to create a formula, so equals first and I'd like this minus my costs return, and my Net Profit is currently minus $28,000. That's because my Trainer cost was so high. So let me just style this a little bit, just so we've got some easy way to see it. Now, let's look at updating this because this is quite important. Remember, there's two things. If I go to my Cost Of Sale, and let's say, I have missing to this because it's $30,000. Let's say it's just $300. If I go back to my Dashboard, nothing changes. So remember you've got to go and refresh that data. We learnt that in our Pivot Tables. But there's something else that's going to happen as well. Let's say the cost, I'm clicking in my Cost Of Sales here and I'm going to go to my "Analyze" and there's my "Refresh" button. Look what's going to happen. It does refresh, get the right number in there, 300, but it's kind of resize the columns as well. So I'm going to undo that. What we can do in here, is we can go to "Options" along the top here, click on that and where it says "Layout & Format", we want to go down to where it says "Auto-fit columns and widths on update", because it's going to automatically just have the columns just wide enough to show things. I don't want you to do that. I want you to just leave it as I had. Let's click "Okay". Now if we hit refresh, it's adjusted and our total is adjusted and we had a net profit of May of $868 in the money. Again, for no reason, centered bolt white. That looks good. What you can do though is, refreshing it's a bit of a pain. There's something you can do, click inside your table, and go up to your Analyze, go to Options and along where it says data, there's an option that says refresh data when you open the file. At least when you open it and goes and checks again, you do the same for this one here. So you need to do it to both of them. I like to go to data and say refresh this data when you're opening it. Okay, and awesome. I didn't do it to this one here. I need to also go into Options and say whenever I refresh, this one here, auto-fit columns on update. So I did a for this bottom one, remember the top one. Next thing I want to do, and maybe one of the last things is just my Dashboard looks kind of cool. So I've got my Profits and my Losses and my Total. I want to show a nice little graph in here. What I'm going to do is, I'm going to click in this top one here because I want the graph or chart to be from my Sales. I don't want to share my costs. You could do a cost one as well. Up here it says Analyze, there's one along here that says Pivot Chart. So it's just going to give you a chart based on the pivot table. It's given me default to columns, which is perfect for what I want and this is just going to give me a nice, undo. I'm going to go up the outside white bit and this is going to give me my Sales on the top here, so this is going to be total sales, May 2017 Sales, even, nice. You could do the same thing for the costs just to see what they're like, maybe pick a different kind of chat. Last thing we might do is, I'm going to close down the charts and charts areas, I don't like all these little lines everywhere, so this is going to be my dashboards is my kind of presentation sheet. What I'm going to do is I'm going to go along to Page Layout and along here where it says guidelines view, we're going to turn those off so we've got a nice, prettier table. So that my friends was a long video. We made some drop-down menus, we did some data validation and we made some Profit and Loss charts using Pivot Tables and we made a chart at the end there. How good is that? All right, that is going to be the end of this tutorial series, okay? There is a more advanced tutorial series if you want to go and do that one as well, but that's going to be as for now, [inaudible] and I will hopefully see you in another course. There's a bunch of other courses. There's Word and PowerPoint and X's, plus a lot of Adobe products like Photoshop, Illustrator, InDesign. So go check those out as well. 24. Excel 2016 Cheat Sheet & Shortcuts: Hi there. My name is Daniel Scott, and this is a 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. The first tip from our cheat sheet is to insert a column. I need to add my headers. I'm going to click in this top row here and go to "Control", "Shift", and hit "Plus". It allows me put in either the rows or the columns. Great. Next step, I'm going to type in January, or just Jan. If I grab the bottom-right square and drag it across, Excel is pretty clever and knows that I mean the string of dates. It 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 of these. I want them to be currency, because pretty much, there's always a currency in Excel. "Control Shift" and hit "4" on your keyboard, and that will put it to a default currency. Mine's at the pound at the moment. Yours might be $. Next tip is, say you want to select the whole bit of data that you've got. Mine's pretty small. Yours might be huge with lots and lots of rows and columns. Just hit "Control A" and it selects the whole thing. That is going to segue into a cool little under "Home" along here where it says "Format as Table." I'm going to pick one of the predefined ones. Click "Okay, " and it's going to make it a pretty little table for you. Next shortcut is "Control" and then "Tab" on your keyboard. Normally just above Caps Lock on the left. I'm going to click that, and it toggles between any open Excel file. So if you've got a couple that you'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 Colon" and it's put in my exact today's date. So using my control tab again to jump to my next open Excel document, I want to do a couple of things. One is I'm working on this long document. It's pretty long. I can double click the bottom of any cell and it jumps me all the way to the end of that column. Cool? But wouldn't it be handy if I had my header rows along the top here because I have no idea what may be this number is while I'm all the way down this. So what we can do is go to "View", go to "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, and this means when I scroll down, hey, look at that stuck to the top. You can unfreeze it by going to "Unfreeze Panes." Another handy cheat sheet for working with this type of data is I'm going to select all member "Control A" grabs my whole bit of data, and I'm going to go along "Home" and I'm going to go to this one. This is "Sort and Filter" and I'm going to hit "Filter", and that's all I need to do to turn on these cool little drop-down menus. It just means that I'm going to need to hunt out the people who haven't paid. So I'm going to drop down this menu and I'm going to unselect all and click "Unpaid." Now I'm going to find these people and try and wrestle money from them. That is the sorting and filtering. So I'm going to undo that to turn my sorting off, and the last thing I want to do is something called Flash Fill. It's going to be our last about shortcuts, and remember, in this column, here I'm going to hit "Control Shift Plus" to insert column for me. Thank you very much, and flash field does some cool stuff where I want to join these two names because at the moment they're separated, and what I can do is, if I type in "Gwendolyn Pearson", man, I pick the toughest name of them all okay. I put it how I want it. That's how I want it to please. What we can do is just underneath here we can hit under "Data", there's one called "Flash Fill". You see it goes through and it knows what I mean now, and starts drawing 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. I said that was the last of the cheat sheets, but it's not go to "File", go to "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 and just go find an invoice that looks close to what you want. Switch out the logos in your way. Now remember this cheat sheets 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 and you can download a PDF with all of these tips we just went through and a nice printable format where you can stick it up next to you desk. My name is Dan Scott and I will see you later.