Transcript for:
Microsoft Excel Beginner's Tutorial

hi there Jamie keyed here today at teachers tech hope you're having a great day today we're going to look at a beginner's tutorial on Microsoft Excel so I'm gonna go through this tutorial and show you just a basic lay out some terminology how to input your data how to format your data how to work with functions how to work with formulas how to work with charts and how to do some printing at the end so there's lots packed in here I'm going to throw in some tips for you too so that when you're done this that you feel comfortable maybe advancing your levels to the next one I'll put out some other videos about Microsoft Excel to help with that process too but if you're looking for any parts of this video just look down below in the description and I'm gonna timestamp everything so you can just jump to that part if there's something specifically you're looking up remember I do these weekly tech tips and if you're looking for other Microsoft products or any other tech products just hit that subscribe button and hit that little notification so when my new videos come out you'll get notified right away so today let's start on this Microsoft Excel beginners tutorial I'm just gonna go ahead and open up my Microsoft Excel here I just have it in my taskbar so when I open this up just something to know I am using Windows 10 and Microsoft 2016 but if you're using other versions this beginners tutorial will match quite closely to a large percentage of it so just some first things as you open up your Microsoft Excel you can see I might have on the or you might have some recent files if you have opened up things before these are just going to go in order of how old they are from top to bottom but if there's something important that you want or there's a worksheet that you can that you want all the time and to stay on the top just notice this little pin here and if you click the pin it just will be at the pin to the top so he can quickly find what you're looking for or you can unpin it and put it back in the order so another thing is we have all these great templates here I'm a big fan of templates once you know kind of what you're doing and this in this tutorial that I'm doing today will help you maybe get to that step where you can save a lot of time making some professional-looking work workbooks just from using the templates and modifying them somewhat but there's lots of templates to choose from you know for homework or school use right through here or you can even search online here but the best way to learn first is starting from a blank blank workbook what I'm gonna open up and just understanding the basics of it so I'm gonna go ahead and open up a blank workbook here and when it opens up you should get a view similar to this or maybe if you're in a little different version it might be slightly different and if you look around and now this is similar to other Microsoft products too but across the top we have all these tabs and so whether you're on Word or Excel or PowerPoint we have tabs across the top that you can click on to see something underneath them and that thing underneath them is called the ribbon so if I go to my home one which tends to be kind of one of the ones that you're on lots you can see this is the ribbon through here so in the ribbon you have groups you can see here so this group right here would be my font group this is my alignment group through here at the different groups are named so we go from our tab to our ribbon to a group and sometimes what shows up here there's not enough room so you can see that there's this little button here to push if I click this you'll even get some more options on this so that's just kind of your basic walk through if you use other Microsoft products this will be familiar but those are just some of the terminologies to start with we have our tabs and then a ribbon and then our groups so Microsoft Excel you can see so what we have in Microsoft Excel we have if I just click on a on the letter C this is a column and then if I click here on the number 4 this is a row and now there's your you're not gonna run out of these columns or rows there's so many can't remember the exact number but if you screw you can keep scrolling and put a tremendous amount of data into this here now an individual spot is called a cell so where the two places meet so where this Row 3 and C meet right here this is called a cell whatever cell i click on notice it will show up here in the top left hand corner here so as I click on different ones we have f6 you can see and I click on and c7 if I grab a bunch of cells that's called a range on it so a range is going to be a group of cells all together and that's these all these things you're going to be using as you learn more Microsoft Excel because you're gonna be taking the data and from whether it be a cell or a range and then trying to apply formulas and functions to it so inside this one range could would go from in this case you can see the top left-hand corner which would be C 4 all the way down to K 17 and that would represent that range from there now when I click on each cell right now all these cells are empty so if I click on a cell whatever you see in a cell is going to be right up top here so you can see it's empty here so if I write something right here so if I just write something up here it will go into that cell and if I click on the cell it will show me exactly what's in there so you can always check to see what's in the cell you can write up when you're on a cell you can write something up here or you can write in the cell here and if I raise something else you can see it just goes right in here so a couple other things to talk about just before we get starting started in putting our data is so this whole thing is called a workbook that you're gonna be working in here though so when I open it up I open up a workbook but within the workbook you have worksheets and you can see down at the very bottom here we're on sheet 1 you can add more sheets here you can see I can just click new sheet and as they click new sheets now I have sheet 2 I can click again g3 and I can rename these sheets just by double clicking on them and calling them something else so if I just wanted if this was gonna be an example of I'll just write data and you can see I can name each of these and I actually can drag these around in different orders if I wanted to change them around too so that's just some terminology to start off it's important to know that just as you've read through and add more things you're gonna see when they talk about cells or ranges in the worksheets when we taught start talking about different things with formulas and functions later on in this video so let's get started with entering in some data and then we can get going on formatting that too ok so it's time to start entering some data now the one thing I'm going to do before is make it larger so I can see things a little bit better and then you'll be able to see things a little bit better so how I'm gonna do this you can go to view up top right here and when you click on it you can see you have soom options where you can zoom up and when I hit OK it gets larger the other thing you can do at the bottom right hand corner there's this little slider with a zoom you can see how I can adjust it very quickly to like that so I'm gonna go back to my home and now this will just make it a little easier to work with so I'm gonna be entering the data I'm going to be using and manipulating today is going to be the top 10 grossing movies of all time so I'm just gonna enter some titles and then just do some formatting show you some basic formatting zhan text so I'm gonna write this in so we're gonna start with the rank and then we're gonna have our title and then we're gonna have our studio name who made it and then we'll have our worldwide grossing income and then we'll have our domestic and then we're gonna have our overseas and one more we're gonna have a year so I just quickly typed in and what I was doing was tabbing over each time to when I was done a certain cell so here's how you move around you can click I can click in any cell to move around but what I tend to do when I'm typing all tab and if I hit tab it moves to the right if I hold shift down when I tab it moves to the left you know I ever hit enter it will move down if I hold shift down and hit enter it will move back up you can also use the arrow or sometimes I'll use the arrows and you can just arrow your way around your worksheet too so I have these titles here and you can see everything kind of fit in across the top here because the words weren't too long worldwide is getting a little squishy in here at any time if you do need some extra room if you hover right around that you can see right in the middle when you get this handle you can stretch out your stretch out your different columns or you can stretch out different rows but the one thing is not to worry about if it's overlapping it's not really messing up anything so for instance if I was I'm just gonna write some I'll say studios in the United let's say in the US so notice that right now it looks like it's going in 2d so if I click out of it it stops right here and if I click into it it didn't mess up this cell at all you know even though you can't see it at all that doesn't mean it's messed up so what you can do is this is where you can stretch out your cell like this if you ever want to make a change remember just use the back arrow or ctrl Z and you'll go back the other thing you can do if you just double click in between those you'll instantly it will just fix it for you to even without dragging so those are just a couple options that you can do to quickly make those adjustments and remember on the slide you can get that handle on the same thing so you can adjust those two so if we're formatting maybe you want to make it look nicer you can quickly change if I click on a single cell and if I was gonna write something now so maybe I wanted to add something the rank and I click on it once it will just disappear now the reason if you want a format inside that cell and maybe I want it ranking you have to double click in it and then I could add it that would be one way another way is if I click once I can just go up top here and type it in up in this spot too so just know that if you click on it once and try to write something in it it's gonna delete it on it you need to double click on it or go right up top here so I'll just undo those ones and so the next thing what we're gonna do is just some basic formatting you can see this works like Microsoft Word if you've used that before so if I click on it you can see I can quickly change my text on it I can change the size notice how it's disappearing right away so it may be double click here and it instantly changes to this double click here and it fixes it for me too if I wanted to change the whole row at once I can click on it and now I can go back and adjust maybe I wanted that smaller if I wanted it bold and maybe I wanted a different color like blue on it you can see now that I've made it bigger things aren't quite fitting in the right places so then I need to adjust those columns and remember it doesn't it won't affect the formulas or anything as this is just how it looks so we can fix it so again if I double click on it you can see how I can instantly fix these very very quickly on it so those are just some formatting things to make it look nice it doesn't change or doesn't wreck anything to have them overlapping but to visually look at it it's just a quick thing to do so I'm just gonna hit bold let's bold all these I'm gonna double click on here and just get I'm just gonna call this studio and I don't want it that large I'll just double click on it so now we have our titles the other thing you can do is you can add so you can just you can select by dragging it you can add borders to different things too so you can see through here I could add borders between each of them if I wanted to border around it just click in here and you can kind of go through and you can draw borders and you have different options on the styles through it so you can then you change how thick and everything you want on your border too so if I click off it - now you can kind of see that that line that goes around it in a lot of this is for visual maybe helping as you're working through it or when you're printing it off because if you print it off these cells don't show off I show up these are blank you need to add borders and everything if you're printing it off and you wanted the the outline around certain things make sure you add your borders and then your colors and then it's just easier to look at a spreadsheet especially when printing off and finding different areas the other thing you can do if you notice the Styles right over here so if I click on this and I want this whole row in a certain way so if I click down maybe I want a certain heading on this and you can see right here it will add the heading to it and then it's all it's adjust in a hurry so you can use these styles to quickly adjust on these - all right so we have some titles here I'm just gonna take a moment and enter in some data so rather than watching I'll just stop and we'll get back when I entry out in a bunch more so I've entered in some of my data from a website that I have on the top 10 grossing movies of all time you can see there's a little bit more formatting to do now a different way to format and I've already showed you that changing the width of this here but what you could do is another way and I'm gonna make sure this whole column is this let's select it so I apply this notice that we have wrap text when I click on wrap text what it does is it wraps the text in that in that cell so it goes to a new line within it so I can read everything without changing that the width to it so if you just wanted to kind of it creates you can see it did widen everything through here but this just makes it so if you don't want a super wide cell you could just wrap the text instead so the next thing I want to do with this I kind of want to add a main title here and I can insert rows so I can insert more rows even after I have something I'll set up like this so I'm just gonna right on this and I'm gonna go to insert here so I selected the whole row and then I'm gonna hit insert notice now everything kind of moved down and I have a brand new row to it so I'm gonna write down give me give us a title here and I'll say top grossing movies of mmm all-time here okay so I have a title of this but this title would be nice if it was kind of centered on top of this here but I have all these cells so this is an a1 and this stretches all the way over to g1 so what I can do with this now is if I select the cells all the way to G here and then I go and hit merge and center notice now it took that it took all those cells and made it one cell kind of merge them all together and then Center that title for me so again I can go through here and I can maybe I'll add another I'll just add a style to this I'll call it heading 1 and now I have a title to it like so so I made a little bit of more formatting so I've wrapped my tax tight added a title and merged it now what I'm gonna do is these are in dollars right through here so these are in billions of dollars I just use the abbreviated apart just like I took off the internet but these are in billions of dollars now I could highlight multiple rows and apply something but in this case because I'm not sure what I'm gonna do down here yet I'm just gonna select the area with the money in it and notice right here we have a dollar symbol and if I click it it automatically puts it into a dollar format here and gives me the decimals just like the currency now I can pick different currencies here also so you can see I could switch from US Canada United Kingdom and so on it'll make the adjustments to those dollars on it so how does this look if I was printing it a couple and I'm gonna show you right here and something I want you to point out notice that this dotted line that goes through right here that is the print line of the page the current settings and everything so everything within if it's within this dotted perforated line right here should be fine to print I just noticed this ranking I don't need this whole thing this wide for those small numbers so I'm just gonna change it you can see now I would have a little bit more room if I go over to my file here and I'm just gonna click on print it gives me a preview here what it would look like when I print it noted the notice that the cells don't come up the style that I picked did come up through here so it's nice to have that style to break through things up if I wanted everything in more of a cell format if I was highlighting everything through here so I just dragged my mouse down I could go up and choose maybe I wanted those borders around everything I could pick and choose I'm just gonna go to all borders to make this quick and now if I go back to my file and my print you can see how it's adjusted so if I was just printing now to my printer I can adjust this and this goes the same thing if you wanted color in certain parts so maybe I wanted this ranking a colored here I could pick a certain cell maybe if I wanted this orange right here and I could bold my numbers on this one and remember you can Center just like in Word and everything I could if I wanted everything centered in here I could highlight everything and Center to adjust to that way so those are some more just basic formatting here and get in if you wanted to print what you had so far you can always check to see under the print what things would look like now to get everything kind of looking very well it because it just makes things so much easier to analyze that the data when you're if you're printing it and showing it to somebody they'll be able to find things very very quickly okay so let's start now with what makes Excel powerful and that's formulas so formulas are of course an equation but it's when you're taking a cell because you want if the cell changes the formula will change so let's go ahead here's an example if I was just clicking here and you always need to start a formula with an equal sign so if I hit equals and I'm gonna choose a cell so actually I could use a number here so if I go equals to and I'll say times two and I'm just going to hit enter and I get four so I could use Excel like a calculator you you may see four here but that's actually not what's in this cell now look at the four and now look up top here you can see what actually is in this one so there's a formula here equals two times two even though there's four over here so if you wanted to solve something really quickly in Excel just put the equal sign in and then write the formula after that now if I click on these cells you can see it's just a number but this is a formula here so sometimes you might need to look in this spot right up here to see the formula inside this is the formula bar so I'm just going to go ahead and delete this so let's apply a formula now to our spreadsheet that we have over here so let's say I wanted to know the percentage of domestic versus worldwide so I want to put it right after domestic here so what I'm gonna do is I'm gonna click on this F row right click and hit insert now this is going to give me isidro I meant to call them this is going to insert another column right here rate this side here so now I'm gonna enter in the information here but I want an equation so the first thing I said you always start with in an equation is just double clicked in here I could write it in the formula bar - I give it a an equal sign so I start with an equal and now this is the powerful part so if I click on domestic I want to take whatever is in III and then the divide sign is they so I'm going to put the divide sign is this one right here through here so I want to divide III by d3 I'm gonna hit enter and notice that they went to 27 cents but that's because I formatted earlier with dollar signs and I don't want dollar signs on this one I want a percent so if I go click on this one notice I have a percent symbol rate up here and I can click it and it's 27 percent maybe you want more accurate and different percentages notice that I talked about this little button before I could add I'll go to two decimal places and I can adjust it a little bit more if you want that a little bit more exact now here's the great part with Excel so if I click in this cell so I could go through and go down and do every single one and it would work out fine but the formula that I gave III divided you can see if I click on this cell here's the formula it's saying divide a 3 by 3 now if I click on this right here in the bottom right hand corner notice I get if I hover over in this I have this little square this is kind of a copy the cell down I'm going to drag it down and I want to copy it all the way down look what it just did for me it did the form so now as I click on each one now it's efore divided by d 4 e 5 divided by e phi divided by d 5 it copies all the way down I didn't have to spend time I just made that one cell then the way I wanted it it knows when I Drive when I drag that down and copied everything to adjust it to the side so let's do this again with the over C so I'm going to click on this one I'm gonna right-click insert another column it goes to the left here I'm gonna get make a formula here so I'm gonna hit my equals I'm gonna click on my overseas and then I'm gonna hit my divide sign here and then I'm gonna click on my worldwide hit enter again didn't want money I'm gonna hit my percentage make an adjustment here back to my two percentages grab this little handle get that little black cross right here and drag it down and I get all these percentages that fill in automatically so that is a power of a formula and copying it through using a percent symbol you can do it in so many you know once you have your your your numbers in you can just set up these formulas and then we get so easy now why are they so great to let's say there was an error in something or this changes or you were updating it the next year so if this domestic number changed now let's say if I changed it to nine hundred I'll have nine hundred so I'll make it larger notice that this number changed automatically because it's connected to the cell and so it knows how to adjust automatically to it and now this is the power of spread and how it works so well in Excel so you can play around with some this is just the beginners one so there's so much more you can do with formulas and there's more adjustments but that's for a different tutorial but try some basic basic formulas in your spreadsheet to see how it works and how it can save you some time now but let's not go look at some of the functions that even can save you some even more time here's a tip before I get into some more formulas and functions here and I'm gonna zoom up to show you this tip here so I'm gonna try to zoom up and I'm gonna move over all the way to the left notice now we have a little bit of the actually going to zoom up a little bit more here and notice now we have some of the last few cells we can see so a handy feature if I look under the view here is freeze pane so let's say if I wanted to be all the way I wanted to be able to kind of look at the rank and then look at the numbers over here but I just want to make sure that I'm looking at the right spot if I go ahead and click on freeze pane here you can see I can freeze panes top row first column well I want to freeze this column here now you might have a different spreadsheet setup for a different purpose but if I click on it now if I was going over notice it stays in that ranking so I can compare things all the time to that rank and so it just makes it really visually easy to kind of scroll through to where if it whether it be rows or columns and you can compare it to that first thing you can turn it off very quickly like so I'll just go back out a bit so you can see everything alright so let's talk about some more formulas and functions here just kind of save you some time and I'll actually I'll zoom out just a little touch more here so I'm gonna write the word total right here sort o ttle and so i just showed you a formula with like using it like a calculator where you hit the equal sign and then you could just select the cells that you want so i could do the same thing if I wanted to add up all these different ones in the worldwide to get a total amount I could hit the equal sign and then click on a certain one I hit the hit the plus go to the next one hit the plus hit that go on to the next one and he had enter when I be all done it would give me mount it would work fine now here's a quicker way to do it here I'm just gonna go over two formulas here and what you can see is autosum another way you can do it if you're on your home notice all the way over to your right you get Auto sum right up here but I'm going to just be in formulas right here and I'm gonna click on autosum and it just instantly highlighted all this I don't really need this sell it gave me a range and I talked about ranges earlier on I'm just gonna highlight the exact range I need here so it says equals sum everything between D 3 and d 12 so in this case and you could just write if you know the different functions I could hit equals and write the word sum and then the range and it's gonna take care of the job for me so if I hit enter it gives me a sum of everything here and same thing as I showed you before you can copy over you drag that little handle grab that little square drag it over and it just does it for you here the other thing you can do I could copy so if I go right click copy and then maybe I want it over here and right click and paste it will do the same thing for me so I can put it to a specific spot and notice when this one is selected right now it shows me some G 3 to G 12 and it did G 3 here 2 all the way to G 12 so here's another one so if I wanted to rate average so if I was typing average I want to know the average amount so let's look under recently used here and usually this one is gonna be rate up top so you can see I just did some and now I'm gonna click on average what do I want to average it's asking for a range looks like better click on here just one the sec here I'll go back I'm gonna click it's going to ask for a range here and then what's my range it's gonna be this one right here and I'm gonna hit enter and now I have average of all these and the same thing I can copy it over I could copy it all the way over and now it even averaged this but let's say I didn't want that and I could change that to a percentage if I go back to my home hit percent and gives you a total amounts right through here so you can see how quickly and again if any of these change so if this was changing to a bigger a larger number and hit enter everything changes because it's connecting to it so I'm gonna hit undo because that wasn't accurate so if I go back you can see under formulas there are so many in here now each each of these can be a video by themself explaining how these all work and this shows the power of Microsoft Excel here as you go through you can look at all the different ones and it's about understanding how to set your worksheet up and once everything is set up and as you understand Microsoft Excel more it just makes your life a lot easier when you're kind of processing all this all this data together but then as one thing changes it just will fix your percentages or amounts very very quickly for you so that's just kind of an introduction to your formulas and functions if you're starting off basic with some of your spreadsheets you can use these basic ones and as you learn more start to start looking at some of the other ones so the last thing I wanted to show you today in this beginners Microsoft Microsoft Excel tutorial is how to create a great chart to kind of display your information so I'm just gonna show you how to graph or chart your information really quickly I'm gonna go do a different video on this to kind of expand it but something before we get to that and I hadn't pointed it out yet and if you're used to Microsoft Information are the products that tell me up top here so if you're ever looking for something in here if you just click in here so for instance if I'm looking for a charts if I start typing anything it's gonna show me the information right up top here so if you can't find something in the tabs or in the ribbon I just go ahead and start typing what you're looking for and it's gonna find it for you here okay so that's just a handy feature through all the Microsoft products but so when we put a chart in that's gonna be under the insert here now I'm going to show you one way here just a simple way here today for this beginners tutorial and I'm gonna just want to chart the title and the worldwide gross on so what I'm gonna do is I'm gonna highlight this right through here so I'm gonna highlight all these different titles here I'm gonna hold down control so what holding down control allows me to do is it the slot allows me to select different parts of it so I held out control and I could have two different areas highlighted and that's what I want to chart in here so I'm gonna just go ahead and click on recommended charts here so you can kind of see it gives you some examples right away here you can go to all charts if you wanted to look through if I wanted a pie chart for this example I'm just gonna go to a normal bar graph like I said I'll do a different video all about graphing to show you more information on it but I'm just going to go ahead and hit OK and so real quickly what it did it took it took that information that I selected and put it into a graph now it might not be but for me it's not finished yet because I maybe I want to add a title so I just double click in here and I can add a title so if it was top grossing movies here and I'll just leave it there and so I've added a title but what you've noticed here on if you wanted more things so if you wanted your axes if I click on this for chart elements you can see I have that maybe I want a title on the side so maybe I wanted to I'll put in all to say in billions here and I can just start adjusting so make sure my charts are coming out the exactly the way I want to so you can look at the different formatting options if I wanted to change it you can hover over so maybe if I like this one a little bit better I can click on this one and then it's adjust adjusted so really simple to start some basic charts on this one so if I was putting it down below I can drag this around and maybe I wanted to see what everything was going to look like when I print it you can see right now uh since I have the chart selected that's what it's going to be printed right here if I click off of it and go to back to my print and now it's the whole page right here and now notice that I might have to make some adjustments because some of the some of the information is off the page I'm missing some of the years so maybe I want to make some adjustments to my columns or choose a different type of page to print on so this is just your kind of your general overview of Microsoft Excel for beginner just if you haven't used it before in showing you some of the power behind it I'm gonna have some other some other videos about Microsoft Excel to maybe help you increase how much you could use and how effectively you could use it if you did like it they hit that like button give me any ideas what you might like to see in Microsoft Excel tutorial just write it in the comments down below thanks for watching this week and I'll see you next time on teachers tech