Microsoft Excel – Full Tutorial 2019

(light music) – So what we’re going to be looking at is a blank workbook But to show you a couple different options on how to start Let’s go down one from home and left click on new If there’s something specific that you’re doing, let’s you’re creating a budget for yourself or you’re making a calendar, then starting with a template is going to save you a lot of time You have a budget here, you have a calendar here, it’ll be pre-formatted, you’ll be ready to go But since we’re going to be building a spreadsheet from scratch to show you how to do it, we’re going to left click on blank workbook and it just gives you a blank canvas We’re going to zoom in a little bit by going into the lower right hand corner and left clicking on the plus sign, we’ll have 200% and this’ll just help you to see it a little bit better and now you’re ready to go Each one of these rectangles is called a cell And you activate a cell by left clicking on it with your mouse or you can navigate through them with your keyboard arrow keys You press enter, it goes down and you press tab and it goes to the right So each one of these cells is referenced by the column letter first so this is D and then the row number, and this is 4 So we’re going to make D4 the active cell and we’re just going to type in a label, we’re just going to start typing We’re going to say amount So we’ll press enter and the active cell goes down one and so now we’re in D5 and we have a label in D4 So let’s just type in a few numbers here, we’ll go four, five, six You have a label which is just a string of letters and you see Excel automatically align the values to the right and the labels to the left This helps you to see what’s going on in the spreadsheet And when you’re working with data there’s an important concept going on here called a range So the range where your values are right now is D5 and I’m going to hold down the left mouse button and go all the way down to D7 and if you look in the lower right hand corner, Excel is already doing some calculations for you without you doing anything It’s averaged these amounts, counted them and summed them I could also right click on here and change what it’s doing But it being set to average count and sum is usually pretty sufficient What’s to the right of this is that you can switch it to a page layout, so if you’re getting ready to print this you can see what it’s going to look like including the headers and you can see a page break preview where you can move around these page breaks But if you’re not going to print it, the only view that you really need is a normal view and let’s say that you want to add these numbers and show the value in cell D8 I’m going to left click on cell D8 and there’s your shortcut for summing We’ll get into how to build formulas later in this tutorial but for now the most common one, which is sum is already up in the ribbon waiting for you, it’s, they call it auto sum If you left click on it, it’s smart enough to know where continuous data is around it so it assumes that you want four, five, six and if you’ll notice it’s designating this range and it’s putting a colon in between the two cell references so it’s saying we’re going to sum starting at D5 which is here, going all the way down through D7 That is what you wanted so let’s hit enter and you’ve done your first function Four plus five plus six, hopefully is equal to 15 Now we’ll show you the next concept in spreadsheets that makes them so efficient to work with is that if we have another column of data, let’s say those were amounts, let’s say, let’s get really creative, this is more amounts I’m going to left click in D5, select this range I’ll go up to the clipboard area, I’ll copy this range Come into E5 and we’ll paste it But I’m going to take the six, I’m going to type over it, I’m going to change it into a nine and then let’s go back into this sum function, I want to copy it into E8 so I want to end up with totals for both of these columns what you could do, you can copy and paste it but you can also go into the lower right hand corner, if you see the little square on the rectangle and when you hover over it, your mouse turns into a plus sign Left click on that and hold it and just pull it over to the right That’s going to copy the contents which is the sum function but the big thing that it did, the most important thing that it did, was it adjusted the cell references So if I left click in D8, it’s summing the amounts in column D but if I left click in E8, it’s summing the amounts in column E So that’s what helps you work around spreadsheets quickly and efficiently is that these formulas are dynamic and they move when you move And we’ll dive more into functions a little bit later in this tutorial and now since we’re just focusing on ways to get data into a spreadsheet, another way that you could do it is if you already have data, you can copy and paste it into your spreadsheet

