If you work in accounting or you're planning to
become an accountant, make sure you know the Excel
functions in this video and the great thing is they
work for all Excel versions. Ready? (upbeat music) Number one, the AGGREGATE function. The AGGREGATE function allows
you to summarize values and it gives you the ability
to ignore error values, as well as hidden cells. So for example, here I have date, transaction number, account and amount. What happens if I sum the amount column? Let's use Control + Shift +
down to select the whole range, close bracket, press
Enter, I get an error. Why? Because I have an error in there. With the AGGREGATE function, I get to ignore errors. Just start off with AGGREGATE, then you get a lot of choices for the type of
aggregation you want to do. In this case, I want to sum, so I'm going to go with nine. Next, I get my ignore options. I can ignore hidden rows,
ignore error values, ignore hidden rows, error values and nested SUBTOTAL and
AGGREGATE functions. Now the SUBTOTAL function
is the older version of the AGGREGATE function. It does the same thing, except that it wasn't as
flexible as AGGREGATE. For example, you can't ignore
error values with SUBTOTAL. In this case, let's say I just
want to ignore error values. So I'm going to go with this option. Next is the array. This is my range that I want to aggregate, which is this one right here. The last option doesn't apply to us. It's something you need if you use the small and large functions. Now, we're just going to close bracket and press Enter and we're
going to get our number. Let's update the number
formatting of this. Now, at this point, I'm
only ignoring error values, I'm not ignoring hidden cells. So if I restrict this to
employee-related expenses, and click on OK, I still get the sum of
the entire amount column. To ignore hidden cells, I can change my option here. Five would ignore hidden rows. Three ignores it all. So I'm just going to go
with that and press Enter and I get the sum of
the visible rows only. So now if I change this to
Other Nonoperating Income, I get the sum of these two. Now let's quickly take a
look at another example. I have the total revenue here. I'm using the AGGREGATE
function to sum these two. I'm doing the same for total costs. Again, another AGGREGATE function. Now, what's the benefit
of using these here? Well, if I want to
calculate profit before tax, I can just use the AGGREGATE function, go with SUM and for my option here, I can say ignore nested
SUBTOTAL and AGGREGATE functions and then as my array,
select this whole range. When I close bracket and press Enter, it only adds up these values and it ignores anything that has AGGREGATE or SUBTOTAL in it. If you also wanted to ignore
hidden rows and error values, you can switch your
option to number three. Number two, the ROUND function. The ROUND function allows
you to round your values to the number of digits that you want. So in this case, I have
salary and a bonus percentage. I want to calculate total salary. Let's start with an equal sign. Go to C2 multiplied with open bracket and one plus C3 where
we have our percentage. Close bracket, press Enter and that's my total salary. But here I have more digits than I need. I want to round this to two digits. That's when I can use the ROUND function. So start off with ROUND, open bracket. The number in this case is the result of the formula and the
number of digits I want is two in this case but you can put any number that you need. Close bracket, press Enter and that's my salary
rounded to two digits. Let's update the format
to accounting format. Now, I want to pull this formula down so we can look at different versions here but before I do that, I'm going to fix my cell referencing to make sure they don't shift. Now let's send this down and take a look at how we
can round to a whole number. Well, instead of two here, I need a zero for that. How about rounding to the closest 10? Instead of a one, I need to put -1 and how do I round to the closest 100? What do you think? Minus two. Number three, end of month function. With the end of month function, you dynamically calculate
the date associated with the end of the month based on your specified date. Now, this can be the
end of the current month but it can be the end of the next month's or previous month's. All of this can be
controlled with a formula. To start with, EOMONTH. The start_date is the
current date we have. And the number of months you want to jump, in this case, it's the
end of the current month, so I'm going to go with zero. Close bracket, press Enter. And I get a number back because I'm in the General format. So let's switch the
formatting to a short date and I get 31st of January. So let's just send this
down and see what we get. The last dates of the months
we have in these cells. Now, what if we want to get the last date of the next month? Well, that's easy. Start_date is this and the
number of months is one. If you want to go backwards,
you would put -1 here. In this case one, close
bracket, press Enter and let's just copy the formatting of this to this and send this down. Number four, the EDATE function. The EDATE function allows
you to move a few months into the future or in the past based on your specified date. So let's say we want to move
14 months from this date into the future. We're going to start with EDATE, start_date is this one right here and for months, I'm just
going to type in 14, close bracket, press Enter and I get the 1st of March 2022. So notice the day is the same, the month and the year can be different. Let's just send this down and these are all 14
months from this date. You can also move backwards. So if you wanted 14
months prior to this date, we just have to change
the sign to a minus. I'm just going to press
Control + Z to go back. Now, you can, of course,
nest these functions so if you wanted to move 14 months from the date but also
get the end of month, you can just wrap this
in the EOMONTH function. Your start_date is going to be this and for the months, I'm
just going to put a zero, close bracket, press Enter and that's 14 months from this date but it always gives me
the last day of the month. Number five, the WORKDAY function. So let's say I have these days and I want to get the date that's seven business
days after this date. I need to make sure I exclude weekends and public holidays. The function you can use
here is the WORKDAY function. And there are two
different versions of this. This is the updated version where you can pick your weekends because not all weekends in the world are Saturdays and Sundays. So in case your weekend is different, go with this one, it's more flexible. It requires a start_date, this is it, the number of days, well,
we want seven business days or seven working days. Let's pick our weekends. These, in this case,
are Saturday and Sunday, so I'm going to go with
the default, which is one and holidays is a list that you provided and I already have the list
of public holidays here, so I'm just going to select
it, press F4 to fix it because I'm planning to
copy my function down. Close the bracket, press Enter. And that's seven business
days after the 1st of January. Let's send this down and cross-check our April dates. My start_date is the 1st of April, seven business days gives
me the 13th of April. Here I have a screen shot of the calendar. Let's start counting. That's one working day, two,
three, four, five, six, seven. So notice, the weekend is excluded but also the 5th of April because that's a holiday here. If it wasn't a holiday, so if I remove this, keep
your eye on this value, my end date is going to
be the 12th of April. Number six, 3D formulas. The 3D formulas aren't functions but they're a shortcut
to writing functions. Here I have a separate sheet for each account with different amounts and transaction numbers. I want to get the total of
these in the Total sheet. The long way of writing this is to write a separate SUM function and reference each of these sheets. The better way of writing this is to use a 3D formula. Just start off with equals, type in SUM and in this case, I'm using SUM because
I'm adding the values but you can use other functions, depending on your requirements. Now, let's go to our first sheet and select the range that we want. I'm going to go all the way to row 15 because some of our sheets have more data. Now, here comes the part that's important. Hold down the Shift key and then go to the last
tab you want included. Take a look at the formula bar. It's going from the COGS sheet to the Non-Operating Expenses sheet. Everything else in the
middle will be included. close bracket, press Enter and we get the same result. The advantage of doing it this way is that it's dynamic. If I happen to have another account, I'm going to drop Lease
in the middle somewhere, it's automatically going to be included in my total column. So in Lease, I have two values. This is the difference
between my 3D formula version and the old version. Number seven, SUMIFS, and other IFS functions like
COUNTIFS and AVERAGEIFS. And the great thing
about the IFS functions is that you can sum, get the average of or count values based on
one or multiple criteria. So in this case, let's say
I want to add the amount where account equals services. I can use the SUMIFS function. The first requirement is the sum_range, so this is the column
where I have my values in, in this case, it's the amount column. Now, I'm going to fix the cell referencing by pressing F4 because I want to copy my formula down. Next requirement is the criteria_range1. This is the range on which
my condition is based on. In this case, it's the account column. So I'm going to select
that, fix it with F4. Last requirement in this
case is my actual criteria. That's services, which is sitting in G3. Close bracket, press Enter. And that's the total amount
for the services account. Let's drag this down and
we get the total amount where account equals
employee related expenses. But what if I want to
add another condition? That condition is based on the date column and I want to only add the amounts for the days after 15th of January and account has to be
employee related expenses. So I have two conditions. Well, it's very easy to
add another condition. We have criteria_range2. First is the range which
the condition is based on. In this case, it's the date column. I'm just going to be consistent and press F4 to fix this and then it's the actual criteria itself, which is sitting right here. Now, notice this is not just the date but I have the greater than sign in front because I want the days after this date. When I press Enter, I
get my total amount based on these two conditions. Now, in case you don't want
to put the greater than sign in the cell, you can
add it to your formula but you have to put it in quotation marks. So this is where my criteria comes in. I want to put the greater than sign. I have to put it inside quotation marks. Then use the ampersand to connect the text with the cell reference. Okay, so keep this in mind if you're putting the
signs inside the formula. Now, in the same manner, you can use the AVERAGEIFS, as well as the COUNTIFS functions. The only difference between
COUNTIFS and the other ones is that you don't have the value range. It only counts your criteria. Number eight, the IF function. The IF function allows you to check for a condition and then decide on what you want returned
if that condition is true and what you want returned
if that condition is false. So basically, you're not
just saying equals the cell but you're checking for something and then deciding what you're returning. In this case, I have a list
of accounts and amounts and I want to put the
word check in the cells if my amount is greater than 20,000 because I want to flag those rows. Here I have a condition, so I'm going to use the IF function. Now, the first thing the IF function needs is the logical_test. This is what we're checking for. In this case I want to say if this number is greater than 20,000. So I'm going to type in 20,000 but it's good practice to put your numbers in separate cells because
you can visually see what your filter is and also be able to change it easily. Next requirement is what I want returned if this condition is true. So basically if this number is really greater than 20,000. Well, I want to return the word check and I have to put this in quotation marks because it's text. The last requirement is what I want returned if
this condition isn't met. So if my number is less
than or equal to 20,000. Well, in this case, I don't want
to put anything in the cell. I want to put nothing and nothing is quotation mark, quotation mark. Close bracket, press Enter. And in this case, I get nothing because this number is less than 20,000. Now, I'm going to send this down and we get two checks here. There is a lot that you can
do with the IF function. You can check for multiple conditions by nesting an IF function
inside another IF function or you can also use the IFS function. Number nine, VLOOKUP. The VLOOKUP function allows you to look up a value in another range and return a corresponding value. So here I have account numbers, I'm missing description. I have the description
in a separate table here. Now, this information
could be in another sheet. Just for simplicity, I
put it on the same sheet so it's easier for us
to follow the formula. It starts off with VLOOKUP. First thing we need is the lookup_value. Which value are we looking up? It's this one right here. Next is the table_array. What is the range where we can find this value and what we want returned? Well, my range is right here. I just need the content, I don't need the headers. And important here is that the column where my lookup_value's sitting in has to be the first column. The column I want returned needs to be to the right-hand
side of this column. Now, they don't have to be stuck
together like in this case. There can be other columns in between. In this example, I just
have these two columns. Another important point is
that we have to fix this because I'm planning to
pull down this formula. Next requirement is the column index, which I want returned. Do I want to get back the first
column or the second column? Well, my account description is sitting in the second column, so
I need to put a two here. And last is important
because I need to decide if I am looking for an approximate match or an exact match. The default is approximate, so if we don't put anything, and leave the formula,
it's going to look up for an approximate match. This is something we definitely
don't want in this case. We want to go with an exact match. So select FALSE, close
bracket, press Enter and we get the description back. Let's just send this down. If you have Office 365, you have an improved
version of this function and it's called the XLOOKUP function. It's a lot more flexible and easier to use and if you need more information on that, I have a few videos on the channel. Number 10, the TRIM function. The TRIM function is
something you might need when your VLOOKUP function doesn't look. So check this out. Here, just like before, we want to get the account description from this table right here. We're going to go with VLOOKUP, look_up value is our account code, table_array is this right here. We're going to fix it with the F4 key. I want to return the second column and I want an exact match. So I'm going to go with false. But now when I press Enter, it's not going to work. When I send this down, nothing works. Why? Well, take a look at our account codes. There is an additional space right here and some of our codes have
also spaces after the code. This causes problems for VLOOKUP. What TRIM does is it
gets rid of the spaces. So if I just type in TRIM here and reference the account code and just pull this down to here, notice that the first space is gone. Now, I'm just going to copy
and paste special these so that we can see in
the cell here the spaces after the code are also gone. This means if I put the lookup_value inside the TRIM function, I can get rid of all of these extra spaces and my VLOOKUP function will work. This is something you might come across when you're extracting
data from other systems. That was my list of basic
functions you need in accounting. But here's the thing, if
the office you work at has Excel for Microsoft 365, make sure you watch this video because those are amazing simple functions that will make your accounting
life so much easier. Now, if you're an accountant and you have other tips
and functions of your own, please comment below and let us know. As usual, if you enjoyed this video, please give it a thumbs up and if you're new here, welcome and consider subscribing so we get to see each other more often. (upbeat music)
