Simplify Your Billing Process with Our Invoice Tracker Template Excel for Personnel

Invoice tracker template excel for Personnel

[Music] hi everyone welcome back here in this new video tutorial in this video we are going to learn how to create a dynamic template for tracking the customer invoice and payment here in the screen you can see already build invoice and payment tracker template first of all I will explain you what are the feature of the template and how to enter the data after that I will start creating this template from the step by step process let's understand what are given in the column headers in column A we need to enter the invoice number in column B the customer name is given in the column C invoice date is given and in the column D invoice amount is given this four column we need to enter manually when we raise a invoice to our customer then we need to enter the detail of the invoice here this you can also copy from other location like you can copy this from your invoicing software also in the E column the payment due date will be calculated by using formula in the F column the number of days from invoice date will be calculated in G column we need to enter the payment amount received from the customer against the invoice number given here in the H column we have to enter the payment received date the I column calculates the difference between invoice amount and received payment this J colum contains the credit or debit amount if any this is a adjustment column sometime the customer may pay you the amount that is not same with the invoice amount because customer may have some credit or debit amount which customer adjust while doing the payment that will come here in the K column detail of debit and credit amount is given here we have to write the detail of the amount that we put here in the J column in the L column the days taken for payment will be calculated from this column we will know in how many days the customer is paying payment to us in the M column the status of invoice will be calculated there is two value either paid or due this will be autoc calculated by using a formula in the N column the overdue status will be displayed it will display either yes or no if the invoice is already overdue then yes will be displayed here if it is not overdue then no will be displayed here and if the invoice is clear the row will get automatically highlighted with the blue color and if the invoice is overdue the row gets Auto highlighted with this red color now let me add here some detail of the invoice so that you will get more clarity on this template in the invoice number I am putting one in the customer name I can choose the name of the customer by using this drop down menu I will select this customer C in the invoice date I am going to take the date of March month let's say 7th of March 2024 in the invoice amount I am going to put here 23456 for example when I put here the invoice amount here you see the row gets automatically highlighted because this invoice number one is overdue here let me add one more invoice the customer name I'm taking customer a in the invoice I will take this 1st of April 2024 in the invoice amount I'm going to put 76540 now in this case this row doesn't get highlighted because the overdue status is no because this invoice will be overdue after 1st of May 2024 only that's why the overdue status is given as no and here in this case both of the invoice is not paid hence it is showing sh due here suppose that this customer paid us payment here the payment amount is 23456 in this case now the color will Auto change to Blue and the status of invoice will be given as paid and overdue status will be given as no because it is already paid and here we need to enter the date of payment here I'm taking this 23rd April 2024 in this credit or debit amount we have to put the value if there is any credit or debit amount for adjustment purpose here suppose that this customer paid us 76,500 rupees Only You see there is a 40 balance payment suppose that if the customer a has 40 rupees credit balance then here we have to put that credit balance value for the adjustment purpose when I put here 40 then the difference becomes zero and the invoice will be marked as paid and there will be a blue color highlight so this is the way this template works now let's start with the process of creating this template for this let me go here in this new seat where I have only the format but I don't have any formula used here first of all you have to create two seets in your workbook one is the customer table the customer table should have the information of customer name and the credit side given to these customers because this credit side is required to calculate the payment due date and while creating this information put the information in table format if you put the information in table format then it will be easier for you to use the formula here now let's go to invoice and payment sheet and here in the customer name column we need to create a drop- down list to choose the customer name for this select the range click on data tab click on data validation then from this drop down choose list and click on the source then go to the customer table seat then select the customer name range if you will have additional name here in future then you have to select more row here then click okay now here you will see the drop- down menu from which you can select the name of the customer now before using the formulas here let me copy the detail of the invoice from this seat copy and I will paste it over here paste special is values now here to calculate the payment due date we need to use a formula we have to add add the credit side given to the customer with this invoice date so here the formula will be press equal to click on the invoice date plus then use vup function to grab the credit side from this seat so here use vook of function open bracket in the lookup value click on the customer name comma in the table array click on the customer table then select your data from here customer and the credit sides then give a comma in the column index number we have to provide two because the credit side is on the second column of this selection so here type two comma in the range look up choose this exact match by double clicking then close the bracket and press enter now you get the payment due date for this invoice you can now click on the field handle then drag this formula down and here you will have the payment due date and here while dragging the formula down this gives the not available error because we don't have any detail of the invoice here if you want to eliminate this not available error from here you can combine the IF function with this formula so double click on the formula then click at the beginning of the formula then combine the IF function open the bracket in the logical test of the IF function click on the invoice amount then type less than greater than symbol together it means not equal to then type double quotation two times it means blank then give a comma here what we are doing is if this cell is not blank or if this cell has some value then we will use this formula here in the value if true parameter now click at the end of the formula then give a comma in this value if fals we have to type double quotations two times which means blank close the bracket and press enter now click on the FI handle and drag this formula down now what happens is if this cell is blank nothing will be displayed here in this cell if this sale contains some value then the due date will be given here suppose that here if I add a new invoice number customer D in the invoice date I'm taking this when I put here invoice amount then you see the due date is calculated so we combined the IF function here to display nothing if we don't have any data here now to calculate the number of days from invoice date we need to subtract this invoice date from today's date so here the formula will be press equal to type the IF function open the bracket as we did earlier we will test whether this sale is blank or not so here click on this sell then type less than greater than together it means not equal to then type double quotation two times it means blank here we are checking if the sale is not blank then give a comma in the value if true or if this logic is true then we will subtract the invoice date from today's date so here type the today function bracket Open Bracket close minus then click on the invoice date then give a comma in the value if false type double quotation two times close the bracket press enter now it gives the number of dat is from invoice date sometime you may see this in an date formatting in that case you have to convert this into number formatting by going to the Home tab then click on the number formatting from here now simply drag this formula down and you will now see how many days it has been from the invoice date now in the payment received column we need to put the value manually when you receive the payment from your customer then you have to enter it over here here I'm putting 23,000 just for example then in the payment receip date I am taking this 21st of April 2024 in this difference column the formula will be press equal to then combine the IF function here also open the bracket in the logical test click on the invoice amount not equal to blank comma Now here in the value if true subtract the invoice amount from the payment reip amount click on this minus click on invoice amount then we need to add the credit or debit amount here so type the plus symbol then provide here the j6 sale reference which is the credit or debit amount now give up comma in the value if fals type double quotation twice close the bracket and press enter now here you see the difference is given as 456 if the customer has this amount in credit then here you need to put this 456 then the difference will become zero now here in the detail of debit or credit you need to put the description of the debit or credit amount here I'm putting amount credited for rate difference this is just for example now here in the days taken for payment the formula will be press equal to type the IF function Open Bracket in The Logical test click on this payment receive date not equal to blank give a comma in the value if true subtract the invoice date from payment receive date so click on payment receip date minus the invoice date then give a comma in the value if fals type double quotation two times close the bracket and hit enter now this is the days taken for payment simply click on the fill handle then drag the formula down and here in the column the days taken for payment will be calculated here for this invoice there is no any payment received hence the D taken for payment is not given now in the status of invoice we need to calculate whether the invoice is paid or due to display the status of invoice we need to check whether the invoice is paid or not for the invoice to be paid the difference should be zero here so here the formula will be press equal to type the IF function Open Bracket in The Logical test click on the difference amount is equal to zero if this logic is satisfied then the invoice is supposed to be paid so here give a comma and in the value of true type double quotation then type paid then again type double quotation then give a comma in the value if fals type double quotation then type du then double quotation bracket close then press enter now here it is given as paid because the difference is zero now here also we need to combine the IF function to display nothing if we don't have any data here so double click on the formula then click at the beginning combine the IF function open the bracket in the logical test click on this invoice amount sell not equal to blank comma then in the value if true this function will work click at the end of the formula give a comma in the value if false type double quotation two times close the bracket press enter now drag the form down and here you see if there is no data this will display nothing now here in the last column to display the status of overdue we have to check for multiple Logic for the invoice to be overdue the first logic is this s should not contain paid and the another logic is the due date should be less than today's date if both of these two condition meets then the invoice is supposed to be overdue so here the formula will be press equal to type the IF function here now in The Logical test to check for multiple condition or multiple logic we have to combine the and function so here type and function bracket open in The Logical one click on this status of invoice sale this should not be equal to so here type less than greater than symbol together which means not equal to then type double quotation type paid double quotation again if the sale is not equal to paid this is the first condition then give a comma in The Logical two we need to give the second Logic for the invoice to be overdue the second logic is the payment due date should be less than today's date so here click on the payment due date type the less than symbol then type here today function bracket Open Bracket close this is the second logic now close the bracket then give a comma in the value if true we have to put here double quotation then yes double quotation close give a comma in the value if false we have to put double quotation no double quotation again then close the bracket and hit enter and here you see the overdue status is given is no because this invoice is already paid now as we did earlier we need to combine the another IF function to display nothing here if there is no data so here double click on the formula click at the beginning combine the IF function open the bracket in the logical test click on this invoice amount not equal to blank comma this entire if and and function combination will work as value if true click at the end of the formula give a comma in the value if fals type double quotation two times close the bracket and press enter now simply drag the formula down and here you see the first invoice is given as no the second invoice is also given as no because the payment due date is on 1st of May 2024 today is only 24 4 of April so this invoice is also not overdue but the third invoice is overdue which is giving s here because the payment due date is on 2nd of April 2024 today is already 24th so this invoice is given as overdue so this formula is working perfectly here now we need to display the color highlight automatically ing to the value that is here in the status of invoice and overdue for the paid invoice we need to highlight with the blue color here I'm giving the color this blue color is for paid invoice and for the overd invoice I'm going to give the red color highlight so here I'm choosing this color and it is for overdue invoice now to highlight the row based on the value given here first of all select your data range excluding the column header then click on Home tab click on conditional formatting click on new rule then click on use a formula to determine which C to format and here in the formula bar the formula will be press equal to then we are going to highlight the row based on the value in the M column so here type column M then give the row number from which our data is starting that is the row number six here so type M6 before the column M apply the dollar symbol then after M6 press is equal to type double quotation p a ID paid double quotation close now click on the format button click on field tab then choose the blue color from here click okay again click okay now here you see when paid is return here in the startus of invoice the row gets Auto highlighted with the blue color similarly for overdue invoice we need to highlight by red color so select this range again click on condition formatting new rule use a formula to determine and here in the formula bar the formula will be press equal to type the dollar symbol then our overdue status is on the N column so here Type n then the first row number of the data that is six is equal to then here if the overdue status is s we need to highlight with the red color so here type double quotation yes double quotation close click on the format button then choose the red color then click okay again click okay and here you see when the invoice is overdue the color is red if I change the date here to a different one one if I take here a previous date then here you see the color Auto change to red because this invoice is now overdue now here to display the total invoice amount total payment received amount and total difference amount we need to use the sub total function because when we select a specific customer here by using the data filter then it should display the value for the filtered data only so to display the value here for the filtered data only use the sub tootal function here press equal to then type sub total open the bracket in the function number choose this function number n for Su by double clicking then give a comma in this reference select this invoice amount range then close the bracket press enter now it gives the sub total here simply copy this formula then paste this here in the payment amount received and here in the difference you can also paste it here in the credit or debit amount if any now here let me add one more record for customer C invoice date I'm choosing here third of April amount I'm taking here in this case if I select the customer C only from the filter then this will display the total for customer C only here you see these two invoice is added and the result is given here by the sub tootal function from this we can analyze the invoice and payment startus of every single customer so this is the way of creating this Dynamic template for the customer invoice and payment tracker in Microsoft Excel this much for today's video if you are new here in this channel I request you to subscribe and turn on all the notifications so that you will get similar kind of video updates in future thank you very much for watching this we will again meet on next video tutorial