So I just found a random webpage online that has a table of data in it and let’s select all of this data Right click and copy and then we’ll go back to the spreadsheet I’m going to start it in cell G5, I’m going to right click and paste, you have two options here, whether or not you want to keep the formatting of the text or just paste it in with the default formatting of the spreadsheet, I’m going to choose the second option just so it looks nicer and something special happened here too, so on the web this was in a table, and Microsoft Excel also figured that out so each of these values is in its own cell And because they’re in their own cell, now you can do some operations on them so if you go into cell H9 and you click on auto sum again, you can add these values Now if you’re really paying attention, you probably wouldn’t add latitude and longitude and elevation together That makes no sense (chuckles) But just go with it, okay? So let’s say we think this worksheet is getting cluttered, right, but we want to add more data, so you can come down in the lower hand corner, you’re working right now on sheet one, this is called a worksheet The entire file is called a workbook You can add another worksheet into the workbook, so we’re going to left click on this plus sign, adds an entire new clean sheet, you can start over on this sheet and when you’re using your formulas, we’ll show you later how you can reference back and forth to add values, subtract them, whatever, do some operations on them from one sheet to another If you get a lot of different sheets into here, you can do things like, put a custom name on the sheet Let’s say this one is raw data And you can also color them So maybe the green worksheet’s for the ones that you’re done with And the red are a work in progress So let’s say we’re done for now, we’re going to come back into this file later The file that I’m working on right now has auto save turned off, that’s going to vary based on what your configuration is but if we need to save this file, we’ll left click on file and let’s go down to save as and you’re going to have several different options here It’s going to depend on what environment you’re working with but if you’re working at home, the best practice would be to save this in your one drive So I left click on one drive These are the files within my one drive If you give it a name here, when it saves it to your one drive, it’s also going to be backed up to the cloud But you can also go old school, right? And just save it on to your hard drive Or if you’re working at a business, this would, you could select the shared drive or to a different place on the web We’re going to save this to my one drive and now it’s saved and you’ll notice that auto save has been turned on so as I work with this going forward, it’s going to automatically save the changes to my one drive and those are going to be synced into my Microsoft account So if I go up to the top of the file and I left click on the name, it’s going to show me where it’s saved and you can also come in here and revert to an earlier version, so this’ll be the version history of your file If you want to go back to three days ago, you could backwards and undo your latest changes (light music) So I’m going to select this table and for now going to go to the home ribbon, going to left click on clear and I’m going to clear the formats that’ll bring this back down to raw data So let’s left click and select the range of B3 through C3 and we’re going to do some formatting with just these two cells The home ribbon in Excel is categorized by different activities that you want to do and right now let’s say we just want to concentrate on formatting the fonts so we’ll go to this box that’s the fonts You can expand these boxes also, there could be some more options when you left click and expand them but your most common options are going to be showing So here’s where you would do the basic things like changing the font family, changing the size of the fonts and if here we want to indicate that these are headers and they’re not part of this data, it could make it a bit more obvious, let’s say if you made them bold and let’s underline them so instead of underlining the fonts themselves, what we’re going to do is put a bottom border on those two cells So if I left click on this box, these are all the different options for styling the borders Let’s just say we want to do a bottom border, we’ll left click on that and if move out of this cell, you can see what that did They actually look really small on this screen so I’m going to go back, I’m going to select the border, I’m going to down the line style and just make it thicker

