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.