Collaborate on Invoice Reconciliation Template Excel for Management with Ease Using airSlate SignNow

Invoice reconciliation template excel for Management

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)