Okay, we’ll escape out of that so now you can see these bottom borders to show that this is a table and that the end of the data before the sums is the six and the nine Let’s select these and put a bottom border on those as well The next thing that we’ll want to address is that, let’s say we want to center the headers, so you have a few things going on here, we talked about how labels left the line by default So the next thing that happens is that you can have an overlap so this data in cell C3 looks like it’s going into D3 It’s really not, that’s just how it’s presenting it so if I come into D3 and I put another header, let’s say it’s quantity and I hit enter, you can see that this overlap will now go under D3 and you won’t see it anymore So there’s a couple of different ways to address that We could turn on the word wrap, if I left click on wrap text I’m not going to do that right now ’cause I want to have my rows all the same size so a left click on that again to turn that off and what I’m going to do is I’m going to select both of these columns and I’m just going to double left click on them and that will auto size the columns to fit the data Let’s get rid of this quantity that I used and another way to show you some of these options is if I left click on column C and I go to the cell’s area, I left click on format You can control all of these attributes here so the column width is here You could just select auto fit column width Just if you know the trick of double clicking, that’s faster and because this is Microsoft Excel, these options go on and on and on so you could do just about anything you want in here But let’s bounce back so we have these headers the way that you want and let’s go down to these totals and let’s say these are dollar values so this introduces the concept of different data types, you could have labels like we said but the values can be numbers, they can be decimals, they could be dates but let’s say that they’re currency right now so we’ll come up to the number format and we’ll just left click on a dollar sign Now if you’re in another country, obviously this is going to be a different type of currency but I’m in the US so let’s say these are dollars and the default behavior for this currency is to add on the two decimal points, there’s a shortcut to add more or to get rid of those So if you hover over these, they’re going to tell you what they do and I want to decrease the decimals so let’s left click that twice Okay so now column B has the format that we wanted to get to We’ll left click on B7 I want C7 to be formatted the same way but I don’t want to have to go through all those steps So a shortcut that you can do is left click on the format painter So you’re going to format paint, a bunch of different cells, you double click on those, it’ll stay on but we’re just going to do one, so I’ll left click and if you notice the icon has a little paintbrush by it now, come down, we’ll left click on C7 and that will paint that format onto C7 instead of doing all the different steps to change it to currency and decrease the amount of decimals All right now we want to get a little bit fancier so let’s come over to this sample data that I put on the right We’ll go into the lower right hand corner and we’re going to move the entire spreadsheet to the right a little bit by left clicking on the scroll bar Let’s just say we don’t want to see that other data anymore So you’ll notice that now the columns start at D A, B and C are still there, you just can’t see them right now so I could reference them if I needed to But I don’t need to see them so let’s go to this table and let’s make this look nice The first thing that we want to do is the word sample data applies to the entire table So what we can do is select all of these columns so we can merge and center that label and now it’s more apparent that it belongs to the entire table We want that font to kind of fill up this row so let’s make it bold and then click on increase font size a few times There we go, sample data Now if we left click in E5 and we’ll drag this down, notice I did not select these total, they’re not part of the table that I want to format and that’s important because it’ll treat them like another piece of data if you do that These are units, this is a total of a unit They’re different things So now that we have this table selected, something that Excel can do is format an entire table at once so if you left click on format as table, you have all of these different options So you’ll notice it knows what the header row is and it can make that a more contrasting color than the rest of the table and let’s just select this option and it wants to confirm the range so it’s saying, where’s the data for your table? Since I already selected it, it’s prefilled here and there’s a check mark and my table has headers

