In this session we're gonna go over some of the basic functions in Microsoft Excel 2019 which is
part of Office 365. Remember those functions are not tied only to Excel 2019. They are useful also
for previous versions or and also later versions of Excel. If you'd like to follow along you might
need to download this accompanying file that has a whole bunch of those tabs here and basically all
the various components that we'll go through in this tutorial. So let's get started. So
now that we opened Excel we understand the various interface components of Microsoft
Excel 2019 now we'll go under the basic concepts tab here in the bottom and perform some specific
and basic functions here let's say we wanted to calculate the office expenses we have all
these cells in each one of those cells you can type texts the numbers and values and such can
be a general number it can be a specific number currency accounting type of format numbers dates
percentages you can format these cells or the values in the cells in percentages fractions
and scientific notations and text so here we basically want to calculate the expenses for
our office suppose we have office supplies computer expenses for the copying printing
travel expenses service contacts now for each month we have recorded the amounts and then we
want to get the total for the training for each category also we want to get the totals for each
month down here first we want to format these currency we simply select those specific cells
go up here under the number grouping and then click on the drop-down and we choose currency
currency it's going to put that dollar sign in front of it and other two decimal points
you can do it from here or you can click on this dollar amount in here if you are using various
other currencies you can pick those in here now let's say that you wanted to get the totals for
trainings all those calculations are performed by using formulas to perform any calculations
in Excel start with the equal sign followed by some kind of function now the function itself
can be any of those in here so if we click on the formulas click on function the functions can
be financial functions any of those that you see right here logical functions look up in reference
math and economically so if we want to look at the whole list of them here under all notice
that there are hundreds of functions that you can utilize in Excel now for the purpose of this
tutorial we are just going to learn about some of those functions so that you get the concepts
and then once you know how this stuff works then you can go and explore this even further more on
your own so for example here I clicked here under formulas and I chose insert function in case you
want to find what some does typing in there click go here notice it says it adds numbers in a range
of cells so if we go back here to get the sum of those numbers like I mentioned earlier you need
to start with the equal sign the word sum and then can open parentheses and then here you can
reference the actual starting point which will be b6 know that you have column B and then the
row is number 6 so you type in there b6 this is the manual way that we were learning to do this
at this point and then colon this means anything from that starting point to let's say d2 where it
says here for march training should be d6 and then close parentheses and hit enter so that's one way
to do this so notice now the calculation it's 700 if January's training expenses were 250 notice the
total automatically and that's the beauty of Excel that's why you use formulas and calculations and
you use this tool so that's one way to enter the formula in here let's do this another manual way
here so here we say some and you can press tab and it will put the capital letters and also the
parentheses thing then you can either tap in here the starting points and know this it's 300 and it
puts b7 automatically so you don't have to gauge it where it goes horizontally and vertically where
they meet and also you can put their colon and then the end point or you can simply select the
cells that you want to add up and notice it will put the colon in there automatically and then all
you have to do is you hit enter so again before I hit enter to summarize it you press the equal
sign telling the computer that you're going to calculate something in there to start a formula
then you put the function which is in this case it's the sum adding those numbers and then we
simply select it what the range that we want it to select then hit enter and notice the amount
here has been calculated in most cases in business and most individuals also use this function here
the automatic calculation so let's suppose that I didn't have either one of those and I want
calculation in here notice under the Home tab and then under auto Sami it automatically will add up
a bunch of cells and the values in them you click on it and the computer is going to select those
items automatically you hit enter here and it gives you the calculation now what about if I
wanted to do the calculation over here notice it's going to try to do this election as well
now you of course you can adjust that selection before hitting Enter and that's another way of
doing it now I showed you the manual way before because that's gonna be more accurate and it also
gives and reinforces the concept as to how this works so now if we wanted to get the totals for
January here we could do it either way we can either use the automatic calculation here on
the top right on the Home tab or we could do the manual way and select the range and then
hit enter next here how can we populate the same formulas here that we entered in this cell for
these other ones without having to do it manually for each one of them instead of us spending time
to enter the formulas here in each cell we can simply replicate what's entered in this
cell which by the way here is what's entered in that cell behind the scenes this is the formula
bar here on the top not to replicate it notice there is a little dot here in the bottom right
all you have to do is you hold a mouse on that dot on the bottom right and then drag far down
you want to go and notice the computer is going to calculate everything for the subsequent rows
whenever you do this try to double check the work and the way you do that is that you click
on any of these calculations then you come here under the formula bar and then see what's being
calculated so it's getting us a sum of b12 all the way through d12 which is b12 that's the 13
here and then all the way to d12 so spot check that work before you submit something that you
later regret that it was not calculated correctly if we want it to do the calculation here as well
for the totals for for each month you can do it the same way so you can go to the bottom here
there's dot and then drag the mouse to the right this works vertically it top-down and
horizontally from left going to the right notice we got a bunch of these number signs in here and
this is a very common thing that that means that the column is not wide enough and all you have to
do is either drag this to the right a little bit manually like that or by double clicking
between the columns here C and D and the computer is going to adjust it exactly to the widest value
in any the cells and we do that for the next one as well the autofill feature has works for
anything sequential can also work for days of the week or a specific sequence of numbers that you
have so for example if we have here Monday and then we want to replicate the other days of the
week you can simply drag this down and you have all the other days to wait if you wanted to do
it for months of the year just type the name of the month drag it down and it will replicate the
months and keep on going it can all sort of work for sequences of numbers now you can drag this
down by selecting those two values and notice it will do 5 10 15 20 and so on let's say in
order to learn about formatting and making this look slightly nicer you can select any of those
cells and then you notice you have these Styles it's very similar to the styles and formatting
styles in Microsoft Word we can either click on the drop down and pick any of those designs and
notice it's giving us what's called a live preview so you can do this for all the cell's in here the
other thing that you can do is you can simply select here the whole table and you can choose
format as a table and then you can pick one of those designs and click OK here and now know this
applied that specific design in there instead of us doing it manually and I just undid it for now
the other thing to keep in mind is conditional formatting the conditional formatting is a tool
that you can utilize to easily spot ends and patterns in your data using bars colors icons
to visually highlight important ones so in this case we just want to pinpoint which ones were the
highest expenses in our data set here so we go here under conditional formatting and you have
all of these different rules so you can choose to show anything greater than a specific value
anything less than anything it equals or duplicate values to highlight them and things of that nature
also the top 10 in the bottom 10% so if you had if you wanted to identify the top 10 items or
10 sales and such all you have to do is you just pick any of those you can do data bar so basically
they'll give you a little chart similar to that or you can do color code it and you can create your
own rule as well in your own criterias so for now I'm just simply going to use the color coding and
this basically puts a little chart within each cell to give us the definition of the items with
a higher value nm so if we change this to 5000 notice everything else will be readjusted now we
can do it for the total conditional formatting and let's say we want color coded or a range of cells