Excel Tutorial for Beginners

hi everyone my name is Kevin today we are going to do an intro to Microsoft Excel or a tutorial of Microsoft Excel and now whether you're a completely new user to Excel and you've never used it before or even if you've used Excel before and you're looking to find out about more advanced functionality of Excel this video is for you now my goal is by the end of this video you will know how to use Microsoft Excel both the basics and some of them more advanced functionality so if I do a good job today you will be able to do that if I don't then I'll apologize in advance now one of the first questions you might be wondering is well what is Microsoft Excel well Microsoft Excel is a spreadsheet program and with a spreadsheet program what you can do is you could analyze data in really amazing ways you could find insights in your data and you could also analyze trends with this video I'm going to cover a whole bunch of content if there's some content that you're particularly interested in you could go down to the timestamps at the bottom and you can just click into the section that you care about if you just want to watch the whole video that'll really give you a strong foundation in Microsoft Excel and trust me if you watch this whole thing you will know how to use Excel by the end of this the first thing that we're gonna start out with is how do you get Microsoft Excel and to do that why don't we jump on the PC and I'll show you how to do it Here I am on my desktop and my computer came with office pre-installed if you already have office pre-installed what you can do is you could simply click on the Excel icon Excel is the one with an X and it's a green icon however if you don't have office on your machine what you can do is open up a web browser I'm going to click into Chrome and then navigate to the website office.com once you land on office com you'll see two different options one is get office one is sign in if you want Excel on your desktop what you're going to do is you're gonna click on get office Excel on the desktop is the full feature rich program there are a few different options you could get a subscription to office 365 with a subscription not only do you get Excel but you also get a number of other apps as well the difference between these two subscriptions with this one which is a little more expensive you could share it with to six people total with the office 365 personal it's limited to just one person but once again you get all of these apps including Excel if you'd rather just pay for it one time there's also an option office Home and Student which is $150 and you get Word Excel and PowerPoint and you just pay for it once and then you get the software on your machine alternatively what you can do is if you're not interested in paying for office and you'd rather use it for free what you can do is you could also use it for free online the way to do that is on my screen here what you're going to do is you click on sign in and you're gonna land on this sign-in page what you do then is you could sign in if you have an existing microsoft account if you don't have an existing microsoft account you could simply click on create new and then you could create a new account i already have an account so what I'm going to do is I'm gonna go ahead and type in my account and then I'm gonna sign in and you type in your username and then you type in your password once you enter your password just simply click on sign in and then you'll land on office comm within office comm you'll see all your different apps and so I can click on Excel and I could just jump into a new spreadsheet or I could go to an existing spreadsheet okay so you have these two options you could get what's called the desktop software which is included on Windows which I showed you before or you could get the web software so what is the difference well in terms of kind of basic usage of Excel both of them are pretty similar you'll be able to do all the basic functionality whichever one you choose the way the offering is still a lot newer and so doesn't have quite the same feature parity as what you'll find in the desktop version although Microsoft is doing a really good job at adding more and more functionality if you want to use Excel while you're offline so let's say you're on an airplane or maybe a train or somewhere where you don't have internet connectivity the online app of course won't work because you're not online and so then the offline version or the desktop software is probably a good choice so you just have to decide what are your needs and which one do you want to get now that we've looked at how you can get Microsoft Excel the next thing I want to do is why don't we just jump into Excel and for this I'm going to be using the desktop software I'm gonna go ahead and click on Excel on my taskbar I could also simply search for Excel type it in it'll show up as my best match but go ahead and just click on it on my taskbar and the version that I'm using in today's video is office 365 in the version of Excel that comes with office 365 so this is the latest and greatest that's available when you first open Excel you land on this screen and this is called the home screen and so on the left hand side you'll see that it's calling home what you'll see on this page is you have a blank workbook that you could jump into if you just want to start from scratch you also have a number of templates you have some learning templates you have a welcome to excel get more out of pivot tables if you're new to excel this is a very nice sheet to walk through and it really does a good job at introducing Excel you also have a number of templates that you can choose from and if you want more you could simply click on more templates on below a page you have all of your recent Excel workbooks that you've been working on and what you can do is if if you don't see it immediately in the list you can also search for it so today we're gonna be looking at my tutorial sheet so I could search for tutorial and what you'll see is it pops up with Excel tutorial so you could also search the list what I can also do is I get pin sheets so here if I click on the pin icon it'll add it to my pin tab and then I could also see workbooks that other people have shared with me now what I could do is let's say that I want to see more templates I could also click on new and this gives me the full comprehensive view of all the templates that I have also categorized and I can also click on open if I want a more expansive recent list or if I want to open it say from a onedrive location or a SharePoint site or I could just browse my PC for a file so I have lots of different options to get back to existing content and one of the fun things is when I worked at Microsoft I actually got to work on designing this new page so I hope you liked it let me know in the comments what you think about it if you think it's good if you think that we could improve it even more love to hear your thoughts on that what I'm going to do for today is we're going to click into this workbook called Excel tutorial I've included a link in the description if you want access to this sheet and we're gonna start on the first worksheet called welcome to kevin's excel tutorial here we are in excel now this is the core experience that you see when you jump into a new sheet we're gonna click through these different worksheets within this workbook and if you'd rather jump ahead to the sections feel free to just go through the time stamps that I listed down in the description you could jump forward to what you care about if instead you want to follow it end to end just keep watching what we're gonna do first is let's jump to the orientation worksheet so I'm gonna jump in here and the first question is will where am i right now we're in a workbook and this is the orientation worksheet what I can do is I could click on these worksheets and here what I could do is I could rename a worksheet I could insert a new worksheet I could even change the color on different worksheets so maybe I'll make this one red now what is this box called right here I have all these boxes on the sheet we refer to one of these boxes as a cell and how do I reference a cell well so right here right now you have things that are called columns so this is column a and then this is row six that's how a spreadsheet is set up you have columns and then you have rows and so if I want to reference an individual cell so let's say I'm in this cell what I would say is Amun I'm in cell C and then six so you start with the column and then you say the row and you can see up here as well it says that I'm in cell c6 and what you can also do is you could have a range of cells so here I'm in cell a8 through c8 so that's how you can reference cells and I've included this picture of Battleship because when I look at Excel and I think of cells it very much reminds me of a game of Battleship how you have your columns and you have your rows it's kind of the same seem similar idea or similar set up okay so that's kind of just the basics of Excel what we're gonna do next is we're gonna click on format and let me zoom in a little bit here what we have is some different ways you could format numbers and so we're gonna have some fun little questions here so what's my annual salary well what you could do is right now when you look at this number it's a very big number because my annual salary is very high so how do we figure out or how do we view this in a better way well you have different formatting options so here I could apply an accounting number format so I'll click on that and that turns it into a dollar value I could also get rid of the decimals since they don't really add too much and that is not my annual salary I do not make that much I wish I did but I don't when I scroll down the page here how many subscribers do I get a day on YouTube and you probably noticed the theme here that these are all made-up numbers but this number is also hard to read so instead of doing a dollar sign what i could do is i could add commas and so here this added a comma to it here once again i can get rid of decimals or I could add decimals and so it just makes numbers easier to read when you apply formatting to those numbers using this formatting section right up here on the page what you can also do is let's say I have fractions I could apply a fraction format and the way to do that is I simply click on these styles up here and as I go down you know you can see all types of different formats that you can apply but down here I could apply a fraction and that'll say 1/2 so depending on how you want to see your number you could format it in more friendly names now another thing that you can do too is I've entered some text here and let's say that you know right now all the text is in this one cell but what if I want it to cover multiple columns so I'll simply highlight that first cell let the text is in and I'll highlight all the columns I want the text to show up in and there's this option called merge and center so I'm going to go ahead and click on that and what you'll notice is it simply merged all these cells together and now it shows the text across those what I can also do is you have other formatting at options where you could wrap text so today you know this goes through multiple cells off to the right I could click on wrap text and what that'll do is it simply puts all the text within this one cell so those are a few different formatting options I could also do things like I could fill with a color I could adjust the font color so there are lots of different ways that I could format text and numbers within Microsoft Excel the next thing that we're gonna do is we're gonna jump into how to adjust the cell size so here in my sheet I have this table of data and I have my columns I have my rows what you can do is if you click in between a row I could expand the size of the row or I could contract it I could do the same with columns as well when I click in between columns I could expand and contract the size of the columns but let's say that I have a column here and there's lots of empty space you know I can manually pull it in and make better but oh maybe I went too far and I cut off some text what you can do is you could simply double click on this separation so I'm going to double click and it simply adjusts it to make sure it fits all the text nicely I could also do the same with rose here I'm going to click into the row and then there I could reduce the height a quick little trick that you can do if you want all your data to fit perfectly as you simply click up here in the top left hand corner and then I'm going to double click on the column and you'll notice now that all the columns adjusted to the text size and then I could do the same with a row and it will adjust all the rows so kind of a nice way to simply format your data what I can also do is let's say I'm working with my data and I want to insert a column let's say I wanted some other value in between model and color I can simply right click up here and I could go to insert so let's say I insert another column and then I could go in between rows and I could do the same where I right-click and then I could insert and then I'll appear right above if I want it to say add another vehicle or maybe I want to add some other attribute about the vehicle so that's how I could manipulate data on a sheet ok so that's a little bit about formatting adjusting cells and all that but the true power of Excel comes from using functions and formulas so why don't we do a quick tutorial and we'll look at how you can use those I'm going to click into this basic formula sheet and what I want to show is let's start off with just basic addition so I'm gonna click into this so and I want to add 5 & 4 and now obviously I know what that is and so a quick little trick is I could highlight those two cells and then if you look down here in the bottom right hand corner you'll see some information about it so the sum is 9 I have two numbers that I've selected and the average is 4.5 so just kind of very quick information that you can get when you highlight numbers but now what I want to do is I want to add these and so we're gonna go through the basics of how you create a formula and the way you always start a formula is you start with the equal sign and then what I can do is I'll simply say one way you could do addition is you say 5 and then plus 4 so I can do that and that'll tell me nine but the power of Excel comes from referencing these different cells and so I'm going to get rid of these numbers here and what we're going to say is I'll say equals and then I could simply click on a two and then I could say plus a 3 and so what that's going to do now it simply adds those two cells together and it tells me that it's nine now what I can also do is instead of going through like here you know I have a few more numbers that show up instead of going through and saying hey I want to take this Plus this Plus this what I can do instead is there's another formula called sum so I'm going to type in sum and then I'm going to open parentheses and now what I do is I simply highlight all the items that I want to sum and then I close the parentheses hit enter and there it's added up all the values now what I can also do is I could also type in equals sum and I can do that across these numbers which are horizontal and I'll close the parentheses and hit enter and now it added up all these numbers now a nice little shortcut and this is kind of more of a pro move what you can do is you simply press the alt and the equals key at the same time so I'm gonna press Alt + equals and you'll notice that it automatically sums up all those values and I can hit enter if you're not big into learning formulas or remembering remembering formulas what you can also do is up here in the right-hand corner there's something called Auto sum so if I click on that I can simply click on sum or I could go through and we can do average I can go through and do average you can also count the numbers that appear within there and you could also do the max and the min so there are lots of different functions you could just automatically apply it take some of the most common ones and presents it within this little Auto sum list what's also nice with the the sum function so I'm going to equal sum again what you can do is you could highlight a set of numbers and then I'm going to answer a comma and I can highlight another set of numbers and I'm gonna hit a comma and then I'm gonna select an upper number and then close parentheses and what that did is it added up all these different numbers so I'll click into the formula field and you can see all the numbers that had added up so pretty powerful now what would addition be without subtraction well here what you could do is I could say equals this cell and then minus this cell and there it subtracted the two you can also multiply if I click on this one the asterisks is the multiply sign and I'll click on this next cell and that'll multiply the two and you could also divide where I click on a hundred and then forward slash ten and that'll divide the that will divide 100 by 10 which is 10 and I showed you earlier with this Auto sum what I could do is I could click on average that'll give me the average across those numbers I could simply go up here and I could click on max and then I'm giving the max of all those numbers and here I could count all the different numbers that appear within this set so using Excel it's very easy to use Excel when you use functions and formulas you could really do some nice kind of calculations on your data in the sheet when you when I think of Excel it's really like a very powerful calculator okay now that we wrapped up on basic formulas now we're going to jump into fill series and I'll kind of walk through what I mean by this so let's say I have this table of data so it's a car dealership and I want to know what my profit margin is well to calculate profit margin you want to take the price and you want to subtract the cost so here I'm going to enter a formula again I'm going to type in equals and then we'll take cell e4 minus cell f4 and so this is my profit margin now what I mean by fill series is see that little plus sign if I double click on it it'll simply take my formula and apply it all the way down so here if I click into this cell and I click on I click into the formula you'll notice that what it's doing is it's taking this value minus this value so basically took my formula and applied it all the way down which is pretty nice now one of the things I might also want to do is let's say I want to do my profit margin less Kevin's fee because maybe I own this dealership and I want to get paid for every car that gets sold the price minus the cost and then here's my fee so I'll minus my fee there and now I can copy the formula all the way down now one of the problems is when I go to the second cell you'll see that it moved all of these down one that's called a relative cell reference what I want to do is I wanted to keep this reference to this spot and what I could do then is I'll go back to my original formula you can insert a dollar sign before the management fee before the letter and before the number and what that basically tells Excel is that you want to fix the position of that cell when you do your calculation so I'm gonna hit enter and now I could apply that again and here if I go into the last cell you'll see now that it locked the management fee but the price and the cost or the calculation for the profit margin adjusted based on where I was in the sheet that's referred to as a relative reference and then I have my absolute reference there when I can also do just a little shortcut if you hate entering dollar signs you could also press the f4 key when you're hovering over this value and that will automatically toggle back and forth between an absolute reference and you could also just do a dollar sign for a row or for the column depending on how you're filling your formula so there there it is one of the things I can also do with this sheet I can also transpose the data so right now you see that the data is organized in kind of this vertical orientation where I have my column headers and then the data down below but maybe I want to have my column headers on the side instead of above so what I can do is I can highlight all my data and what I'm gonna do is I'm gonna go to paste so I'm gonna go up to paste I'm going to click there and I'm gonna go to paste special and now I'm gonna go to transpose and so I'm gonna click OK and you'll see what happened is it transposed all my data so now all of my columns are now rows and all of all of my data now appears going this way so it's a nice way if you want to adjust the orientation of your data and another thing that I want to show with filling data is Excel was pretty smart when you fill data down so here I have q1 and you know maybe this is the total number of sales I sold each quarter so if I click on q1 and once again I'm going to click to drop that down Excel is smart knowing that if there's a q1 well they're gonna be four quarters in a year and it automatically increments that by one so when you fill with Excel it's pretty smart about how it fills the next thing that we're going to look at is how to split data so I'm going to click into the split data tab in here one of the things that you'll see is I have first-name and then I have lastname what I would like to do is I want to separate these by first name and then last name so what I can do this is a really neat thing to separate data what you can do is click on all these names and then we're gonna click on the data tab and within the data tab we are going to click on something called text to columns and what we're going to say is this is delimited so I'm going to click on next and the way that they're separated is there's a space in between them but you could also do it by tab semicolon comma or any other way that the data might be separated so I'm going to click on next then and for the destination what we're going to do is right now it wants to put it in a - and I'm going to say is let's put it into B - so basically I want the data to go into this cell and I'm going to click on finish and what you'll see happened is it just separated all of my list by the space that appears and it split them up between first name and last name so kind of a nice way you can manipulate your data the next thing that we're gonna look at is filtering and sorting data this is something that I really love and really helps me look at my data in better ways and click on the filter and sort and what I can do now is I have this data table of all these different cars models colors mileage price and costs and what I can do is I'm going to go up into the data tab up on top and I'm gonna click on this thing called filter so we're gonna apply a filter and what happened is it added this little drop-down with an arrow on top of all of the different columns I'm gonna go ahead and click on that and what you'll see I can do is I could sort now I could sort A to Z I could sort the Z to a I can sort by different colors if I had colors there so let me go ahead and I'll just sort by the name so here are sorted from A to Z and so now it starts with Chevrolet all the way down to Toyota at the bottom what I can also do is I could sort from largest to smallest or smallest to largest or largest to smallest so here I'll see you know which one cost me the most which one costs the least um so you could sort your list by largest to smallest what I can also do is let's say that I only want to see my Chevrolet's in this list that's called filtering so what I can do is I'll click on this again and here you'll see all the different unique items that appear within this list and so I'm going to click on select all to deselect all these items and here I could click on just Chevrolet and I'll click on OK now what that's done is it's filtered my list just to my Chevrolet's what I can also do is if I click back on here I can maybe just see my Chevrolet's and my Dodge and I'll click on OK and once again it filtered the list and here I could sort it so my Chevrolet's are together and my dodges are together and so that's just a quick overview of how you can filter and sort your data you could also sort up here there are also some advanced sorts where you get so based on one thing and you could add another level so let's say if they both have the same make then you swore it by model so you can do some things like that but you could play around with it and play just see all the advanced power of sorting the next thing that we're gonna look at is creating tables in Microsoft Excel now what's the value or benefit of a table so I'm gonna go here and I'm back on the Home tab and I'm going to click on format as a table and we'll just go with I'll go with the light design and my table has headers okay so I've just inserted it so the benefit is that it's a little easier to read where each row in the table has this color that's been applied to it so it's easy to read across what you can also do is let's say that I expand so here if I click down here I could expand my table by one and maybe I want to call this my margin column and for the margin when we did the formula earlier but I'll take the price minus the cost and I'll hit enter what happened is I don't have to drag it down because it's a table it knows that this formula applies to every single item in the table so it automatically applies it to the entire table it's kind of nice it saves you some time if you know all the data is related and one thing that's really cool I didn't actually know this until recently but something I think is cool up here under table design there's an option called total row so I'm gonna go ahead and click on that and so see what happened is it added a total now so my total of all my margins is about $12,000 if I click on that value I get this little drop-down and I could say well what's my average what's my average margin or what's my max margin that I have or I can just say what's my sum so with a with a table you can also very easily calculate sums the next thing that I want to show you is how to do quick analysis in Microsoft Excel so I just clicked on quick analysis and one of the things that we can see is you know here back to my car data you know I have all these different all these different costs but let's say I want to visualize very quickly which one is the most expensive which one is the least expensive what I could do is I could right-click on this and I could go down to a quick analysis and so with quick analysis I could also click that button so here if I highlight the data you'll see this quick analysis button so I'll click on that and I could add data my data so here I can see you know the more expensive ones have the larger data bar behind I could also apply a color scale and so there are many different kind of quick analysis tools that I could use when I'm looking at my data or maybe I just want to identify my top 10% I'm so just kind of a nice little quick way to analyze your data the next thing I want to show is ideas and ideas of something new in Excel it's up here on the Home tab and you just click on ideas this is part of office 365 and what it does is it'll analyze all my data that I have and it comes up with interesting insights based on it so here I can see by color what is the cost this one's interesting price and field cost appear highly correlated so the cost and then the price of the car appear highly correlated the Toyo black Toyota's have a noticeably higher cost and so what it does is it goes through my data and it finds different outliers in my data so it calls out kind of interesting insights to me so that's kind of a fun thing to run against your data to see if there are any interesting insights that might be there ok now that we've looked at ideas we're going to go into charts and I'm going to show you how to create basic charts in Microsoft Excel so I have this little table here with you know I have years on the left-hand side and then I have conference attendees on the right-hand side and so what I could do is I'm going to go up here and I'm going to click on insert and then under insert I'm just gonna click on recommended charts and what you'll see is it just gives me all these different charts that I could insert and the first one looks pretty good it shows me attendees on the y-axis or the vertical axis and it shows me the years on the x axis or the horizontal axis and so I'll just say ok and this inserts a very basic chart for me based on my data that I have over here what I can also do is I'm going to go back and let's go back to insert so here I have the same data that I had above but now we have food sales in different years and so what I could do is I'll go back click on recommend the charts and this time what I'm going to do is I'm going to click on all charts and I can do a combo chart where I have two pieces of data that I want to then I want to chart out and so food sales maybe I want to make that a secondary access so this is my primary one this is my secondary one and so my food sales are now against the secondary access and I'm going to go ahead and click on okay and so this is kind of a nice view where you know here I could see the attendees and then I could see the food sales against the number of attendees so just kind of quick charts what I like doing with charts is anytime I insert a chart I like playing around with it you know here I could click on change chart type and I could go through and you could try different views to see kind of what type of view you like and just kind of experiment with it see what you like what you don't like and it's pretty powerful in terms of what you can do the next thing that I want to show is how to freeze a pane and here I have once again I have all my car data in here and I have a lot of data so here I'm scrolling down I have hundreds of hundreds of rows that show up and unfortunately when I scroll down I don't know what column D is or what column E is or what column F is it would be nice to have the header stick with the data and I can do that with something called freeze panes so if I click on I'm gonna click on View and then you have this option calls called freeze panes I'm gonna click on that and what I could do is I could freeze the top row you could also do things like freeze a top column or you can simply choose where you want to do the split but I'm just gonna do the top row and so I froze a top row so you see that line there's a little darker than the other ones and now when I scroll down you'll see that my column header stays in place so kind of a nice way especially when you're looking at a large set of data to know what that data is that you're looking at the very last thing that I want to show is pivot tables and I have a whole separate video on pivot tables that goes really in-depth on this topic but I wanted to do just a quick overview of what you could do with pivot tables because this is really getting to more advanced levels of functionality in Excel so here in Excel I have my card data again and what I'm going to do is go back to insert and now I'm going to insert a pivot table and so it selects my data selects the range everything looks good and I'll click on OK and so what this data is it creates something called a pivot table I see all my data here so all the columns appear within this pivot table fields and now I could do things like you know I could drag my color down to a row and I could drag let's say my total cost by color so I could drop that in values and so what that did then is you know here are my rows here's my total cost and so I can see by cost so for all my black vehicles this is some of my cost and you can kind of do unique things where you summarize by well let me see the counts of vehicles that I have by color so what I wanted to do is just give a very quick preview of pivot tables you could feel free to play around with them and kind of see what the capabilities are I also have a few additional videos down in the down the description and in the comments down below with links to more in-depth overviews of what our pivot tables how do you do conditional formatting how do you use formulas like vlookup or H lookup and things like that so there's a lot more that you can do with Excel beyond this well that's the that's the end of the Excel tutorial what I wanted to do is just give a quick overview of kind of the core fundamental functionality of Excel as well as give a little kind of peek at some of the more advanced functionality I have more videos that go more in depth on that advanced functionality for example if you want to learn about vlookup or H lookup or if you want to learn about pivot tables or conditional formatting or a lot more functions and formulas I have videos that cover all those topics and I've included links in the description if you want to check those out and really increase your expertise in Microsoft Excel but with this video this really covers most of the fundamentals on a day to day basis this is most of the stuff that I use and it really helps me use Excel in a really good way and really helps me analyze data identify insights and all of that Excel is a super powerful program hopefully this video helped you really kind of build your skills in Microsoft Excel if you have any questions feel free to leave a question in the comments I always read all the comments that come in and I'll be happy to answer any questions that you might have if you want to see more videos like this in the future please hit that subscribe button that way you get a notification anytime new content like this comes out and if you want to if there are any other questions or any other topics that you want to see me cover in future videos feel free to leave a comment down below and I'll add it to my list of videos to create in the future and that's all I had today hopefully you're well on your way to becoming an expert in Microsoft Excel and hopefully it's going to help you either be Praful in your professional life by being able to analyze data better or maybe in your personal life if you just want to analyze different maybe your finances maybe your buying a house like me and you want to look at the data and of how you made things alright well that's all I have for you today and I'll see you next time bye

Channel: Kevin Stratvert

Views: 190,284

Keywords: excel, pivot table, pivot, table, analyze, data, guide, help, microsoft, office, office.com, recommended, filter, column, row, value, calculated field, chart, free, excel 2019, excel 2016, excel 2013, excel 2010, excel 2007, excel 2003, office 365, o365, example, beginner, for dummies, sample, training, charts, macros, pivottable, home, orientation, intro, cell, format, split, sort, tables, quick analysis, ideas, freeze, freeze pane

Id: e7xGuGqgp-Q

Length: 31min 12sec (1872 seconds)

Published: Fri Sep 20 2019