YT YouTube2Text

YouTube2Text → Transcripts → Excel for Accounting - 10 Excel Functions You NEED to KNOW!

Excel for Accounting - 10 Excel Functions You NEED to KNOW! — Transcript

by Leila Gharani · 3,198 words · 453 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

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

About this transcript

This page contains the full transcript of Excel for Accounting - 10 Excel Functions You NEED to KNOW! by Leila Gharani, generated from the public captions YouTube serves with the video. The transcript has 3,198 words across 453 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.