Create the Perfect Contractor Bill Format in Excel for Technical Support

Contractor bill format in excel for Technical Support

welcome back to new video tutorial in Microsoft Excel and in this video we are going to learn how to create a customer payment tracker in Microsoft Excel and this payment tracker system is a fully dynamic system and in the screen you can see I already built customer payment tracker and first of all I will quickly show you what are the features included here and how does the system works and then I will show you how to use formulas here in this system and make this system fully Dynamic and if you notice here there are two seats the first one is the payment tracker sheet this is the master sheet here and if you go to the another sheet this is the customer information sheet here in this customer information Suite we have to list out our customers name of the customer is optional here this address does not have any link to the payment tracker sheet so this is optional column and here the credit side date is given and this is a compulsory column because based on the credit side days we calculate the uh invoice due date here in the F column and this status of invoice is also linked with the due date so this is a very important information here in the customer information table and when you add a new customer simply go here and add the new customers name address and credit side dates let's go to the master sheet and here I will show you how does the system works first of all let's understand what are given in the columns the First Column column A contains of the invoice number here we have to record the invoice number of the customer the next one is date of invoice here we have to put the invoice date manually the third one is the customer's name column and here you can either choose customers name from this drop down list or you can directly type the value or you can copy the customer's name from other source for example if you raise invoice to your customer by using accounting software then you can directly copy the customer's name from the software you can export the details from the software into Excel file then copy the customer's name and paste it over here and while doing paste you have to do paste special values because direct paste will remove the formatting from here that's why you have to right click then go to paste special and then paste special and then values you have to use paste special values option while pasting the data from other sources here and the third column contains invoice amount and here we have to put the value manually or we can copy from the other source and one thing you have to be clear that when when copying the customer's name the name should exactly match with the name which is written here this name should exactly mess with the customer name that you copy from other source so that you have to be clear and the next column is number of days column and here the number of days from the invoice date will be calculated for example the invoice was made on 1st of October 2022 today is 28th of October 2022 so the number of days from the invoice date is 27 days and all these numbers are automatically calculated and by using this number of days we can follow up the payment with our customer the next one is the invoice due date column and here the value will be calculated by using formula due date means when we add the credit side days in the invoice date then the invoice due date will be calculated the next one is payment received amount and these columns we have to fill manually when customer page payment for a particular invoice then we record the payment received amount here the next one is data payment and here we need to put the date of the payment by customer the next column is days taken for payment and this this taken for payment is a Formula calculated column which calculates how many days the customer took to pay the payment for example here you can see the invoice was made on 1st of October and payment was received on 25th of October so the days taken for payment is calculated is 24. and the next column is due amount column and this is the total outstanding column also when we subtract this invoice amount from the payment received amount this due amount will be calculated and here I have calculated the due amount in negative figure because this is a receivable amount from the customer that's why I have calculated this in minus figure the next one is the credit note or debit note amount if there is some receivable or payable uh credit notes or debit nodes to the customer then we record the amount here because sometimes uh the customer if the customer pay is this amount then the original invoice amount in that case there may be some credit note which we have raised to the customer so in that case the customer deducts the amount of credit note while paying the payment so in that case for the adjustment purpose we put values here in this credit note or debit load amount the last column is the status of invoice and this is a very important column here in this uh template because based on the value given here in this status of invoice the caller highlight in the row will automatically change for example if the status of invoice is paid it is highlighted with the blue color and if the invoice is partially paid it is highlighted with the yellow color if the payment is invoice is overdue then it is highlighted with the red color if the invoice is due there is no color highlight so the color highlight will automatically change ing to the value which is calculated here and here if you see a very long formula is used and writing this formula is little bit complex so you have to be very very careful while writing this formula and here if you see ifs function is used we will later learn how to use formula here in this column and this is all about the columns heading in this template and now I will show you how to use this template for example invoice number here I am putting one more invoice for example invoice number 107. the invoice date is suppose that October 14th for example customer name you can choose from this drop down list which is customer jh and invoice amount is I am taking invoice amount 15 472 for example when you put the invoice amount then here you can see it is now the Highlight is gone because this is due only the invoice is only due that's why there is no any color highlight similarly I am adding one more record here for example invoice number 108 invoice made that is suppose October 15th customer name I will choose from the drop down list which is customer a B for example 988 now when you put all the records here then here you can see this is given as overdue because if you go to the customer information sit and see the credit side of customer a b the credit side is only 12 days and the invoice was made on 15th of October and today is the today is 28th of October so the the number of days is already 13 days from the invoice date and we have given only 12 days side to the customer that's why it is calculated as overdue here and this is automatically calculated and the color highlight is also automatically given so in this way the template works and for example if I put here the payment received amount for example the customer paid this full amount of 80 541 I will post the payment receipt amount here which is 80 541 rupees when you put the full amount here then here you can see the color highlight is automatically changed to glue and the status of invoice is given is paid and for example the data payment is uh suppose that October 23rd then here you can see the days taken for payment is auto calculated which is 20 days and for example in this next uh invoice if the customer paid partial amount for example the customer paid 1 lakh rupees only then here you can see it is given as partially paid because the invoice amount was to 205 000 and the received amount is only one hundred thousand that's why it is given as partially partially paid and for example if the customer paid excess amount for example 210 000 here in this case then it will be given as excess payment here you can see the excess payment is given so everything in the seat will be dynamically updated and if you notice here the total outstanding amount is given and this will also dynamically update and if you see here the total overdue amount is given for example if I delete this number from here then here you can see the value is changed the total outstanding value is changed here which is 383926 and it if I put here two zero five zero zero zero then here you can see the outstand against uh reduced and here you can see the overdue amount is automatically calculated for example if I delete this amount from here then it will be given as overdue and in this case the amount is also changed here so in this way the template box now I am going to show you how to use formulas here in this template because the user formula is very important part here I am not going to make this formatting part because the formatting is very simple and if you want to download this template you can simply go to the description section below and uh visit the website given in the description section from which you can download the free template for your personal use you can download the template and practice and here I am first going to delete all the formulas used and all the data validations and all the conditional formatting so I am selecting these already filled values from here I am deleting this from here delete then I am clearing the conditional formatting from here which part is to automatic color coding so I am going to clear rules and uh clear rules from the selected cell now I am going to data validation from the home menu from the data menu then I am going to data validation option here then I am clearing all the validations which is used here clear all now click OK and here you can see all the uh data validation formulas and conditional formatting is now removed there is nothing left only the formatting is remaining and here I am also going to delete these formulas from here in this way now the format is fully blank now let's learn how to use formulas here in this template to make this template fully Dynamic and before applying formula here I am going to add some records which will be easier for you to understand how to write formulas so here I am going to add some records invoice number 101 and data universe is suppose October 1st customer name here we need to create a drop down list to to choose the name of the customer so to create a drop down list here first of all select the column then go to the data menu and then go to data validation which is given here data validation then we have to choose list from the drop down then click on the source and here we have to define the source of customer name so click on the source then go to customer information table and select the name of the customer in this way and while selecting you can select till some more row here because in future if you add new customer here then it will display in the drop down list so choose some more rows here now click ok when you do this here you can see the drop down menu from which you can choose the name of the customer here I am choosing customer AV for example and suppose the invoice amount was 104 000. similarly I am adding one more record here 102. and invoice that is suppose that October 3rd customer name is suppose that customer JS invoice amount each suppose that's two hundred three thousand 147 so this is the data I have added now we need to use function formula here in this number of days column and in this column we have to use a very simple formula which is equal to then we have to subtract this invoice date from the current date so the formula here will be equal to then write today formula today Open Bracket close bracket then minus click on the invoice date press enter and here you get the result which is 27 days now you can simply drag this formula for other rows and here when you drag the formula here you can see some number are given because this is given because here we don't have any data so if you want to hide these ballots if here is no data then you can combine the if formula here in this formula so double click here then go to the beginning of the formula then type if formula if Open Bracket and here in this logical test you have to test logic which is if the invoice number is blank then we have to display blank here and if the invoice number is not blank then we have to use this function so after typing e formula and opening bracket then click on the invoice number then equal to then type double quotes twice this double quotes twice denotes blank in the formula so if this sale or the invoice number is blank then give a comma in this case if this cell is blank then we have to display blank here in number number of days so after typing comma then you have to type double quotation again which will display blank if the invoice number is blank now give a comma and here in this value if false this formula will calculate the value so we have tested a logic which is if the invoice number is blank then we have to display blank if not we have to calculate the number of days so now go to the end of the formula close bracket and press enter and here you can see the number of days is calculated and when you double click here then if here is some data then the number of days will be calculated otherwise the number of days will not be calculated or it will be given as blank so in this way we use function or formula in the number of days column and if you want to learn more about this if formula then you can go to the description section below where I will put the video tutorial link for the if formula tutorial now let's go to another uh column which is invoice due date and here in this invoice due date we have to use this formula equal to we have to add the credit side in this invoice date based on the customer name given here so here in the invoice due date the formula will be equal to this invoice date click on the invoice date Plus then we have to use the vlookup formula to uh capture the credit side for the customer given here so here combine the vlookup formula type vlookup open the bracket and within this vlooker formula the lookup value is the customer's name so click on customers name then give a comma and in the table array we need to go to the customer information sheet and select the name of the customer two credit side days and while selecting this table you can select till some more row because in future if you add a new customer here then the formula will work properly after selecting this range then press F4 key in the keyboard to fix this range press ok then give a comma because the credit side is given on the third column column one column two column three so here we have to type 3 for the column index number now give a comma and in this range lookup we have to type 0 for the exact match now close bracket and press enter and here you can see the invoice due date is calculated the invoice was made on 1st of October and the credit side given for this customer a b is 12 days so when we add 12 days in this first of October then the invoice due date will be 13th of October 2022 which is calculated here so in this way we calculate the invoice due date now you can simply drag this formula till the last row and before dragging the formula first of all we have to combine if formula here so that if the invoice number is blank then it will display blank here because if we directly drag this formula down in that case if here is no invoice number given then the N A error will be given here that's why we have to combine the if formula here D in this formula also so go to the beginning of the formula then type if open the bracket as we did earlier in the number of days we have to click on the invoice number equal to blank type double quotation give a comma and in this value if true we have to display blank so type double quotation twice for Value if true now give a comma and in the value it falls this formula will work now go to the end of the formula then close bracket and press enter now you can see the invoice date when you double click on this here you can see if the invoice number contains some value then it is calculated and if there is no invoice number then we don't need to calculate anything here we have to leave it blank so we use this formula here and if you want to learn more about this B lookup formula you can go to the video description below and watch the video tutorial link for B lookup function because if you don't understand this B lookup function then it will be hard for you to understand this formula here so if you are new for vlookup then you can go to the vlookup tutorial from the video description below and learn vlookup then it will be easier for you to understand this formula is here now here in the payment received amount we have to put the value manually when customer page payment for the invoice then we will put it here manually the next column is data payment receipt and this column is also manually calculated or manually filled the next one is days taken for payment and here in the days taken document we use this formula equal to then we have to subtract this invoice date from the invoice due date to calculate this taken for payment so press equals then the date of payment minus the invoice date and here in this case the test taken is calculated in negative number because we don't have put any data payment here that have received here that's why it is given and given as negative if I feel the payment received amount here for example it is 104 000. and the payment was received on suppose that uh 15th October 15th now here you can see the days taken is calculated and here also we combine e formula because if this debt of payment is not given here in that case we don't need to calculate the test taken procurement here because the days taken cannot be calculated if this sale is blank so we can combine a formula here in this formula in this way go to the beginning of the formula then type if open the bracket in the logical test clean click on the date of payment then equal to blank comma then in the value if true type blank Again by typing double quotation twice give a comma and then go to the end of the formula close bracket and press enter now here you can see that this taken is calculated and you can now simply drag this formula to the last row here and here you can see if the cell contains of some date then it will be calculated otherwise not so we use this formula here and while when writing formula here sometimes this may display wrong values this cell May display the date formatting in that case you have to Simply select the column then go to the home menu and change the date formatting into number formatting because sometime this will result in this format so if if the cell displays this format you can simply go to the number formatting from here in this way and remove the decimal here by going here in the decrease decimal in this way now let's move on to another column which is column J and here in this interview amount we have to use this formula click on the cell then press equal to then formula here will be the payment received amount minus the invoice amount this formula will calculate the due amount here in this column now you can simply drag this formula by holding the mouse right click and uh left click and hold the mouse then drag it in this way so that the div amount will be calculated here you can see uh the amount is given on minus figure because because this is the receivable from the customer that's why I have calculated this in a negative number so this simple formula will calculate the due amount here in this column the next one is credit note or debit note amount and this column is manually updating column we don't have to use formula here in this column the next one is invoice status of invoice and this is a very important column here because we have to use uh a long formula here and this is little bit tricky also so you have to be very careful while writing formula here in this status of invoice column and here in this status of invoice we will display some values which is uh the the invoice may be due the invoice may be overdue the invoice may be partially clear the invoice may be excess clear the invoice may be uh there there are some values to be displayed here like dues overdue partially clear excess payment like that so we have to use a formula here in this status of invoice and before using formula here we have to be clear on the logic to be tested here in this column so here I am going to write the values to be displayed here in this status of invoice column and here I am writing the first value we will display is due then we will display another value which is paid the first one is paid I am writing here paid invoice the next one is due invoice or we can also say first of all we will display the paid invoice then we will display partially paid partial paid the next one will display is excess payment or you can say access speed the next value is the due and the next one is overdue we will display these values here in this start of invoice by using formula and here we will also write the logic for these values these are the values and this is the logic here I am writing this because if you if you clearly understand this then it will be easier for you to write the formula here in this column so for the invoice to be paid this du amount should be exactly equal to zero if the div amount is 0 then we can say that the particular invoice is paid so here I am writing the logic which is due amount should be equal to zero this is the logic if this logic is satisfied then we can say that the invoice is paid and for the invoice to be partially paid what should be the logic here so we have to be clear on this for the invoice to be partially clear first of all there must be some value in the payment received amount because if the if the invoice is partially paid for example if uh the customer p is one lakh rupees here this is partial payment because the invoice amount is 203 147 and customer paid only one lakh rupees so there must be some value here in these payment received amount so the our first logic here is payment receipt sum value or we can also say that payment received should be greater than zero this is the first condition and the next condition for partial payment is this due amount columns should be greater than zero should be less than zero because the number is given a negative figure and that's why the second logic is the div amount should be less than zero and here should be sum value for the invoice to be partially cleared so here I am going to write the another logic which is due amount should be less than 0 which is the diamond should be in negative number so when these two conditions are satisfied we can say that the invoice is partially payment partially paid the next is excess payment and for the invoice uh to be excess payment the logic should be this D amount columns should be greater than zero so here the logic will be due amount is to be greater than zero for excess payment similarly for the due invoice to calculate due here in the status of invoice our logic will be we have to check the invoice due date based on the invoice due date column we calculate the due or overdue status of invoice so here if this invoice due date in if the invoice due date is greater than current date we can say that the invoice is due so here the logic will be invoice due date should be greater than current date or today's date it's logic we have to test here similarly for the invoice to be overdue the invoice due date should be less than current date So based on these Logics we will Define formula here in the status of invoice column so let's learn how to write the exact formula here because I I have written the value and logic here to make you understand about the formula that we are going to use here in this column now so now use the formula here in this way equal to then here we will use the ifs function iefs function type ifs then Open Bracket and here in this logical test one first of all we need to test the first logic which is the do amount equal to 0 so click on do amount then type equals 0 this is the first logic then give a comma and uh here we we return we have written the first logic which is D amount equal to 0 and if this logic is satisfied then the invoice is supposed to be paid so here in here in value if true we have to type paid between inverted comma type inverted comma then type paid inverted comma close now give a comma then the second logic here will be we have to test the second logic here for the partial payment and in case of partial payment we have to test two different Logics the first one is payment receipt should be greater than zero and the second logic is due amount should be less than zero so to test two different logic we have to combine the and formula here in this logic Test 2 so type A and D and the formula then open the bracket and within the and formula we have to give the first logic which is the payment received should be greater than zero so here click on the payment received amount greater than zero now give a comma the second logic here is the div amount should be less than zero so click on the due amount then type less than zero now close bracket and this end formula is our second logical test now give a comma and if this logical test is satisfied then our invoice is supposed to be partially partially paid so here in the value if true we have to type inverted comma then partial partial then partially paid then inverter command close then give a comma the third logic we have to test here is the excess payment and for the invoice to be excess payment the due demo due amount should be greater than zero so here we have to uh click on the due amount and here because of this formula I am not able to click on the D amount so I can simply type the sale address which is J5 so here type J5 and if the due amount is greater than 0 in this case the invoice is supposed to be access paid so give a comma and in the value value if true type double quotes then excess payment excess payment or access paid now double quotation close then give a comma then the fourth logic here is the due is for the invoice due so here we have to test the logic which is invoice due date should be greater than current date so click on invoice due date then type greater than and the current rate will be given by the today formula so type today formula here today then Open Bracket close bracket then give a comma and here in this value if true 4 we have to type due so double quotes then type due double quotation close then give a comma then the fifth logic here is here is for the overdue and for your body the invoice due date should be less than current date so click on invoice due date then less than today formula today Open Bracket close bracket then give a comma and then type double quotes then type overdue overdue double quotation close then bracket close now you can press enter and here you can see the value is given is paid because this invoice we have already received the payment the invoice amount was one lakh 104 000 and payment received amount is also 104 000 so there is no any due amount for the particular invoice that's why it is given as paid now after writing this formula you can now drag this formula directly here and here you can see partially paid is given because the invoice amount was two zero three one four seven and the partial payment is received which is only one hundred thousand here that's why it is given as partially payment and if there is the values given here is blank then we don't need to display this result here that's why we have to again combine the if formula here as we did earlier here in other formulas so double click on the formula here then go to the beginning of the formula then type if formula if open the bracket now click on the invoice number equal to blank then give a comma then in the value of true display blank by giving double quotes then again give a comma then go to the end of the formula and close this formula by giving one up giving a bracket now press enter and then go to the plus button and double click now the formula is copied down and if there is no value given in the row then this will be given as blank and if there is value then it will be calculated so in this way we use the formulas here now I am going to add some more invoice number here and test whether the formula will display the result correctly or not so here I am giving the inverse number one zero three date of invoice is suppose that October 5th customer name is suppose that customer EF invoice amount suppose that one zero four five zero zero now it is given as due because we added a new invoice and there is no payment for this invoice that's why it is given as due I am again one adding one more record here which is one zero four uh October 5th customer name is suppose that customer a b invoice amount is suppose that two zero five one four seven and uh payment received I'm leaving it blank now here you can see it is given as overdue because the customer a B's invoice was made on 5th of October and credits right given for the customer is 12 days only and the invoice due date is 17th of October 2022. today is already 28th of October and there is no payment receipt from the customer that's why this particular invoice is given as overdue so the formula is calculating the value correctly here now we have to use the conditional formatting to automatically highlight the colors here ing to the value given here in this column so let's learn how to use conditional formatting to automatically color code the row here based on the value that we calculate here so to use conditional formatting first of all select this row here from here in this way then go to conditional formatting in the home menu conditional formatting then go to new rule option or if you can go to the Highlight cell rule here you don't need to go to new rule here you can go to highlight cell Rule and then go to equal to and here in this case we have to go we can we have to go to conditional formatting and we have to go to the new rule option go to new rule and here choose use a formula to determine which cell to format click on the you know on this option then here we have to write the formula which is equal to then we have to type this last column which is L and we have to type the first row which is five after typing L5 then before L we have to give the dollar sign now uh go to the end of this L5 and then type equal to then here we have to type paid and we have to type paid between inverted comma inverted comma then type paid Now inverted comma close and after writing this formula here then go to the format option which is given here and uh if the invoice is paid then we if you want to display the blue color then you can choose blue color from this color option here the color dialog box is opening so after opening the color format sales dialog box go to the field option and then choose blue color from here I'm going to more color option here and choosing this blue color highlight for the paid invoice so click OK and click ok then again click ok and here you can see when the invoice is paid it is highlighted with the blue color and this is now fully automatic if the the value given here is paid then it will be highlighted the blue color for example if I put here 203 147 then here you can see it is auto highlighted by blue color so in this way we have to repeat uh values for we have to repeat the conditional formatting for other values also so again select this data then go to conditional formatting go to new rule option given here and go to user formula to determine which cell to format now we will use conditional formatting for the overview invoice so go to the formula ribbon here and type equal to then type the last column which is l l and type the first row which is five then before here I'll give a dollar sign then type equal to then here we have to type overview between the inverter comma overdue then inverted come up close now go to the format go to field then go to more colors and choose red color from here for the overdue invoice click ok now click OK again click ok now here you can see when the invoice is your body it is highlighted by red color and for the due invoice we are not going to use any uh color coding here and now let's go to use color coding for the partial paid invoice so here I am going to give some number which will display partial payment here now select the data then go to conditional formatting go to new rule option go to use a formula to determine which you'll do format and use this function here equal to then type dollar then J5 equals then double quotation then type partial speed now double quotation close go to the format then choose a different color for partial payment here I am choosing this yellow color highlight for the partial payment now click OK and click ok and here you can see there is no formatting applied so I am again going to conditional formatting and go to manage rules here may be some error happened that's why it is not working so I am going to edit the rule by clicking on edit rule option and here I have put j instead of putting l we have to put l here because the last column is l or the value given here is in the L column that's why we have to change this to L now click ok and again click on apply and then okay now here you can see yellow color highlight is given for the partially paid invoice now we have to use another color code for the excess payment for example if there is excess payment of 210 000 rupees in this case it will show as access paid so here use the color coding for excess payment by applying the same method go to conditional formatting go to new rule choose this user formula option then type this formula in the formula even equal to dollar and then J5 and then L5 equal to then double quotes then you have to type Xs access paid double quotation close go to format then go to the color option here in the more colors I am choosing this green color highlight for the access payment now click ok and again click ok now here you can see when the invoice is access paid then it is given as excess payment so now we applied our formula here in we applied the conditional formatting here in this table so that ing to the value calculator here the color highlight will be automatically given now we have to use this uh we have to use formula here in this black highlighted cells and here we have to calculate the total values here we have to calculate the total invoice amount here we have to calculate total payment received similarly total due amount or total outstanding amount and here we have to calculate total credit note or debut amount and then we have to link the total outstanding here and we have to link the total overdue here so let's learn formula here in this cell and here uh the invoice number is not highlighted because When selecting the values I forgot to select the invoice number that's why the color is given till here only so you can simply go in the conditional formatting and manage the rule here you can you have to select these then go to conditional formatting and manage rule option and here you can see in this applies to we have selected from the B column that's why it is given it is not highlighted in the column base so here we have to change this just uh change this B to a in the applies to section I am changing all B to a here in this way now click apply and then click ok I'm again going here in the conditional formatting manage rules A5 to L6 now here the color is now already given in the invoice number so after doing this now let's use formula here in this cell and here in this cell we have to use the sub total formula because when we apply filtering here and then choose a particular customer in that case the total for the selected customer should be displayed here that's why here really is the subtotal formula equal to then types of total open the bracket then in the function number type 9 for the sum then give a comma and then here we have to select this range from the beginning to the end in this way close bracket and press enter and here you can see the total invoice amount is displayed now copy this paste the same formula for payment received paste the same formula for do amount and for the credit note the dividend amount so the values are now calculated here and benefit of using subtotal formula here is for example if you select the headings then go to data menu then apply data filter here then if you choose customer a b only for example if you want to see the record of customer a b only then here you can see the total for the customer a b only is displayed here here you can see the detail of customer a B is displayed in this way now I am again going back to all customer then removing the filter or I can leave this filter here now after applying the soft total formula then here we have to link the total outstanding amount from this do amount because this du amount means the total outstanding and here I have used the text box to link the value and to link value in the text box I am here going to show you first of all we need to create a text box from insert menu click on insert then go to text box option and here you can choose text box then draw the text box where you want to link the value in this way now the text box is drawn and click on the text box area in this way then which value you want to link here you have to go to the formula menu then press equals then if you want to link this invoice amount click on this cell then press enter and here you can see the sale is linked and it is given an yellow color text that's why it is not visible you can simply go here and change the color to Black now here you can see when you change this to blank here you can see the value is linked so in this way we can display value here in the text box by using this method and you can increase the font size within the text box by going to the font size option in this way here you can see the font size is are displayed so in this way we can display value in the text box this is very simple and here the total overdue is calculated which is 205147 if the overview is given on this column then we have to calculate display this value only for example if I delete this number from here this will be given as overdue and this value is automatically changed and here I have linked this text box to this m one cell where the formula for total over D which is given so here I am going to show you how to calculate the total over the amount here I am making the number visible this text box is linked with this number and here if you see the summit formula is used so here I am going to show you how we can calculate the overdue amount only by using the sumeep formula so I am delete formula from here and we calculate the overdue amount only because we have to add the values when there is overdue return so we have to add these two values only in this cell to display the overview so the formula here will be equal to then type summary formulas sum if open the bracket and here in the summary formula first of all you need to select the criteria range so our criteria range here will be this status of invoice column so select this column in this way now give a comma and our criteria is the overdue so type double quotation type overdue here then inverted comma clothes for double quotation close now give a comma and here in this sum range we have to select this due amount range in this way now close bracket and press enter this formula give you the total overdue and if you want to learn more about the summit formula then you can go to video description below and you can get the video tutorial link for Summit formula you can learn the summit formula from that link now after displaying the value here then here you can see this value is linked to the total overdue so you can make this value Invisible by selecting the font color for these two white now the value is overview so in this way we calculate the total overdue here now we are almost completed creating this template all the formula we calculated successfully here now if you add a more record here for example invoice number 105 uh invoice date is suppose that October 6th customer is suppose that customer I am choosing customer IG and uh invoice amount is suppose 50 214. and when I entered value here it is given as overdue because the invoice was made on 6th of October and today is already 28th and the number of days from invoice date is 22 days and if you go to the customer information and see customer IGA credit days it is 21 days only and today is already 20 2 so that's why it is given as overdue here I am adding one more record invoice number 106 data invoice is suppose there October 8th here I am choosing the customer having 30 days credit limit for example customer EF I am choosing here customer EF and when I put the invoice amount here now it is given as due because the customer has got the credit side of 30 days and number of days is only 20 dates this that side is given as due so in this way values will be automatically calculated here in this template now here I am going to add the payment received amount for example the customer a b paid the excess payment for example two zero five here I am putting two two one zero triple zero this is an excess payment here you can see when the customer pays payment excess payment then it is highlighted with the green color and here you can see the excess payment is 4853 and in this case suppose that if you have some payment receivable from the customer or if you have issued a debit note to the customer in that case you can put the debit note amount here which is 0.853 and when you put 4853 here this amount should be zero and here I have uh missed one formula because we have to add this credit node or debit note amount in this TV amount column so go to the formula here and then add the debit note or credit node amount in the D amount because this column also has to be linked here in the formula now double click on this in this way and here we have to put the number in negative figure because the debit node means we have to receive this amount from the customer so I have to put here in it is in minus figure minus 0.53 when you put a number here then the div amount will be 0 here and it will be given as paid so in this way we can update the payment here and for example here I am if I put uh the the amount which is less than inverse amount for example 150 000 only in this case now it is given as partial payment and it is auto highlighted by the yellow color and if I put here the full amount for example two zero three one four seven then it will be given as paid invoice and suppose that the data payment is October 25th then you can see that this taken is calculated which is 22 days and if you put here the days taken for payment for example October uh 28th the days taken for payment is how to calculate it and the city is now fully Dynamic so in this way we create a customer payment tracker in Microsoft Excel by using this formula and we can make the template fully dynamic and if you want to download this template for your practice you can go to the description section below and visit the website which is given Below in the description then you can download this template from there so this was for today's video thank you very much for watching and if you are new in my channel I would like to request you to subscribe my channel so that you will never miss similar kind of videos in future and if you have any comments