and what that’s telling Excel is that this first row of data is not values, it’s labels So keep that into consideration, when I left click on okay, several things change here and one of them is the fact that it added filters to every column and we’ll go into these more later but it gives you the ability to sort all of this data by any one of these columns which can be super handy and also this entire table now is named so if you want to reference this in a formula, this is called table one and that’s how you would grab it So if you go up to formula, you left click on name manager You’ll see that this is table one It’s not really apparent at first but this gives you the power to point formulas at this table and then pull out particular values Let’s look into this, there’s a couple other things we need to address so the order date column, these are supposed to be dates but their format of right now is numbers Let’s go back to the homme ribbon Left click on number and let’s format those as dates and then we’ll say that column I, these values aren’t that wide so we want this row to get smaller The first thing that we can do, which we did before, is to double left click on the column but we have even more room here so let’s do something a little bit different We can take these units, left click on the bottom right hand corner of the aligned section to get more options and let’s rotate the text so we’re going to change it to 45 degrees, click okay and now we can shrink this column even more if you want to I was expecting it to shrink even more but because this filter is on here, it won’t shrink it anymore but you see what I’m doin’, right? It’s shrinkin’ a little bit more You could do that to all of these And there you go, kind of cool lookin’ But if you don’t like that, come all the way to the top and there’s a tool bar here and you can just left click on this little curvy arrow and just undo your last change, click on it again to undo the change before that, we’ll have to click one more time and there we go, we’re back to the way that the headers were before Another formatting option that’s on the ribbon is called conditional formatting Show you briefly what this can do, it’s kind of its own subject because there’s lots of different options in side conditional formatting but let’s say this table was a lot larger, you wanted to format any of the values over 20 and over, select the column, go to conditional formatting Highlight cell rules, greater than 19 Click okay, this is going to format anything that’s greater than 19 You can do conditional formatting with custom formulas So you could do something like, so let’s highlight all of the lines sold in the east region by the rep Jones If you find that you want to add a row or a column in the middle of this, let’s say we want to add one to the left of H, just select H, come up to the cell’s category, left click on insert and let’s say insert sheet columns Insert a new row for you, gave you a default header, you could change that to whatever you wanted The same concept applies to rows We’ll do a shortcut for the rows so, we’ll left click on the row and just right click on the entire row and you can insert from this right click menu We’ll delete this column for now Let’s go back over to the left so it’s a little bit easier to show a few more of these concepts And we’ll work with this little piece of data here that’s not formatted as a table through these functions so it’s more flexible If I wanted to move some of these columns around, you could just select the entire column or select the piece of data Hover over it with your mouse around the outline so that it turns into those four arrows and you can just drag these around and they’ll just flop over there See the formulas still work no matter where that is, that will sum these three numbers I want to concentrate more on this little table so in order to do that, we could highlight all of these columns Right click on them and hide them So what that does is this data doesn’t go away You could still reference that with a formula if you wanted to but you don’t have to look at it You can tell it’s still there because these column letters now go from D to L and if you wanted to select both of them, right click, you could un-hide it But this can be really handy in working with this data when you want to get some stuff out of the way, so let’s get them out of the way again, right click and hide, we’ll come over to my small table here You know what, I’m going to move this to the middle, the technique I just showed you And auto fit these columns again so it looks nicer

and let’s say this table of data is very long, so we’re going to fake it out and make it long, give me one second So I’ve added some more data to the table and what happens is when you scroll down, you can’t see the headers anymore so I don’t know which one’s amounts and which one’s more amounts unless I scroll up so what you can do if you select the row right below where you want to freeze, I want to freeze one and two If you go up to the ribbon and you go to view, left click on freeze panes and click the first option which is just freeze panes, you can also just do the top row or the first column but I added two rows here on purpose just to show you what would happen selecting the one below it Freeze panes and if I left click out of that, I scroll down, you notice those first two aren’t moving anymore So this is a concept that you can end up using all the time ’cause you can have headers but a long table of data and you won’t know which column is which unless you freeze those panes So that covers the basics on how to format your spreadsheet, how to move some data around, just make it look nice and now that you got it ready to work with, we’re going to cover cell references and how you use them and how you point at different data and you’re going to bring it into your formulas (light music) Okay so when you’re using Microsoft Excel, a very basic concept that you use is cell references So I’m going to use this table to show you four basic types of cell references We’re going to, going to need to use them for different steps to put this together So the most basic type of cell reference is a relative cell reference and I’m going to use that to figure out the operating income here so I’m going to grab gross revenue and I’m going to subtract expenses before tax so I’m going to click in E6 and you can just start typing and we’ll type C6 I use capital letters, you don’t have to but it’s the best practice to use capital letters, it’ll work either way C6 and you see it’s already kind of highlighted C6, going to say minus D6 and it’s highlighted that as well, you see the blue is highlighted in blue and maroon is highlighted in maroon so this’ll let you follow a long formula and see what’s happening I type enter, it does the calculation and since this is formatted as a table, Excel is smart enough to copy that formula all the way down So I’m done calculating the operating income and for the next calculation that we’re going to do, we’re going to calculate the tax and it involves the tax right here in cell E3 but this cell stays here while this calculation is going to move down so you need to use a fixed cell reference to reference D3 so I’ll show you how to do that next We want to do workbook, we’re getting taxed on our operating income so we’re going to do a regular, relative cell reference to E6 We’re going to multiply that by D3 and whenever you’re trying to get these cell references, you can also just left click in the cell as long as you have a blinking cursor in your active cell so I’ll just left click on D3, it pre-fills it but it pre-fills it with the basic type of reference So what will happen, if I enter this as it is, it’ll copy down, the next line’ll be E6 times D4 But there’s no tax rate in D4 So I need to fix the three with a dollar sign Now I could also fix column D and it wouldn’t make a difference in this case ’cause it stays column D the entire time So I won’t do that but if you were working perhaps horizontally and you didn’t want it to shift, you could fix the column, you can fix the row, or you could fix both So that’s our fixed cell reference, I’ll hit enter now It auto copies again because we’re in a table and that’s done and it’s calculated for you And we’ll go ahead and fill in that income, you know how to do this one, this will be E6 minus F6 and we’ll hit enter so we want to sum these three values You could use auto sum which we talked about before but the purpose of this is to show you how things are built so I’m going to type in sum We’re going to start in G6 This doesn’t need to be fixed, I’m not dragging this formula down anywhere, we’ll do a colon and we’re going down to G8 Close out the parentheses, hit enter and then we’re done with adding up the net income in our table but now wee need to pull in the other income and this other income can be found on another worksheet so if I go into the lower left hand corner and I left click on other income, other income lives in this worksheet and it’s in cell D6

