馃攳
Excel for Accounting - 10 Excel Functions You NEED to KNOW! - YouTube
Channel: Leila Gharani
[0]
If you work in accounting
[1]
or you're planning to
become an accountant,
[4]
make sure you know the Excel
functions in this video
[6]
and the great thing is they
work for all Excel versions.
[10]
Ready?
[11]
(upbeat music)
[15]
Number one, the AGGREGATE function.
[17]
The AGGREGATE function allows
you to summarize values
[21]
and it gives you the ability
to ignore error values,
[24]
as well as hidden cells.
[26]
So for example, here I have date,
[28]
transaction number, account and amount.
[31]
What happens if I sum the amount column?
[34]
Let's use Control + Shift +
down to select the whole range,
[37]
close bracket, press
Enter, I get an error.
[41]
Why? Because I have an error in there.
[43]
With the AGGREGATE function,
[45]
I get to ignore errors.
[47]
Just start off with AGGREGATE,
[49]
then you get a lot of choices
[51]
for the type of
aggregation you want to do.
[55]
In this case, I want to sum,
[57]
so I'm going to go with nine.
[58]
Next, I get my ignore options.
[61]
I can ignore hidden rows,
ignore error values,
[64]
ignore hidden rows, error values
[66]
and nested SUBTOTAL and
AGGREGATE functions.
[70]
Now the SUBTOTAL function
is the older version
[73]
of the AGGREGATE function.
[75]
It does the same thing,
[76]
except that it wasn't as
flexible as AGGREGATE.
[79]
For example, you can't ignore
error values with SUBTOTAL.
[83]
In this case, let's say I just
want to ignore error values.
[87]
So I'm going to go with this option.
[88]
Next is the array.
[90]
This is my range that I want to aggregate,
[93]
which is this one right here.
[95]
The last option doesn't apply to us.
[97]
It's something you need
[98]
if you use the small and large functions.
[100]
Now, we're just going to close bracket
[102]
and press Enter and we're
going to get our number.
[106]
Let's update the number
formatting of this.
[109]
Now, at this point, I'm
only ignoring error values,
[112]
I'm not ignoring hidden cells.
[115]
So if I restrict this to
employee-related expenses,
[119]
and click on OK,
[120]
I still get the sum of
the entire amount column.
[124]
To ignore hidden cells,
[126]
I can change my option here.
[128]
Five would ignore hidden rows.
[130]
Three ignores it all.
[132]
So I'm just going to go
with that and press Enter
[135]
and I get the sum of
the visible rows only.
[138]
So now if I change this to
Other Nonoperating Income,
[143]
I get the sum of these two.
[145]
Now let's quickly take a
look at another example.
[148]
I have the total revenue here.
[150]
I'm using the AGGREGATE
function to sum these two.
[153]
I'm doing the same for total costs.
[155]
Again, another AGGREGATE function.
[158]
Now, what's the benefit
of using these here?
[160]
Well, if I want to
calculate profit before tax,
[163]
I can just use the AGGREGATE function,
[167]
go with SUM and for my option here,
[170]
I can say ignore nested
SUBTOTAL and AGGREGATE functions
[175]
and then as my array,
select this whole range.
[178]
When I close bracket and press Enter,
[180]
it only adds up these values
[184]
and it ignores anything that has AGGREGATE
[186]
or SUBTOTAL in it.
[189]
If you also wanted to ignore
hidden rows and error values,
[193]
you can switch your
option to number three.
[197]
Number two, the ROUND function.
[199]
The ROUND function allows
you to round your values
[202]
to the number of digits that you want.
[204]
So in this case, I have
salary and a bonus percentage.
[207]
I want to calculate total salary.
[209]
Let's start with an equal sign.
[211]
Go to C2 multiplied with open bracket
[215]
and one plus C3 where
we have our percentage.
[218]
Close bracket, press Enter
[220]
and that's my total salary.
[222]
But here I have more digits than I need.
[224]
I want to round this to two digits.
[227]
That's when I can use the ROUND function.
[229]
So start off with ROUND, open bracket.
[232]
The number in this case is the result
[234]
of the formula and the
number of digits I want
[237]
is two in this case
[239]
but you can put any number that you need.
[241]
Close bracket, press Enter
[243]
and that's my salary
rounded to two digits.
[247]
Let's update the format
to accounting format.
[249]
Now, I want to pull this formula down
[251]
so we can look at different versions here
[253]
but before I do that,
[255]
I'm going to fix my cell referencing
[257]
to make sure they don't shift.
[259]
Now let's send this down
[261]
and take a look at how we
can round to a whole number.
[264]
Well, instead of two here,
[266]
I need a zero for that.
[268]
How about rounding to the closest 10?
[270]
Instead of a one, I need to put -1
[274]
and how do I round to the closest 100?
[276]
What do you think?
[277]
Minus two.
[280]
Number three, end of month function.
[283]
With the end of month function,
[284]
you dynamically calculate
the date associated
[287]
with the end of the month based
[290]
on your specified date.
[291]
Now, this can be the
end of the current month
[294]
but it can be the end of the next month's
[296]
or previous month's.
[298]
All of this can be
controlled with a formula.
[300]
To start with, EOMONTH.
[303]
The start_date is the
current date we have.
[305]
And the number of months you want to jump,
[308]
in this case, it's the
end of the current month,
[310]
so I'm going to go with zero.
[312]
Close bracket, press Enter.
[314]
And I get a number back
[315]
because I'm in the General format.
[317]
So let's switch the
formatting to a short date
[320]
and I get 31st of January.
[323]
So let's just send this
down and see what we get.
[326]
The last dates of the months
we have in these cells.
[330]
Now, what if we want to get the last date
[332]
of the next month?
[334]
Well, that's easy.
[336]
Start_date is this and the
number of months is one.
[340]
If you want to go backwards,
you would put -1 here.
[344]
In this case one, close
bracket, press Enter
[347]
and let's just copy the formatting
[349]
of this to this and send this down.
[353]
Number four, the EDATE function.
[356]
The EDATE function allows
you to move a few months
[359]
into the future or in the past based
[362]
on your specified date.
[363]
So let's say we want to move
14 months from this date
[366]
into the future.
[368]
We're going to start with EDATE,
[369]
start_date is this one right here
[372]
and for months, I'm just
going to type in 14,
[374]
close bracket, press Enter
[376]
and I get the 1st of March 2022.
[380]
So notice the day is the same,
[382]
the month and the year can be different.
[384]
Let's just send this down
[386]
and these are all 14
months from this date.
[389]
You can also move backwards.
[391]
So if you wanted 14
months prior to this date,
[394]
we just have to change
the sign to a minus.
[397]
I'm just going to press
Control + Z to go back.
[399]
Now, you can, of course,
nest these functions
[402]
so if you wanted to move 14 months
[404]
from the date but also
get the end of month,
[408]
you can just wrap this
in the EOMONTH function.
[412]
Your start_date is going to be this
[414]
and for the months, I'm
just going to put a zero,
[417]
close bracket, press Enter
[418]
and that's 14 months from this date
[421]
but it always gives me
the last day of the month.
[425]
Number five, the WORKDAY function.
[428]
So let's say I have these days
[430]
and I want to get the date
[431]
that's seven business
days after this date.
[434]
I need to make sure I exclude weekends
[437]
and public holidays.
[439]
The function you can use
here is the WORKDAY function.
[442]
And there are two
different versions of this.
[444]
This is the updated version
[446]
where you can pick your weekends
[448]
because not all weekends in the world
[451]
are Saturdays and Sundays.
[453]
So in case your weekend is different,
[455]
go with this one, it's more flexible.
[457]
It requires a start_date, this is it,
[460]
the number of days, well,
we want seven business days
[463]
or seven working days.
[464]
Let's pick our weekends.
[466]
These, in this case,
are Saturday and Sunday,
[469]
so I'm going to go with
the default, which is one
[472]
and holidays is a list that you provided
[475]
and I already have the list
of public holidays here,
[478]
so I'm just going to select
it, press F4 to fix it
[481]
because I'm planning to
copy my function down.
[484]
Close the bracket, press Enter.
[486]
And that's seven business
days after the 1st of January.
[491]
Let's send this down
[492]
and cross-check our April dates.
[494]
My start_date is the 1st of April,
[497]
seven business days gives
me the 13th of April.
[500]
Here I have a screen shot of the calendar.
[502]
Let's start counting.
[504]
That's one working day, two,
three, four, five, six, seven.
[508]
So notice, the weekend is excluded
[510]
but also the 5th of April
[512]
because that's a holiday here.
[515]
If it wasn't a holiday,
[516]
so if I remove this, keep
your eye on this value,
[519]
my end date is going to
be the 12th of April.
[523]
Number six, 3D formulas.
[525]
The 3D formulas aren't functions
[528]
but they're a shortcut
to writing functions.
[531]
Here I have a separate sheet
[533]
for each account with different amounts
[536]
and transaction numbers.
[537]
I want to get the total of
these in the Total sheet.
[542]
The long way of writing this
[544]
is to write a separate SUM function
[546]
and reference each of these sheets.
[549]
The better way of writing this
[551]
is to use a 3D formula.
[553]
Just start off with equals,
[554]
type in SUM and in this case,
[557]
I'm using SUM because
I'm adding the values
[559]
but you can use other functions,
[561]
depending on your requirements.
[563]
Now, let's go to our first sheet
[565]
and select the range that we want.
[567]
I'm going to go all the way to row 15
[569]
because some of our sheets have more data.
[572]
Now, here comes the part that's important.
[575]
Hold down the Shift key
[577]
and then go to the last
tab you want included.
[580]
Take a look at the formula bar.
[582]
It's going from the COGS sheet
[585]
to the Non-Operating Expenses sheet.
[588]
Everything else in the
middle will be included.
[592]
close bracket, press Enter
[594]
and we get the same result.
[596]
The advantage of doing it this way
[597]
is that it's dynamic.
[599]
If I happen to have another account,
[602]
I'm going to drop Lease
in the middle somewhere,
[605]
it's automatically going to be included
[607]
in my total column.
[609]
So in Lease, I have two values.
[611]
This is the difference
between my 3D formula version
[614]
and the old version.
[617]
Number seven, SUMIFS,
[619]
and other IFS functions like
COUNTIFS and AVERAGEIFS.
[623]
And the great thing
about the IFS functions
[625]
is that you can sum, get the average of
[627]
or count values based on
one or multiple criteria.
[632]
So in this case, let's say
I want to add the amount
[635]
where account equals services.
[637]
I can use the SUMIFS function.
[640]
The first requirement is the sum_range,
[642]
so this is the column
where I have my values in,
[644]
in this case, it's the amount column.
[646]
Now, I'm going to fix the cell referencing
[649]
by pressing F4
[650]
because I want to copy my formula down.
[652]
Next requirement is the criteria_range1.
[655]
This is the range on which
my condition is based on.
[659]
In this case, it's the account column.
[661]
So I'm going to select
that, fix it with F4.
[664]
Last requirement in this
case is my actual criteria.
[668]
That's services, which is sitting in G3.
[671]
Close bracket, press Enter.
[673]
And that's the total amount
for the services account.
[677]
Let's drag this down and
we get the total amount
[680]
where account equals
employee related expenses.
[684]
But what if I want to
add another condition?
[687]
That condition is based on the date column
[689]
and I want to only add the amounts
[692]
for the days after 15th of January
[695]
and account has to be
employee related expenses.
[697]
So I have two conditions.
[699]
Well, it's very easy to
add another condition.
[702]
We have criteria_range2.
[704]
First is the range which
the condition is based on.
[708]
In this case, it's the date column.
[710]
I'm just going to be consistent
[711]
and press F4 to fix this
[713]
and then it's the actual criteria itself,
[715]
which is sitting right here.
[717]
Now, notice this is not just the date
[719]
but I have the greater than sign in front
[721]
because I want the days after this date.
[724]
When I press Enter, I
get my total amount based
[728]
on these two conditions.
[730]
Now, in case you don't want
to put the greater than sign
[733]
in the cell, you can
add it to your formula
[736]
but you have to put it in quotation marks.
[739]
So this is where my criteria comes in.
[742]
I want to put the greater than sign.
[743]
I have to put it inside quotation marks.
[746]
Then use the ampersand to connect the text
[749]
with the cell reference.
[751]
Okay, so keep this in mind
[752]
if you're putting the
signs inside the formula.
[755]
Now, in the same manner,
[756]
you can use the AVERAGEIFS,
[759]
as well as the COUNTIFS functions.
[762]
The only difference between
COUNTIFS and the other ones
[765]
is that you don't have the value range.
[768]
It only counts your criteria.
[771]
Number eight, the IF function.
[773]
The IF function allows you to check
[775]
for a condition and then decide
[777]
on what you want returned
if that condition is true
[780]
and what you want returned
if that condition is false.
[783]
So basically, you're not
just saying equals the cell
[786]
but you're checking for something
[788]
and then deciding what you're returning.
[791]
In this case, I have a list
of accounts and amounts
[795]
and I want to put the
word check in the cells
[797]
if my amount is greater than 20,000
[800]
because I want to flag those rows.
[803]
Here I have a condition,
[805]
so I'm going to use the IF function.
[807]
Now, the first thing the IF function needs
[809]
is the logical_test.
[810]
This is what we're checking for.
[812]
In this case I want to say if this number
[814]
is greater than 20,000.
[817]
So I'm going to type in 20,000
[819]
but it's good practice to put your numbers
[821]
in separate cells because
you can visually see
[824]
what your filter is
[825]
and also be able to change it easily.
[828]
Next requirement is what I want returned
[830]
if this condition is true.
[831]
So basically if this number
[833]
is really greater than 20,000.
[836]
Well, I want to return the word check
[838]
and I have to put this in quotation marks
[840]
because it's text.
[842]
The last requirement
[843]
is what I want returned if
this condition isn't met.
[846]
So if my number is less
than or equal to 20,000.
[851]
Well, in this case, I don't want
to put anything in the cell.
[854]
I want to put nothing and nothing
[855]
is quotation mark, quotation mark.
[858]
Close bracket, press Enter.
[859]
And in this case, I get nothing
[861]
because this number is less than 20,000.
[865]
Now, I'm going to send this down
[867]
and we get two checks here.
[869]
There is a lot that you can
do with the IF function.
[872]
You can check for multiple conditions
[874]
by nesting an IF function
inside another IF function
[878]
or you can also use the IFS function.
[882]
Number nine, VLOOKUP.
[884]
The VLOOKUP function allows you
[885]
to look up a value in another range
[888]
and return a corresponding value.
[891]
So here I have account numbers,
[893]
I'm missing description.
[894]
I have the description
in a separate table here.
[897]
Now, this information
could be in another sheet.
[900]
Just for simplicity, I
put it on the same sheet
[902]
so it's easier for us
to follow the formula.
[905]
It starts off with VLOOKUP.
[908]
First thing we need is the lookup_value.
[910]
Which value are we looking up?
[912]
It's this one right here.
[914]
Next is the table_array.
[916]
What is the range
[918]
where we can find this value
[919]
and what we want returned?
[921]
Well, my range is right here.
[923]
I just need the content,
[925]
I don't need the headers.
[926]
And important here is that the column
[929]
where my lookup_value's sitting in
[931]
has to be the first column.
[933]
The column I want returned needs
[935]
to be to the right-hand
side of this column.
[938]
Now, they don't have to be stuck
together like in this case.
[940]
There can be other columns in between.
[943]
In this example, I just
have these two columns.
[946]
Another important point is
that we have to fix this
[950]
because I'm planning to
pull down this formula.
[952]
Next requirement is the column index,
[955]
which I want returned.
[957]
Do I want to get back the first
column or the second column?
[960]
Well, my account description is sitting
[962]
in the second column, so
I need to put a two here.
[966]
And last is important
because I need to decide
[969]
if I am looking for an approximate match
[971]
or an exact match.
[974]
The default is approximate,
[976]
so if we don't put anything,
[977]
and leave the formula,
it's going to look up
[980]
for an approximate match.
[981]
This is something we definitely
don't want in this case.
[984]
We want to go with an exact match.
[987]
So select FALSE, close
bracket, press Enter
[990]
and we get the description back.
[993]
Let's just send this down.
[994]
If you have Office 365,
[997]
you have an improved
version of this function
[1000]
and it's called the XLOOKUP function.
[1002]
It's a lot more flexible and easier to use
[1005]
and if you need more information on that,
[1007]
I have a few videos on the channel.
[1010]
Number 10, the TRIM function.
[1013]
The TRIM function is
something you might need
[1014]
when your VLOOKUP function doesn't look.
[1017]
So check this out.
[1018]
Here, just like before,
[1019]
we want to get the account description
[1021]
from this table right here.
[1023]
We're going to go with VLOOKUP,
[1025]
look_up value is our account code,
[1027]
table_array is this right here.
[1030]
We're going to fix it with the F4 key.
[1032]
I want to return the second column
[1034]
and I want an exact match.
[1036]
So I'm going to go with false.
[1038]
But now when I press Enter,
[1040]
it's not going to work.
[1042]
When I send this down, nothing works.
[1045]
Why?
[1046]
Well, take a look at our account codes.
[1048]
There is an additional space right here
[1051]
and some of our codes have
also spaces after the code.
[1056]
This causes problems for VLOOKUP.
[1058]
What TRIM does is it
gets rid of the spaces.
[1062]
So if I just type in TRIM here
[1064]
and reference the account code
[1067]
and just pull this down to here,
[1069]
notice that the first space is gone.
[1071]
Now, I'm just going to copy
and paste special these
[1075]
so that we can see in
the cell here the spaces
[1079]
after the code are also gone.
[1081]
This means if I put the lookup_value
[1084]
inside the TRIM function,
[1087]
I can get rid of all of these extra spaces
[1091]
and my VLOOKUP function will work.
[1093]
This is something you might come across
[1095]
when you're extracting
data from other systems.
[1099]
That was my list of basic
functions you need in accounting.
[1102]
But here's the thing, if
the office you work at
[1105]
has Excel for Microsoft 365,
[1108]
make sure you watch this video
[1110]
because those are amazing simple functions
[1112]
that will make your accounting
life so much easier.
[1116]
Now, if you're an accountant
[1117]
and you have other tips
and functions of your own,
[1120]
please comment below and let us know.
[1122]
As usual, if you enjoyed this video,
[1125]
please give it a thumbs up
[1126]
and if you're new here, welcome
[1129]
and consider subscribing
[1131]
so we get to see each other more often.
[1133]
(upbeat music)
Most Recent Videos:
You can go back to the homepage right here: Homepage