Microsoft Excel 2016 Bootcamp - Zero to Hero Training

1. Introduction: Hi there, my name is Dan. Welcome to this Microsoft Excel Program. Now, together we're going to learn how super useful Excel is... for pretty much every part of our professional lives. I made this course for beginners. There is no need for any previous Excel experience. And what we'll do is, we'll stick to a lot of the pre-made... built in amazing features of Excel to work with... rather than getting bogged down in complicated code... or very complicated formulas. We're going to stick to the easy methods. This training course is project based. We'll start with our 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. And 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'd be making super helpful drop down menus to help you fill out... and sort your financial data... turning uninspiring profit/loss statements like this... into good looking dashboards like this. There is also a downloadable exercise file... and a printable cheat sheet we can use. So my friend, it's time to sign up, and together... we'll get you from Excel zero, to Excel Hero at work. Now I meant to keep looking at this 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 Tayla, right there. And you can go and download those 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... there's a few videos in this course. At the end of every video... I'll save my file to where it's at 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... to see the difference between yours and my one. Now, there'll be a link on every video to those particular files. The other thing that's quite useful is the cheat sheet. So I've made, both the video version, and a printable PDF version... so you can check out this link here. And that will get you to the printable version... where you print it off, stick it off next to your desk, be awesome. There's a video version, it's one of our last videos in this course. Now 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, post a review, post a comment. Thumbs Up, Text Share, whatever the platform that 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 that video does... the better I do as a trainer. Whenever you're ready, leave a review, be honest... go to bad, leave a review, actually no, don't leave any bad reviews. I lie. That's it. So, here's the files. You've got the cheat sheets. Post a 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: 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. So let's go and do that now. First up, let's create, this first option here, it says 'Blank workbook'. And we get our kind of 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. So what we need to do is go along the top here to 'View'... and switch along to this third option here called 'Page Layout'. You can toggle back to 'Normal' if you prefer... but 'Page Layout' is going to allow us to kind of get our page size... so there's no point using these cells that are already 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 along here it says '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'... then you go to 'Advanced Options'... and somewhere along here, scroll... there it is, 'Ruler units', yours might be set 'Default'... so it will pick which country you're in... but say I do stuff for the US all the time... and for you, I have to decide to switch between these two. You might switch it to 'Centimeters' or 'Millimeters'. I'm going to make it to force mine to 'Inches'. Let's click 'OK'. Next thing I want to do is, I'm still under 'Page Layout'. I'm going to go to 'Margins', and I'm just going to make them a little bigger. I'm going to go to 'Wide', that just puts the edges away from-- bit of a space, margins between the edges of the page. This looks better, we don't have a lot of detail in a quote... so we can kind of center in on the page a little bit more. Great! So that's how to set up your page sizes, change the measurements... get your 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 re-size them, and put them in the top left of our quotation. So let's go and do that now. It's super easy, doesn't really matter where you click because images can be moved around anywhere you like. Click on the cell, roughly where you want it to go. And then go up to 'Insert'... ...along towards to where it says 'Pictures'. Go off and find your pictures. If you're working with my course, go and download the exercise files. There'll be a link in the description, along with the comments, on this page. And in 'Exercise Files', we're going to use this... ...Bring Your Own Laptop, 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. So I'm going to select anywhere, and use the bottom right corner. And I'll just go click, hold, and drag. We're going to drag up towards the top left corner. And you can re-size it. So I'm going to get it to a more appropriate size for our quote. About there. And that is how you bring in an image. Now, to move it around, you just click anywhere, but the edges. It doesn't really matter, they don't sit in cells like text does. You can put it anywhere you like. You can't put it up past the margins that we set 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, depending 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: So, first and most important is... let's put in the big word that says, I am a 'Quotation'. Now, that's fine, and it's fitting. Now, I'm going to show you, let's change the font size. I'm going to select it all, you can 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 floated across to the other page... ...and it causes everything to fall apart. So, we're going to look at aligning things up. Aligning them. So, at the moment this is aligned left... so it pushes out that way. So we're going to have this cell selected... and up the top here, under 'Home'... there's 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. So, if I keep adding text in here now... it will keep going that way and not bunch into another page. The other thing we need to know is... looking up the top here, we just need to keep an eye on this... because there's going to be times where it does just jump out to this other page. And you're going to wonder what's wrong. So what's happening is... you can see there's this white area, and there's this kind of darker gray area. So the darker gray is what actually appears on the page. These whiter gray here are the margins. So nothing can appear here, and you can see... there's a little bit of gap between this last column. This guy here. This guy here, we'll forget this. I can make him a little bit bigger. A little bit bigger. But if I go too far, watch this, if I go past, into the white stuff... it goes up... boom.. and ends up wrecking everything. So I'm going to 'undo' that. You can push it all the way out here. Just make sure our cell doesn't span across this gap here. Just to make sure everything fits in there. There's my quotation, great. Fonts, you can change easily up here. We're going to use 'Calibri', fine. And you might have a corporate font, you can change it too. And we're going to add just some basic details in here. Now I'm going to leave this line here free... just like a spacer. I'm going to type in our address. Our office in New Zealand is at 666, Great South road. Terrible address. It brings good luck for us anyway. And we're going to put in our addresss. Easy, that's our address. We're going to look at putting in some other digits. So we're going to put in our date. I'll just put it over here. There's no specific place. Now, what I want to do is have the 'Date' here. And underneath it I'm going to have the 'Quote number'. I'll just put in a hash '♪'. Hash even, '♪:' And underneath that, it's going to be 'Valid for'. Now, quotes can be a little bit different... depending on the industry, 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. You can add or remove as you need. One thing I want though, you can see it's kind of aligning to the left... and if I start putting details over here... I want them all to align to the right, 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 are lined up. Now, 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 in a number in here. We're starting our-- Say it's the first day of business... we want to impress people, like we've already got customer 99... but you're not. You're no. 1, but you want to feel like that 99. What happens when you're formatting numbers is... text wants to be left aligned by default. So if I put text in... left aligned. If I put in numbers, watch... right aligned. These are just the defaults for those two... those two ways of entering data. We can overwrite them. So, first of all, I want my number to be left aligned. And I also want those 0s back in. And 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. See this little group called 'Number'. And in the drop down, at the top here, 'General'. Kind of gets rid of those two 0s. And what we're going to do is switch the text. This makes it do less... just kind of goes in as block text. So now if I hit '00'... it will stay there and not move. You might need these for your quotation numbering. Valid for, we're going to say '30 days'. The other thing we want to do is... We'll deal with the date as well, I know I 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... and what I could do is, I could kind of grab these... and cut them, and paste them over here... and make this one really small. That kind of 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 one in here called 'Indent'. So there's a decrease, and increase. I want to increase the indent, just to push it across a little bit... ...so there's a big gap between the two. There's still the rest of our details. This is going to be client details. 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 in some place holders. This is obviously going to change depending on every client. Maybe, 'Client Name', 'Business Name'... and this will be replaced for every person. Also 'Address'. Often, some businesses, well a lot of businesses... I deal with need these details. They need 'Business Name', their limited company name, and their address. And potentially their 'Phone Number' as well, otherwise... they don't accept this as a valid quote. The next thing we're going to do is... we're going to add some special instructions. Now, this might seem simple, we're just going to add, like 'Notes'. It might just be some details... like special delivery instructions, or just any sort of thing... like special things you need to add to this quote... to explain it right That's pretty easy. But what I want to do is, I want a nice big chunk of--- I get text that I can put in to this... because what happens is, I've got some text that I want to put in... so if I go to my 'Exercise Files'... and open up a quote, '01 Quotation Copy'... there's just a little bit of text that I want to add. So I'm going to copy that and paste it into Excel. And if I paste it into a cell here... what happens is, this runs on forever, and pushes it on to this other page. Loads of problems. So I'm going to 'undo'. There's a couple of things I want to do. I'm going to select all of these guys... so I've got two rows, and a bunch of columns. And what I'm going to do is I'm going to merge them, so under 'Home'... and along the top here, there's one called 'Merge & Center'. Merge and center means it's going to merge these cells... and it means that the text is going to appear in the center of the celll. That's not what I want. In this little dropdown here, there's one simple one, called 'Merge Cell'. So now it's one big text box. If I paste inside of it, 'double click', hit 'Paste'. Still kind of works, watch this, I click off... kind of goes there, but then it's... it's cut off. So what I want to do is... I've pasted my text in... like I just did there... got it selected, this one cell... and at the top, just above where it says 'Merge'... ...there's one called 'Text Wrap' That's what I want. 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. 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. So that is how to do some basic text input. We've done some cell merging, and some aligning. Let's look at putting the date in, in the next video. Why is this special? We'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 days are in the front, and months, second, and vice versa. So let's go and do that in Excel now. That's not hard, but there's some formatting issues that pop up sometimes. I'm going to put in the date here, I'm just going to manually type in '4/4'... ...and I'm going to put in '17'. Press 'return', and it does some things where-- you can see, it's auto-formatted it, it's added the year, '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 up here, whereas before, all these cells were set to 'General'... this one's automatically been changed to 'Date'. We can drop this down... go down the bottom where it says 'More Number Options'... its defaulted to 'Date'. You can see, these are the different formats. You might want to go back to where you had it before... where you were just using the two digits for the last date here. And, what else, there's a couple of other ways... where it removes '0', it's up to you, the formatting that you want. Down the bottom here, this is the location... so at the moment, mine's giving the date first, then the month, then the year. If you're based in US, you do the month first, right? Crazy, but yes, we do. You can change that down here, where it says 'English (United Kingdom)'. And you can switch it out to 'English (United States)'. And that will switch that around. I'm going to leave mine on 'United Kingdom'. One thing we will do is-- So first, if I switch it back to this one here... kind of the format that I first chose. Doesn't matter what I put in there now. So I can go in here, and double click it all. And I can say, actually, I want... '4/4/2017'. The long version, and it will always format it, be consistent. Another thing you can do is, with it selected, I'm going to go to 'Date'... there's a long version... 'Long Date'. Click on this one. Now, you'll notice mine has gone to hashes '♪♪♪♪'. All that means is that I cannot fit in there. So it's showing data that can't fit. Says, not enough room, so I need to make it wider. If I make it too much wider... it's going to go off, and be on this page. Not good. So what I'm going to do is... with this cell here, I'm going to... With this cell here, I'm going to grab it at the top here. Next to 'H', drag along to give myself a Little bit more room. So I can drag it out, and hopefully all my dates fit in there. You need to leave enough room for the longest date. I don't know what that one is. September, that's a guess. So we're gong to need to leave a lot more room probably for that. So I'm going to go along... move a few of these along... just so I've got enough room for the word 'September'. So that's formatting the dates. What you could do is, you could put in today's date automatically. So instead of having this date here-- I'm going to delete that. And up the top where it says 'Formulas'... there's one here called 'Date & Time', and I can say 'Today'. That will just calculate. Click 'OK'. That will calculate whatever date it is today. The problem is, when I open this quote... it's always going to be today's date... Say you might require to open it in two years... and this date's going to change. It's up to you, if you like this automatically filling in. Bear in mind, the date's changing whenever you reissue a quote. That's fine. I'm going to 'undo' mine and go back to how I had it before. There is another way of getting around this. Say you hate the formatting... the formatting's driving you bananas. You just want it to do what you want it to do. It's under 'Home', where it says 'Date'... go back to this one that says 'Text'. 'Text' is just as you type it. If I put '14/04/2017'... its not going to change it. It's going to be exactly how you want it. And that might actually just be the easiest way to do it for this quote. So that's working with dates in Excel 2016. Let's go on to 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 got some shading, some lines around the outside. We're going to use pre-made styles. We're going to make our own for the bottom here. Plus this little total. Lurking 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 2-columns. So there's going to be one for the 'Description', one for the '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. So, I've selected all the ones I want to select. And up the top, under 'Home'... there's this option that says 'Merge & Center'. That's going to work perfect for us. So we'll merge all the cells. And the text inside is going to be centered. So, '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 and click back in. And up here is 'Center'. I'm going to make them 'Bold' as well. Click on 'B' for bold, or 'Control B', nice and bold. Next thing I want to do is I want to style the lines around the outside. So, with them both selected, so I click, and drag across these two. You've got some pre-made styles... so if you're under 'Home' tab, along here, under 'Styles'... click this little arrow here, the bottom one. Gives you a bunch more. If you hover about him, can you see... in my document there-- actually it's updating. So just find something that works, there's no exact rule. And this one here that says 'Output', looks very boring and traditionally... the tops of heading, so I'm going to use that one. Perfect. Next thing I want to do is, I want to make a nice big box... for all the different description options. And I just want a line around the outside. So, I'm going to select a big chunk. Now, for us, because there's nothing really that goes underneath... we're going to select a big chunk of this. It's easy to add rows. We're going to do that a little later on... but you might as well make this nice and big here. Just in case we get a quote with blocks in here. So to select this, just one of the lines around the outside-- Actually I want the line around this bit. So I want a line around this bit first. And what we do is-- I don't want any of these styles, I want to just manually do it. You can do it by hovering above... dropping this little menu here down. It says 'Borders', it's just underneath the 'Font' there. You’ll have to look at the little icons here... the one I want is the 'Outside Border'. So it's just going to put a line around the outside. Now, how do you know there's a line around the outside? There's gray lines, there's black lines, it's hard to know. So what we're going to do is... we're going to turn off these things called 'Grid Lines'. So we're going up here, under 'Home'... actually it's under 'View'. And there's one here that says 'Grid Lines'. So 'Grid Lines', if you turn them off-- Grid lines are only there as a helpful use to you while you're 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 you're doing lines around the outside. Makes life a little bit easier. Back to 'Home'. Same around 'Amount', select this guy. Can you see, it's defaulted to the last one I used. I can just click on this rather than having to go to the drop down now. Perfect. There's a couple of other things I want to do. First of all, I want-- There's going to be here, a 'Subtotal'. And there's going to be 'Tax'. And then there's going to be a 'Total'. I'm going to make this 'Total' bold... because it's the most important one. I'm going to make them all right aligned, so I've selected them all. Up here, I'm going to click 'Right Align' And in here is going to be my 'Subtotal', 'Tax'. And the 'Total' here is going to have... a slightly different line around the outside. It's really common to have this. Under 'Styling' here, drop this down, I want... this one here that says 'Total'. One line at the top, two at the bottom. It's our 'Total'. So those are some of the styles to get the lines around the outside. One of the things we will have to do before we move on is... I'm going to turn grid lines back on, just so you can see. You'll see in here, there's actually lots of different columns. I want to join all these. So I click, go through, select them all. Go to 'Home', 'Merge'. Probably not 'Merge & Center' because it will center the text... I want it to be left aligned. I can go to 'Merge Cells'. I can keep doing that over and over again. I'll try and select them all, and try do it all in one big go... just make it one giant box. Bad. So we're going to look at this other merge technique. And what it means is, I'm going to select all of these. And there's one in here that says 'Merge Across'. So it's going to merge everything along the rows, but not the columns. So that becomes really handy, you can see there. It's exactly what I wanted, all these little 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 start calculating them for our quote. All right, see you in the next video. 8. Use these formulas to calculate a quotation or invoice with sales tax GST or VAT in Excel 2016: In this video, we're going to add our amounts, that has the dollar signs. Great! Then we're going to make an option where we add something to our quote. Say, adding an 'InDesign Course'. Then we add some money to it. It's going to add them all at the bottom for our 'Subtotal'... then it's going to calculate the tax, and give us our total all together. So let's go and do that now in this video. First thing is, we're going to put in some dummy text. So over here, it's going to be 'Excel Course'. And I'm going to charge you, let's say, $300. Now, at the moment it doesn't have the currency symbol, so let's change that. So, instead of selecting just this one... I want it to be for all of them, so 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'. You might be lucky, and it might give you the right symbol. Mine's given me Pounds. I don't want English Pounds, I want either Euros or Dollars. So you go and change it. And there is a drop down here, to these options. But if you want the full list... drop this down, all the way down the bottom... where it says 'More Number Formats'. Be on currency. And you can see, here's the symbol that I want. You can drop it down, there is a bunch in here. So, depending on where you are in the world, 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'. It's applied a currency format. It hasn't changed the number at all, just knows that it's the dollar signs. Next thing I like to do is, I'd like to do the 'Subtotal'. 'Subtotal' is really easy, just all of this added together. So what we'll do is, we'll put in another one. Let's say you want to do a Word course as well. I've got a Word course, go check that out. And this one here is cheaper, I'm not sure why. One thing we'll do is, currency is right aligned here. I want them all to be right aligned. So select it all, 'right align'ed. Just to line up with these totals down the bottom here. So our first bit of formula worked. Just to add everything in this column, and put it here... in the 'Subtotal' where we had the text. Next, we're going to do something called 'Autosum'. The most common and easiest Excel formula to do. And it appears in lots of different panels. You can kind of see, I've got it selected down here. He's down the bottom here, nice and small. If you've got a smaller screen you might have to go to 'Formulas'. Here he is, right at the beginning. Just click 'Autosum'. And it's gone up, and reached up... and says, "Would you like all of this?" And it's close. So I'm going to say, "Kind of, I want you... Actually I'm going to drag across them all above these. There's no point having that one in there. So, just drag across all the ones you want. You can manually type it in here. Can you see, it's running from column I, along the top... 18, which is there. So I18. All the way down to I35... which is this last option here. I'm going to click return on my keyboard. And you can see there, it's added it up. It's added the dollar sign, as well as made it the right currency. Awesome Excel stuff. That's it for adding things up using 'Autosum'. Let's calculate the 'Tax' next. So, to calculate 'Tax'... click on the 'Tax'-- you might call it VAT, or GST... or Sales Tax, whatever you want to call it. We're calculating some sort of tax, right? Click in this cell. And up here, in our 'Formula' bar, we're going to start with equal, '='. This is just telling-- this is really important, it tells... it tells Excel we are not typing a word... we are typing our little formula. And what I want to do is... I want to take this 'Subtotal' here. So I'm just going to click off with my mouse. You can see, it's added to there automatically. I can just type it in. And I want to times, '♪' this with my tax rate. Tax rates can't be typed in as percentage in formula. It needs to be a decimal place. So it's going to be zero point something. And you need to find out what it is for your country... or state in your country. Where I'm at, at the moment, Ireland... it's 23%. And if I hit 'return'... I have that 'Tax'. And, in New Zealand... it's 15, which I thought was high... went up to that from 12.5. 23 is a lot higher. And if you're in some states of America... I know it's 7.5. And this is, I guess it's a little bit interesting... you need to put in a little zero, '0'. '75' If you don't, and if you put in just '75'... that's going to be 75% tax. That's pretty high. So it needs to be '0'. '0.075'. Now it will give you 'Tax'. Nice! Let's add these two together for our 'Total'. So 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 'Autosum', or I can just do '='. And I'm going to say, you... Can you see, it picks it there, I'm going to hit plus, '+'. And I'm going to click you. And then I'm going to hit 'return'. And that’s going to give me my 'Total'. My 'Subtotal' plus my 'Tax' equals this. Happy days. Let's see if it works. Let's say you've done this, you're going to now do my Photoshop course. I've got one of those too. But that's super awesome, and it's super expensive. Say, it's '700'. You can see, it's calculated it. 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: All right, we're going to look at... creating a template for this quotation... or an invoice, it doesn't really matter what it is. You're probably sick of opening the last job, doing a 'Save As'... and changing it out, and hopefully not-- Hopefully remembering to change the file name. And not saving it over the last one. It's probably why you are here, because you've done that a few times... but that's the cave man way of doing it. A slightly less cave man way... is to use 'Sheets', so, this is our 'Workbook', the whole document... and you're going to have sheets within here. So what I can do is, I can double click 'Sheet1', and call this one, maybe... 'Invoice... I'm going to call it BYOL, Bring Your Own Laptop invoice... with invoice number at the end here, '0099'. You can see it up there. 'Enter'. And what I can do is I can right click this one... say I need a new one... so the next quote that I need to do, I can right click it... go to 'Copy'. Just remember to click 'Create a copy'. Click 'OK'. And we get this extra option. So it's just got appended with the number '2' there. I can double click that, and just change this... to '100'. I can keep doing that. It's kind of good, keeps all my quotations in one book. And that might be enough for you. You might go, "Job done, that works enough for me." And you just toggle between obviously the two here. We'll just go and change these. So let's say that's not what you wanted 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. So I've only got one workbook. And I'm going to get rid of the name '0099' just to make it all tidy. Now all we need to do is, do 'Save As', as a template. At the moment it's just a regular Excel document. We can go to 'File', 'Save As'. And here, under this drop down here that says 'Excel-- Yours would be defaulting probably to 'Excel Workbook'. You want to go down to 'Excel Template'. Just know this, it's going to put that, not in a strange place... but under your 'Documents', under 'Custom Office Templates'. Leave it to go in there. Give it a generic name. So mine's going to be 'BYOL Invoice'. Mine's actually a quote. So that's perfect. And I might actually call it a template just so I'm really aware of what it is. Cool, hit 'Save'. So now I have a template. Now, using this template is quite important... because you can overwrite your template accidentally. So the way to use it-- so we've created our template... what we want to do now is... we're going to close this down... and if we open up 'Excel'... what we can do, in the 'Welcome' screen here... we can go from 'Featured' to 'Personal'. And you'll see there's our quote just here. So if I open this up now... it's given me-- It's kind of generated a copy of it. You can see it's got a similar name, but it's got '1' appended to the end. And if I try and hit 'Save' now... either this, or at the top left here... or 'Control S'... it's gone to 'Save As' by default. It won't really overwrite that template. Now I go to 'Browse'... and I'm going to put it in my 'Documents'. I'm going to call this one 'Quote'... and instead of 'Template', this one's going to be '0099'. And it's just a regular old Excel workbook. Click 'Save'. So this is how to kind of 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 something differently, I close this down... I pull up 'Excel', just be careful. If I go to my 'Recent's and open this thing here... my 'Template'... I haven't generated a new document. Based on that template, I'm actually just opening that template. If I go and amend this now, and change it all... I've changed my template forever. So it's just about the way you've opened the file mainly. There are a couple of ways that make this work. If I close this down, and I find my template... it's under 'Documents'... it's under 'Custom Office Templates', there he is there. And 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 '1' at the end, and this is perfect. So it's 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 where you go to 'Personal'... or you can double click the file. You just can't go to 'Open'. Anything but 'Open', or using these 'Recent's... because that will update the template. Okay, 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 US letter or A4 documents. And making a PDF that can be emailed out as a quote, 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: In this video, we're going to look at printing... our quote or invoice on to one page. And also, maybe creating a PDF that we can email to the client. So printing's pretty easy. Just go to 'File, click on 'Print'. And pick your printer from this option here. Make sure the page size is correct. And hit the big old 'Print' button. One thing you might find is that-- My preview's looking great, but often... it can be split across two pages... and that just looks weird. So the first thing to check is that, your letter size down here... if you're in the US, you're using 'US Letter'... if you're anywhere else, using maybe the 'A4' size. Just make sure that corresponds with... back in here, under 'Page Layout'... under 'Size', is what you're using in here. So you might have opened up a template... fall in line, and it's set up to 'Letter'... but you're trying to print on A4 and things aren't lining up perfect. So just make sure those two match up. Another thing you can do to make sure everything lines up... is to set your print area. 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. If you can't see it in this format... go up to 'Home', then go to 'View'... and it will be on 'Page Layout'. Then you got to figure out what's going to go on your page. Then go to 'Page Layout'... and this option here that says 'Print Area'... So 'Print Area', then 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 this stuff. Then when you go to 'File', 'Print'... often, it will be on one page. Next up is to make a PDF that we can email out... or send to someone another way. You can do it-- there's a hundred different ways in Excel, it turns out. Under 'Print', you might have a PDF printer, like I've got. I can do it that way. I can go to 'Save As'... and I can go to this drop down here, and there's a PDF there. I can go to 'Share'... and I can go to 'email', and say 'Send as a PDF'. It's going to generate the PDF... add another step by dumping it into your Outlook... an email system. If you're using Outlook, this is really handy. I don't, I use gmail, so I have to make a PDF... and then... I have to make a PDF and then send it out separately... or attach it separately using gmail. You might have noticed, on my one I've got... a few extra options that you might not have. See this one, 'Send as PDF'... It's this one, right? But I've used the word Adobe in here. It's because I've also got something installed... called Adobe Document Cloud, I think they call it. Acrobat Reader... or I've got Acrobat Professional installed. It just adds a few little extras to this. So 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. This is a little extra one you probably don't have. So loads of extra PDFs, but don't worry, you've got more too. Let's go to 'Export'. And we can go to this one here, the one you probably have. 'Create PDF'. We can generate this one here. All roads lead to here. We've got a PDF, we're going to give it a name. The next invoice is going to be '100'. And that's it. I'm going to open mine up 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. There you go, I've got it. I can go and find it on my 'Desktop' somewhere. And I can attach it to my mail, there he is there. Great. So that is how to print, and how to make a PDF. If you're having problems... drop me a line in the comments, the questions... because sometimes it's a bit of pain getting these things all to line up on print properly. I will 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 holy crap... I've got to go off and I've got to make, I don't know, a profit/loss sheet. So when you open it up... it comes up under this featured options. And you can type in 'Profit'. I'll just type in 'Profit Loss'. And there are lots of templates ready to go. Now, you might not like the styling of them... they’re not perfect... but it might just have the calculations in there... with the formulas that you can rob out of it, say that you are-- We just did that thing with our quotes... or say you want to make an invoice... you might not like any of these... because you're going to style them all perfect... but you can open one up... and get started with this. Maybe, the only thing you want out of 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. So don't be afraid... there are lots of options in here. Sometimes, you need to use the language correctly. You might be calling it something in your country... but it's called generically something else... in the US, I think, US. Say 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 mad new Excel skills that we're learning. All right, don't forget about templates. I cheat all the time, and start with them, and work backwards. That's it for this video, on to the next one. 12. Cleaning up Tidy up messy spreadsheets in Excel 2016: So we've been given an Excel document... or some sort of export from some sort of database. It might be something online... it might be from some sort of internal system that you got in your business... but the data has been messy, and we need to clean it up... before we can do anything really. So we're going to look at the tactics for doing that. So, we're going to open up, in your 'Exercise Files'-- If you haven't got the exercise files, download them... there'll be a link on your screen. And let's open up 'Half Marathon Entries'. So we've got all the entrants for our half marathon. The times they got... it's a charity event... there's the prices, and times, and data stuff. But there's some messed up formatting. The quickest and easiest is that... its kind of ordered that along the top... instead of-- what would probably be nicer if it was ordered top to bottom. So this is pretty easy, we're going to select all the cells. So we're going to click on this one here. Go all the way to the end. And hold 'Shift', and click the last one. Where is the last one? Just there. So these are all my cells. Then I can go to 'Copy'. And it selects the whole lot of them. Then anywhere down here... or you can make a new sheet and just paste it on to this. Up to you, I'm not going to use another sheet. I'm just going to paste it underneath. And I'm going to go to 'Paste'... and there's this little drop down underneath 'Paste'... rather than the shortcut 'Control V', use this. And there's one little option, you can kind of see... a little arrow 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. And with this other stuff, I'm going to click 'cell 1'. 'Delete' all this. One thing I should probably remind you is... don't overwrite, we've only got one copy of this original data. We might do a 'Save As' so that we're not wrecking the original. I'm okay with wrecking it... because I've got lots of copies. So, I've selected all of this... and I'm going to right click any of the 'columns'. And hit 'Delete'. Cool, so we got rid of those. Phase 1 complete. Phase 2 is, there's no column heads... so we need some heading on the top that says... 'Name', whether 'Paid' or not. So I'm going to right click anywhere 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 I can just click in the next cell. This is going to be 'Entry Fee'. This is 'email'. And this one here is the 'Sponsorship'. This one here, the next one is the date they entered. So 'Entry Date'. And the last one was the time they ran... rather, 'Finishing Time'. Great, so we've got our column headings. We need column headings for lots of reasons. We got to pull charts out of this, and lots of other things. So what we might do for this top one here... is we might select all of these. So 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. So 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. I'm going to hit 'Save'. Last thing is, I might just drag these columns out here... so I can see a little bit better. They're different areas. So I can see the email addresses, sponsorships. And we go there. 'Date of 'Entry', if you see these hashes here... just means that it's only showing part of the data. Generally doesn't do it for text, but will definitely do it for numbers... because missing a few zeros off a number could be very dangerous. So what we're going to do is... do some formatting with the currency and the dates. 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. At the moment, we've got kind of a shortest version... with 4 digits, for the year... there's ones with 2 digits for the year. There's some long words... with the full word 'October' there, we need to clean all that up. Another thing is, we'll add our dollar signs to this column here... because it's our money. And it will look like... this dollar signs, all consistent dates. So let's go and do that in this video. 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 4 digits. Some of them have got them in just 2 digits. We need to get some sort of consistency. Doesn't really matter if the month is first, or the day is first. It's the same process. So what we want 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 it might work. You might have lots of problems... other problems where you've got other values... where I say, "Yes, let's just be the long date"... and it just doesn't change. So we need to do something slightly more manual. So you might have to do the easy way, and just go and change it... or this option here. So I'm going to select this one. Hold 'Shift' down 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 'Text to Columns'. It's reasonably easy. Leave it as 'Delimited'. And hit 'Next'. Doesn't matter what this is, click 'Next'. And you're looking for this one here... say, I want dates to be day, month, year, 'DMY'. If you are in America... you might go month, day, year, 'MDY', doesn't really matter. It's just going to force all these guys to be... at least text that we can adjust. Hit 'Finish'. And something kind of changes... you can see, nothing really happens... except now, when I go to 'Home'... and I go to 'Format', 'Date', 'Short Date'... they are all changing. And when I go to 'Long Date'... they all become long dates. I'll scale this up, so you can see them all. So you might have to do it 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, this 'Text to Column' option. Cool. Nobody likes long dates. And we go back to short dates. And do our currency, so, back to here. Back to 'General'. 'Short Date'. Great. I'll make it a bit smaller again. Next thing we're going to do is this one. This one here is missing all the currency. So that's '4100'. I'm just going to select this whole column by clicking 'D'. Up here, I'm going to pick 'Currency'. There we are. It's picked pounds '£' by default. You can go into here, and pick 'More Number Formats'. 'Currency', and I want... not the £, I want English... doesn't really matter, all those symbols, we'll go to New Zealand. We love New Zealand. We set the dollar signs to it there. Lovely. One thing you might also do is... can you see, it's added these '.00' to the end? That 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. So I'm going to select the whole column again. And over here, under 'Home'... just underneath, where we got 'Currency' here... you can decide how many decimal points you want to show. You can show more, in our case we want to show less. Just down to the nearest dollar. Lovely, let's hit 'Save'. And that my friends is how to format... dates and currency formats, and other stuff. 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: In this video, we want to go through, and remove any empty cells... or in this case, any completely empty row. There's two really quick and easy ways. We need to select the whole data sets. So I've clicked at the top left of my set. Come down to the bottom here... I'm scrolling down, holding '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, where it says 'Sort & Filter'... I'm going to say I want to go from 'A' to 'Z'. And it's listed everybody's name from alphabetically A, all the way through... and you notice that... those three missing blank cells... which were 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. I got it all selected. And there's an option over here. Make sure you're on your 'Home' tab. Over here, where it says 'Find & Select'... go down to 'Go to Special'. There's one in here that says 'Blanks'. Click 'OK'. It's got them selected, see we've got all selected there. And then an option up here, where it says 'Delete'. I'm going to use this little drop down, I'm going to use 'Delete Cells'. It's going to ask me what I'd like to do once it gets deleted. I want to shift the cells up, great. And just moves them all up. And we kind of end up at a very similar place. Blanks are gone... but we haven't re-ordered our list. Either way works. Before we go on to our next video, of tidying up these lists... I just want to kind of do one quick little thing. Let's say that the columns here... are in a bit of a bad order, so I want to move... 'Finishing Time' right next to the names... because, let's say that's the most important for us... in terms of this runner's time for Half Marathon. So I could select 'B', right click 'Insert column'... then cut and paste it across. There's a quick and easier way, I can click on 'F'... selects the whole column here... and I can hold 'Shift' down on my keyboard. And what happens is... if I grab any of these edges down here... black edges here, you can see, it's a little crosshair. I click and drag it. You can see, you get this little I beam... and it can go anywhere. I'm going to put it just next to the names. That's a quick and super easy way to re-order your columns as well. 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 contents. 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. And I want to remove them... but it's a big long list, I want to go and automatically check it. There's going to be an instance though where-- 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 they got a time stamp of when they were imported into the list... so there's two different ways we're going to do this. So what we need to do is select the whole data set. And we can do that now that we've cleared our blank lines. Before we had to kind of 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... go to 'Data', and 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 kind of done it okay. What I'd like to do after that is... just to kind of highlight anything, so-- That might be all you need, and you can finish this video. But let's say we want to just double check. It's quite important, I do this quite a bit for my subscription service. I want to send a specific email... to people who have signed up for my free stuff... but in different email from the people that have paid. I don't want to hassle the people that have already paid... and say, "Hey, come pay." I want to make sure that emails goes out to people that are-- So what we're going to do is-- a cool little thing to do... select 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 to 'Highlight Cells'... and go to this one that says 'Duplicate Values'. And what it's going to do is-- Click 'OK'. It's going to just highlight anything that's a duplicate. So it's not just the whole row like it was when it was deleting before. Anything that's a duplicate here, it's going to highlight. And it kind of makes it a little bit obvious that-- not to worry about these times... because obviously somebody else has got the same time as them. Somebody is also 'Unpaid'. And it's these lines here. I can see, there's two 'Haty Chavez'. And then I can kind of 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 her, or his daughter ran as well. And they ended up being classed as the same person... with different times, or maybe, just-- I have no idea. Don't need to know. It's just a really handy way to kind of double check... before you go off and commit to things. I'm going to 'undo that' and get rid of my styling. So what you can do to get around that... just to kind of make your deleted duplicate content... a little bit more robust than our first option... I'm going to select all again, go back to 'Data'. Go along here where it says 'Remove Duplicates'. And instead of saying "I want to compare all of these column headings"... and find something that matches 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 email"... because I feel that's really unique. If you have the same email as somebody else, you're the exact same person. So you might have something else-- that's the date of entry in here... you might be a Social Security Number... Inland Revenue document date, or something like that. So, client ID So 'email address', I'm going to click 'OK'. And it's found, those are the two, so it's removed 'Haty'. And there was another data option in there... so it's removed the entire column. Which one? It's removed the second one. So we had two for Haty, so it's just removed the second one. So be careful when you are just randomly deleting. Make sure you delete the stuff you want to. All right, 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... they're kind of bunched together in one cell. And presto, turn them into our two column thing. 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 I'll introduce this 'Flash Fill' to you. I like to say 'Flash Fill' loads. And in fact, 'Flash Fill' has so many other kind of cool uses. So let's do the first one. What I want to do is, I want to split the first name and last name. I'm going to select this column here. Actually, first of all, I want to separate them into two columns. 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 'A'... and I'm going to make sure I'm on my 'Data' tab, on top here. And there's one that says 'Text to Columns'. 'Delimited', click 'Next'. What spaces, what delineates these two words? In my case it's a space between them. You might have maybe a comma between them. Mine's a space, you can kind of tell down here, that little preview. Pick whatever separates them out. Mine's space, I'm going to click 'Next'. Then I'm going to click 'Finish', and magic. You can see, it's separated my 'First Name' and 'Last Name'. That could be cool if you've got maybe cities, states, and post codes... as you can separate them all out. They've got spaces between them. So that's one thing. It works good. What I want to do though is I'm going to 'undo' that. I'm going to insert another 'column'... because I want to show you something called '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 kind of goes... "Oh, you probably mean to do this over and over again, and It learns." It's pretty cool. So what you do is-- I've got 'Cynthia Wolfe' here. Let's say I want to separate them out. I can do a couple of things at the same time... though I can say, actually, I want it to be 'Cynthia', I'm typing this in. You can see, I'm using title case... so I'm putting 'Cynthia' in 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'. And click on 'Flash Fill'. And it just kind of guesses what you were doing. You can see, it's pulled the first name... and it's changed the case at the same time. Same here with 'Wolfe'. And you say 'Flash Fill'. And it goes through and reaches into these ones here. I love 'Flash Fill'. So there's lots of uses for this when you're re-ordering data. Let's say you want it to be-- still you want it to change the case... but you want it to be like this where... they're still together, like they are... but they're re-ordered so their last name is first... because it's easier to sort like that. I've switched them around, put a comma in there as well. And underneath it, I go 'Flash Fill'. Cool, huh? So we reached in, grabbed them, sorted them where I want. And you can do that with any data. Just give it a shot, it works most of the time. Make a column next to it, re-order it the way you want. Next row underneath... ...and just click 'Flash Fill', and see how it goes. That's it for 'Flash Fill'. I'm just going to tidy up my columns down, you can skip along to the end. 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 first name first, I wanted 'Cynthia'. 'Tab' across. 'Wolfe'. You can't do two of these at once. So you might just select both of these and go to 'Flash Fill'. But you can't click 'Flash Fill' when there's two of them. So you got to do this one, that one. And what I'll do is, this would 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 on to the next video. Let's hit 'Save'. I will see you in the next video, I promise not to sing the word... 'Flash Fill' in any more videos. I've got it out of my system. All right, see you in the next one. 17. Sorting & Reordering a Excel spreadsheet by name price or date: So now we've beaten our list into some submission. Now it's looking nice, the formats are the same... now you want to do something basic with the data. Just to grab what we need out of it. And the easiest way is this 'Sorting & Filtering'. The easy one might be a nice, quick one. 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-Z'... and it just goes through and puts it alphabetically. Same with last name. Spreads across the rows for you automatically. This is going to be more interesting potentially... when we get to the 'Finish Time'. So it can have the person who ran the fastest in this first one. It looks like an even tie against three people. I randomly generated this data. Three way tie for first. Sucks for Kelly Ellis, Irvin, and Doty. But you can do this for any of them. 'Date' centered, just click 'A-Z'... or you can obviously hit 'Z-A' to get it the other way round. 'Sponsorship', same thing. I'm just figuring out who earned the most. So that's one 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. It's different than sorting, it's just going to clear the ones I don't need. So I want to find out everybody who's 'Unpaid' so I can chase them. So, what we can do is... click anywhere in here actually, along the top columns here. Let's go to 'Filter'. And these little drop downs up here... these are really handy, because now I can go along... to show-- I'm going to 'unselect' it all and say... just the people who are 'Unpaid'. Now I can send these guys a group email to say, "Where's my money?" Especially, Dety Riley. That was 4 grands. This is really handy. Filters, don't worry, it hasn't deleted the data. Just means we need to turn that 'Filter' off. All comes back to life. 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 type of things. 'Filter'ing off. All right, so that's sorting and re-ordering your spreadsheet. Making it a little bit more usable. So that's going to be it for this video. Let's get on to the rest of the tutorial series. Next we do charts and graphs. I love charts and graphs. Let's go do that. 18. Repeating formulas in Excel: 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. Simple list here, what I'd like to do is... figure out how much stock I have in..... the inventory I have in stock in terms of dollar value. I want to times this quantity by the cost per unit. So, what I'm going to do is... in here, I'm going to create a formula. We always start a formula by having the equal '=' at the beginning. Then we decide which cells we want to work with. I want to work with this cell here. 'C4'. And I want to times that, so we use this little asterisk '♪' key. It's normally hiding under your '8'. And then I want to times it by... this one here, 'D4'. I'm going to click 'Enter'. So it's times that by that, and it gives me my value. Now I don't want to have to do this over and over. I could copy and paste my formula. Click 'Copy', and then down here... Oops, '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 'return' 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. And in the bottom right corner here, there's this little dot... click and drag this down. We're kind of expanding this box. And it's clever, thank you Excel. It 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'. It's done that for all of them down there. So that's a nice quick way to go and repeat your formulas using Excel. What you can also do with it is... instead of just repeating formulas, you can repeat for pretty much anything. If you're sitting there, and you're on Excel... and you're doing something like... "I wish I could do this over and over again"... it's probably that feature there. Things like the month... its's 'January', if I click, hold, and drag this down... it's pretty clever. Starts working out what you need. You can do the same left or right, nice and easy. It works with days of the week as well. 'Monday'... you don't have to put the full thing in. And drag. And any sort of numbered list... you can see this one here, 'Item 11', say we need a bunch more. Numbers, goes through and adjusts those. Doesn't work for everything, but just clicking and dragging, give it a try. Super easy for repetitive things. All right, I will see you in the next video. 19. Practise exercise: All right, so it's exam time... or just a practice... because you can cheat in this exam... because I'm not there actually watching you. So you can go back to the videos... and check, and if you can't work anything out... you can check out this one here... 'Charts & Graphs', the finished one. Open up 'Data Clean Up 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 columns. Sorry, any empty rows. Then I want you to go through and remove any duplicate rows. Let's see if we can find 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'. They're kind of worked as center. I want you to change that. I would like you to move it. At the moment, 'Wholesale Price' is here. I'd like you to move this... so it's just after 'Product Name'. You can see here, 'Product Name'. And then there's the 'Wholesale Price'. I want you drag that column across. I'd also like you to make sure that these are set as currency... rather than the wholesale price. So you can see, I want you to add dollar signs to it. I'd also like you to create four formulas. So, the first two are going to be... calculating the 'Total Sales'. There's 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. So I want you to-- In dollars. So I want you to figure out, take the 'Total Sales'... times it by the unit cost of the 'Wholesale Price'. And then give us our price here. Just a tip, remember... '=' 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' and the price. Last thing I want you to do is, I want you to re-order your list. So that the one with the most sales are at the top there. You can see, at the moment, it's this 'Paw Patrol' stuff. My son loves Paw Patrol. Re-order 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 that you made it work. So that's it for our practice exercise. Let's get on to the next video. 20. How to create a graph in Excel 2016: In this video, we're going to look at making charts, adding different labels. And we'll also look at doing some of these in-cell graphs as well. So let's go and do that now. So we're going to start with this spreadsheet... it's called 'Charts & Graphs'. You can open that from your exercise files. And what we're going to do is we're going to create a graph. You'll often be creating graphs from data like this... where there's more than one field. So what we want to do is decide what's going to go into this. I'm going to decide it's column 'B' that it's going to go in. So just clicked on 'B' up the top there. Now I need to pick some other parts. In this case, let's say I just want to pick 'Total Sales'. So I need to select him, and just this option here. So I hold down 'Control' on my keyboard. And I click on 'G' at the top here, in the column heading. And it selects both of these columns. So that's how you can decide... what data goes into your chart. Then we're going to go into 'Insert'. And there's different options up the top here. A good way to get started is this one that says 'Recommended Charts'. Especially if you're not sure, you're like... "How am I going to show this thing?" Click on that. It gives you, often, really good advice on what can go into it. In this case, this one here, 'Clustered Column'. You can go into 'All Charts', and then just... see what it looks like, as a Pie chart. Kind of working, but maybe too much detail in that one. It gives you a little preview of the actual data... which is really cool, and you can work through it. I am going to go back to 'Recommended Charts'. I'm going to use this one here. Column, click 'OK'. To get it bigger, you can grab any of these edges here. I'm going to make this one nice and big. To move it--- Be careful not to drag the inside parts. If I start dragging this inside part... it kind of works but I can often drag bits with end of the chart around. So I'm going to 'undo' that. So when you're moving the whole chart, grab the outer edge. That ensures you get the whole chart coming along with this. Next thing we want to do is, we got to decide-- It's given us the right kind of titles... 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. This might be 'Toy Sales'. So you can just go over the top. It's kind of giving you some place holder 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. 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... you can see, there's one legend that appeared. Let's say it doesn't-- 'Axis Titles'. You can see along here, this might be profits for... this might be, you know... 'May 2018'. You can add all the different elements using this '+' button here. Now to change the styling of this, there's a kind of a generic way... and then there's more specific ways. The generic way is... if you have your chart selected, grab the outside... make sure you're on 'Design'. You can see there are some 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, okay, you can go through that. And you can also pick 'Change Colors'. Say you want to change the color of the bars-- all the colors... click on 'Change Colors'. 'Change Color' is a bit weird. I don't often use this one because it changes the group of colors. There's like a whole mix up here. I want to pick specific colors rather than this. So these are the generic changes along the top here. Kind of overall big changes. Let's say I just want to make some smaller changes. Like, I want to change the color of this bar, maybe make it a bit whiter. 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, so I've got my 'Fill'... I've got 'Effects', and this last part here... where we can play around with this series option. This first one here, I want to change the 'Fill'. Let's go to 'Automatic'. I want to say, I want to give it a 'Solid Fill' of-- And see here, it says 'Color'. Drop that down, I can pick some of the paint colors... some of the standard colors. For me, say I've got a corporate color that I want to use. I'm going to 'More Colors'. And I need to type the RGB value. If you have no idea what your company's RGB value is... you might have to reach out to your marketing department... or your designer, or somebody to work out what your RGB value is. Just type it in there. What if you just want a pretty color? You can just drag this target around. Pick it. I'm not sure what color it is. Click 'OK'. I've done it for one of them. What we might do is, instead of doing it for just one... I can click off, and click on this one. I've kind of clicked 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... I can go to 'Fill', I can go to 'Solid Fill'... and because I've mixed that color already... it's done recent colors. So it's done through the whole lot. Another thing I might do is, with these bars here... with them all selected, you can see, they're all highlighted here. Move on to here where it says 'Series Options'. And 'Width Gap', I end up doing this quite a bit. I like the gap between to be smaller. Just, I want a fuller 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. Couple of other things before we leave is, with your graph selected... move over to 'Design', and you can go through and 'Change Chart Type'. Say that this column's not working... and the bar's going to be there for you. Click on 'Bar', and we're going to run it this way. The other thing you might do is-- You might find that the axis are just along the wrong way... it would be better to have these guys along the top here... and these down the bottom. So you can have it selected... and there's one here that says 'Switch Rows/Columns'. This kind of flips it up the other way. So it depends on how it's looking... you might have to play around with this 'Switch Rows/Columns'. Another thing before we go-- I'm just going to move this out of the way. Remember, I'm grabbing the edge of it. Sometimes it's just nice to have... not a full graph, but just a nice little visual. So what we can do is, I've selected it off. And 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, called the 'Quick Analysis'. So I click on 'Quick Analysis'. There's some cool stuff it does, you can see... A bar chart, it's actually putting 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, that 'Paw Patrol' sales... are by far a lot more than the rest of them. And there's different options here, the 'Top 10%'. Some nice little bits that go inside of here. So I'm going to leave my data bars on, they're kind of helpful. So that is it for creating graphs. We'll look in the next one how to 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: In this video, we're going to look at exporting this chart... to lots of different places. We'll look at-- what have 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 it vector, and editable. And the same thing for InDesign... if I can find it... InDesign. Also... make a PDF version that we can share on all sorts of other things we might need it to go. All right, let's get exporting charts. So we'll start with putting it into other Microsoft products. It's really easy. You click on the edge of your chart. And you use 'Copy'... or 'Control C'... and then jump to the product you want to put it into. Let's jump to Word first. And if you use a shortcut, 'Control V'. It goes in, and I put a 'return' in. There's another way of putting it in. At the top here, where it says-- under 'Home', 'Paste', this little drop down... there's pretty much two options. You can see at the end of that... 'Use Destination Theme & Embed Workbook' is the important one. The next one is somewhat similar, it says 'Embed Workbook'. This one here says 'Link Data'. And the next one says 'Link Data'. So, you've got two options, linking data... it's what happened to this first one. But if you just use your shortcut, 'Control V'. It's going to link the data. It just means it's still connected to Excel. So if you make changes in Excel, it's going to adjust in here. And 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 'Embed'. So I can use this first one here that says 'Embed'. Looks exact same in PowerPoint, no difference. So let's switch over to PowerPoint. Again, I'm in PowerPoint... and I can just use 'Control V'. One option. I can drag it to be a little smaller. You can see a lot of the same features up here, from Excel. So doesn't really matter if you want to add chart elements in Excel... or do it in here. So I've got this one option... then I'm going to go to the one that says 'Embed'. By default, it links, and I'm going to use this one that embeds it. So we got two options here. Let's check what happens when I change them in Excel. So, in Excel, let's make a big change. This guy here, the 'Paw Patrol' sales. Let's say that there were mistakes in. We want to go through-- it was only '6' rather than... the 1000s that we had in there. You can see, it's all changed in here. 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. We can see in here, the one that's linked, and the one that's embedded. So it's really easy to go between Microsoft products, obviously. Next we'll look at getting stuff into... some other desktop publishing documents. We're going to look at Illustrator and InDesign by Adobe. So the technique is very similar. So, I'm in Excel, I'm going to click on my graph. I'm going to hit 'Control C' or this 'Copy' button. And I'm going to jump into, let's say, Illustrator first. If you've never used Illustrator, it's just a desktop publishing program... or graphic design program, you can use it for lots of things. And all I'm going to do is paste in here. So 'Control V', or 'Edit', 'Paste'. And the cool thing about it... is that it's still kind of editable, and it's still vector. So it's scalable, so in Illustrator... I'm going to hold 'Shift' and grab the corner here. And I can scale it up. So if you're an Illustrator user this is going to be useful. If you're 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... they're kind of all grouped together. Right click them, 'ungroup' it. And they're actually bits. They're little bit hard to play with... because there are bits all over the place. I'll delete you. And it's that bit there. So it's not perfect, and not fully editable... like it is when we go to other Microsoft products... but still pretty cool. What you can do is grab the white arrow here. And click on these columns here. And you can go through and decide that... actually I'm going to use one of my colors. And you can change these colors here in Illustrator. Very similar if you want to do it in InDesign, so jump into InDesign. So in InDesign, it's same, 'Control V', or 'Edit', 'Paste'. It's slightly different. What you'll notice is that... it's 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 still see, it's vector. You might be seeing it a little bit blurry. You can go to 'View', and say... 'Display Performance', and make sure it's set to 'High Quality'. It will look nice. You can scale it really big. The only trouble is you can't do the adjustments like we could in Illustrator. So we can't go through and, like-- "I want to change this to corporate color, pink." So it's all kind of fused. So, ways around that, it's pretty easy. You can go into Illustrator. You could make the changes in here... then copy and paste from Illustrator into InDesign. Even better, you can just have Illustrator open. Say you want to go straight from Excel to InDesign... but you want that control... so what you do is, you paste in here... then you just copy this version which has ended up here... and copy this into InDesign. And paste it in there. So I can select on it... right click, and go to 'Ungroup'. And you can start to see... I'm starting to get the little bits and pieces like I did for Illustrator. A little bit hard to work with... but you can start adjusting them. Same thing with the white arrow in here now. I can click on these guys... these guys here... and I can start adjusting. Super easy to work with them. So I can select on this and pick a new 'Fill' color. So there is ways of making it vector and adjustable in InDesign. You just got to copy and paste it... enter Illustrator, and then copy that into InDesign. One last thing we'll look at is... say you want just a file format, you want to copy them straight in here... more like a PDF, to go into other products... might not be some of these Adobe ones, might be something completely different. The easiest format to do that is a PDF, so let's go and do that. So in Excel, what you need to do... pretty much the only thing you need to do is to have your charts selected. The one you want to export. If you got lots of charts, just pick the one you want. And then go to 'File', 'Export'... and 'Create PDF'. Great. And in here, there is... actually, by default it's going to work... but you can go check in here in the 'Options'. And it says 'Selected Charts'. But that's by default it's going to do it. And give it a name. And save it, and you got a PDF. So I'm going to have my 'Profit Chart'. And this is going to be my '2018' one. Hit 'Publish', and now I've got a PDF that I can use. It's scalable, it's vector. You can use it for lots of different things now... other than the Microsoft and Adobe world. 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 everybody's been waiting for, it's Pivot Table time. What is a 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 wanted to show you a bit of a demo. So, on this other sheet down the bottom here... I've broken down the data to show me the 'Products'... by the 'Years', and how much they were sold. I can break it down even further... and I want to say... the 'Products' that were sold by just 'Daniel Scott', the sales person. Or maybe, 'Ben Samuels'. So it's a way of taking this data... and feeding it into a sheet. And we have to kind of re-jig it to prove a point... to extract data, to see it in a different way. So let's go off and build our pivot table. To create a pivot table... open up the exercise files, there's one there called 'Pivot Table Data 1'. Open that up. And what we need to do is, have our cursor anywhere inside this data set. 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'm going to go to insert, and 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 'Sheet1' down here. And you can have multiple ones in here, it's just like pages in a document. So it's going to create a new one of these. I'm going to click 'OK'. And you'll see down here, here's 'Sheet1' still, with all my data... but I've got this new sheet too which is going to have my pivot table on it. Let's just name it, let's double click 'Sheet1' and call this one 'Raw Data'. And 'Sheet2' is going to be my 'Pivot Table'. Just so we know. What we need to do is, we're going to use these 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. The most important ones are rows and values. You'll use these the most. We'll use columns a bit more, and filters even less... but rows and values. So rows are this. So the ups and downs, the Y-axis, the vertical. 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's listed them down here. So we're cleaning up our data from that nice, big raw data set... and just pulling the bits we want. So I want to know to know the 'Products'... and I want to know how much was 'Order'ed, 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. Let's say I want to get rid of the 'Products'. I can click on it, and there's one that says 'Remove Field'. I want to find out actually-- From my 'Sales People', there he is there... under 'Rows'. I can see, the rows down here... this is the totals that they've earned as sales people. I can 'bin' that one as well. I find rows and values... often gives me most of the things that I want. Let's say we're 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 the values. Columns is kind of 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 'Customer1'. And I'm going to drag in my 'Products'. And in the columns here-- so that's 'Products', but what I want to do is add some columns along here. 'Q1', 'Q2', 'Q3', 'Q4', so I can put in 'Quarter'... and it breaks down my 'Apples', and when they were 'Sold'. So, in 'Q1', 'Blueberries'... were sold, but there were no 'Apples' sold in 'Q1'. It might be nicer for this one, instead of 'Products'-- I’m going to remove this one, I'm going to say 'Sales People'. So there's 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 sales person on the planet. Good work, Dan. Poor Tayla! But that's all right. So that's a pivot table. And there's a lot of-- When you try to work it out... you'll know that dragging it like this means 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. Kind of, doesn't work. Kind of a strange looking pivot table. So you end up swapping these around, so don't worry. Often, their values are the same. It's the price, the quantity, the units sold. It's their numbers, it's their digits. And these columns and rows. Rows mainly, and then columns to kind of break out of it with more detail. Now the last one here is filtering... and I find that 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. So what I want to do is, I want to kind of-- I want to break it down, actually I just want... the results for 'Apples'. I don't want the rest of the data. So instead of filtering it... what I can do is, you can see, here it says 'Row Label', this drop down here. And I can untick 'Select All', I just want to show 'Apples'. And it breaks down that content a little bit more. And 'Apples' were only sold in 'Q3'. Or I can go into here, and say, actually not 'Apples', I meant... I want to check on how 'Boysenberries' are going. Boysenberries were sold in all four quarters. And it's their grand total, so we're kind of filtering down. So you can use these little filters. It doesn’t matter if you're doing it up here. I'm going to turn all these back on. And you can see, 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 12 totals. So you can do these filters that are within the labels here. I'm going to turn all these back 'on'. So when's a good use to use these filters? It's 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 you want to filter by the 'Sales Person' now... but we can't because it's not there. So this is where filters are useful. So in here, I'm going to add 'Sales Person'. You see along the top here, there's this extra filter. Something weird plays for me here, I don't like it sort of at the top here. Drop down here, and I'm going to say, I want to find what Daniel Scott's doing. It's kind of this third filtering. I've got my 'Quarters', I've got my 'Products'... but I want to filter it a little bit more. And I want to break it down by 'Sales Person'. Now, I don't really like filters... just because there's a nicer way of working with this same function here. So I'm going to drop this down, and go to 'Remove Field'. And we're going to use something called 'Slice'. Or 'Slicer'. I clicked in my table here... I'm at 'Analyze', and there's this one here called 'Insert Slicer'. I'm going to pick the exact same thing I had before, 'Sales Person'. And it gives the exact same details, but on a cool little push button version... rather than this ugly thing at the top that's insignificant. So I can say, actually I want to go by Daniel. I want to figure out what Tayla's doing. And I want to see what Ben's doing. The same thing, it's a filter. They call that a slicer. And it's this cool little box out here, you can click multiple things. I want to find 'Ben', so there's this option here. I can move out 'Ben' and 'Daniel'. 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 that gets rid of the slicer. You can have more than one slicer. You can puddle 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, what I'm going to do is I'm going to pick my 'Clients'. You don't see 'Clients', it's 'Customers'. Drag him into here, get rid of my 'Products'. And I want to find out their-- 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. And I can either just look through, and see where this 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 largest to smallest. These guys here, 'Fabrikam'... have been the best client for me for that data set. But say I want to break it out a little 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 little labels... they're cool for re-sorting things, but once you're done with them... they just kind of confuse the pivot table. So, under 'Analyze'... there's an option here that says... 'Field Headers', and I'll turn this off. The graph's still perfect, except you don't have... these little things you can re-order by. And nothing, it just cleans up the 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... it's this option here, 'Field List', you can turn it on or off. Also, if you lose it, it's probably because... you got your cursor clicked out here. Click over this, double check that's on. The other thing, and probably the most important... and I like to save this till the end... is that, if you've updated 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 put that to '0'... I've copied that across, lots of them are '0' now. Even more to make it more obvious. 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'. And it will go and re-jig it. So that's a pretty big one for the pivot table. A big marker right next to your desk. Raw data did not update 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. So, I want you to go through and create a quick little pivot table... just to show me. There's a couple of ways of doing it... and how to display it, so I'd like you to display it... and send me little screenshots. Stick it in the comments, or links, any which way... Dropbox, any which way you can. I'd love to see it. 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: Hi everyone, welcome to this tutorial. We're going to look at making our profit/loss spreadsheet... a nice, simple one. And it's going to do cool things, like down here, I've got a 'Sales' tab... and allows me to do things like data validation... which sounds really scary... but it just means, if I type in 'Dan' here... it says, don't put Dan', put in a date please. And when we get across to here, when I'm putting in the items that I've 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'. A 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 all my 'Sales'... my 'Cost of Sales', 'Net Profit'. And also, a pretty looking graph. So 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 reasons other than to satisfy my designer urge. I'm going to make it a 'blue' background... I'm going to have 'white' text, make it 'Bold'. I'll make this a bit higher. And in this cell here, I'm going to tab across a little bit. And I'm probably going to get it to center inside this cell. So this is my 'Sales'. Inside this sales document, there's going to be the date the sales were made. And 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-- I'm going to stretch this out a little bit. You can do it all at one go... by selecting all three of these columns, and dragging any one of them. And they all get a bit bigger. So, 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... and we want to make sure they don't go and wreck it. So, we're going to give them some... some boundaries. And that's called data validation. So what we're going to do is, in this column here... I'd like to say, all the things in this column have to be a date. And we do that by going up to 'Data' along the top here. And you're looking for this one that says 'Data Validation'. So, click the drop down, click 'Data Validation'. And it will default to here. What we're going to say is... at the moment it says, you can enter any value here, no problem. What we want to say, actually it needs to be a date. You can see, it could be a 'Time'... we'll look at 'List' in a second... it needs to be 'Decimal' point, or 'Whole number'. You can give it a specific value that it has to hit. I want it to be definitely 'date'. And I'm going to say, I just want it to be 'greater than'... and it could be any date as long as it's greater than 01/01/2000 Just a random date, as long as it's above that. And it's a 'Date' format. Click 'OK'. So what happens is, if I put in a date here... '01/01/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. You can retry... but you can't go any further, it doesn't allow you to do it. So 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, this one here which goes to 'Stop', there's these other two. 'Warning' is like a 'Stop'... but you can override it. And the last one here, which says 'Information'... it's more just a general kind of... frequently asked questions kind of thing. So let's quickly look at 'Warning'. Let's type the error message, so, let's say... 'Date invalid'. 'Please enter date into this field.' I spelled field right. So that's going to be the pop up 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 'Dates Invalid'. Please enter a date, that's the pop up that comes... to the person that's using it. So the last option in here - I'm going to hit 'Cancel' on this one. - is I'm going to select all my cells... and I'm going to say, the last one, 'Data Validation'... we just had a little look at it. Here you are. And the message in this one's going to be 'Information'. Same sort of 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. It's up to you how you work it, I'm going to put mine back to 'Stop'... because I need it to be a 'Date'... because I'm going to use a report at the end of this tutorial... that's going to show the dates... and what money we've earned for different dates of the month. So with it all selected... back to 'Data Validation', and I'm going to say... it will not go forward unless the dates are put in there properly. Next one, easy one... under 'Price', we're 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 'Accounting'. Actually I need mine to default to £s... so I need to change mine to $ sign. This first one, I'm entering off my bank statement... and it was on this date, it will cost this much, and the item. So now, 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 10 different things. We don't want people to just wily nily adding the name of it... because when it comes to creating graphs... or tracking data... we need to have consistent naming. So what we're going to do is create a nice little drop down list so that... people can just choose from it. Then it's going to be exactly the way we want to display it. So, to create a drop down list is pretty easy. You create a list somewhere, could be on an extra workbook... sorry, extra worksheet. So we've created an extra sheet here... but what we'll do is, we'll just create it to the right here. We'll call this one our 'Product List'. And we sell courses that are Photoshop courses. We sell InDesign courses. We also sell Illustrator courses. And of course, Excel courses. And Word courses, and all the courses. So that's going to be my list here. So I want people to be able to pick from this drop down here. So what we're going to do is, in this list here... I'm going to select them all. And we go back to 'Data Validation' like we did for our date. So we say, you have to pick 'Data Validation' here. You have to pick from this list. We just used 'Data' a second ago. So, pick from this list, I would like this 'Source' to be... so click in here. I'm going to say this source, please. This is where we're going to pick our list from. Click 'OK'. And you can see, this little drop down menu appears. I'm in here, and this is for my Photoshop course. So let's go through and add a little bit of fake data. This is going to be a different month. And the same year. And we've got another one, that was-- Another statement for $500. And this one here is going to be InDesign course. So I'm going to go through and add a bit of data. Tayla would fast forward this little bit, while I go and do this; boring. So I've entered all my details from my bank statement. We've only got a few details here, yours will be a lot longer. Next thing I want to do is... I'd like to create another sheet, or another document... because this is my 'Sales'... I'd like to keep the "Cost of Sales' in another place. And that could be just below here, 'Cost of 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 could do this on your own... because this is 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 spent money on So it might be things like printing materials... the venue, the trainer cost. These are the costs involved. So you're going to go through your statement... and put that into your 'Cost of Sales' spreadsheet. Now I'd like to see if you can do it, 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. It might be telephone, might be rent, power, water. And I'd like you to create a drop down for people to pick from. I know you're not going to do that, are you? I wouldn't either, because... this thing here is exactly the same thing. I want to just go change the list in the drop down, so let's do that together. So I'm going to right click this guy here, and go to 'Copy or Move'. "Move or Copy'. And I'm going to 'Create a copy'. I'm going to move it to the end. This sheet goes afterwards. Double click this one, I'm going to call this one 'Cost of Sales' Not much difference. This one here is just the 'Cost of Sales'. The only thing I need to do in here, is over this side... I'm going to have to go through and change my list. So this might be the cost of the venue. 'Venue'. This might be the cost for, what else? the kind of trainer cost that we have... 'Trainer'. We have the cost for materials. You might have lots of other ones. So telephone, all those different ones. And in here, we're going to clear these out... but leave the data validation, so in here, the list changes. So this one here is going to be a cost. Let's say my venue cost me $150 for some reason. That was my venue. And then there was the trainer cost, which was 1 million. because trainers are awesome. So we go to this drop down. Change it to 'Trainer'. This one here, we're going to go to... this was some printing costs, cost us $20. And the telephone cost us $30 for the month. And that's-- that field there we don't need. So that is my cost that come into the business. Down the bottom here, I'm going to rename this one here 'Sales'. I've imported my 'Sales', the 'Cost of Sale'. And now I would like to build a little dashboard... to show me my profit and loss. So we're going to create a new blank sheet down here... by hitting this '+' button. This one here is going to be called my 'Dashboard'. And what I might 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 in to add a total. And I'm going to fancy it up a little bit. For no reason, remember. Actually I'll do it for this whole column so it looks nice. And I'm get him to 'center', and I'll get the 'font' to be a bit bigger. And I'll 'indent' within. Nice and finished. 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. So, click the cell I want it to go into. I'm going to go to 'Insert', 'Pivot Table'. Now I can't see it here. All you need to is-- I want to go to 'Sales', and grab all of this. Then click 'OK', and it jumps back to here. Back to our 'Dashboard'. And I want to show my items in the rows... and my price, and the values. I'll do the same for showing the cost of those sales. So 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. Click 'OK'. Same thing, I want the items to go in the rows. And I want the price to go into values. Yes, that is my 'Sales', and 'Cost of Sales'. We're going to have to play with the labeling a bit. You can see here, I'm going to stretch this cell. I'm going to call this one 'Sales' instead of.. 'Row Labels'. So this is going to be 'Sales'. 'Sales'. Some of the prices are probably not what I want, I want... let me do 'NZ$'. I'll do the same for here, 'NZ$'. I'm going to select this whole column, and say... actually I'd like you to be 'Accounting'. And I'd like it to be 'New Zealand $'. Great! This one here, I want to be 'Cost of Sales'. And I would like to start doing some totals. At the moment, 'Grand Total' seems a bit-- These are just 'Totals'. Not 'Grand Totals'. What I'd like to do is, look at what my 'Net Profit' is. Expenses, of my sales. So we're going to go into 'Net Profit'. And what we'll do is, I would like it to-- I want to create a formula. So '=' first, and I'd like this, '-' the cost. 'Return'. And my 'Net Profit' is currently -$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 say 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've mis-entered this as 3000... because it's $30,000. Let's say it's just $300. And if I go back to my dashboard, nothing changes. So, remember, you've got to go and refresh that data. We did that in our pivot tables. But there's something else that's going to happen as well. So, let's say the cost-- I'm clicking in my 'Cost of Sales' here... and I'm going to 'Analyze'... and there's my 'Refresh' button. Look what's going to happen. It does refresh. Get the right number in it, 300... but it's kind of re-sized the columns as well, so I'm going to 'undo' that. And what we can do in here, 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 'Autofit Column 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 'OK. Now if we hit 'Refresh'... it's adjusted... and our 'Total' is adjusted... and we had a 'Net Profit' of $868. In the money. Again, for no reason, 'Center', 'Bold'. 'White'. I'm going to-- What you can do though is... Refreshing it is a bit of a pain. There's something you can do, click inside your table. And go up to 'Analyze'. Go to 'Options'. And along here where it says 'Data', there's an option which says... 'Refresh data when you open a file'. So at least when you open it, it goes and checks again. Do the same for this one here, so you need to do it for both of them. I'd like to go to 'Data', and say... 'Refresh this data when you open it'. 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... 'Autofit columns on update'. So I did it for this bottom one, or maybe did the top one. Next thing I want to do, maybe one of the last things... is just-- my dashboard looks kind of cool. So I've got my 'Profits', 'Losses', and 'Totals'. I want to show a nice little graph in here. So what I'm going to do is... I'm going to... click in this top one here... because I want the graph or charts to be from my 'Sales'... I don't want to show my cost, you could do the cost one as well. And up here, where it says 'Analyze'... there's one along here that says 'Pivot Chart'. It's going to give you a chart based on the pivot table. It's given me default columns... which is perfect for what I want. And this is just going to give me... a nice-- oops, 'undo'. And we're going to grab the outside white bit. And this is going to give me my 'Sales' along the top here. So this is going to be 'Sales'. 'May 2017 Sales'. Nice! You can do the same thing for the 'Cost', just to see what they're like. Maybe make a different kind of chart. Last thing we might do - I'm going to close down the 'Charts'. - is... I don't like these little lines everywhere... so this is going to be my-- My dashboard is kind of my presentation sheet. So 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 this off, so we 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. There is a more advanced tutorial series... if you want to go do that one as well. But that's going to be us for now. Bye for now. And I will hopefully see you in another course. There's a bunch of other courses. There's Word, and PowerPoint... and Access. Plus a lot of Adobe products... like Photoshop, Illustrator, InDesign. So go and check those out as well. 24. Excel 2016 Cheat Sheet & Shortcuts: Hi there, my name is Daniel Scott. And this is our Microsoft Excel 2016 cheat sheet. Check out my full Excel course on bringyourownlaptop.com Before I forget, there's also a printable PDF version that you can print off, and stick next to your desk... and look all awesome. It's on bringyourownlaptop.com/resources So the first tip from our cheat sheet is to insert a column. I need to add my headers, so I'm going to click in this top row here... and go 'Control', Shift', and hit '+'. It allows me to put in either the rows or the columns. Next tip, I'm going to type in 'January', or just 'Jan'. And if I grab the bottom right square, and drag it across... Excel's pretty clever, and knows I mean this string of dates. That works the same for days of the week. If I type in 'Monday', or 'Mon'... and I drag it out... it puts in the days of the week as well. Thank you, Excel. Next cheat is, selecting all these... I want them to be currency... because pretty much they're always a currency in Excel. So, 'Control', 'Shift', and hit '4' on your keyboard. And that will put it to your default currency. Mine's set to £s at the moment, yours might be $s. Next step is, say you want to select the whole bit of data that you've got. Mine's pretty small. Yours might be huge, lots and lots of rows and columns. Just hit 'Control' 'A', and it selects the whole thing. Let's go and set it into a cool little-- under 'Home'... along here, where it says 'Format as Table'... I'm going to pick one of the pre-defined ones. Click 'OK', and it's going to make it a pretty little table for me. Next shortcut is 'Control', and then 'Tab' on your keyboard. Normally, just above caps lock, on the left. And I'm going to click that, and it toggles between any open Excel files. So if you've got a couple of those 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' ';' And it's put in my exact today's date. So I'm using my 'Control' 'Tab' again... to jump to my next open Excel document. We want to do a couple of things. One is, I'm working on this long document, it's pretty long. I can double click the bottom of any cell. It jumps me all the way to the end of that column. But wouldn't it be handy if I had my header rows along the top here... because I have no idea what maybe this number is... while I'm all the way down there. So what we can do is... go to 'View', 'Freeze Panes'... and here I'm going to freeze the top row, you could do the first column. I'm going to do the row. This means, when I scroll down, hey, look at that, stuck at the top. You can unfreeze it by going to 'Unfreeze Panes'. Another handy cheat sheet for working with this type of data. I'm going to select all of it, 'Control A' grabs my whole bit of data. And I'm going to go along to 'Home'... and I'm going to go to this one that says 'Sort & Filter'. And I'm going to hit 'Filter'. That's all I need to do to turn on these cool little drop down menus. This means that I'm in need to hunt out the people who haven't paid. So I'm going to drop down this menu, and 'unselect' all. And click 'Unpaid'. And I'm going to find these people. And try and wrest all my money from them. That is the sorting and filtering. So I'm going to 'undo' that to turn my sorting off. Last thing I want to do is something called Flash Fill. It's going to be the last of our shortcuts. And remember, in this column here, I'm going to hit 'Control' 'Shift'. '+' to insert a column for me. Thank you, very much. And Flash Fill does some cool stuff where-- I want to join these two names... because at the moment they are separated. And what I can do is, if I type in... Gwendo-- Man, I picked the toughest name of them all. And I put it how I want it. That's how I want it, please. And what we can do is, just underneath here, we can head under 'Data'. There's one called 'Flash Fill'. And you see, it goes through, and it knows what I mean now. And starts joining them all up. Flash Fill can be used for all sorts of things. It can be used to pull these names apart, as well as putting them together. Now I said that was the last of the cheat sheets, but it's not. Go to 'File', 'New'... don't be afraid to use the templates. There's so much in here, if you need an invoice... you can follow my course and do it, or, you can cheat. Just go and find an invoice that looks kind of close to what you want. Switch out the logos, and you're away. Now remember, this cheat sheet's just a little bit... from my longer course on learning Excel. Check it out on bringyourownlaptop.com Also remember, on that same website... there is a resources tab along the top. You can download a PDF... with all of these tips we just went through, in a nice printable format... where you can stick it up next to your desk. My name is Dan Scott, and I will see you later.