Come back to C corps The easiest way to grab this is to hit the equal sign, go to where you want to reference, left click on it and if I look up in the formula bar, this is what it’s showing in the active cell which is on the other worksheet right now It put the name of the worksheet inside these, what I call, single quotes, you call them apostrophes, it’s the key to the right of the semicolon and the left of the enter key on a standard US keyboard and you hit enter and it brings you back to the worksheet where the active cell was So far we’ve done relative, we’ve done fixed and then we did a cell reference to another worksheet You could also fix this if you needed to but we don’t need to, it’s just the one and the next line that we need is spouse’s income Now I know this amount is on another workbook completely or another file, same thing, so again just start by hitting enter and I have it opened in another workbook already, that’s what you need to do Go to that workbook, you still have the active cell in the other file selected, so if you left click on D6, you can see that you’re referencing this file so we have these brackets, the full name of the file, it’s in the same folder on my computer so you don’t need any path showing here other than just the file name, the worksheet name and the cell reference This has automatically made it fixed here with the dollar signs and that’s fine, that will work for what we want to do so just hit enter, it’ll bring you back to your original workbook So now I have all three of the amounts that I want in the total household income, so we’ll just do another sum formula And there you go, so that covers the basic types of cell references, there are some more advanced ones which we’ll touch on in a later tutorial, you could reference cells inside of a named table and you can also create named ranges of cells and reference those too If you want to see that tutorial once it’s made, there’ll be a link in the upper right hand corner of this video (light music) All right so let’s change over to this spreadsheet that I built so we can just do some nice and clean examples here and since we’ve done the sum function so much, we’re going to jump in to some other types of functions We’re in the formulas ribbon and if you look at the function library, we’re going to step through some of these that you’ll be likely to use Financial functions are a little bit more involved You probably won’t be using those until later but they involve things like time, value of money, or you could calculate depreciation We’ll leave those where they are for now but we can do some logical functions So the perfect example of logical function is the F function So we’ll select that with the mouse You could also just type it in and it breaks the function down into several steps These are the steps that you would see and the little helper window below the function, if you would just type it in but here it’s saying, okay first what’s the test? So if, and what we’re going to do is if the values in column C are more than five, we’re going to call this expensive so the logical test is C5 greater than five And the value if true so this is going to be the value that it shows in the cell if C5 is greater than five, we’re going to say, yes, it’s expensive Value of false says, not really You’ll notice that when I get out of these boxes, Excel is automatically putting quotes around these values It’s trying to help me out If you’re using numbers, you don’t need quotes but if you’re typing in a string of characters like this, you should always surround it with a beginning and ending quote And because I forgot that, Excel put it on for me I click okay and then I’ll drag this formula down Just so you know before I do this, these cells are not formatted as a table I did some custom formatting on them but not the actual format as table So it didn’t automatically fill down A left click on it and I will drag it down We’ll come down to cell E10 We’re going to skip over a few of these, we’ll come back to ’em, don’t you worry We’ll go to math and trig and now we’re going to use an example of the math function Most of these are pretty involved math functions, wouldn’t be using them unless you’re in a math class or maybe you’re an engineer but we’ll pick one of them That’s kind of a combination of two that you’ve seen already so there’s a function called sum F, I’m going to cancel out of this window, sometimes I think it’s easier to just type it in Once you hit the parenthesis, then it’s ready to go so we’re going to sum this range and leave it as relative cell references, we’re only doing this in one spot And the criteria is going to be, and we’ll surround this in quotes because it’s a little operation Greater than 10, that’s really expensive And this particular function takes three arguments

