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!

MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you can 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 in either Mac or Windows. When you start off a new document in Excel it may give you a new blank document of 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 could 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 can 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. This is column A and this is column B. To the left you see numbers representing rows. This is row 1 and this is row 2. So any cell has a column and a row. This is cell B2. 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 a 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 to perform calculations on it. 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 Dates, 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. Notice how I'm tabbing to go to the next cell. So the vendor here, we'll do a fictional company. 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 want 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 was exactly how wide I needed these columns. But now that I'm entering data I could 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 can 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 this cell that's got our Heading in it and if I go over to Home then I could see there are various things I could do to adjust the style. For instance I could make it Bold. I can change the Font. I can change colors. I can increase the font size. I could also click here under Cell Styles and there are a variety of preset cell styles. Here's one for Title. Let's use that. Then for these here I'm going to select all of these. It actually might be easier if I just click the number 2 for the entire row. Let's select Cell Styles and do this as Heading 1. 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 when I get to this part here notice I put $30 for airplane meals. But it really wasn't $30 for my meal, it was $10 a meal and I decided to put the meal expense for my two companions on my credit card. So it really is 3 meals at $10 a piece. Now I know my expense department likes to have that broken up. So I'm going to 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 at Home you just go to Insert and it says Insert Cells. 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. Let me do another column. 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 are three meals. Now let's tighten up these columns a bit here. 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 can 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 D3 and multiply it by E3. I could type that, D3 and then use the asterisk and 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 row and column is. Now when I hit Return and you could 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 copied something from F3 and pasted 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 will 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 I needed a taxi to get to the hotel. So Taxi, Inc company and let's say this was $35 and just one of those. You could see how Excel is 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. 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 is your report is 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 then with a parenthesis and you grab the cells that you want and you could see how you could type that as F3:F14. The range from F3 to F14. But by selecting it it filled that in automatically. Then I'll type right parenthesis and there's the formula. SUM F3:F14. I'm going to hit Return. You could see it creates the sum there. 

Let's Style that. I'm going to go here to Cell Styles. Let's pick something that's appropriate, like say Calculation here. Now the great thing using Calculations in 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 $2105 instead of 2115 dollars. But 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 can expense that. So I'm actually going to go to the row below this and I'm going to choose Insert and Insert Cells. It will insert a blank line. Now I can type in another expense. Now notice how it adds it to the bottom. Remember before this was F3:F14. Well now it says F3:F15. You could see it here. When I inserted that row it automatically expanded this. It knew that it has 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 bought everybody a hour at the spa. So you could 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. You could see it resorts these. So now the spa here is perfectly placed with those other expenses in that date.  

So now I think I'm gone but then I realize that, Oh No!, the Expense Department in 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. Then I'm going to add Type for each one of these. So now that I have a Type for 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 to 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 start a new formula and do SUMIF. SUMIF 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 then 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. Then the range for the numbers we want to add up are these. So close parenthesis there and Return. We can see it adds them all up. There's only one and it's $300. Now if I were to take this and Copy it and Paste it here it seems to work for Travel as well, except that 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 this 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. Now when I Copy and Paste it here it's 615 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. 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 will put dollar signs in front of it like that. Now we have a nice total here of the expenses by Type. 

 Now what would be nice that instead of that hidden under here we had this in a separate area where it could be printed separately. You could 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. It's new but we could use the Plus button to 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 to Paste. Paste them in. Let's widen this column a bit. Now we have this here and notice that the formulas still work. In fact, it's giving a sheet reference in addition to the cells in that sheet. Notice it's not referring to C3 in this sheet, it's referring to it in sheet 1. So these all work here. 

Now the next thing you might 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 there are 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 could change the title. I can select the actual Pie Chart inside of this and also select the numbers inside of that. I can go to Label Options and then take away percentage and put values instead. Then maybe instead of Label Position being in the center I could do Outside so it fits nicely with that. So now I have a nice looking figure of a summary all in here with this sheet. In this sheet here its got all of the data. Note it updates it to the raw data here. It's going to be reflected everywhere. So notice here, for instance, it's Meals $310 and it's 310 there. Go over here and I correct something, like make this $120. You could see it's going to add it to the total there and it's changed right there as well. 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 there was a miscellaneous expense that was $20. Notice this now goes from Row 3 to Row 17. 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 and the better you'll be at using it.