Microsoft Excel For Absolute Beginners

hi this is gary with macmost.com looking for a quick way to learn microsoft excel this is the video for you [Music] macmost is brought to you thanks to a great group of more than a thousand supporters go to macmost.com patreon there you could read more about the patreon campaign join us and get exclusive content and course discounts so i'm going to be using excel for mac here version 16.55 the version available at the end of 2021 but everything should be pretty much the same for any recent version of excel and either mac or windows when you start off a new document in excel it may give you a new blank document or it may show you this page here with some templates at the top and recent documents here at the bottom you can select blank workbook here but if you're interested in viewing more templates look for this link here and then you can see all these different templates there are a lot of templates already included with excel so chances are whatever you want to create already has a template here that you could start with but to learn excel skills we're going to start with a blank workbook so i'm going to double click on this and we get a blank document like this now the microsoft apps are a little bit different than a lot of standard mac apps in addition to having just about everything available here in the mac menus you also have them available here in this extended toolbar at the top you can jump to different categories and access all the functions in excel i'm going to go to view here and i'm going to enlarge the document here so that we can more easily see things here in this tutorial the main part of the document here consists of cells each one of these boxes is a cell at the top you see letters that represent the columns so this is column a this is column b to the left you see numbers representing rows so this is row 1 this is row 2. so any cell has a column in a row this is cell b 2. if you want to put a value into a cell all you need to do is select it and then start typing so you can type anything you want like text you can click to select and then delete to clear a cell if you want to put a number in the cell you can type the number and then return note that when you hit return it goes down so i can do a number and then return to the next cell below it another number and then keep going vertically however if i want to go horizontally i can type something and then use tab and that will go to the right you can select several cells by clicking and dragging and selecting cells like that so i can delete all three cells like that you could also use the arrow keys to navigate around so down arrow up arrow left and right the main point of a spreadsheet is to have a place to put some data and perhaps perform calculations on it but let's start off by just having some data you want to record something like as an example some expenses from a recent business trip so at the top here in cell a1 let's put a title so i put that in there and now below it let's put some headings for different columns i'll start off with date so the date for each expense then i'll put a description for each item the vendor who i bought the item from the cost and notes it's always handy to have a column there for notes for anything else that you might want to include now let's start entering some data so let's do the conference fee first you start by entering in the date use whatever format is typical for your area so let's do a description here like that and notice how i'm tabbing to go to the next cell so the vendor here will do a fictional company and let's say this was a 300 expense if i put the dollar sign in front then it recognizes that this is currency and it will format it as such so it keeps it there with the dollar sign in front let's go to the next one here and let's say on the same day that i bought the conference pass i also got the airplane tickets now at this point let's stop and make some adjustments i'm going to build this table just as you might do it in real life so i don't always know everything that i'm going to do when i start off one of the things i didn't know here is exactly how wide i needed these columns but now that i'm entering data i can see that i need a little more space for columns b and c to do that i'm just going to grab the line between b and c here and drag that over and give it enough space for names like these the same thing for c here i could always adjust it more later on this seems to be all i need right now let's also make this table look a little nicer here i'm going to select the cell that's got our heading in it and if i go over to home and then i can see there are various things i can do to adjust the style for instance i can make it bold i can change the font i can change colors i can increase the font size i can also click here under cell styles and their variety of preset cell styles and here's one for title let's use that and then for these here i'm going to select all of these and actually might be easier if i just click the number two for the entire row and let's select cell styles and do this as heading one and that makes these bigger and puts this nice blue line under it so this is already looking a lot better now let's enter in some more expenses here now to get to this part here notice i put thirty dollars for airplane meals well it wasn't really thirty dollars for my meal it was ten dollars a meal and i decided to put the meal expense for my two companions on my credit card so it really is three meals at ten dollars a piece now i know my expense department likes to have that broken up so i'm gonna have to do a little more work here that's okay modifying spreadsheets is something you need to do when building them so instead of having just a basic cost here let's have a price per unit and then the number of units so to do that i'm going to select column d here and i'm going to insert some new columns you may think you go to insert here but in fact here in home you can just go to insert and it says insert cells and notice the button here basically shows the inserting to the left so i'm going to just click it once and you can see it inserts a column and let me do another column and let's put one here called price and let's do another one called amount so i'm going to select these here and cut command x and paste them here now the airplane meal was actually 10 the amounts are 1 1 1 but there were three meals now let's tighten up these columns a bit here and let's get the cost to be the price times the amount this requires using a formula which is one of the primary things you do in spreadsheets so instead of typing an actual value like a number or some text you actually type some instructions to perform a calculation you could do that by clicking here where it says fx for insert function but we can also just type the equal sign right here and then type the formula so what we want to do is take d 3 and multiply it by e 3. i could type that d3 and then use the asterisk e3 and that will work but an easier way to do it is to click the cell that way it inserts it in without us having to type and figure out exactly what wrong column it is now when i hit return you can see it does the calculation so this cell here shows 300 but if i look here it can show me exactly what the cell really is which is this formula here now if i were to copy it go down to this cell here and paste it in notice this cell says d4 times e4 excel is smart enough to recognize the fact that you've copied something from f3 and pasted it in f4 one row below so it changes all of the references to be one row down i don't have to copy and paste i can actually select the cells and then grab this green dot here and stretch it it'll actually fill in the rest of the cells so this is now d5 times e5 and this is d6 times e6 so let's add in another expense let's say needed the taxi to get to the hotel so taxi inc company and let's say this was 35 and just one of those and you can see how excel smart enough to say oh you probably want this formula to move down one here so now i get that formula added here now it's d7 times e7 and i don't have to worry about filling this in so let me fill in a bunch of other things now what we probably want to do is have a total of all the expenses so we want to total all of this up and it would make sense to put that here so we start a formula i'm just going to hit equals and of course i could do this plus this plus this but that's going to get tedious especially if the report's even longer than this so instead we're going to use a function so the most common function is probably sum to sum up a range of cells so you type sum and then with the parenthesis and then you grab the cells that you want and you can see how you could type that as f3 colon f14 the range from f3 to f14 but by selecting it it filled that in automatically then i'll type right parentheses and there's the formula sum f3.14 when i hit return you can see it creates a sum there let's style that and i'm going to go here to cell styles let's pick something that's appropriate like say calculation here now the great thing about using calculations and functions in excel is that it will recalculate automatically so let's say i made a mistake i'm looking this over and i see that the ride to the hotel wasn't 35 it was only 25 so notice here if i change it to 25 a few things happen first is that 25 times 1 is now 25 it changed the value here and then since that value changed the total is now two thousand one hundred and five dollars instead of one hundred and fifteen dollars so the calculations are performed automatically when you make a change to the data now another thing you may need to do is you may need to add more to this so for instance let's say that after dinner on this night here we stopped and got some ice cream and i expensed that so i'm actually going to go to the row below this i'm going to choose insert and insert cells and it'll insert a blank line and now i can type in another expense now notice how it adds it to the bottom remember before this at f3 to f14 well now it says f3 to f15 you can see it here when i inserted that row it automatically expanded this it knew that it's got to keep the range from here to here so inserting a cell actually expanded the sum here to include everything now you don't necessarily need to add everything in the perfect spot i could go here to the bottom and i can insert a row right here let's insert that and let's say on the 11th when everything was done i uh i bought everybody uh a hour at the spa so you can see i've added that in here but it's out of order now i want these to be in order so you can sort very easily i'm going to select any cell here and then i'm going to go to data here and then i'm going to click the sort oldest to newest and you can see it resorts these so now the spa here is appropriately placed with those other expenses in that date so now i think i'm done but then i realized oh no the expense department of my company wants everything to be categorized so let's do that let's add a category before vendor here so i'm going to select this column here i'm going to go back to home and then insert some cells and create a type here and then i'm going to add type for each one of these now i've got a type here at each one of these but now my company wants me to total up all of the expenses by type so what we want to do is we want to take one of each one of these and put them down here so we can summarize each one and get the total so we've got meetings we've got travel and then all the rest now we want to total each one of these up so for meetings here what we want to do is use sumif so we'll start a new formula we'll do sum if and sum if is a formula that will compare two things and only add to the total if they match so i'm going to take the range the range is going to be all of these and comma and i want to match it with this now extract will try to extract the value from here we don't want to use that we're going to instead type in c20 here to match that and then the range for the numbers we want to add up are these so close parenthesis there and return and we can see it adds them all up there's only one and it's 300. now if i were to take this copy and paste it here it seems to work for travel as well except if i look at this formula here it's doing c4 to c17 instead of c3 to c16 we don't want it to do that so let's go back to this one here and i'm going to edit it and put a dollar sign in front of everything that i want to hold steady i want to make these values absolute so the absolute value of 3 and 16 don't change that no matter where i copy and paste this formula so now when i copy and paste it here at 6 15 and notice that 3 and 16 stay the same but c21 changed it goes down one row there so now i can select these and extend them and it fills it all in and it gives me the total for each thing let's finish this up by adding a title and then some headings here let's set the style to we'll do title for this select these two cells and we'll do heading for that notice these aren't dollar values here these are just plain numbers so let's tell excel that we want these cells to be represented by currency so it'll put dollar signs in front of it like that and now we have a nice total here of the expenses by type now what would be nice is if instead of that hidden under here we had this in a separate area where it can be printed separately and you can do that using sheets so you've got multiple sheets in a single document here we've got a single sheet here in this document because it's new but we can use the plus button create another sheet like this now let's go in here and i'm going to select all these cells and do command x or cut and then command v or paste to paste them in let's widen this column a bit so now we have this here and notice that the formulas still work in fact it's giving a sheet reference in addition to these cells in that sheet so it knows not referred to c 3 in this sheet it's referring to it in sheet 1. so these all work here now the next thing you may want to do is create a chart so charts make things a lot more interesting it's one of the main things people want to do with excel so it's important to know how to do charts let's select all of the data here including the headings and i'm going to go to insert and then they're different type of charts a pie chart makes sense here so i'm going to click here and select a plain old 2d pie chart it puts it in i can grab it and position it put it right there i can change the type like that so maybe something like this might look nicer i can change lots of different options with this chart so here i can change the title i can select the actual pie chart inside of this and also select the numbers inside of that and i go to label options and then take away percentage and put values instead and then maybe instead of label position being in the center i can do outside so it fits nicely with that so now i have a nice looking thing here at the summary all in here in this sheet and in this sheet here it's got all of the data and an update to the raw data here is going to be reflected everywhere so notice here for instance it's meals 310 dollars that's 310 there if i go over here and i correct something like make this a hundred and twenty dollars you can see it's going to add to the total there changed right there as well i go over here you can see meals have changed and the pie chart changes one thing to be careful of is what happens if you add a row so let's go and go to a row here i'm going to insert a new row and let's say that there was a miscellaneous expense that was 20 bucks notice this now goes from row 3 to row but if i go over here these still say 16. so you want to pay attention to things like that so i'll change these to 17 right here copy and i'll paste it here and it updates all of that and updates this now so sometimes it's worth checking to make sure that your formulas are still correct after you insert new rows and do other changes so there are the basic skills to get you started with excel the best way to learn from here is to expand on this example try adding more things to it try different types of charts then create your own examples experiment and play around with different functions with all the different features of excel at the top go to each element in the toolbar play around with those the more that you play around and experiment with excel the more skills that you'll gain the better you'll be at using it [Music] if you liked this video click the thumbs up button below to let me know i publish new tutorials each weekday hit the subscribe button so you don't miss out

Channel: macmostvideo

Views: 1,417

Keywords: excel tutorial, how to use excel, excel for beginners, beginner's guide to excel, microsoft excel tutorial, how to use microsoft excel, excel for mac tutorial, excel for mac, how to use excel for mac, excel basics, learn excel, excel tutorial for beginners

Id: 9Pe8SYRrOBI

Length: 18min 56sec (1136 seconds)

Published: Fri Dec 17 2021