so it wants the range, it’s evaluating to look if it’s greater than 10 and what the criteria is which is greater than 10 and now needs to know what the sums, so I could do something with the order quantity but I’m going to use the same column ’cause I just want to sum the amounts that are really expensive, close the parenthesis, hit enter and there you go So this selectively summed the values in column C if they are greater than 10 The only amount that was greater than 10 is 1999 so that’s all it picked up Right, scroll over to the right I have some hidden goodies over here All right so here we had a list of first names and we have a list of last names and let’s say we want to join them together and just have a list of full names So believe it or not, we would use a text function for that, right, so these operate on text Instead of using that drop down menu though, I know what I want so I’m just going to type it in I want to concatenate And you can shorten it by just typing concat and the arguments are the first piece of text and the second piece of text which I’m going to type it in here myself so these can take cell references but they can also just take values So this value’s going to be a space and that’s all so if you started with quote, it knows it’s returning characters and not numbers I’ll end it with a quote and a comma and then I’m going to grab K5 because that’s where the last name was, close it off and we’re done So we have Jill Oak, drag that down And that’s the concatenate function Scroll over to the right a little bit more A lot of times in Excel, you find yourself working with dates and they can act differently than numbers so they have their own collection of functions that work on them because you need to do different things with them Let’s say we want to count the number of workdays in between these two, and there’s a pre-made function for that called Network Days The first value is start date, second value is the end date and you can optionally specify holidays as well, we don’t need to do that so we’re done with this function We’ll drag this all the way down It’s not working correctly in R7 because I don’t have an end date I’ve left this here because I wanted to show you just another date function, but you think ab couple of different ways that we can combine these The end date here is just, let’s today There’s a function called today and it doesn’t take any values because today is today so you just open and close it because it always needs a parenthesis, hit enter and that function works and let’s double click in this cell and we’ll talk about a few things here, so this is the function as we had talked about, a cell reference but you can also just type a value in here So I could’ve typed, what is the day today? That’s embarrassing, 10/4/19 and enter and you get the same value You’re getting a warning here that, hey your value only has two digits for the year We don’t want another year, a 2000 bug put those in and I don’t get that error anymore but you can also nest functions inside other functions Say you open this spreadsheet tomorrow and you wanted the value of today to be the day that is tomorrow That’s that function right in here it started and ended with a parenthesis, type enter and there you go, same value So there’s lots of different ways to approach using functions There’s a lot more functions that are available in these menus but the ones that we covered are going to be the ones that use when you’re starting out so if you like that tutorial, you could subscribe to my channel and you’d see a lot of other tutorials that are just like this and if you want to continue to learn more about Excel, in the upper right hand corner, I’ve linked the next video which starts to progress you through some more advanced concepts, thanks for watchin’